There are a bunch of wonderful apps and location-based services for managing supply lines, keeping track of door-to-door sales associates, and optimizing these paths. But I came up with a quick model using Google Spreadsheets.
The basic idea is that you have a map with n locations that you have to visit. The goal is to find out how many places you could go in a day based on a bunch of parameters (distance between points, average speed, etc.). So how do you do it?
The first thing I did was determine what my map would look like. In the real world you would actually know the distances between each location and how many locations you have. In this case I don't, so I guessed using a mathematically-pleasing design. I decided on a orthographic projection of a rectified 5-cell pentagon, which is a complicated way of saying, in 2 dimensions, a small pentagon inside of a big pentagon.
To do the calculations you can determine distances from each points as long as you know the length of each side (T) and the length of diagonals (D) of the pentagons. You can adjust this in I2 and I3 on each of the person tabs. Other variables you can change include average speed while traveling, amount of time spent at each location, some lag time to account for variation, and number of hours worked per day.
Once you have added all of your information you can get a forecasted number of locations to be visited per day as well as other random information. I've also included a sensitivity analysis by varying time at location and lag time, all as percentage change from current forecast.
If you'd like to play around with if you can access the spreadsheet here and go to File>Make a copy for an editable version.
Enjoy!
No comments:
Post a Comment