Where should your warehouse actually be?
Your customer map already knows the answer. This template takes 20 US metros with monthly shipment volumes and lets the optimizer drag a warehouse to the spot that minimizes shipment-weighted miles. Then it frees up a second site so you can see exactly what another warehouse is worth.
Operations Advanced Optimization free
After you install, this is the model to open.
Warehouse Location Optimizer
- 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
One click on Run and the optimizer slides both sites around the map until the shipment-weighted average stops falling.
- Today, one DC near Philadelphia
- 1,132 avg miles per shipment
- Best single site, near Memphis
- 867 avg miles per shipment
- Best pair of sites
- 519 avg miles per shipment
- Total distance cut
- 54% two optimized sites vs today
Even the best single warehouse, near Memphis, still averages 867 miles per shipment because one building cannot be close to both coasts. Free up a second site and the optimizer splits the network, landing one warehouse in eastern Tennessee and one in Southern California, which drops the average to about 519 miles, a 54 percent cut from the current East Coast setup. That distance falls straight to the bottom line as cheaper ground zones and faster delivery promises.
The model
Twenty customer metros, each with a monthly shipment count, plus two movable warehouse locations. Every city is served by whichever warehouse is closer.
| Customer metros | 20, Boston to Seattle |
| Monthly shipments | 163 total (5 to 18 per metro) |
| Starting position | Both sites at the current DC near Philadelphia |
| Cells Sortia may change | Latitude and longitude of warehouses 1 and 2 |
| Distance model | Straight-line miles: 69 per degree latitude, 53 per degree longitude |
| Objective | Minimize average miles per shipment |
| Search bounds | Continental US (lat 25 to 49, lon -124 to -67) |
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
- Which rows in these two lists are the same company?Match Two Customer Lists That Don't Agree
- What do the next four quarters of demand actually look like?Seasonal Demand Forecast
- What does website downtime really cost us per year?What Does an Hour of Website Downtime Cost?
- Is the feasibility study worth it before you bid?Should We Pay for a Feasibility Study Before Bidding?
- Should we pay the overtime or take the late penalty?Overtime vs Late Penalty Scheduler
- What order should you drive your stops in?Shortest Route Finder
Every model like this one, and the method behind them: Optimization in Google Sheets.