What is the safety stock formula?
Safety stock is the extra stock held to cover usage above average and deliveries later than average. The standard formula combines both sources of variation. Two simpler versions are common, and both can mislead.
| Formula | What it covers | When to use it |
|---|---|---|
SS = Z × σd × √L | Usage variation only | Lead times that are genuinely reliable |
SS = Z × √(L × σd² + d² × σL²) | Usage and lead time variation | Most mining spares, because lead times move |
SS = (max usage × max lead time) − (average usage × average lead time) | Worst case on both at once | A quick check only. It usually overstates |
d is average usage per period, σd is the standard deviation of usage per period, L is average lead time in the same periods, σL is the standard deviation of lead time. Z comes from the service level you choose, which is the chance of not running out during one lead time:
| Service level | Z |
|---|---|
| 90% | 1.28 |
| 95% | 1.645 |
| 97.5% | 1.96 |
| 98% | 2.05 |
| 99% | 2.33 |
Safety stock then feeds your reorder point: reorder point = d × L + safety stock. See how to set min max levels for the next step.
Worked example: how much safety stock does a steady spare need?
Example with made-up numbers: a conveyor wear part with steady usage and a supplier whose deliveries vary. Monthly net usage over 12 months was 38, 45, 41, 52, 36, 47, 44, 40, 55, 39, 43 and 48.
- Average usage d = 528 ÷ 12 = 44 a month.
- Standard deviation of usage σd = 5.74 a month (sample standard deviation). σd² = 32.9.
- From PO history, average lead time L = 2.5 months, standard deviation σL = 0.75 months.
- Target 95% service, so Z = 1.645.
- Usage term: L × σd² = 2.5 × 32.9 = 82.3.
- Lead time term: d² × σL² = 44² × 0.75² = 1,936 × 0.5625 = 1,089.0.
- √(82.3 + 1,089.0) = √1,171.3 = 34.2.
- Safety stock = 1.645 × 34.2 = 56.3, round up to 57 units.
- Reorder point = 44 × 2.5 + 57 = 110 + 57 = 167 units.
Compare the three formulas on the same part:
| Method | Safety stock |
|---|---|
| Usage variation only: 1.645 × 5.74 × √2.5 | 15 |
| Usage and lead time variation | 57 |
| Max and average: (55 × 4) − (44 × 2.5), with a worst lead time of 4 months | 110 |
The lead time term (1,089) is about 13 times the usage term (82). On this part, supplier reliability drives the safety stock, not usage. That is common on remote mine sites. Fixing the supplier's delivery spread would cut more stock than any amount of forecasting.
How does service level change safety stock?
Each step up in service level costs more stock than the last. Using the same part:
| Service level | Z | Safety stock (units) |
|---|---|---|
| 90% | 1.28 | 44 |
| 95% | 1.645 | 57 |
| 98% | 2.05 | 71 |
| 99% | 2.33 | 80 |
Going from 98% to 99% adds 9 units for one point of service. Set service levels by class: higher for parts that stop production, lower for parts with a workaround or a local supplier. Putting 99% on everything is the fastest way to tie up working capital in parts that never needed it.
How do you calculate safety stock for lumpy, slow moving spares?
The standard formula assumes usage follows a smooth bell curve. Lumpy spares, used rarely and several at a time, do not. Model the number of issues and the size of each issue separately.
Example: a pump part with a 6 month lead time (treated as fixed here). Over 24 months it was issued 6 times, in lots of 2, 3, 1, 3, 1 and 4, for 14 units. The last 12 months show 8 units, an average of 0.67 a month with a standard deviation of 1.37.
| Method | Reorder point at about 95% | Problem |
|---|---|---|
| Normal formula: 4 + 1.645 × 1.37 × √6 | 10 | Assumes a smooth curve. Close here by luck |
| Poisson, mean 4 over the lead time | 8 | Assumes units are used one at a time. They come 1 to 4 at once |
| Highest 6 month total in the last 24 months | 5 | Only 6 issue events of history. Covers about 77% of outcomes under the model below |
| Issue events times issue sizes (compound Poisson, simulated) | 10 | Best fit, but needs code or an add-in |
Four reasonable methods give answers from 5 to 10. On lumpy items the method matters more than the decimal places. Check how many issue events you actually have. Under about 4 to 6 a year, show the number to the maintenance planner and ask what work is coming before you trust any formula.
Why don't critical and insurance spares use the safety stock formula?
Critical and insurance spares are held against a failure, not against usage. Most have no usage history to put in a formula. Decide them on risk.
Example: a $96,000 component for a critical asset with a 6 month replacement lead time. At a holding cost of 20% a year (use your own finance rate), keeping one costs $19,200 a year. The question is whether the asset stopping for up to 6 months would cost more than that, weighted by how likely a failure is. For most critical assets on a mine site, it would. So the answer is usually hold 1, review once a year, and keep it out of any automatic safety stock calculation. More on this in critical and rotable spares.
What mistakes break safety stock calculations on mine sites?
Most errors are in the inputs, not the formula.
- Mixed time units. σd in months and L in weeks gives a wrong answer that still looks plausible. Put everything in the same unit.
- Quoted lead time instead of actual. Use PO date to receipt date from your own history. For remote sites that includes freight and receipting, which the supplier's quote does not.
- Gross usage. Returns counted as usage inflate both d and σd. On an international tier 1 mining operator's stock level work, netting returns off usage took agreement on disputed items from 73% to 98.4%.
- Transfers counted as usage. A transfer to another warehouse is not demand. Count usage where the part is issued to a job.
- Shutdown spikes. Planned shutdown demand inflates σd. Take planned work out and order it against the work order instead.
- Stockouts hide demand. A month with zero issues because the shelf was empty is not a month of zero demand.
- Units of measure. Usage in each and stock in boxes of 10 is a common silent error.
How do you calculate safety stock in Excel from an ERP export?
Lay out 12 months of net usage per stock code across columns, calculate lead time statistics from PO history, then apply the formula in one cell.
- Export issues and returns for 12 months, net them by month and stock code, and pivot so each row is a code and columns B to M are months.
- Export POs with stock code, PO date and receipt date. In that sheet (
PO), add a lead time column E:=(D2-C2)/30.4to get months. - Average usage in N2:
=AVERAGE(B2:M2). Standard deviation in O2:=STDEV.S(B2:M2). - Average lead time in P2:
=AVERAGEIFS(PO!E:E, PO!A:A, A2). - Lead time standard deviation in Q2:
=IFERROR(STDEV.S(FILTER(PO!E:E, PO!A:A=A2)), 0). - Z in R2:
=NORM.S.INV(0.95), or look it up from a service level by class. - Safety stock in S2:
=ROUNDUP(R2*SQRT(P2*O2^2+N2^2*Q2^2), 0).
Flag any row with fewer than 4 non-zero months or fewer than 3 POs. Those need judgement, not the formula.
How does Source Digital automate safety stock and stock levels?
Source Digital builds a stock level engine inside your own data platform that calculates safety stock, reorder points and max levels for every line, on rules agreed with your stores team. A person approves every change.
- Matches your experts: at an international tier 1 mining operator, the engine matched the site inventory expert's own spreadsheet 99.8% of the time. See the min max inventory engine case study.
- Deterministic maths: the numbers come from fixed formulas. The AI only explains why a level changed.
- Every number links back to the usage, return and PO lines it came from.
- Read-only: it runs in your Azure, Databricks or Snowflake and does not change your ERP.
Getting started is a free 15 minute call with Tristan, or a quick enquiry. No obligation and no pitch. He asks what you run and what is costing you, then tells you straight whether there is something worth fixing. Work is charged at a consulting day rate. The price depends on your sites, systems and data, so you talk first, then Tristan sets out what he would build, how many days it takes and what it costs. See how the stock level engine works on the mining spares optimisation page.
