spacer1
spacer2 1_1 1_2
2_1
 Subscribe
 The MP2K Update!
 
 
 
 Magazine
Front Cover
What's New
Articles
News
Sample Data
Gallery
Advertise
About
 Features
MapPoint 2013
Press Releases
MapPoint Forums
Companies
Link to MP2Kmag
Wish List
MapPoint Trial
Authors
 Earlier Content
Past News Items
Past What's New Announcements
 Sponsors
 Order

MapPoint 2013

Programming MapPoint in .NET

MapPoint Book

  Spatial Community
SVG Tutorials
MapPoint

Map Visitors

  ARTICLES  


Using MapPoint and Excel for Supply Chain Management

John Sewell submitted this 3000 line application developed using MapPoint and Excel which performs a number of functions related to Supply Chain Management

Attached is an Excel Add-in called RouteneXL. It operates by directly linking with Mappoint and contains lots of code using both Excel & MapPoint functionality.

As part of our business - supply chain consultancy - we have done a number of distribution network strategies; with typical components being how many distribution centers to have, where to locate them and every question associated with them. To support this - and reduce the cost of off-the-shelf network modelling software - my colleague, Jon Nicholas, and I have put together a tool kit to support network planning.

I have attached the add-in. We would really be interested in any comments and improvements. The key things the tool does are:

  1. Creating distance & journey time matrices for a series of locations
  2. Creating maps & reports allocating demand points (for instance, stores) to existing hubs (for instance, Distribution centers)
  3. Generating new potential hubs to service demand points
  4. Testing how much difference adding new hubs makes to transport costs
  5. Route planning of deliveries from a hub to demand points
  6. Calculation of Centers of Gravity for the demand points. Either the straight-line location, with the minimum distance (weighted by volume) from all the demand points; or 'real' Center of Gravity, an iterating approach to refine the search based in actual travel distance
  7. Calculation of optimal delivery frequency; given the delivery distance and volumes, how frequently to deliver to each delivery point
  8. A fun utility to calculate the mid point of journeys by journey time; a good one if you have to meet and you want an equitable travelling time!
  9. Various utilities to convert latitude & longitude to addresses and visa versa.

We started in VB.NET but, triggered by David Hager's article about using MapPoint directly from Excel, changed to creating an Excel VBA add-in (mediating with both MapPoint & Excel from a third platform was both beyond us and, and for what we did get running, very slow). In the process we have plundered ideas from the site. As well as David Hager we want to acknowledge Chandu Thota for the techniques for finding an address from Latitude & Longitude and Gilles Kohl for the opposite in CalcPos. We have also relied on John Walkenbach for his Excel expertise.

The tool is currently configured to work with MapPoint Europe but if you change the reference (on the tools menu of Excel VBA) and the file reference on line 24 of the MainMod module we think it should work for Mappoint North America. After that to get running, put the .xla in the Excel Add-in directory. Go into Excel and in tools/add-in menu browse for RouteneXL and load it. It will create an extra menu item in Excel. The code is in Excel VBA (look under Tools/macro) and is without password.

There are about 3000 lines of code, so you might have a bit of wading through to get into it. But we hope you find it interesting and would be really pleased to have any comments and, particularly, improvements or ideas.

Download RouteneXL.zip

Discuss this story in the forum.

Author: John Sewell
Email: John.Sewell(AT)tamarack-uk.com
URL: http://www.tamarack-uk.com
John is a supply chain specialist working for Tamarack Partners, a supply chain consultancy boutique based in the UK. This tool has been developed for supporting logistics network modelling and strategizing typically for retailers or consumer goods manufacturers.

For more more information see the Our Work and Who We Are sections of our web site.



Google
 
MP2Kmag Internet


 Recent Discussion
-GCPowerStation v17.2
-GCPowerStation v17.2
-Gaussian 09W v8.0 Rev B.01 SMP
-Gaussian 09 E.01 Linux64
-Atmel Studio v.7.0.1931
-Steinberg Cubase Elements v9.5.40 WiN
-Nero Platinum 2019 Suite 20.0.05900 Multilingual
-Serif DrawPlus X8 14.0.0.19
-PROMT Master 19 Final + PROMT 19 Dictionary Collection
-Apple Logic Pro X v.10.4.2 Multilanguage (MacOSX)
-PROMT Expert 19 Final + PROMT 19 Dictionary Collection
-Waves Complete v2018.10.16 WiN by R2R
-VMware Fusion Pro 10.1.1 MacOSX
-Foxit Phantompdf Business v.9.3.0.10826 Multilanguage
-Bluestacks v4.31.56.2510 Multilingual
-Xara Web Designer Premium 16.0.0.55162 (x64) Portable
-Adobe Bridge CC 6.1.1.10 Update 3 by m0nkrus
-Garmin MapSource v5.4
-Alien Skin Exposure X4 Bundle 4.0.2.43 | macOS
-Unity Asset Cubebrush - Scrap City Assets 1.0 x64
-Magix Samplitude Pro X3 Suite v.14.4.0.518 Multilanguage
-Waves Complete v2018.10.16
-Oxygen XML Editor 18.0 (Win/Mac/Lnx)
-Steinberg Cubase Elements v9.5.40 WiN
-The Foundry Nuke Studio 11.2v4 Win x64
 Resources
Browse GIS books and periodicals
Find a MapPoint Partner or Consultant
Real Estate Columbia, MO Real Estate


Want Your Site To Appear Here?

   © 1999-2012 MP2K. Questions and comments to: website@mp2kmag.com
  Microsoft and MapPoint 2002/2004/2006/2009/2010/2011/2013 are either trademarks or registered trademarks of Microsoft.