How to Calculate Safety Stock in Excel (6 Easy Methods)

Safety stock is essential in many sectors, specially in those that deal with inventory management and supply chain operations. It makes sure that a business can continue to meet client demand in the face of crisis. Utilizing several Excel tools help to calculate safety stock precisely and quickly.

In this article, we have demonstrated some most used methods of calculating safety stock in Excel. We have illustrated the simple arithmetic way of measuring safety stock along with some other non-conventional methods that are precise in determining safety stock.

Other than that, you will get to learn how one can calculate reorder point from calculated safety stock.

Download Practice Workbook

You can download the workbook used to elaborate methods in this article from here.

What is Safety Stock?

Safety stock, also known as buffer stock, is used in inventory management and supply chain management. Safety stock indicates the additional stock that a business organization keeps on hand as a security against the change in demand and supply. No matter the emergency, safety stock can guarantee that the consumer receives the product on time. Companies spend higher costs to maintain the extra inventory needed to provide outstanding client service.

Safety Stock Formula

In several ways, safety stock can be measured. In our article, we will learn 6 different approaches that allow you to calculate safety stock. With the aid of several Excel functions, you can determine safety stock within the quickest possible time. The most used formulas are mentioned below for calculating safety stock in Excel.

• Basic Formula of Safety Stock: Average Sales * Stock available
• Safety Stock Based on Average-Max Method: (Max. lead time*Max. sales in a day) – (Avg. lead time*Avg. Sales in a day)
• Safety Stock Using Kings Method: Coefficient service (Z) * √(Average Lead Time * (Demand S.D)² + (Average Sales * Lead Time S.D)²)

6 Methods of Calculating Safety Stock in Excel

We will be using the following dataset as an example to elaborate methods in this article. The dataset represents a shop’s total quantity sold per month’s record and the delivery lead time.

1. Using Basic Formula

We will calculate the safety stock in an old-fashioned way. In this method, we need to assume the number of safety days for the safety stock.

• To calculate safety stock using the basic formula, let’s modify our dataset first as follows.

• Now first, we will calculate the total quantity sold using Excel SUM function.
• Select cell C17, apply the formula below, and press Enter.

• After calculating the total quantity sold, we have to calculate the values of average sales per day, average lead time, and maximum lead time.
• Apply the formulas below in cell C18, F15, F16 respectively.

• Let’s assume, we have 3 days of in-hand stock in our hands.
• Select cell F17 and type 3.

• Finally, select cell C20, insert the formula below, and hit Enter.

• We have already seen the formula for this method and that is:

• Here, value of Average Sales is in cell C18 and value of Stock Available is in cell F17.

2. Utilizing Average-Max Method

The previous approach offered a foundational understanding of safety stock which is not recommended for usage in real-life situations. We suggest using this approach as opposed to this outdated method. If we consider the worst case for supply and demand fluctuation, this approach to calculate safety stock in Excel is appropriate. Here, Excel’s AVERAGE and MAX formulas have been combined to get the safety stock.

• First, modify the previous dataset as follows. We calculated total sales, average sales per day, average lead time, and maximum lead time in the previous section and used those here.

• To calculate average sales per month, maximum sales per month, and maximum sales per day, simply select cell C19, C20, C21 and insert the following formulas respectively:

• We have already seen the formula of safety stock for the average max method and that is:

• Value of lead time is in cell F16, value of Max. sales in a day is in cell C21, value of Avg. lead time is in cell F15 and value of Avg. Sales in a day is in cell C18.
• Select cell F18, type down the formula below, and hit Enter.

The safety stock returns in cell F18.

3. Normal Distribution with Uncertainty of the Demand

You can use this method if you want to manage your safety stock using a tolerance of your choosing. Here we will use normal distribution if we have the uncertainty of demand. Here, compared to the previous method, we decided to use time units in months. So we need to add a new column for that.

• Modify the previous dataset as follows.

• Let’s assume our targeted service rate is 92%.
• Select cell C20 and type 92%.

• We can determine the value of coefficient service (Z) using Excel’s built-in NORMSINV function and the value of demand standard deviation using Excel STDEV function.
• Select cell C21 and C22 and insert the following formulas respectively-

