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.

**Table of Contents**hide

## 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)**^{2}+ (Average Sales * Lead Time S.D)^{2})

## 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**.

`=SUM(C5:C16)`

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

`=C17/365`

`=AVERAGE(F5:F14)`

`=MAX(F5:F14)`

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

`=C18*F17`

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

**Safety Stock = Average Sales * Stock available**

- 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:

`=C17/12`

`=MAX(C5:C16)`

`=C20/30.5`

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

**Safety Stock = (Max. lead time*Max. sales in a day) – (Avg. lead time*Avg. Sales in a day)**

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

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

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-

`=NORMSINV(C20)`

`=STDEV(C5:C16)`

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

`=F5/30.5`

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

`=AVERAGE(G5:G14)`

`=MAX(G5:G14)`

**Note:**

**Safety Stock for Normal Distribution with Uncertainty of the Demand = Coefficient service (Z) * Demand Standard Deviation * √ Monthly Average Lead Time**

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

`=C21*C22*SQRT(G15)`

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

`=STDEV(F5:F14)`

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

`=STDEV(G5:G14)`

**Note:**

**Safety Stock for Normal Distribution with Uncertainty of the Lead Time = Coefficient Service (Z) * Average Sales per Month * Month wise Lead time standard deviation**

- Apply the formula below on
**cell F19**and press**Enter**.

`=C21*C19*G17`

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-

**Safety Stock = Coefficient service (Z) * √(Average Lead Time * (Demand S.D)**

^{2}+ (Average Sales * Lead Time S.D)^{2})

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

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

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-

**Safety Stock = Coefficient Service (Z) * Demand S.D * √(Lead Time) + Z * Average Sale * Lead Time S.D**

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

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

## 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:

**Reorder Point = Safety Stock + Average sales per month * Monthly Average lead time**

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

`=F19+C19*G15`

- 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:

`=NORMSINV(A1)`

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.