How many units does one delivery wait really need on the shelf?
Five hundred units on the shelf, 35 a day going out, a delivery in 12 days. The sheet says 80 are left over. Across 20,000 futures the shelf holds in 82 in 100 runs, and 90% odds of no stockout takes 545 units.
Operations Starter Monte Carlo Pro engine
After you install, this is the model to open.
How many units to stock for 90% odds of no stockout?
- 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.
What it does
Five hundred units on the shelf, 35 selling a day, and the next delivery lands in twelve days. As typed, 420 units go out before it lands and 80 are left over, and nobody looking at that sheet would order more. Click Run with daily sales as a three-point estimate of 26, 35 and 52 and the lead time drawn from a distribution over 10, 12, 14 and 18 days.
The seed is set to 7 and the trials to 20,000 so your first run reproduces these figures exactly. Units demanded before the delivery lands come back with a mean of 424.9 and a median of 409.5, close to the sheet's 420, but the ninetieth percentile is 544.1 and the ninety-fifth is 599.95, and the 500 on the shelf cover the wait in 82 in 100 runs.
Units short have a median of 0, which is what the sheet promised, and a ninetieth percentile of 44.1, which is not. The stock for 90% odds of no stockout is 545 units, the ninetieth percentile rounded up: 545 on the shelf covers the wait in 90 in 100 runs, and 600 covers it in 95 in 100. The tornado puts the lead time first at 0.73, carrying 56% of the spread, and daily sales at 0.65, so the supplier's reliability matters slightly more than the customers' appetite.
Second run: if the supplier will commit to twelve days, change the lead time draw in the Risk Analysis panel to a single value of 12 and rerun. The ninetieth percentile of demand falls and the stock for 90% odds falls with it, which prices what a firm delivery date is worth in units. What the model cannot tell you: one daily rate is drawn and held flat for the whole wait, so a promotion or a spike inside the window is not in the range; the lead time and the demand are drawn independently, when a late delivery and a busy fortnight often have the same cause; and a stockout is counted in units, not in the customers who did not come back.
To make it yours, put in your own shelf count, your daily sales range from the last few months, and the lead times your supplier has actually delivered, and set the same draws in the Risk Analysis panel.
The model
It arrives on a tab called Template: Stock With 90% Odds:
| Units on the shelf today (units) | 500 |
| Units sold per day (units per day) | 35 |
| Days until the next delivery lands (days) | 12 |
| Units demanded before the delivery lands (units) | 420 |
| Units short if demand runs past the shelf (units) | 0 |
| Units left when the delivery lands (units, negative = stocked out) | 80 |
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 hires does a support week really need, in odds rather than averages?How many hires keep 85% odds of hitting the support target?
- Is a second shift enough for the peak week, or does it take a second line?What capacity gives 95% odds of covering peak demand?
- How much should you order when demand is uncertain?Inventory Order Quantity (Newsvendor)
- Should you hold your price, or cut first?Price War: Hold or Cut?
- Which tasks actually set your launch date?Launch Plan: Find the Critical Path
- Which risks actually deserve the money?Project Risk Register, Priced
Every model like this one, and the method behind them: Monte Carlo simulation.