Does the cheapest quote stay cheapest after freight?
Three factories quote on four lines. Add freight and the tariff on each origin and the ordering changes completely: the factory that quotes best on half the range lands the most expensive on all of it, and still ends up with part of the order.
Operations Starter Optimization free
After you install, this is the model to open.
Which Suppliers Fill the Order Cheapest?
- 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 buyer's plan against the optimized split:
- Optimized order
- $34,230.80
- Straight off the quote sheet
- $35,737.80
- Saving
- $1,507 4.2% of the buy
- A second slot at supplier 1
- $1,049 about $1.75 a unit
The optimizer still sends 850 units to the dearest landed cost, which is worth understanding rather than ignoring: by then the other two factories are at their ceilings, so those units have nowhere cheaper to go. The report shows both capacity limits binding, which is the sheet telling you the constraint costing you money is not price at all, it is how much the two good factories can make. Raise the first supplier's capacity from 1,400 to 2,000 and the order falls to $33,182, which is a number you can take into the conversation before you go and ask for one. Lead time, minimum order quantities and single-factory risk are not in this model.
The model
A quote grid, a landed-cost block that adds freight and duty per origin, units needed per line and a capacity ceiling per factory. The decision is how many units of each line go to each supplier.
| Suppliers / lines | 3 / 4 |
| Landed cost | quote plus freight plus duty |
| Capacity | a ceiling per factory |
| Opening plan | best quote per line, spill over |
| Order cost | solved |
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
- When does the next unit of stock stop paying?How Much Safety Stock Do We Actually Need?
- How much stock does a longer lead time cost you?When Should We Reorder?
- What does a 95% promise cost, in units?What Stock Level Holds a 95% Service Level?
- Which category did the buying plan get wrong?Is the Product Mix What We Planned For?
- Is the overrun general or is it one bad line?Is the Event Build On Track?
- Is the rollout slowing, or does it just feel slow?Is the Rollout Where It Should Be by Now?
Every model like this one, and the method behind them: Optimization in Google Sheets.