CELLWRIGHT

Calculator › Guide

Safety Stock and Reorder Point Formulas: A Step-by-Step Guide (Excel & Google Sheets)

Service level to z, demand and lead-time variability, EOQ, MOQ rounding and order-by dates, with one worked example

Cellwright · Updated 22 Nov 2026 · 8 min read

Most small inventory businesses reorder on gut feel: "we usually order when we're down to about a pallet." That works until a supplier ships ten days late during your best month. A reorder point turns that gut feel into a number you can defend, and safety stock is the part of it that protects you from the bad weeks.

This guide walks through every formula with one worked example: a bamboo cutting-board set that sells about 13.6 units a day from an overseas supplier with a 45-day lead time. Every formula is given in Excel and Google Sheets syntax, and you can check each number with the free reorder point calculator.

The four inputs you need

Everything below is built from four numbers per SKU:

Symbol Meaning Example
d Average daily demand (units) 13.6
σd Standard deviation of daily demand 4.2
LT Average supplier lead time, order to shelf (days) 45
σLT Standard deviation of lead time (days) 10

Plus one decision: the service level, the probability that you do not run out during one replenishment cycle. We'll use 95%.

Lead time means order placed to stock available to sell. Include production, transit, customs clearance and receiving, not just the quoted ocean transit.

Step 1: Demand and its variability from weekly sales

Most shops and ERPs export weekly unit sales. With 26 weeks of sales in B2:AA2:

Average daily demand  d  = AVERAGE(B2:AA2)/7
Daily std dev        σd  = STDEV(B2:AA2)/SQRT(7)

Dividing the weekly standard deviation by √7 converts it to a daily figure, assuming days are independent. A weekly standard deviation of 11.1 units becomes 11.1 ÷ 2.646 = 4.2 units per day.

Use at least 8 weeks of history, and ideally 26. Leave stock-out weeks blank instead of entering zero: a week with zero sales because you had nothing to sell is not real demand, and it drags the average down and inflates σd. AVERAGE and STDEV both skip blank cells.

Step 2: Turn the service level into z

The service factor z is the number of standard deviations of cover you hold:

z = NORMSINV(0.95)        → 1.645

NORM.S.INV is the newer name in Excel; Google Sheets accepts both.

Service level z Stock-out risk per cycle
90% 1.282 1 in 10
95% 1.645 1 in 20
98% 2.054 1 in 50
99% 2.326 1 in 100
99.9% 3.090 1 in 1,000

Notice how z climbs faster near the top. That is why "let's just go to 99.9%" is expensive (see Step 7).

Step 3: Safety stock, three formulas

There are three common safety stock formulas. Which one fits depends on what varies.

Demand varies, lead time is fixed:

SS = z × σd × √LT  = 1.645 × 4.2 × √45 = 46.3  → 47 units

Lead time varies, demand is steady:

SS = z × d × σLT   = 1.645 × 13.6 × 10 = 223.7 → 224 units

Both vary (use this one):

SS = z × √(LT × σd² + d² × σLT²)
   = 1.645 × √(45 × 4.2² + 13.6² × 10²)
   = 1.645 × 138.9 = 228.5 → 229 units

In Excel, with the inputs in B6:B10:

=ROUNDUP(NORMSINV(B10)*SQRT(B8*B7^2+B6^2*B9^2),0)

This example teaches the most important lesson in the guide. Demand noise alone needs only 47 units. Supplier unreliability needs about 224. For importers, the biggest safety stock lever is usually a more reliable supplier or a buffer in the quoted lead time, not a better forecast. If you only use the demand-only formula, you will be badly under-protected.

To measure σLT, take your last 5–10 POs from that supplier and compute STDEV of the actual days from order to receipt.

Step 4: The reorder point

Lead-time demand = d × LT        = 13.6 × 45 = 612
Reorder point    = d × LT + SS   = 612 + 229 = 841 units

Excel: =ROUNDUP(B6*B8+E7,0).

When your stock on hand plus on order reaches 841, place the next purchase order. Using on-hand alone is a classic mistake: it double-orders as soon as an open PO is in transit.

Step 5: Inventory position, cover and the order-by date

Inventory position = on hand + on order          = 820 + 0 = 820
Days of cover      = on hand ÷ d                 = 820 ÷ 13.6 = 60 days
Order-by date      = today + (position − ROP) ÷ d

With 820 units against a reorder point of 841, the position is already below the trigger. The order-by date is today. Without an order, the shelf would be empty in about 60 days, but the next delivery takes 45 days on average and can take 65 or more.

