By Joannès Vermorel, December 2007 (last revised January 2012)
The approach detailed below is a several decades old classic, however recent developments of Lokad around quantile forecasts
are now making the safety stock
model somewhat obsolete. Indeed, the reorder point
is nothing but a quantile forecast. Hence a direct
calculation of the quantile vastly outperform, on average, an indirect
calculation, as provided by the safety stock model.
This guide explains how to optimize inventory levels by adjusting safety stocks to their optimal level
. This guide applies to retail and manufacturing. The theory is illustrated with Microsoft Excel
. Advanced notes are available for software developer who would like to reproduce the theory into a custom application.Intended audience:
This document is primarily intended for supply chain professionals in retail or manufacturing. Yet, this document is also useful for accounting / ERP / eCommerce software editors that would like to extend their applications with stock management features.
We have tried to keep the mathematical requirements as low as possible, yet we can’t really avoid all formula altogether since the precise purpose of this document is to be a practical guide that explains how to compute safety stock.Download: calculate-safety-stocks.xls
(Microsoft Excel Spreadsheet)
Inventory management is a financial trade-off between inventory costs and stock-out costs
. The more stock, the more working capital is needed and the more stock depreciation you get. On the other hand if you do not have enough stock, you get inventory stock-outs, missing potential sales, possibility interrupting the whole production process.
Inventory stock depends essentially of two factors
- demand: the amount of items that will be consumed or bought.
- lead time: the delay between reorder decision and renewed availability.
Yet those two factors are subject to uncertainties
- demand variations: customer behaviors can evolve in rather unpredictable ways.
- lead time variations: suppliers or transporters may be faced with unplanned difficulties.
Deciding the level of safety stock is implicitly equivalent to making a trade-off between those costs considering the uncertainties.
The balance inventory costs vs. stock-outs costs is very business dependent. Thus, instead of considering those costs directly, we will now introduce the classical notion of service level
The service level expresses the probability that a certain level of safety stock will not lead to stock-out
. Naturally, when safety stocks are increased, the service level increases as well. When safety stocks get very large, the service level tends toward 100% (i.e. zero probability of encountering stock-out).
Choosing the service level, i.e. the acceptable probability of stock-out, is beyond the scope of this guide, but we have a separate guide about calculating optimal service levels
Inventory replenishment model
The reorder point is the amount of stock that should trigger an order
Get optimized safety stocks with our inventory forecasting
technology. Lokad specializes in inventory optimization through demand forecasting. The content of this tutorial - and much more - are native features of our forecasting engine tool.
. If there was no uncertainty (i.e. future demand being perfectly known and supply being perfectly reliable), the reorder point would simply be equal to the total forecasted demand during the lead time, also called lead time demand
Lokad provides many tools to compute the lead time demand directly from the historical data. You can have a look at our Forecasting Methods and Formulas with Microsoft Excel
In practice, because of the uncertainties, we have
reorder point = lead time demand + safety stock
If we assume that the forecasts are not biased
(statistically speaking), having zero safety stocks would lead to a service level of 50%. Indeed, unbiased forecasts mean that there is as much chance for the future demand to be greater or lower than the lead time demand (remember that the lead time demand is just a forecasted value).Caution:
forecasts can be unbiased without being exact
. The bias indicates a systematic error by the forecast model (ex: always over estimate the demand by 20%).
Normal distribution of the error
At this point, we need a way to represent the uncertainty in the lead time demand. In the following, we will assume that error is normally distributed
, see the picture below.Statistical notes
: this normal distribution assumption is not totally arbitrary. Under certain situations, statistical estimators converge to a normal distribution as outlined by the Central limit theorem
. But those considerations are beyond the scope of this guide.
A normal distribution is only defined by two parameters: its mean
and its variance
. Since we assume the forecasts to be unbiased, we assume the mean of the error distribution to be zero
, which does not mean that we are assuming a zero error.
Determining the variance of the forecast error is a more delicate task. Lokad, as most forecasting toolkits, provides MAPE estimations
(Mean Absolute Percentage Error) associated to its forecasts. For the sake of completeness, we will explain how simple heuristics can be used to overcome this problem.
In particular, the variance within the historical data can be used as a good heuristic to estimate the forecast error variance
. David Piasecki also suggests to use the forecasted demand instead of the mean demand in the variance expression, that is
σ2 = E[ (yt - y')2 ]
is the mean
is the historical demand for the period
(typically the amount of sales) and
the forecasted demand.
The key idea behind this assumption is that the forecast error is very often correlated to the amount of expected variation: the greater the upcoming variations, the greater the error in the forecasts
Actually, the computation of this error variance involves a few subtleties that will be treated in greater details below.
Safety stock expression
At this point, we have determined both the mean and the variance, thus the error distribution is known. We must now calculate the acceptable
error level within this distribution. Here above, we have introduced the notion of service level (a percentage) to do that.Notes:
We are assuming a static
lead time. Yet, a very similar approach can be used for a varying lead time. See
In order to convert the service level into an error level
also called the service factor
, we must use the inverse cumulative normal distribution
(sometimes also called inverse normal distribution) (see NORMSINV
for the corresponding Excel function). As it might sounds complicated, it is not, we suggest to have a look at normal distribution applet
to get a more visual insight. As you can see, the cumulative function transforms the percentage into an area-under-the-curve
, the X axis threshold corresponding to the service factor value.
Intuitively, we calculate
safety stock = standard deviation of error * service factor
More formally, let
be the safety stock, we have
S = σ * icdf(P)
is the standard deviation (i.e. the square root of
the variance defined here above),
cumulative normal distribution (zero mean and variance equal to one) and
the service level.
reorder point = lead time demand + safety stock
be the reorder point, we have
R = y' + σ * icdf(P)
Matching lead time and forecast period
So far, we have been simply assuming that for a given lead time
, we were directly able to produce the corresponding future demand forecast. In practice, it does not exactly work that way. The analysis of the historical data usually starts by aggregating the data into time periods
(weeks or months typically).
Yet, the chosen period may not exactly match the lead time; thus, some further calculations are required to express the lead time demand and its associated variance (considering that we are still assuming a normal distribution for the forecast error, as detailed in the previous section).
Intuitively, the lead time demand can be computed as the sum of the forecasted values for the future periods that intersects the lead time segment
. Care must be taken to properly adjust the last forecasted period.
be the period and
the lead time. We write
L = k * T + α * T
0 ≤ α < 1
be the lead time demand. Then, we have the final expression for the lead time demand
D = (Σt=1..k y't) + αy'k+1
is the forecasted demand for the
period in the future.
Considering the same normal distribution assumptions, we can compute the forecast error variance as
σ2 = E[ (yt - y')2 ]
the average forecast per period
y'= D / (k + α)
is computed here as a per-period variance
whereas we would need a variance that match the lead time instead. Let
be the adjusted per-lead-time variance
, we have
σL2 = (k + α) σ2
Finally, we can re-express the reorder point as
R = D + σL * cdf(P)
Using Excel to compute the reorder point
This section details how to calculate the reorder point with Microsoft Excel
. We suggest to have a look at the sample Excel spreadsheet
The sample sheet is basically split into two sections: the assumptions
at the top and the calculations
at the bottom. The forecasts are assumed to be part of the assumptions because sales (or demand) forecasting is beyond the scope of this guide. You can refer to our tutorial for sales forecasting with Microsoft Excel
Most of the formulas introduced in the previous section are very plain operations (additions, multiplications) that are very easy to perform with Microsoft Excel. Yet, two functions are noticeable
NORMSINV (Microsoft KB): estimates the cumulative normal distribution, noted
cdf here above.
STDEV (Microsoft KB): estimates standard deviation, noted
σ here above. We recall that the standard deviation
σ is the square root of the variance
For the sake of simplicity, the first sheet does not implement the heuristic
σ2 = E[ (yt - y')2 ]
when calculating the service factor. This approach is implemented in
(2nd spreadsheet of the Excel document). Since we have assumed stationary forecasts in the example, the reorder point remains identical with or without this heuristic.
Notes for developers
This section is intended for developers who want to implement a replenishment feature for automated inventory management.
Most of the formulas expressed in this guide can be concisely implemented with regular development tools. If you happen to work with .NET, we suggest to use Math.NET Iridium
an open-source mathematical library written in C# that provides both the standard deviation operator and the cumulative normal distribution function.
Otherwise, the standard deviation can be easily re-implemented based on its definition, see the Wikipedia page
. The cumulative normal distribution is slightly more complicated, but Peter J. Acklam provides a good algorithm
that has been implemented in many languages. The code dedicated to those two methods should not require more than a few dozens of lines.
ResourcesInventory Management and Production Planning and Scheduling
, Edward A. Silver, David F. Pyke, Rein Peterson, Wiley; 3 edition, 1998