Which plant should ship to which store?
The sensible way to build a shipping plan is to take each store in turn and fill it from the cheapest plant with supply left. It produces a plan that meets every constraint and looks finished, and it is wrong. This template ships that plan as the starting point so you can watch the optimizer beat it.
Operations Intermediate Optimization free
After you install, this is the model to open.
Which Plant Ships to Which Store?
- 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
The optimizer keeps every constraint and moves 300 units between lanes:
- Hand-built plan
- $7,000 cheapest-plant-first
- Optimized plan
- $6,650
- Saving
- $350 about 5%
- Extra units shipped
- 0 same demand, same supply
The greedy plan costs $7,000 and the optimizer returns $6,650, a saving of $350 or about 5%, without shipping a single extra unit. The interesting part is why. Cheapest-first gives Store A to Plant 2 at $4, which is locally correct and globally expensive, because Plant 2 is the only plant that reaches Store D cheaply and it has now spent its capacity. Store D then has to be covered from Plant 3 at $12. The optimizer instead splits Plant 2 between Store A and Store D, hands the rest of Store A to Plant 3 at $9, and gives Store B entirely to Plant 1. Plant 2 gives up part of its best lane because its capacity is worth more on the lane no one else can serve. That is the difference between a rule about lanes and a plan for the whole network, and it is the reason the transportation problem is worth setting as an exercise at all.
The model
A cost grid of twelve lanes, a shipping grid the optimizer chooses, and totals down each side. Every plant has a supply ceiling, every store has demand that must be met exactly, and nothing can ship a negative quantity. The objective is the SUMPRODUCT of the two grids.
| Plants | 3, with supply of 350, 400 and 450 |
| Stores | 4, with demand of 300, 350, 275 and 275 |
| Lane costs | $4 to $14 a unit across twelve routes |
| Starting plan | cheapest available plant per store, costing $7,000 |
| Constraints | supply ceilings, demand met exactly, no negative shipments |
| Objective | minimize total shipping cost |
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 many servers does the queue actually need?How Long Will the Line Get?
- Can you actually keep your wait-time promise?What Shape Are Your Wait Times?
- Does the night shift really produce less?Shift or Line: What Moves Output?
- Is one line underfilling, or is that just spread?Are Two Fill Lines Filling the Same?
- Two machines, same average. Which one wanders?Is the New Machine More Consistent?
- What is next month's demand, give or take?Smooth the Noise, See the Trend
Every model like this one, and the method behind them: Optimization in Google Sheets.