There are many ways to calculate safety stock. In this post, we will explain to you the main calculation methods on Excel as well as those I recommend.
(Safety Stock calculation video / Please activate the automatics subtitle in English)
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…)
How to calculate your safety stock? By searching on google, you can find dozens of different solutions. In this article, I will come back to the main methods I recommend to calculate its safety stock, with always concrete examples of Excel that you can download at the end of this article
Why do you need a safety stock?
You need a safety stock to cover yourself against two hazards or uncertainties: demand and lead time
1) Demand uncertainty
For the request, you have different types of products. You have very stable products such as toilet paper, and you have much more uncertain products such as umbrellas (which you only sell when it rains). You will probably have a better forecast quality on toilet paper than on the umbrella. You must, therefore, have a stronger safety stock on the umbrella to cover this uncertainty.
2)Lead Time uncertainty
For the deadline, let’s take the following example: you produce in China and you deliver in France, you can have a production hazard (missing components, production lead time or a problem on transport, customs clearance, reception or even a hazard on the computer part). So you can have a lead time that can be different, with some deliveries arriving early and some deliveries arriving much later than your average lead time.
With this safety stock, you must, therefore, cover yourself on these uncertainties “lead time” and “demand”.
Safety Stock with EOQ (Economic Order Quantity)
Before going into detail on the different methods, I come back to Wilson’s /EOQ formula (which you can find in another video). This Wilson formula will optimize the frequency of your order as well as the quantity to be ordered. The problem here is that this method will not cover you if, for example, you have a high sales that were not expected. So you’re going to have to combine this Wilson formula with this famous safety stock.
Reorder point Calculation
- Re-order point timing: You have a safety stock. The time of reordering is called the control point. You will not reorder when you reach the safety stock level, you will reorder X days before starting your safety stock. For example, if you have a 10-day lead time, you will order 10 days before consuming the parts to arrive at the safety stock.
- Reorder Point = Safety Stock + Average Sales x Lead time
You now have the theory, but in reality, you will see that the safety stock will cushion a problem on-demand or a problem on your lead time to limit the impact on your service rate or customer availability rate.
Risks related to safety stock
Before considering the formulas, I would like to stress the risks involved in safety stock, because it can certainly be reassuring, but it often reflects fundamental problems such as problems with stock management, forecasting, unreliable suppliers, production lead time or logistical problems. We come to put this famous safety stock (which is more expensive for companies) to come and “hide” our problems.
I advise you to be very vigilant about the safety stock and focus on the fundamental issues I have mentioned and always focus on the balance between the service rate, customer availability rate, and inventory cost.
Safety Stock Calculation: 6 different formulas
I will present you 6 calculation methods for your safety stock, from the simplest to the most complex:
Method 1: Basic Safety Stock Formula
The first method I will present is the basic method, which I also call “the old-fashioned way”. simply, you want “X days of safety sales”. If you have an average sale of 100 quantities per day for a product you, have an average time of 10 days, and you want to have 5 days of the average sale in safety stock.
So your safety stock is simply 100 x 5 and therefore 500 quantities.
Safety Stock Formula :
Your order point is the safety stock plus the average sale times the lead time:
500 + 100 x 10 = 1500 quantities.
In the graph illustrating this example, you have a safety stock of 500, you have a reorder point of 1500 (so when you get to 1500 remaining quantities, you will place an order for 2000 pieces (given by Wilson’s Formula). During the 10-day transit time, you will consume 1000 pieces, which brings your stock to 500 and you will suddenly receive 2000 quantities.
This example illustrates the safety stock/reorder point ratio.
Nevertheless, this calculation method is extremely basic and very manual, which means that it remains frozen in time. It does not have any logic and is rather used “by experience” after several years in the supply chain. If you use it, I advise you to at least combine it with an ABC XYZ classification.
Method 2: Average – Max Formula
It is the “average – max” method that I also call the “prudent father” method.
You will use here the (maximum sale x maximum lead time) – (average sale x average lead time).
Safety Stock Calculation :
On Excel, you have sales over 12 months with a total of 12 000, an average per month of 1000, which makes about 33 pieces per day.
Your maximum sale per day is 39.5, here you take the month “max” with the formula “max” which you divide by the number of days in a month.
Then you have had 10 deliveries during these 12 months and the average time is 35 days on average while the maximum time is 40 days (delivery number 4).
The formula of this safety stock : (maximum sale x maximum lead time) – (average sale x average lead time).
Taking the previous data, this gives you a safety stock of 427.
For the order point, it is always the same formula :
Safety stock + average sale (or average forecast) x average lead time: This gives us here 1578.
This quite common method has the advantage of being quite simple if you have the data. The problem is that if you have an extremely long lead time once, for example, it will have a very strong impact on your formula (the same goes for sales). I, therefore, advise you to cap the lead time and sales by a percentage for example. Also, this method does not take into account a target service rate based on the type of product or risk of the products I will address in the following methods.
4 Methods with the normal distribution
To fully understand this method, you can download the Excel of the example HERE
The following method will use a normal distribution. This method is also called the King method. Quickly, the normal distribution is a mathematical law that will make it possible to predict the probability of selling a certain quantity.
For example, if you sell an average of 1000 quantities, you have a high probability of selling around 1000 and you have a much lower probability of selling 500 or 2000, for example. You can see from the image that this distribution is symmetrical, that is, you are as likely to sell less than 1000 next month as you are to sell more than 1000 next month. This normal distribution is extremely practical because you will indicate which service rate you want, and it will return you a safety factor that you will use in your safety stock.
For example, if you want an average service rate of 50%, you don’t need safety stock because you have a 50/50 chance of selling more or less than the average next month.
On the other hand, if you want 90% of the service rate, the normal distribution will give you a multiplier coefficient of 1.28 to meet 90% of the demand.
Concretely in Excel, you indicate the desired service rate, 90 for example and automatically you will get a service coefficient Z. To find this, we use an Excel formula called NORM.S.INV with the service rate as the only variable.
With the table that you can find by simply downloading Excel HERE, you can find the Z coefficient of service rate for all percentages, knowing that a percentage of service rate is theoretically impossible because you would have to have infinite stock.
We now have this service coefficient defined, you have four different formulas to use this normal distribution.
Use constant time units for your calculation
Be careful, contrary to our video on the security stock above, it is necessary to use the average time in Months and not in the number of Days because we use the standard deviation of sales per month.
You have 3 options: Either you use all your data in DAYS, WEEKS or MONTHS. It is essential to always remain constant with the time units in your calculations.
In the example below, since we only have sales in MONTHS, we must, therefore, convert our lead times to MONTHS also to have a single unit (see column Lead times Months). This is a classic mistake that is very often made. Thanks to Jacques Bojoly (JB-Conseil) for reporting this error.
Method 3: Normal Distribution with uncertainty about the demand
Safety Stock Calculation :
To find the standard deviation of the demand, you must use the standard deviation formula overall months (it can also be per month, per day, or week), including the standard deviation of the demand x the root of the average delay (the average delay is here 1.15 months). With these formulas, we would, therefore, have a safety stock of 194 parts. The reorder point is always the same: SS + Average sale x Average time = 1345 pieces
Instead, use this method when the uncertainty is only on demand and the time frame is rather stable and predictable.
Method 4: Normal distribution with uncertainty on the lead time
Safety Stock Calculation:
Then for the second formula, if your problem is only with the lead time and you have an extremely reliable forecast, you will use this formula with the safety factor Z x average sale (32.9/day) x times the lead time deviation (standard deviation = deviation from the average). The more unstable your lead time, the greater the standard deviation will be.
As a result, with this formula, you obtain a much lower safety stock (183) because, in this example, the variation in time is rather small.
Method 5: Normal distribution with uncertainty on-demand and independent lead time
3rd Formula using the normal distribution: you consider that you have a high uncertainty on the request and also uncertainty on the deadlines, both being completely independent.
For example, for umbrellas, the lead time will not impact demand.
Therefore you end up with a more complex formula
Safety Stock Calculation :
In the end, we end up with 267 quantities of safety stock. This safety stock is more important because it takes into account both uncertainty about lead time and sales.
Method 6: Normal distribution with uncertainty on demand and dependent lead time
If you consider that the request and the lead time are dependent, i. e. that the lead time causes uncertainty on the request and vice versa, you have the following formula which will sum the safety stock of Formula 1 and Formula 2, giving 377 quantities in our example.
Safety Stock Calculation :
This is the sum of methods 3 and 4. As in this case, variability can impact sales and vice versa, even more, safety stock is needed. This method is extreme, I don’t recommend it.
These formulas may seem very complex but it is sufficient to simply apply the formulas in the function of the cases: generally, Formula 1 is used a lot, but if you also have problems such as production or uncertainty about the time, I recommend that you use Formula 3 instead (Method 5).
Limits of the normal distribution for your safety stock :
In any case and whatever the method on the normal distribution, you have certain limitations:
- The first is that in the end, not all requests apply to this mathematical law. For example, on very low sales the same behavior is not considered.
- Secondly, the safety factor Z is not seasonal, so if you have very strong seasonality, it is not necessarily ideal.
- Finally, you will have an underestimation of somewhat extreme cases. For example, if you have a production problem for three months, it will not be considered in a normal distribution that will always consider that the extremes have a very low probability.
- These extreme cases can generate a lack of trust. We can often see an increase in the parameters, for example, of a company that wants to have a 90% service rate will finally get the 99% because it does not trust this mathematical law.
What I recommend is that you use a service rate that is targeted based on a classification. If you have a goal that can be unique, for example, 90% (which I do not recommend), I advise you at least to have a service rate that is different depending on the ABC classes, and even better, an ABC classification XYZ or XYZ represents uncertainty. You can find all the details and advice on classification ABC XYZ in our video right here.
Others ways to compute your Safety Stock
There are still other methods for calculating the safety stock:
- Binomial Distribution
- Poisson Distribution
- McKinsey Method
The problem is that to apply these methods requires even more parameters and reliable data, which greatly increases the risk of stock problems. I, therefore, recommend that you stay with the methods presented above.
How to choose the right formula for your Safety Stock?
The most important thing already will be the quality of the data, which is even more important than the method chosen in terms of sales and lead times.
Then, if you have low volumes, I advise you to use Method 2 “medium max”.
If you have higher sales, from 100, 200, 300 quantities per month per product, I recommend Method 3 with variability only on demand.
In case you have high uncertainty about your deadlines, I recommend Method 5
8 tips for your Safety Stock
To conclude, here are 8 tips I recommend you to follow to optimize your safety stocks:
- First, avoid manual settings. The problem often is that we will put a safety threshold of 1000 quantities, for example, and sales will change but not the safety stock. It is, therefore, necessary to have dynamic formulas that evolve.
- Check your safety stocks regularly (especially 20/80) to ensure that there are no inconsistencies.
- Focus especially on the quality of the forecasts. I recommend improving your forecast rather than increasing the safety stock.
- Try to stabilize demand if you can by limiting the number of promotions or limiting the number of new products, for example.
- Lead your suppliers and factories on the stability of deadlines. Be demanding to have stable deadlines rather than once again increasing your safety lead time.
- It is of course ideal to be able to reduce your lead times to reduce your safety stocks and thus reduce your total stocks.
- Accept the break on your low sales (review ABC XYZ classification)
- Keep in mind that the ultimate solution will be to use Machine Learning and artificial intelligence to optimize your stocks and forecasts (see next point)
The best solution for inventory management: Machine Learning
The ideal solution for the future is the machine learning that I will talk about in a future video. Some companies have started to use it. This machine learning or artificial intelligence will simulate all possible combinations to give permanently the optimal stock and the optimal forecast. If you want to use it, you need to consolidate and structure your data today so that it can be used in the future.
We are currently preparing several articles and videos on this subject. If you are interested in joining our training: How to use Machine Learning & Artificial Intelligence in Supply Chain, please contact us directly
Download Excel Safety Stock + Reorder Point
To conclude, I advise you if you have not already done so, to download the Excel that I presented in the various examples in the article HERE. You can choose the right method and then compare the results with your current safety stocks to perhaps adjust the differences if they are significant. Also check out our articles on the ABC method and Wilson’s Formula, which will help you better understand and apply these methods.
Go Further :
First of all, if you haven’t already done so, I recommend you to check out our videos:
- ABC XYZ Analyse to define your target service rate given demand and uncertainty
- EOQ / Wilson Formula to optimize your volumes and reordering frequency
To go even further :
If you want to go much further, we have just launched a new program called “reduce your inventory from 10% to 30% without impacting your customers” where we will look back at a real case of a company that has managed to reduce its stock by 21% while increasing its customer service rate by applying all the theory (including the safety stock calculation presented above) to a real case.
If you want to more know about this new program, please contact us.