How to calculate safety stock?
You can find many ways of calculation on Google. It can look sometimes complicated. In this article, I will explain in simple terms the main calculation methods in Excel, as well as those I recommend.
Step-by-Step video tutorial: how to calculate the safety stock in Excel 👇
Table of Contents
Safety Stock Definition
The safety stock (or buffer stock) is the stock level that limits stock shortages due to unforeseen events (forecasts not in line with demand, longer than expected supply time, etc…)
Why do you need safety stock?
You need a safety stock to cover yourself against two hazards or uncertainties: demand and lead time.
1) Demand uncertainty
Products have different levels of demand uncertainty.
For example, there are stable products such as toilet paper, and others much more uncertain, such as umbrellas – which are sold only during rainy periods. You will probably have a better forecast quality on toilet paper than on the umbrella.You might have a stronger safety stock on the umbrella to cover higher risks of shortages.
2) Lead Time uncertainty (or supply uncertainty)
There are also supply hazards: specifically, lead time uncertainty.
Indeed, there are many factors impacting the total lead time:
- Review period: how often you order
- IT confirmation delay / Purchase Order delays
- Production lead time
- Picking/Packing time in the warehouse or factory
- Waiting time before shipping
- Transportation time / transit time
- Customs
- Delivery time
Any hazard over one of those factors directly impacts the lead time, and so the risk of shortage or overstock.
Let’s take the following example: you produce in China and you deliver in France, with an average lead time of 40 days. You have different supply hazards: missing components, higher production lead time than expected, third-party transportation problems, customs clearance delays, or even IT issues. Then, your lead time highly varies, with some deliveries arriving early and others arriving much later than your average lead time.
The distribution of your products’ lead time looks like this:
Because most of the time you cannot predict when those issues happen, you need safety stock to cover supply uncertainties.
Safety Stock with EOQ (Economic Order Quantity)
The basic Safety Stock scenario follows a Continuous Review Policy: quantities to order are fixed.
The optimal quantity to order in terms of cost savings is the EOQ (Economic Order Quantity). To know more about EOQ, check out my article: EOQ formula with examples in Excel.
We can then combine EOQ with Safety Stock to ensure optimized ordering quantities and protection against uncertainty.
Reorder point definition
The reorder point is the stock level at which we need to replenish inventory. We make an order when we reach the Reorder Point, and we receive the item when we reach the safety stock level.
Reorder Point formula:
RP = Safety Stock + Average Sales ×Lead time
This is in theory, but in reality, supply and demand are much more chaotic. This is why we use Safety Stock.
Risks related to safety stock
Before considering the formulas, I would like to stress the risks involved in safety stock. Safety stock can be reassuring, but it often reflects fundamental problems: poor data accuracy, poor forecast accuracy, outdated IT systems, lack of communication with suppliers… Typically, maintaining high safety stock is a trick to hide the root causes of our issues. If you hold unnecessary stock levels to cover all those issues, you will have unreasonably high stock costs.
I advise you to be vigilant about the safety stock and focus on the fundamental issues above.
The Safety stock goal is to find the right balance between the customer service rate and inventory cost.
Safety Stock Calculation: 6 different formulas
We will go through 6 calculation methods for your safety stock, from the simplest to the most complex one.
Method 1: Basic Safety Stock Formula
The first method is the most basic method, which I also call “the old-fashioned way”.
Simply put, we want to “secure X days of supplies”, so we need to estimate “safety days”.
Let’s say you have an average sale of 100 quantities per day for a product with an average lead time of 10 days. From experience, you know that owning 5 days of supplies is enough to mitigate supply and demand risks.
Safety Stock formula and Calculation:
So your safety stock is simply 100 × 5 = 500 pieces. This gives us a Reorder Point of 1500 pieces.
If we assume the EOQ to be 2000, then the stock level versus time looks like this:
- When there are 1500 remaining quantities, you order 2000 pieces
- During the 10-day transit time, you consume 1000 pieces, which brings your stock level down to 500.
- You receive the pieces ordered, and your stock level suddenly reaches the maximum quantity of 2500.
Nevertheless, this calculation method is extremely basic: it does not have any logic and is rather used “from experience”.
If you use it, I advise you to at least combine itwith an ABC classification.
Method 2: Average – Max Formula
We can also estimate the Safety Stock by looking at the past variations of both lead time and sales. We make the assumption that what happened in the past will repeat itself: if we protect against the most extreme case (the highest supply variation combined with the highest demand variation) then we are “sure” to cover any future risk.
This is the average – max method that I also call the “prudent father” method.
Safety Stock formula:
SS = (Max Lead Time × Max Sale) – (Average Lead time × Average Sale)
Safety Stock Calculation:
Here is an example in Excel of the average – max formula. Make sure to always use the same time unit for lead time and demand. Here we use days units.
- We have 12,000 sales quantity over 12 months. This is an average sale of 33 pieces per day.
- The maximum monthly sales quantity is 1200. This is an average of 39.5 pieces per day.
- Over 12 months, we had 10 deliveries. The average lead time was 35 days, and the maximum lead time was 40 days.
Applying the formula, we have a Safety Stock of 427 pieces.
For the Reorder point, the formula remains the same as the previous example:
Reorder Point = Safety Stock + Average Sale (or Average Forecast) x Average Lead Time. Here we have a Reorder point of 1578 units.
This method has the advantage to be very simple to implement. But there are many downsides.
First, you must consider outliers values: if you have an extreme lead time or sale value once in your past data, this will give you very high safety stock.
For example, let’s say your supplier had an exceptional issue, and one of your shipments had very high lead time. You don’t want to use this value in the calculation, as it doesn’t reflect your “regular business”: you don’t want to cover such high uncertainty.
Then, even if you exclude outliers, you will always cover the most extreme case: it means you will have high stock levels the whole year, and thus high inventory costs. Remember that the goal is to balance inventory costs and customer service rate. A trick here is to arbitrarily “cap” the maximum lead time or sales by a percentage, to get a lower safety stock level. You can set higher or lower percentage per product according to the service rate you want, but it is completely arbitrary. I will address in the following methods how to better integrate the service rate into your calculations.
4 Methods with the normal distribution
To fully understand this method, you can download the Safety Stock calculator (Excel template) HERE.
The following method assumes a normal distribution of demand (also called the King’s method). The normal distribution is a probability distribution symmetric to the mean. The further data is from the mean, the lower the probability of occurrence is.
For example, let’s say you sell an average of 1000 pieces per month, and we assume the demand is normally distributed. Each month, you have a high probability of selling close to 1000 pieces, and you have a much lower probability of selling around 500 or 2000 pieces.
Because the distribution is symmetrical, you are as likely to sell less than 1000 next month as you are to sell more than 1000 next month. Also, there is a relation between the level of service (the “acceptable number” of shortages) and the X-axis of the distribution curve: this is the coefficient of service or service factor Z.
Let’s say you want an average service rate of 50%: it means you plan to have enough stock to fulfill your customer’s demand 50% of the time. Then you don’t need safety stock (Z=0) because, the next month, there is a 50/50 chance of selling more or less than the average.
A service rate of 100% (you never have shortages) is impossible because you would need infinite stock.
For other Z values, we have this lookup table:
In the next examples, we use a 90% service rate target, so the service coefficient is 1.28.
We will go through 4 different formulas using the normal distribution.
Method 3: Normal Distribution with uncertainty about the demand
In the first case, we will only consider uncertainty about the demand. This is the most used method.
Contrary to the previous examples, we chose here to use time units in months. You could also do it in days, as long as you use the same time units for all the variables (otherwise, the whole calculation is wrong).
We have the following monthly sales data:
By directly using the demand standard variation formula in Excel, we get a demand standard deviation of 141.4 pieces per month. The average lead time is 1.15 months.
To get the safety stock quantity, we need to multiply the service factor Z by the demand standard deviation σ and the square root of the lead time L.
Safety Stock Calculation:
We get a Safety Stock level of 194 pieces. Applying the same formula as the previous examples, we have a reorder point of 1345 pieces.
I recommend using this method if:
- The lead time is quite stable and variations are negligible compared to the demand uncertainty.
- You have almost no visibility over the past lead time data: obviously, you need to work on your data and fix the root cause of this lack of visibility. But you should still start to estimate your Safety Stock levels without considering supply uncertainty: don’t wait to have all the information available to make decisions. You will improve your data and calculations step-by-step.
Method 4: Normal distribution with uncertainty about the lead time
In the second method, we consider uncertainty only about the lead time.
So we need to estimate the lead time standard deviation.
Here we have a Lead Time standard deviation of 0.14 months.
To get the safety stock quantity, we need to multiply the service factor Z by the lead time standard deviation σ and the average demand μ (here, the average demand is the average sale. It would be different if we used a forecast).
Safety Stock Calculation:
We get a lower safety stock value (and so a lower Reorder Point value) than in the previous case because in this example the lead time variation is rather small.
This method can be used if the demand uncertainty is negligible compared to the lead time variation. In practice, supply chain professionals rarely face this situation. Even if the demand is very steady, it is still advisable to estimate the standard deviation. This is why I don’t particularly recommend this method.
Method 5: Normal distribution with uncertainty about the demand and the lead time (independent)
In the third method, we consider both lead time and demand uncertainty, assuming they are independent, i.e: a variation in lead time doesn’t imply a variation in demand and vice-versa.
Here is the full formula:
Safety Stock Calculation:
We end up with 267 quantities of safety stock. We have higher values because we take into account both uncertainties.
I recommend this method if you have a clear view of lead time and demand variations.
Method 6: Normal distribution with uncertainty about the demand and the lead time (dependent)
The last method considers both lead time and demand uncertainty, assuming they are dependent, i.e: a variation in lead time can imply a variation in demand and vice-versa.
We have the following formula (it is the sum of method 3 and 4):
Safety Stock Calculation:
This is the sum of methods 3 and 4. As variations are correlated, we need even more safety stock than the previous method.
An example of lead time and demand dependence is where there is global high demand, driving up supply delays. But you will rarely face such cases, that’s why I don’t recommend this method.
Limits of the normal distribution for your safety stock:
Whatever the method used, there are limitations:
- Your target service rate is not your real service rate. The service rate we optimize using the formula is the frequency of stock out during the replenishment cycle ( also known as cycle service rate). Whereas, what companies measure (OTIF or Fill Rate KPIs) is the total percentage of stock outs over a period.
- It works poorly with low sales volumes.
- It doesn’t consider any seasonality (if you have a forecast that takes into account the seasonality, you can use it with the corresponding deviation).
- There is an underestimation of extreme cases. In practice, the demand profile varies around an average value, with few high sales in the year. The distribution of demand is thus rarely symmetric, the curve is rather “widened to the right”. The same goes for the lead time: your supplier will be sometimes early, sometimes late, and sometimes very late (and never very early).
- The lack of trust caused by the previous issues can incite practitioners to increase service rate parameters manually (we get back to this idea of the Safety Stock being the “trick” to hide problems).
I recommend using a service rate target based on a classification. See my ABC XYZ article here.
Other ways to compute your Safety Stock
There are still other methods for calculating the safety stock, that can solve the problem of demand and lead time that are not normally distributed:
- Binomial Distribution
- Gamma Distribution
- Poisson Distribution
- McKinsey Method
- etc …
If you already have a good understanding of Safety Stock, Reorder calculations and statistics, you can add a bit more complexity by trying those methods. Otherwise, I suggest you to stick with the methods presented above.
The future of Inventory Management: Machine Learning
Some companies already started to use AI & Machine Learning for Inventory Management optimization.
You can choose this path if you already have a dedicated data science team in your company, you have enough budget for AI consulting services, or if you are a data scientist yourself.
I recommend mastering the foundations first to fully understand Inventory Management challenges before trying such advanced techniques.
How to choose the right formula for your Safety Stock?
Keep in mind that data quality is more important than the method.
Here are a few tips for choosing a method:
- You have low volumes: try the average min/max method
- You have higher volumes: use the normal distribution method. Try method 3 or 5 first, depending on your data.
- You don’t have any data about lead time (or unreliable data): this is common. You can still use the method 3.
- Track your performance and adjust (see next the Action Plan I recommend).
Safety Stock: your Action Plan
- Download the Safety Stock Calculator (Excel Template)
- Choose the right method
- Compare with your current values
- Identify and adjust products with major deviations
- Automate
- Track Performance
- Optimize
Other Inventory Management content
I recommend you to check out our videos tutorial :
- ABC XYZ Analysis to define your target service rate (Excel Tutorial)
- EOQ Formula to optimize your economic order quantity (Excel tutorial)
Become an Inventory Management Expert
If you want to go to the next level, join my next Inventory Management Workshop (free): “How to avoid shortages and overstocks in times of great uncertainty”.
During this webinar, I will share with you:
👉 My method for dealing with sales and supplier uncertainty: 13 parameters to master
👉 How to reduce your inventory and increase your service rate simply and automatically
👉 Excel files and case studies presented live (Zara, H&M, Amazon…)
