What does an unreliable supplier cost you in stock?
Buffer stock down the side, supplier reliability across the top, and the chance the line stops in every cell. Tightening the supplier by two days of variability is worth about 7,000 units of stock.
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.
Safety Stock for a Plant That Cannot Stop
- 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
- Chance the line stops
- 1.8% per replenishment cycle at today's 6,000-unit buffer
- Where the risk lives
- 95% of the variance is supplier lead time, not usage
- Supplier one day worse
- 7.9% stall risk at the same buffer if the swing goes 2 to 3 days
- What the buffer costs
- $8,160 a year to hold, at $6.80 a unit and 20% carrying cost
A single-shift line consuming 1,400 units a day of one bought-in component, from a supplier who quotes twelve days and delivers with about two days of standard deviation. Two things can stop this line: the line using more than usual, and the supplier arriving later than usual. The two variation lines keep them apart, and that separation is the whole point of the sheet.
Variation from usage is the lead time multiplied by the daily variance, and it comes to 388,800. Variation from the supplier is daily usage squared multiplied by the lead time variance, and because daily usage is 1,400 that term comes to 7,840,000. More than 95% of the uncertainty this plant is buffering against is the supplier, not the shop floor.
Click Run and the table sweeps buffer stock from 2,000 to 14,000 units against a supplier standard deviation from half a day to three days. Read across the 4,000-unit line: at half a day the chance of stopping is essentially zero, at one day it is 0.45%, at two days it is 8.2% and at three days it is 17.3%. Now read down the three-day case: to get back to the 0.45% risk that 4,000 units buys you at one day of variability, you need about 11,100 units.
Tightening the supplier from three days of variability to one is worth roughly 7,100 units of stock, which at $6.80 and a 20% holding rate is about $9,650 a year of holding cost, plus about $48,300 of working capital released. That is the number to take to the supplier, and it is a far better conversation than asking for a lower unit price.
The other way to read the table is against the downtime rate: at 1,400 units a day the line makes about 175 units an hour, so every hour it stands still costs $4,200, and a 17% chance of stopping on each replenishment is not a stock policy, it is a rota of bad days. Run it again with the average lead time raised from twelve days to twenty and watch the whole grid shift, which is what switching to a further-away supplier actually costs.
The model assumes usage and lead time are independent and roughly normal, and that a stockout stops the line rather than being partly absorbed by work in progress. Put your own goods-received history behind the lead time and its standard deviation, which is the most valuable input on the sheet and the one nobody measures.
The model
It arrives on a tab called Template: Buffer Stock and Supplier Reliability:
| Daily usage on the line (units) | 1400 |
| Daily usage, standard deviation (units) | 180 |
| Average supplier lead time (days) | 12 |
| Lead time, standard deviation (days) | 2 |
| Usage over the lead time (units) | 16,800 |
| Variation from usage (squared units) | 388,800 |
| Variation from the supplier (squared units) | 7,840,000 |
| Standard deviation over the lead time | 2,868.6 |
| Buffer stock (units) | 6000 |
| Safety factor (z) | 2.092 |
| Chance the line stops during a lead time | 0.01824 |
plus 6 more rows on the sheet.
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
- Is the line running on a cycle nobody scheduled?Does Our Output Repeat on a Cycle?
- Is a flat daily plan good enough?The Season Repeats: Forecast It
- Is your contingency big enough if delivery slips?What If the Supplier Misses?
- How many tickets must you sell to break even?What Ticket Price Covers the Room?
- Is it the venue or the time of year?Venue and Month: What Drives Attendance?
- What does outsourcing cost once you price the risks?Outsource or Keep It In House?
Every model like this one, and the method behind them: What-if analysis in Google Sheets.