VRP Spreadsheet Solver

Download “VRP Spreadsheet Solver – XLSM” VRP_Spreadsheet_Solver_v3.8.xlsm – Downloaded 34026 times – 420 KB

Video

Without a unified platform, every VRP should be tackled on its own, with the results getting lost in the ocean of information that is the Internet. Why, then, has nobody ever built one?

On one hand, the standard software for small to medium scale quantitative analysis for businesses has been established as, arguably, Microsoft Excel. On the other hand, most academics develop solution algorithms in C++ and the resulting codes are not for the faint of heart. Distance and driving time data have to be retrieved from a Geographical Information Systems (GIS) database, which requires investment. The results of the algorithms are usually represented as a single value, the total cost, and it can only mean so much. It is not straightforward to manually find a solution of a VRP, much less so to compute its cost or to visualize it. Hence, constructing a unified platform for the data sources, solution algorithms, and visual representation of the results is a problem on its own.

The capabilities of public GIS have significantly increased in the past few years.  The leading public GIS systems at this point are Bing Maps and Google Maps. Both offer a limited free service, and extended services for subscribers. Within this study, we will be using free services of Bing Maps. Please consult their Terms of Service (http://www.microsoft.com/maps/product/terms.html) before using the VRP Spreadsheet Solver.

What’s new in Version 3.7:

• Users now can animate the solution on the visualization worksheet. The animation can be sped up by pressing (holding down) the <ESC> key.
• Users can now select the mode of transportation for Bing Maps routes on the console worksheet, as Driving, Truck, or Walking.

What’s new in Version 3.6:

• The users now can choose the level of detail for the Bing Maps routes being displayed. A setting of 0 will return a single direct line between two points being visited. Higher settings will provide higher amounts of detail.
• The users now can display one route at a time, as well as all routes together. This is controlled by a pull-down menu in the visualization worksheet.

What’s new in Version 3.5:

• Multi-language support. Users can now set the language of the menu and the rest of the interface from the console worksheet. The available languages are: Turkish, German, Spanish, Italian, French, Portuguese, Arabic, Indonesian, Urdu, Bengali, Swahili, Persian, and Malay. As you may expect, I do not speak all these languages. The translations are from Google Translate, so expect minor (i.e. significant) inconsistencies. You can also add your own language or jargon, details are in the new User’s Manual. Special thanks to Rusuhi Cantay for his help with the multi-language support.

What’s new in Version 3.4:

• The solver can handle vehicle-location incompatibility.

What’s new in Version 3.3:

• The solver can handle multi-trip VRP variants, in which the vehicles can visit their depot to deliver their load and leave again for service.
• Driving durations of Bing Maps distances can now be computed using average vehicle speed.

What’s new in Version 3.2:

• There is a “duration multiplier” for each vehicle type, which will allow each different vehicle type to have a different driving duration based on its speed.

What’s new in Version 3.1:

• Populating the coordinates creates a small map in the Locations worksheet. This enables the user to spot coordinate errors before populating the distances.
• If some distances could not be populated due to a connection error, the next attempt only populates the missing distances.

What’s new in Version 3.0:

• Works on both Windows and Mac.
• Changing problem data is reflected on the solution worksheet with a warning.
• Dynamically determined line and marker sizes in the visualization worksheet – smaller markers and thinner lines for larger instances.
• Minor bug fixes.