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

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

	MP2Kmag Internet