How to Calculate Safety Stock and Reorder Point in Excel

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.

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.

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.

• Second, the average sales per day is computed by dividing the total sales with 365 as a year consisting of 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.

• 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.

•  From previous historical data, let’s assume we have 3 days of in-hand stock in our hands.

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

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

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

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).

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

• 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.

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

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

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.

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.

• Then, we will find the standard deviation for our demand using the STDEV function.

• 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.

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

• Similar to the previous methods, we will utilize the MAX function for computing 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.

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

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-

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

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

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

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.

Steps:

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

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

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.

Steps:

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

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

• 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.