Is the round your driver does now the shortest?
Six drops, one van and a chilled order that has to go out first or second. The optimizer cuts 7.6 miles off the round the driver does now, and prices the delivery window at $7.79 a day.
Operations Intermediate Optimization free
After you install, this is the model to open.
Which Order of Stops Makes the Shortest Delivery Round?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Best round found
- 52.9 mi against 60.5 for nearest-next
- The morning, priced
- $103.24 van and driver, down from $118.08
- Saved every day
- $14.84 about $3,700 over 250 delivery days
- The chilled window costs
- 4.0 mi drop its rule and the round falls to 48.9
Six drops and a depot on a plain mile grid, with every leg the straight-line gap between consecutive stops multiplied by a road factor of 1.25 so the miles read like driving miles. The optimizer touches only the priority figures. Ranking those low to high gives each drop a visit order, and each position in the route block pulls in the coordinates of whichever drop holds it.
The depot sits at priority 0 and takes the first position, so the first delivery is the second position. The chilled order is what makes this more than a shortest-loop problem: it has to leave the van first or second, which is the constraint holding the chilled drop position at 3 or lower. The sheet opens on the round the driver does now, which is nearest-next, take whatever is closest and then whatever is closest from there.
That is 60.5 miles and $118.08 of van and driver time. Click Run. The Evolutionary method is a random search, so runs differ: the best round it can find is 52.9 miles and $103.24, cutting 7.6 miles and $14.83 out of a single morning, which over 250 delivery days is about $3,700. Most runs land on that round; if yours comes back higher, or does not move at all, solve again and keep the best.
Nearest-next fails the way it always fails: the first few choices are locally right and they strand a stop that is nowhere near anything, so the driver has a long empty leg at the end that nobody planned. Now do the second run, which is the one that makes this worth keeping. Delete the constraint on the chilled drop position and solve again, keeping the best of two or three runs: the round falls to 48.9 miles.
The chilled window is costing 4.0 miles, about $7.79 a day and $1,948 a year, and that is a number you can take to the customer and to your own scheduler rather than an opinion about whether the window is reasonable. This runs on the Evolutionary method rather than Simplex or the smooth search because ranking is a step function and neither of those can see through it.
The model is a straight loop with one van, no capacity limit, no traffic and no service time at each stop, so treat the miles as a planning figure rather than a promise. To make it yours, replace the six drops and their east and north distances with your own on any consistent grid, keep the depot on priority 0, set the road factor and the two costs to what your van actually does, and move the position constraint to whichever drop carries the window.
The model
It arrives on a tab called Template: Which Order of Stops Makes the Shortest Delivery Round?, carrying these columns:
- Priority
- East (miles)
- North (miles)
- Visit order
- Position
- Leg miles
with the model computed beside the data:
| Back to the depot | 0 |
| Round miles | 60.47 |
| Driving hours | 2.749 |
| Cost of the round ($) | 118.1 |
| Chilled drop position (count) | 3 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- How much does it cost to pull the event now?What If It Has to Be Cancelled?
- Will the new store pay for its fit-out?Is the New Store Worth Opening?
- How many stores does your pilot really need?How Big Does the Pilot Have to Be?
- Should you drop the slow line?Should We Drop This Line?
- Is a random draw a fair way to split the weekends?Assign the Shifts Without Arguing
- How much of the opening date is just waiting?What Sets the Opening Date?
Every model like this one, and the method behind them: Optimization in Google Sheets.