Should you buy more than you expect to sell?
A seasonal buy where leftovers clear below cost and a customer who finds an empty peg costs more than the sale. The optimizer searches every order size against thousands of simulated seasons and returns a number nobody in the buying meeting would have said out loud.
Operations Advanced Optimization under Uncertainty Pro engine
After you install, this is the model to open.
How Much Stock, When Demand Is a Guess
- 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
- Units to buy
- 1,348 against the 900 everyone says in the meeting
- Season profit at the optimum
- $20,800 mean over 20,000 simulated seasons
- Ordering the average costs
- $2,448 a season: 900 units earns $18,355
- Why overshoot
- $31 vs $3 what a stockout costs against a leftover unit
A seasonal style: $14 to buy, $39 to sell, $11 on the clearance rail, and a customer who finds an empty peg costs another $6 in the basket they do not build. Demand for the season is a triangular range from 300 to 1,700 units with 700 most likely, which averages 900 and has a median of 863, so a typical season is smaller than an average one.
Everybody in the buying meeting says nine hundred. Switch the mode to Optimize decisions and click Run. The tool tries whole numbers between 600 and 1,800, re-simulating the whole season for each candidate against the same set of random demands, and comes back with about 1,346 units earning roughly $20,800 a season. Buying the average demand of 900 earns about $18,400 and buying the median of 863 earns about $17,900, so ordering what you expect to sell costs about $2,400 a season and the optimizer wants you to buy half as much again.
The reason is in the profit line and it is worth doing on the back of an envelope: a unit left over loses $3, and a unit you did not have loses $25 of margin plus $6 of basket, so a stockout is more than ten times as expensive as a markdown. The published rule for this shape of problem is to order where the chance of selling one more unit equals the cost ratio, which here is 31 divided by 34, or the 91.2nd percentile of demand.
On this triangle that is 1,349 units, and the optimizer lands three units under it without being told the rule. Now look at what the extra 449 units actually buy, because this is the part a single number hides. At 900 units the season profit runs from about $10,400 at the P5 to about $22,300 at the P95, and you turn customers away in 46% of seasons.
At 1,349 units it runs from about $9,000 at the P5 to about $33,200 at the P95, and you turn customers away in 9% of seasons. The optimizer gave up about $1,400 of the bad case to gain about $10,900 of the good one. Whether that is the right trade depends on something no model knows, which is whether your cash can sit in stock until the clearance rail.
So do the second run. Change Statistic from Mean to P5, which asks what protects the bad seasons rather than the average one, and run it again: the answer moves to about 682 units, worth about $11,000 at the P5 against about $9,000 for the aggressive buy. Two honest answers, 664 units apart, out of the same sheet. That gap is the decision, and it is a cash and nerve question rather than a mathematical one.
The search is evolutionary, so the answer moves a unit or two between runs; read the shape of it rather than the last unit. To make it yours, put your own cost, retail and clearance prices at the top, take the cost of a turned-away customer from your own basket data or set it to zero if you would rather not guess, and set the demand range from the last two seasons of the same style.
The model
It arrives on a tab called Template: How Much Stock to Buy:
| Cost per unit you pay ($) | 14 |
| Full price ($) | 39 |
| Clearance price after the season ($) | 11 |
| Cost of a customer who finds it out of stock ($) | 6 |
| Units bought | 900 |
| Demand for the season (units) | 900 |
| Units sold at full price | 900 |
| Units left to clear | 0 |
| Customers turned away | 0 |
| Season profit ($) | 22,500 |
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
- One store takes more. Real, or a good fortnight?Are These Two Stores Really Different?
- How few units tell you the batch average?How Many Units Do We Have to Check?
- Which of your stores should share a target?Which Stores Behave the Same Way?
- Is unit cost driven by the price or by the volume?Will Unit Cost Land Where the Budget Says?
- The business case says yes. What are the odds?Is the New Machine Worth It?
- How far short could the season finish?Will the Season Hit Its Number?
Every model like this one, and the method behind them: Optimization under uncertainty.