What does a 95% promise cost, in units?
A service level is a promise about a probability. This sheet turns it into stock: 271 units for 95%, 383 for 99%, and the four points between them cost more than the first fifty units ever bought.
Words on this sheet
- Standard deviation: How far a typical reading sits from the average, in the same units as the readings.
Operations Intermediate Goal Seek free
After you install, this is the model to open.
What Stock Level Holds a 95% Service Level?
- 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 same model, asked for two different promises:
- 95% service level
- 271 units of safety stock
- 99% service level
- 383 units of safety stock
- Stockouts a year
- 3.3 to 0.9 going to 95%
- Holding cost
- $1,072 to $1,516 a year, one line
The cost of a promise is not linear and it turns vertical near the top: the first fifty units of safety stock bought nearly twelve points of service level, and the last 112 units buy four. In money that is $444 a year on a single line, which you can multiply by however many lines the promise covers, and it is why a 99.9% service level is usually a sentence in a contract that nobody costed. The lead time assumption matters more than people expect: if your supplier is sometimes late, lead-time variability will dominate demand variability, and this model holds the lead time fixed.
The model
Weekly demand and spread, a three-week lead time, and the one step people get wrong: demand over the lead time varies by the weekly figure times the square root of three, not three times it. Goal Seek changes safety stock until the service level hits your target.
| Weekly demand | 420 units, spread 95 |
| Lead time | 3 weeks |
| Demand over the lead time | 1,260 units, spread 164.5 |
| Opening safety stock | 150 units, 81.9% |
| Stock that hits the target | 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
- 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?
- How often does this store miss its number?Will This Store Make Its Year?
- Did the change work, or do your lines just differ?Did the New Process Actually Help?
Every model like this one, and the method behind them: What-if analysis in Google Sheets.