How to Calculate Inventory Reorder Point and Safety Stock in Excel & Google Sheets

Stockouts result in lost revenue and dissatisfied customers, while excess inventory ties up cash in carrying costs. Finding the exact replenishment balance requires calculating the Reorder Point (ROP) and Safety Stock (Buffer).

This guide provides both the standard deterministic formula and the statistical normal distribution formula using NORMSINV.

---

1. The Core Inventory Replenishment Equations

$$\text{Reorder Point (ROP)} = \text{Lead Time Demand} + \text{Safety Stock}$$

$$\text{Lead Time Demand} = \text{Average Daily Demand} \times \text{Supplier Lead Time (Days)}$$

$$\text{Safety Stock} = Z \times \sqrt{\text{Lead Time}} \times \sigma_d$$

Where:

• $Z$ (Z-Score): Statistical factor for desired service level (NORMSINV(Service_Level)).
• $\sigma_d$ (Standard Deviation): Daily sales volatility (STDEV.S(...)).
• $\text{Lead Time}$: Number of days from placing a PO until goods hit the warehouse.

---

2. Statistical Safety Stock Formula

In Excel and Google Sheets:

=NORMSINV(Service_Level) * SQRT(Lead_Time_Days) * Demand_StdDev

Common Service Levels and Z-Scores:

• 90% Service Level: =NORMSINV(0.90) $\rightarrow$ 1.282
• 95% Service Level (Standard): =NORMSINV(0.95) $\rightarrow$ 1.645
• 98% Service Level: =NORMSINV(0.98) $\rightarrow$ 2.054
• 99% Service Level (Critical SKUs): =NORMSINV(0.99) $\rightarrow$ 2.326

---

3. Step-by-Step E-Commerce Inventory Model

Let’s model an e-commerce brand restocking best-selling wireless headphones:

Cell	Metric	Value	Formula / Source
B1	Average Daily Sales (Units)	45	=AVERAGE(Sales_Last_60_Days)
B2	Daily Demand Std Deviation ($\sigma_d$)	12	=STDEV.S(Sales_Last_60_Days)
B3	Supplier Lead Time (Days)	14	Factory lead time + transit
B4	Target Service Level	95.0%	Probability of avoiding stockout
B5	Safety Stock (Buffer Units)	74	=ROUNDUP(NORMSINV(B4) * SQRT(B3) * B2, 0)
B6	Lead Time Demand (Units)	630	=B1 * B3 (45 * 14)
B7	Reorder Point (ROP)	704	=B6 + B5

What this means:

Whenever the physical warehouse balance drops to or below 704 units, place a new purchase order immediately.

---

4. Multi-SKU Master Inventory Dashboard

Build a live tracking sheet across multiple products:

SKU (A)	Product Name (B)	Daily Demand (C)	Lead Time (D)	On-Hand Stock (E)	Safety Stock (F)	Reorder Point (G)	Status & Action (H)
SKU-101	Pro Wireless Mouse	30	10	240	48	348	=IF(E2<=G2, "🔴 REORDER", "🟢 OK")
SKU-102	Ergonomic Keyboard	18	21	520	62	440	=IF(E3<=G3, "🔴 REORDER", "🟢 OK")
SKU-103	4K Webcam Pro	25	14	310	55	405	=IF(E4<=G4, "🔴 REORDER", "🟢 OK")
SKU-104	USB-C Docking Hub	50	7	890	52	402	=IF(E5<=G5, "🔴 REORDER", "🟢 OK")

Live Dashboard Results:

• SKU-101: On-hand: 240 $\le$ ROP 348 $\rightarrow$ 🔴 REORDER (Place PO for supplier)
• SKU-102: On-hand: 520 $>$ ROP 440 $\rightarrow$ 🟢 OK
• SKU-103: On-hand: 310 $\le$ ROP 405 $\rightarrow$ 🔴 REORDER
• SKU-104: On-hand: 890 $>$ ROP 402 $\rightarrow$ 🟢 OK

---

5. Adding Economic Order Quantity (EOQ)

To calculate how many units to order per batch to minimize holding and ordering costs:

=SQRT((2 * Annual_Demand * Order_Setup_Cost) / Annual_Holding_Cost_Per_Unit)

• Example: Annual Demand = 16,425, Order Cost = $50, Holding Cost = $4/unit/yr:

  =SQRT((2 * 16425 * 50) / 4)

  Optimal Batch Size (EOQ) = 641 units.