When does the next unit of stock stop paying?
Two different questions hide inside the phrase service level: how often you get through a lead time without running out, and how much you run out by when you do. This table answers both at nine stock levels at once.
Words on this sheet
- Standard deviation: How far a typical reading sits from the average, in the same units as the readings.
Operations Intermediate Data Table free
After you install, this is the model to open.
How Much Safety Stock Do We Actually Need?
- 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
What each step up the ladder actually buys:
- No safety stock
- 65.6 units short, 50% service
- At 150 units
- 16.2 units short, 81.9%
- At 250 units
- 4.6 units short, 93.6%
- At 400 units
- 0.4 units short, 99.2%
The first fifty units removed 22 units of shortfall for $198 a year. The last fifty removed 0.6 units for the same $198. Somewhere in between, and on most lines it is around the 250 mark, the next unit stops buying anything you can measure. Where exactly depends on what a stockout costs you, which is the one number this sheet deliberately does not contain: multiply the units short by your cycles a year and by your own cost of a missed unit, compare it with the holding column beside it, and the crossing point is your answer rather than a textbook's 95%. Change the lead time from three weeks to five and the whole table gets worse at every level, which is the argument for paying a supplier for speed.
The model
Weekly demand and its spread, a lead time and a holding cost, with safety stock swept from zero to 400 units. The units-short column comes from the standard normal loss function, which most safety-stock sheets are missing because there is no built-in for it.
| Safety stock swept | 0 to 400 units, nine levels |
| Opening level | 150 units |
| Cycles a year | about 18 |
| Holding cost | $594 a year at 150 units |
| Service level, units short and cost | calculated per row |
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 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?
- Will your shrink budget survive one bad incident?What Is Shrinkage Really Costing?
Every model like this one, and the method behind them: What-if analysis in Google Sheets.