ARTICLES

Routene Excel and MapPoint Update

John Sewell introduces and explains the features in the latest version of Routene, an Excel spreadsheet for of physical supply chain and logistics applications. A full version of Routene is now available for purchase.

Because MapPoint can be accessed programmatically either from VBA or any other programming language it is possible to use MapPoint to solve a wide range of physical supply chain questions.

As part of our business - supply chain consultancy - we have done a number of distribution network strategies; with typical components being how many distribution centres to have, where to locate them and the transport implications of running them.

We found that the off-the-shelf tools to solve this are both expensive and completely ‘black box’ - providing an answer but little explanation as to how the answer has been derived. So we started to use MapPoint driven by Excel and created Routene as an Excel add-in. Incidentally, we started in .NET but found response was too slow and the framework cumbersome. Based on David Hager’s article Excel User-Defined Functions and MapPoint, we started using VBA which was both faster and much more flexible. Excel is the ideal place to put results so you can do supplementary analysis with results; this is why we view Routene as a toolkit rather than a ‘solution’.

At the bottom of this article is a link to our web site where you can download the latest version of Routene (v2.5) which supersedes the version mentioned in the original article in 2006. If you have used the previous version you will find a lot of improvements:

• robustness has been greatly improved; both in not crashing and in producing good, useful answers.
• easy selection between North American or European versions of MapPoint (if you have both).
• scale has improved, the size of your journey matrix is only limited by the size of your Excel sheet.

Unfortunately we are only providing the full version now for a pretty nominal charge (there is a mostly working trial version free); however if you did contribute I would be happy to send you a free copy, I would particularly like to thank Raphael Yue (China) and Jeff Jaegar (USA) for their comments and challenges that triggered a number of these improvements.

So what does Routene do? Below is a list of some of its capabilities.

1. Generating a matrix of distances and journey times between many different locations
2. Generating straight-line journey matrices (very fast and useful where MapPoint has no road data)
3. Working out the best allocation of nodes (e.g. destinations) to hubs (e.g. distribution centres) based on minimising real journey times
4. Working out where to add more hubs into a network to minimise overall cost
5. Working out delivery routes from hubs to nodes
6. Working out the Centre of Gravity between different nodes
7. Working out the relative attractiveness of (specified) different potential locations for a hub
8. Working out the optimal delivery frequency to different nodes, based on managing transport costs
9. Working out the mid journey point between two locations
10. Creating maps of multiple locations' journey range
11. Varying analyses by geographies, miles v. km, and transport constraints.

The best thing to do is to get a copy and play with it, there is a reasonably full manual, as much email help as you want (we are still looking for improvements) and a setup file for installation. The toolkit mainly works on MapPoint’s mapping and route planning capabilities (ie not much use of territories) an example of output is this analysis of options for 1 to 4 distribution centres to service France

Or this diagram showing the one hour driving distance from some chosen sites in England

...both of these diagrams are generated by Routene, making use of MapPoint methods. If you would like to download the trial or buy the full copy - follow this link:
http://www.tamarack-uk.com/placeorder.html

Note that Routene works equally well with North American and European version of MapPoint.

I you have any comments or builds after using the tool we would really appreciate feedback.

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

	MP2Kmag Internet