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:
- Creating distance & journey time matrices for a series of locations
- Creating maps & reports allocating demand points (for instance, stores) to existing hubs (for instance, Distribution centers)
- Generating new potential hubs to service demand points
- Testing how much difference adding new hubs makes to transport costs
- Route planning of deliveries from a hub to demand points
- 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
- Calculation of optimal delivery frequency; given the delivery distance and volumes, how frequently to deliver to each delivery point
- 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!
- 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.