How much stock does a longer lead time cost you?
Cycle stock is what you will use while you wait. Safety stock covers demand running above average while you wait. Sweep the lead time from four days to eighteen and one of them grows four and a half times while the other only doubles.
Words on this sheet
- Standard deviation: How far a typical reading sits from the average, in the same units as the readings.
Operations Starter Data Table free
After you install, this is the model to open.
When Should We Reorder?
- 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 two halves of the reorder point, at each end of the sweep:
- At 4 days
- 1,108 148 of it safety stock
- At 18 days
- 4,634 314 of it safety stock
- Cycle stock
- ×4.5 with the lead time
- Safety stock
- ×2.1 square root of the lead time
That is the square root doing its job: waiting longer is riskier, but not proportionally riskier, because a long wait averages out its own good and bad days. Safety stock is 13% of the reorder point on a four-day lead time and 7% on an eighteen-day one, so the cost of a slow supplier is overwhelmingly the cash tied up in cycle stock, and anyone arguing safety-stock policy on a long-lead item is arguing about the small half. Raise the promised service level from 95% to 99% and the safety column rises by 41% while the cycle column does not move at all, which is the cleanest demonstration available of which lever does what.
The model
Daily demand and its spread, a service level that drives the safety factor through the normal distribution, and a lead time swept down the side of a one-variable table.
| Lead time swept | 4 to 18 days |
| Opening lead time | 10 days |
| Service level | 95% |
| Safety factor | from the service level, not typed |
| Cycle stock, safety stock and reorder point | 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
- 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?
- How often does this store miss its number?Will This Store Make Its Year?
Every model like this one, and the method behind them: What-if analysis in Google Sheets.