• To calculate monthly lead time, select cell G5, insert the formula below, and press Enter.

• After that, use the Fill handle icon to drag down.

• Now, we will calculate the monthly average lead time and monthly maximum lead time.
• Select cell G15 and G16, then apply the formulas below respectively-

• Select cell F18, then apply the below formula and press Enter.

Here in the formula, cell C21 carries the value of Z, cell C22 represents the value of demand standard deviation and cell G15 has the value of monthly average lead time.

This returns you the value of safety stock in cell F18.

4. Normal Distribution with Uncertainty of the Lead Time

In this method, we are considering uncertainty of lead time only. For that, we need to determine the lead time standard deviation.

• Let’s modify our previous dataset as follows.

• To calculate day-wise lead time standard deviation, select cell F17 and insert the formula below.
• Then press Enter.

• Insert the following formula on cell G17 to calculate month-wise lead time standard deviation.
• Then press Enter.

• Apply the formula below on cell F19 and press Enter.

The value of safety stock returned in cell F19.

5. Normal Distribution with Uncertainty of the Independent Demand and the Lead Time (King’s Method)

In this method, we are considering both lead time and demand uncertainty assuming both as independent. This method is also known as king’s method and this one is the most accepted method to calculate safety stock in Excel. A change in lead time doesn’t affect the demand in this method.

Our formula based on king’s method is-

• In the following dataset, cell C21, G15, C22, C19, and G17 have the values of Coefficient service (Z), Monthly Average Lead Time, Demand S.D, Average Sales per Month, and Lead Time S.D respectively.

• Select cell F19, apply the formula below and press Enter.

This returns you the value of safety stock in cell F19.

6. Normal Distribution with Uncertainty of the Dependent Demand and the Lead Time

In this method, we are considering both lead time and demand uncertainty assuming both as dependent. A change in lead time affects the demand in this method.

Formula for this method-

• Select cell F19, insert the formula below, and hit Enter.

How to Calculate Reorder Point from Safety Stock in Excel

A company’s reorder point is the amount of inventory it must have on hand before placing a new order. It is calculated based on the expected demand, lead time, and the safety stock required for variability in demand and supply.

The formula for reorder point is:

• From King’s method, our dataset is as follows.
• Now we will calculate reorder point from these data.

• Select cell F20, copy and paste the formula below then press Enter.

• And the value of reorder point will return in cell F20.

The value of reorder point returned in cell F20.

Why is Safety Stock Necessary?

Safety stock is essential because it serves as a backup supply to stop product shortages. It assists in managing unexpected increases in demand, delays in receiving new stock, and mistakes in estimating the amount of inventory required. Businesses may meet client requests even in disruptive or difficult circumstances because of safety stock.

Downsides of Safety Stock

There may appear some disadvantages of safety stock as well. Such as-

• Extra inventory kept on hand as a Safety Stock may take up funds and drive up storage expenses, which may adversely affect a company’s overall profitability.
• Safety Stock can become obsolete if it is not used, which causes waste and financial losses.
• The amount of Safety Stock that may be held may be limited by a lack of storage capacity, which could make the company subject to shortages during unexpected demand increases.
• Effective inventory management procedures are necessary for handling Safety Stock, but they can be difficult and time-consuming for businesses.

Frequently Asked Questions

1. How do you calculate Z in safety stock?
Z represents coefficient service. Value of Z can be calculated in Excel using its built-in NORMSINV function. Let’s assume, your targeted service rate’s value is in cell A1. Now, pick an empty cell and then apply this formula:

This will return you the value of Z.

2. What is safety stock level?
Safety stock or safety stock level or buffer stock, all have similar meanings. The amount of additional inventory a business keeps on hand to guard against unexpected changes in demand or supply is known as the safety stock level.

3. What is lead time demand?
The term lead time demand describes the total amount of demand for a good or service while the inventory is being refilled. It also includes the demand that arises after an order has been placed while a new supply is being delivered.

Conclusion

We have learned the importance and downsides of safety stock and all the necessary details of how to calculate safety stock in Excel in this article. Along with measuring safety stock, we have also learned the method of calculating reorder point from safety stock. Hope, you have found whatever you were looking for. Visit our site ExcelDemy to explore more relevant articles.