Safety Stock Formula with Lead-Time Variability: King's Formula & Excel Guide

Calculate a safety buffer for changing demand and supplier lead times, with clear variables, Z-score references and an Excel formula.

Why fixed min-max buffers miss changing risk

A fixed minimum and maximum can be useful operational rules, but a buffer that never changes cannot respond to changing uncertainty. Higher daily demand variability can exhaust the same buffer faster; variable supplier lead times can extend the period it must cover. An unchanged buffer may therefore be too small during disruption or unnecessarily large when demand and deliveries become more stable.

The combined-variability safety stock formula sizes the buffer using both sources of uncertainty and your selected service level. It is a statistical planning estimate, not a guarantee against stockouts.

King's formula: demand and lead-time variability

Safety stock = Z × √(LT × σ_d² + d² × σ_LT²)
Safety stock formula variables and units
VariableDefinition
ZZ-score corresponding to the chosen cycle service level (a decimal such as 0.95 in Excel).
LTAverage supplier lead time, in days, from placing an order to delivery.
σ_dStandard deviation of daily demand, in units, on the same daily basis as d.
dAverage daily demand, in units per day.
σ_LTStandard deviation of supplier lead time, in days.

The LT × σ_d² term covers demand uncertainty during average lead time. The d² × σ_LT² term covers the effect of lead-time uncertainty at average demand. Add the variances before taking the square root; do not add the standard deviations directly.

This approximation assumes demand and lead time are independent and uses a normal-distribution service factor. Keep demand and time units consistent. Cycle service level means the modeled probability of avoiding a stockout during a replenishment cycle; it is not the percentage of units filled.

If lead time is fixed, σ_LT is zero and the expression reduces to:

Safety stock = Z × σ_d × √LT

Quick Z-score reference

Cycle service level and approximate Z-score
Service levelZ-score (rounded)
90%1.28
95%1.65
98%2.05
99%2.33

A higher service level produces a larger buffer for the same demand and lead-time history. These are rounded reference values; Excel's NORM.S.INV returns a more precise factor.

Excel safety stock formula

Define named cells for Service_Level, Avg_Lead_Time, Demand_SD, Avg_Daily_Demand and Lead_Time_SD, using the variable definitions above. Enter Service_Level as 95% or 0.95, not 95.

=NORM.S.INV(Service_Level) * SQRT(Avg_Lead_Time * Demand_SD^2 + Avg_Daily_Demand^2 * Lead_Time_SD^2)

Use AVERAGE and STDEV.S on consistent demand and delivered-order histories to prepare the inputs. Exclude open orders from delivered lead-time observations. If the required histories are missing, do not mistake a blank cell for evidence of zero variability.

At around 200 SKUs and beyond, manual spreadsheet maintenance can become difficult: copied formulas, mixed date units, stale supplier histories and inconsistent service levels all require checking. This is a practical maintenance warning, not an Excel capacity limit or a universal cutoff.

How the StockLogic tool applies the formula

The Safety Stock tool uses your 24-month demand history and delivered Order Book lines, with service levels by ABC class (defaults: A 98%, B 95%, C 90%). It calculates all items locally, up to 1,000 SKUs per upload.

The tool scales average monthly demand and its standard deviation to a daily basis by dividing each by 30. If you reproduce its results in Excel from monthly inputs, use that same scaling rather than mixing monthly and daily values.

Lead-time sourcing falls back from item history to supplier history and then a manual default. The combined formula is used only when lead-time variability is enabled and the history is reliable; otherwise the fixed-lead-time formula applies. The default minimum is three usable PO lines. Periodic review can extend the protection period, and results round up to whole units or pack size according to your settings.

Inspect the formula used, lead-time source and insufficient-data reasons for each item. For the wider calculation reference, see Methodology & Formulas.