For a SKU that is not yet at its reorder point, the same formula tells you how many days you have left. That is exactly what you want in a weekly buying meeting: a list sorted by order-by date.

Excel: =TODAY()+MAX(0,(E10-E9)/B6), formatted as a date.

Step 6: How much to order (EOQ, MOQ and case packs)

The reorder point tells you when. The economic order quantity tells you how much by balancing the cost of placing orders against the cost of holding stock:

EOQ = √(2 × annual demand × cost per PO ÷ (holding % × unit cost))
    = √(2 × 4,964 × 85 ÷ (25% × 6.55)) = 718 units

Annual demand is 13.6 × 365 = 4,964. Cost per PO covers the admin, inspection, freight booking and receiving cost of one order. Holding cost is typically 20–30% of unit cost per year (capital, storage, insurance, shrink).

Then apply the supplier's rules. Order enough to get back above the reorder point plus one EOQ, at least the MOQ, rounded up to a full case:

Suggested order = ROUNDUP(MAX(MOQ, ROP + EOQ − position) / case, 0) × case
                = ROUNDUP(MAX(500, 841 + 718 − 820) / 20, 0) × 20
                = ROUNDUP(739 / 20, 0) × 20 = 740 units

At $6.55 a unit that is a $4,847 PO, placed today.

Step 7: What service level costs

Safety stock is cash sitting on a shelf. For this SKU:

Service level Safety stock Cash at $6.55
90% 179 $1,172
95% 229 $1,500
98% 286 $1,873
99% 324 $2,122

Going from 95% to 99% adds 41% more safety stock. Across a 14-SKU sample catalogue in the Cellwright planner, the same step takes safety-stock cash from $9,619 to $13,604, and 99.9% needs $18,071. A common approach is 95% for most items and 98–99% for A-class best sellers, where a stock-out loses the customer and not just the sale.

Step 8: Flag what needs action

Once every SKU has these numbers, a simple status column (first match wins) turns a spreadsheet into a buying list:

Status Rule Action
STOCKOUT On hand = 0 Order and expedite
BELOW SAFETY On hand < safety stock Order today, consider air freight
ORDER NOW Position ≤ reorder point Place the PO
WATCH Order-by date within 14 days Prepare the PO
OVERSTOCK Cover > 120 days Pause orders, consider promotion
OK none of the above Nothing

In Excel:

=IF(O5<=0,"STOCKOUT",IF(O5<L5,"BELOW SAFETY",IF(Q5<=N5,"ORDER NOW",
 IF(T5-TODAY()<=14,"WATCH",IF(R5>120,"OVERSTOCK","OK")))))

Common mistakes

  1. Using on hand instead of inventory position. This leads to duplicate POs while stock is on the water.
  2. Ignoring lead-time variability. As Step 3 showed, it can be four times the demand effect for overseas suppliers.
  3. Counting stock-out weeks as zero demand. This understates d and inflates σd. Leave those weeks blank.
  4. One service level for everything. Rank SKUs ABC by annual value and protect the A items more.
  5. Applying the normal formula to lumpy items. If a SKU sells 0 units most weeks and 40 occasionally, use a manual minimum.
  6. Never updating the inputs. Recalculate monthly, and immediately when a supplier's lead time changes.

Google Sheets notes

Every formula above works unchanged in Google Sheets: AVERAGE, STDEV, NORMSINV, SQRT, ROUNDUP, MAX. Make sure your date cells are formatted as dates, and use TODAY() for the planning date if you want the order-by dates to move each day.

Scaling from one SKU to the whole catalogue

One SKU is easy to work by hand. Forty SKUs from five suppliers, each with its own lead time, MOQ and case pack, is where spreadsheets usually fall apart.

The Cellwright Reorder Point & Safety Stock Planner handles all of the above for 40 SKUs:

Prefer to start small? The free one-tab Excel version implements this guide for a single SKU.

FAQ

Is reorder point the same as safety stock? No. Safety stock is the buffer; the reorder point is lead-time demand plus that buffer.

How often should I recalculate? Monthly for steady items, and whenever a supplier changes lead time or a SKU's sales trend shifts.

What if I have no lead-time history? Start with σLT at about 20% of the quoted lead time for overseas suppliers, then replace it with measured data after a few POs.

Can I use weekly instead of daily units? Yes, as long as every input uses the same time unit. Daily units make the order-by date easy to read.

Importing stock? Work out your true cost per unit with the free Landed Cost & Tariff Calculator.