How to Calculate Safety Stock and Reorder Point in Excel

Safety stock in Excel takes one formula once your usage and lead-time history is in a table. Safety stock is the extra stock you keep so that a busy week or a late delivery doesn't leave you with empty shelves. This guide shows two common formulas, the simple max-minus-average method and the statistical service-level method, then turns safety stock into a reorder point. It's for small business owners, supply chain students and anyone facing an inventory task in a job test.

The figures below are examples to show the method. The right buffer for your business depends on your own data, costs and how much risk of running out you can accept.

What safety stock covers#

Safety stock covers the gap between a normal order cycle and a bad one. Two things can go wrong while you wait for a delivery:

1. Demand is higher than usual. You sell or use more per day.
2. Lead time is longer than usual. Lead time is the number of days from placing an order to receiving it.

If either happens, the stock you planned for runs out early. Safety stock is the buffer that bridges the gap.

Set up the usage and lead-time data#

You need two columns of history: daily usage and lead time, in the same unit (days). Here are the last seven orders for one item:

Summarise each column with AVERAGE and MAX:

The AVERAGE function guide and the MAX function guide explain both functions, and the guide to AVERAGE, MIN and MAX shows them side by side.

Method 1: the max-minus-average formula#

This method compares a worst-case order cycle with a normal one. The worst case is the highest usage over the longest lead time. The normal case is average usage over average lead time. Safety stock is the difference:

=MAX(B2:B8)*MAX(C2:C8)-AVERAGE(B2:B8)*AVERAGE(C2:C8)

That's 50 × 8 − 42 × 6 = 400 − 252 = 148 units.

It's easy to explain and needs no statistics. The catch: it assumes the worst usage and the worst delay happen at the same time. That's rare, so this method often gives a generous buffer. It works well for small ranges and items where running out is very costly.

Method 2: the service-level formula with NORM.S.INV#

This method sizes the buffer for a chosen service level: the share of order cycles in which you don't run out. It assumes daily usage follows a normal (bell-shaped) distribution.

=NORM.S.INV(0.95)*STDEV.S(B2:B8)*SQRT(AVERAGE(C2:C8))

Each part does one job:

• NORM.S.INV(0.95) returns the Z score for a 95% service level, about 1.645. It tells Excel how many standard deviations of cover you want.
• STDEV.S(B2:B8) measures how much daily usage varies: about 4.93 units. The standard deviation guide explains it.
• SQRT(AVERAGE(C2:C8)) scales daily variation up to the lead time. Variation doesn't grow in a straight line with time, so you use the square root: √6 ≈ 2.45.

The result is 1.645 × 4.93 × 2.45 ≈ 19.9. Round up to 20 units with ROUNDUP. Here are common Z scores:

When lead time varies too#

The formula above only covers changes in demand. In this data, lead time also varies, from 4 to 8 days. A wider version covers both:

=NORM.S.INV(0.95)*SQRT(AVERAGE(C2:C8)*STDEV.S(B2:B8)^2+AVERAGE(B2:B8)^2*STDEV.S(C2:C8)^2)

With a lead-time standard deviation of about 1.53 days, this gives about 107.4, so 108 units. Late deliveries clearly drive most of the risk for this item.

Which safety stock method to choose#

The three results are very different: 148, 20 and 108 units. Each answers a slightly different question:

For most items, start with the service-level method that matches your supplier. Use the max-minus-average method as a cautious check. Whatever you choose, keep the inputs in cells so you can update them each month.

From safety stock to a reorder point#

The reorder point is the stock level at which you place a new order. It covers normal usage during the lead time, plus the safety buffer:

=AVERAGE(B2:B8)*AVERAGE(C2:C8)+F2

With 148 units of safety stock in F2, that's 42 × 6 + 148 = 400 units. With the 108-unit buffer, it would be 360. The inventory tracker guide shows how to compare current stock with this level and flag items to reorder automatically.

Tips for accurate safety stock#

• Use the same time unit. If usage is per week, lead time must be in weeks too.
• Use enough history. Seven orders keep this example short. More history gives steadier averages and standard deviations.
• Calculate per item. Each product has its own demand and supplier. Don't use one buffer for everything.
• Round up. You can't stock 19.9 units. =ROUNDUP(...,0) gives a whole number.
• Review it often. Seasons, promotions and new suppliers change the numbers. Planners in supply chain roles and ecommerce roles update these figures regularly.

Practise it#

Safety stock is built on AVERAGE and MAX, plus careful arithmetic with the results. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise a department budget to combine totals and averages in one model. Each lesson checks your formula instantly. Then try the exercise on this page to calculate the 148-unit buffer yourself.