How to Calculate Safety Stock and Reorder Point in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, I am sharing with you how to calculate safety stock and reorder point in Excel. Calculating safety stock and reorder point in Excel is an essential task for inventory management. Safety stock is the buffer stock maintained to ensure that customer demand can be met despite unexpected fluctuations in demand or lead time. Reorder point is the inventory level at which new orders should be placed to replenish the stock.

In the following, you will find an overview of calculating safety stock and reorder point in Excel.

Overview of how to calculate safety stock and reorder point in excel


Download Practice Workbook

You can download our practice workbook from here for free!


What Is Safety Stock?

Safety stock is a term used in inventory management to explain the extra inventory that a company keeps on hand to mitigate the risk of stockouts. By maintaining a safety stock, a company can reduce the risk of lost sales, dissatisfied customers, and damage to its reputation. Safety stock depends on factors such as demand variability, lead times, and supplier performance. The main purpose of keeping safety stock is to ensure that a company can continue to meet customer demand, even when faced with unforeseen events.


What Is Reorder Point?

Reorder point is the minimum quantity of inventory that a company must have in stock before placing a new order. It is calculated based on the expected demand, the lead time, and the safety stock required for variability in demand and supply. The purpose of the reorder point is to ensure that a company never runs out of stock while minimizing the cost of holding excess inventory. Analyzing past demand patterns, assessing lead times, and evaluating supplier performance are the factors to determine reorder point.


How to Calculate Safety Stock and Reorder Point in Excel: 6 Methods

In the following, I have shared 6 simple techniques to calculate safety stock and reorder points in Excel.

Suppose we have a dataset of a Shop’s Sales Quantity months. Now we will calculate safety stock and reorder points with several techniques in Excel.

Sample dataset of how to calculate safety stock and reorder point in Excel


1. Using Simple Arithmetic Formula

In this method, we are going to describe a simple old-fashioned way of determining safety stock and reorder point in Excel.

Steps:

  • First, we will calculate the total quantity sold by using the SUM function in Excel. The SUM function sums up the numerical values from a given range.

=SUM(C5:C16)

Using SUM function to calculate total sales quantity

  • Second, the average sales per day is computed by dividing the total sales with 365 as a year consisting of 365 days.

=C17/365

Calculation of average sales per day by dividing the total sales with 365 days

  • Next, from the data table average lead time is calculated using the AVERAGE function. The AVERAGE function determines the average value from a list of numbers.

=AVERAGE(F5:F14)

Using AVERAGE function to calculate average lead time

  • Following the basic formula we will need maximum lead time. Thus, using the MAX function maximum lead time is determined as the MAX function returns the highest value from the specified data.

=MAX(F5:F14)

Utilizing MAX function to determine maximum lead time
  •  From previous historical data, let’s assume we have 3 days of in-hand stock in our hands.

Assumption of in hand stock from historic data

  • Finally, using the basic arithmetic formula- Safety stock = Average Sales * Stock available let’s find the safety stock.

=C18*F17

Calculation of safety stock using simple formula

  • Then, following the basic formula- Reorder point = Safety Stock + Average Sales*Lead Time we will compute the reorder point.

=C20+C18*F15

  • Finally, we have successfully determined our safety stock and reorder point in Excel. It’s that simple.

Computing reorder point using simple formula


2. Applying AVERAGE and MAX Formula

The previous method provides a basic learning for understanding safety stock and reorder point which is not encouraged for practical uses. Rather than using this old-fashioned technique, we recommend using this method. This method is suitable if we imagine the worst scenario of supply variation and demand variation. Here we have combined the AVERAGE and MAX formula to calculate safety stock and reorder point in Excel.

Steps:

  • Starting with, we will find the month-wise average sales by writing the below formula in cell (C19).

=C17/12

Calculating average sales per month

  • Thereafter, maximum sales for the month is determined using the MAX formula.

=MAX(C5:C16)

Using MAX function to calculate maximum sales per month

  • Similarly, we will compute the maximum sales per day using the previous step. Here we divided the maximum sales/month with 30.5 because converting months to days by dividing 365 days with 12.

Calculation of maximum sales per day using arithmetic formula

  • Now we will calculate the safety stock by using the below arithmetic formula.

Safety stock = (Max. lead time*Max. sales in a day) - (Avg. lead time*Avg. Sales in a day)

=(F16*C21)-(F15*C18)

Calculation of safety stock in excel

  • For determining the reorder point the formula will remain the same similar to the previous method.

=F18+C18*F15

Calculation of reorder point in excel

With advantages, there are some limitations too with this trick. You might face high inventory costs as we are considering the highest sales and lead time to remove stockout problems. In order to stay on the safe side, use a “cap” which is a percentage value from the actual value.  By setting a cap, you can assume that there is a maximum possible delay in the delivery of goods to the maximum number of items that customers may purchase.


3. Using Normal Distribution with Uncertainty of Demand

If you want to control your safety stock with your own choice of tolerance then you can try this method. Here we will use normal distribution if we have the uncertainty of demand. Thus let’s assume we want a service rate of 92% for keeping safety stock.

Assumption of targeted service rate

Steps:

  • With the help of the service rate we will determine the coefficient of service (z) by applying the NORMSINV function. This function determines the inverse of normal cumulative distribution from the given probability.

=NORMSINV(C20)

Calculating coefficient service using NORMSINV function

=STDEV(C5:C16)

