How to Calculate Safety Stock in Excel

This guide will explain how to calculate safety stock with Microsoft Excel.

Safety stock refers to a specific amount of stock kept in the inventory in case of increased demand or other unforeseen events. 

Businesses need to calculate the right safety stock levels for products with an unstable demand or with an uncertain lead time. If a business sells products that have to be shipped over or manufactured, there are a variety of factors that could lead to possible delays in lead time. 

There are several ways to calculate safety stock, but one of the most common methods takes into consideration historical demand, average supply chain lead time, and the desired service level the business is hoping to achieve.

In this guide, we will provide a step-by-step tutorial on how to calculate the necessary safety stock quantity for a particular product using Microsoft Excel.

A Real Example of Calculating the Safety Stock in Excel

One of the most common methods to calculate the safety stock is using the normal distribution to model the demand for a product. We can use the following formula to calculate the safety stock quantity:

Safety Stock Quantity = Z-value of Service Level * Standard Deviation of Demand * Square Root of Average Lead Time

The z-value is based on the desired service level the company is willing to meet. A service level of 90% requires an inventory that has enough stock to fulfill an order on time 90% of the time. A higher service level typically demands a higher quantity of safety stock as a buffer.

Calculating Safety Stock Given Monthly Sales

Let’s take a look at a sample scenario where we may need to calculate safety stock in Excel.

In the table above, we have monthly sales data for a certain product. Based on calculations from our supplier, we know that the average lead time for our product is 14.5 days. Given the demand shown in our dataset, how much safety stock is needed to meet a service level of 95%?

We’ll need to find the standard deviation of our demand per month. We can use the STDEV.S function to calculate the standard deviation of demand:

=STDEV.S(B2:B13)

After using the STDEV.S formula, we’ve determined that the standard deviation of sales is 405.3 sales.

Next, we’ll need to calculate the Z-value for our desired service level. We can use the formula =NORM.S.INV(0.95) to find the Z-value associated with a 95% service level.

We’ll add our desired service level of 95% in cell E3. Using the NORM.S.INV formula, we’ve determined that this service level has a z-value of 1.6.

Now that we have all the data we need, we can now find the safety stock quantity using the following formula:

=E4*E2*SQRT(E1)

Our formula indicates that we’ll need at least 2538.6 units of our product to prevent any delays in case of unexpected spikes in demand.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to calculate safety stock in Excel.

How to Calculate Safety Stock in Excel

1. Given monthly sales data, we’ll need to find the standard deviation of monthly sales. We’ll use the formula STDEV.S(B2:B13) to determine the standard deviation of sales from January to December.
   
2. Write down the average lead time for your product. This value typically refers to the time it takes for a business to order or produce a product and have it shipped to the customer.
   For this example, our product’s average lead time is given as 14.5 days.
   
3. Decide on a service level for your product’s inventory. This value measures the probability of an order being fulfilled on time. Retail businesses typically have a service level between 90% to 95%.
   Based on the desired service level, we’ll use the formula NORM.S.INV to determine the corresponding z-value, which will help us calculate the safety stock.
   
4. We’ll use the formula E4*E1*SQRT(E2) to determine the safety stock quantity required to achieve the desired service level.
   
5. Hit the Enter key to evaluate the function.
   
   In this example, we’ve determined we need a safety stock of 2,538.6 to maintain a service level of 95%.

These are all the steps you need to follow to start calculating the right safety stock for your inventory.

FAQs

1. When determining safety stock in Excel, why might a company adjust the calculated value instead of strictly following the formula output?
   Companies might adjust the calculated safety stock value to account for factors specific to their business, industry, or any strategic changes. This includes considerations like upcoming promotions, expected changes in demand, supplier reliability, or company risk tolerance.
2. How does increasing the desired service level impact the amount of safety stock calculated in Excel?
   Increasing the desired service level leads to a higher Z-value, which in turn increases the safety stock amount. This means a higher probability of meeting customer demand, but also potentially higher holding costs due to larger inventory levels.

To learn more about using Excel for inventory management, you can read our post on how to keep track of inventory in Excel. You may also be interested in reading our guide on how to calculate the stock to sales ratio in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!