How far above average demand should you order?
Simulation tells you what one order quantity is worth. This asks the harder question: which quantity is best, by re-simulating the whole season for every candidate and keeping the winner.
Operations Intermediate Optimization under Uncertainty Pro engine
After you install, this is the model to open.
How Many Should I Order?
- 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.
This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.
The answer
Searching order quantities against simulated seasons:
- Optimal order
- ~1,350 units
- Mean profit
- $28,300 per season
- Ordering the average instead
- $27,700 per season
Overshooting the average by 150 units is worth about $600 a season, because a leftover unit loses $12 while a stockout loses the $27 margin you never earned. This model has a published closed form, the newsvendor critical fractile, and it says order at the 69.2nd percentile of demand: 1,351 units. The optimizer finds it without being told the formula, which is the point, because changing the salvage value or skewing demand breaks the formula and not the search.
The model
One unit costs $18, sells for $45, and clears at $6 if unsold: every figure is per unit, whatever a unit is for you. Demand averages 1,200 units with a standard deviation of 300, and the optimizer is free to order anywhere between 800 and 1,800. One thing the sheet assumes and cannot tell you on its own is that a unit costs the same $18 however many you order. If your supplier drops the price above a break point, put that break into the cost cell as an IF formula that reads the units-ordered cell. The optimizer re-runs the whole season for every candidate rather than solving a formula, so a cost that steps is no harder for it than a flat one, and on these figures the answer moves to 1,500 units and about $31,900 a season.
| Cost / price / salvage, per unit | $18 / $45 / $6 |
| Demand | Normal(1200, 300) |
| Order quantity | optimized, 800-1800 |
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
- What does staffing to an average day cost you?How Many People Should Be on Shift?
- How many extra seats before someone gets bumped?What Is the Best Number to Oversell?
- Everyone fits. Did anyone actually get what they wanted?Seat Everyone Without Breaking the Rules
- What is the cheapest menu that still keeps every promise?Feed Everyone for the Least Money
- Which venue is actually cheapest per head?Rank the Venues on What They Cost You
- More invitations, or more notice?What Predicts Turnout?
Every model like this one, and the method behind them: Optimization under uncertainty.