Using STDEV function to determine demand standard deviation

  • On some points you might need to evaluate safety stock for the whole month. Keeping that in mind, we have converted the lead time to months with a simple formula.

=F5/30.5

Converting lead time days to months

  • Hence, the average lead time is figured out with the help of AVERAGE function.

Using AVERAGE formula to get average lead time

  • Similar to the previous methods, we will utilize the MAX function for computing maximum lead time.

=MAX(G5:G14)

Using MAX formula to compute maximum lead time

  • Finally, we can calculate safety stock by multiplying the coefficient service, uncertain demand, and the square root of lead time. In order to determine the square root of lead time we have used Excel’s built-in function named as the SQRT function. The final formula will be.

Arithmetic formula to calculate safety stock

Determining safety stock using normal distribution

  • The formula for the reordering point will remain the same as previous one.

=F18+C18*F15

Calculating reorder point using normal distribution


4. Use of Normal Distribution with Uncertain of Lead Time

In this part, let’s assume a different scenario where we have uncertainty with our suppliers’ lead time. This time we will find out the standard deviation for the lead time.

Steps:

  • Select a cell (F17) and put the below formula down-
=STDEV(F5:F14)

Using STDEV function to compute lead time standard deviation for days

  • Similarly, for computing lead time standard deviation month wise we will apply the below formula.

=STDEV(G5:G14)

Using STDEV function to compute lead time standard deviation for months

  • In order to get the safety stock you need to multiply coefficient service, average sales, and lead time standard deviation. The formula stands as-

Arithmetic formula of safety stock when lead time is uncertain

=C21*C19*G17

Calculation of safety stock when lead time is uncertain

  • For reorder point use the below formula which will lead to the final output we are looking for.

=F19+C19*G15

Calculation of reorder point when lead time is uncertain


5. Using Normal Distribution with Uncertain of Independent Demand and Lead Time

In the worst scenario, you might face problems where both of your demand and lead time are unsettled. Both demand and lead time get affected by customer behavior, supplier performance, and transportation delays. Well, you can determine the safety stock for your independent demand and lead time by applying this basic formula.

Arithmetic formula of safety stock when demand and lead time is independent

Steps:

  • Choose a cell (F19) and write the below formula down.

=C21*SQRT((G15*C22^2)+(C19*G17)^2)

Calculation of safety stock when demand and lead time is independent

  • Just like the previous methods the formula for reordering point will remain as usual.

=F19+C19*G15

Calculation of reorder point when demand and lead time is independent


6. Using Normal Distribution with Uncertain of Dependent Demand and Lead Time

In this final method, we will find the safety stock when lead time and demand both are dependent. Simply use the below formula where we have multiplied the uncertain demand and lead time with all other factors.

Arithmetic formula of safety stock when demand and lead time is dependent

Steps:

  • Simply, choose a cell (F19) and put the below formula down and click ENTER.

=C21*C22*SQRT(G15)+C21*C19*G17

Calculation of safety stock when demand and lead time is dependent

  • In order to get the reorder point, type the below formula and hit ENTER.

=F19+C19*G15

Calculation of reorder point when demand and lead time are dependent

  • In conclusion, we have successfully calculated safety stock and reorder point in Excel.

Why Do We Calculate Safety Stock and Reorder Point in Excel?

Safety stock and reorder points are crucial parts of inventory management. Safety stock acts as a buffer against demand and supply fluctuations. It will help solve the stock out problem caused by some unwanted events. On the other hand, reorder point determines when to replenish inventory. It optimizes inventory planning, reduces costs, and aligns purchase and production processes. With proper implementation and maintenance of safety stock and reorder points, you can improve your inventory management system.


Which Formula to Choose for Calculating Safety Stock in Excel?

Choosing the appropriate formula for calculating safety stock depends on the specific needs and characteristics of your business. Below I have shared some important tips while choosing methods for safety stock.

  1. If you have two or three types and a low volume of products then you can try the Average and Max Method.
  2. For a higher volume of products and diversified ranges you can try both normal distribution with uncertainty of demand and normal distribution with uncertainty of independent demand and lead time.
  3. When you do not have any previous data regarding lead time then you can try the Normal Distribution with the Uncertainty of Demand method as the main variable here is the uncertain demand.

Things to Remember

  • While calculating choose the appropriate formula according to your inventory management.
  • Before applying formulas make sure all your data is in the same units of measurement.

Frequently Asked Questions

  • What are some factors to consider when calculating safety stock and reorder point?

Factors to consider include demand variability, lead time variability, service level targets, and the cost of holding inventory.

  • How often should I recalculate my safety stock and reorder point?

It depends on how frequently your demand and lead time change. If they are relatively stable, you may only need to recalculate your safety stock and reorder points periodically.

  • Can I use Excel to calculate safety stock and reorder point for multiple products?

Yes, you can use Excel to calculate safety stock and reorder points for multiple products by creating separate worksheets or using different columns for each product.

  • What are the other ways to compute your safety stock?

Other ways to compute safety stock include the service level method, historical data analysis, and simulation modeling.


Conclusion

In conclusion, calculating safety stock and reorder point in Excel is a crucial aspect of inventory management that can help businesses ensure they have enough inventory on hand to meet customer demand. By considering factors such as demand and lead time variability, and  service level targets businesses can optimize their inventory levels to achieve greater efficiency. Take a tour of the practice workbook and download the file to practice by yourself. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

2 Comments
  1. This was simple to understand. Thanks.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo