Expert guide

Reorder Point Formula in Excel

Order at the right time, in the right quantity, based on your own demand and supplier lead times

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: from sales history, calculate average daily demand and its standard deviation per item; from receipts, calculate average lead time. Safety stock = NORM.S.INV(service level) × σ(daily demand) × √(lead time). Reorder point = average daily demand × lead time + safety stock. Compare it with inventory position (on hand + on order − allocated) and order when position falls to or below it, in a quantity based on EOQ rounded to pack size. For multi-user stock control, see Microsoft Access inventory management.

Stockouts and overstock are the same problem seen from opposite ends: orders placed at the wrong time in the wrong quantity. Most small businesses reorder on gut feel or a fixed minimum someone set years ago. A reorder point calculated from your own history adapts to each item: fast sellers with long lead times get large buffers, slow movers with local suppliers get small ones.

The Data You Need

DataSourceUsed for
Daily sales or usage per itemPOS, ERP or invoicing exportAverage demand and variability
Purchase order dates and receipt datesPurchasing recordsLead time and its variability
On hand, on order, allocatedInventory systemInventory position
Pack size, MOQ, unit costSupplier price listOrder quantity and value
Ordering cost and holding cost %Finance estimateEOQ

Step 1: Demand Statistics per Item

Build a daily demand table with one row per item per day, including zero-sales days. Without the zeros, average demand is overstated for slow movers.

Average daily demand (last 90 days):
=AVERAGEIFS(tblDaily[Qty], tblDaily[Item], [@Item], tblDaily[Date], ">=" & TODAY() - 90)

Standard deviation of daily demand:
=STDEV.S(FILTER(tblDaily[Qty], (tblDaily[Item] = [@Item]) * (tblDaily[Date] >= TODAY() - 90)))

A pivot of daily sales from transactions, or Power Query's "fill missing dates" pattern, creates the daily table with zeros.

Step 2: Lead Time per Supplier or Item

Lead time per PO line: =ReceivedDate - OrderDate

Average lead time (days):
=AVERAGEIFS(tblPOs[LeadDays], tblPOs[Item], [@Item], tblPOs[ReceivedDate], ">=" & TODAY() - 365)

Lead time standard deviation:
=STDEV.S(FILTER(tblPOs[LeadDays], (tblPOs[Item] = [@Item]) * (tblPOs[ReceivedDate] >= TODAY() - 365)))

Step 3: Safety Stock

Z for the service level (e.g. 95% in a Settings cell):
=NORM.S.INV(Settings!ServiceLevel)        → 1.645

Demand variability only:
=ROUNDUP(Z * [@DemandSD] * SQRT([@LeadDays]), 0)

Demand and lead time variability combined:
=ROUNDUP(Z * SQRT([@LeadDays] * [@DemandSD]^2 + [@AvgDemand]^2 * [@LeadSD]^2), 0)

Higher service levels cost more stock disproportionately. Going from 95 to 99 percent raises Z from about 1.64 to 2.33, roughly 40 percent more safety stock. Set service levels by item class: A items high, C items lower.

Step 4: Reorder Point and Position

Reorder point:
=ROUNDUP([@AvgDemand] * [@LeadDays] + [@SafetyStock], 0)

Inventory position:
=[@OnHand] + [@OnOrder] - [@Allocated]

Reorder now?
=[@Position] <= [@ReorderPoint]

Step 5: How Much to Order

EOQ:
=SQRT(2 * [@AvgDemand] * 365 * Settings!OrderCost /
      ([@UnitCost] * Settings!HoldingRate))

Order quantity (cover to reorder point + EOQ, rounded to packs, at least MOQ):
=IF([@[Reorder now?]],
    MAX([@MOQ], CEILING.MATH([@ReorderPoint] - [@Position] + [@EOQ], [@PackSize])),
    0)

EOQ is a guide, not a rule. Shelf life, storage space, price breaks and cash flow often override it. Many businesses use EOQ to sense-check a simpler "weeks of cover" rule.

Step 6: The Daily Reorder List

=SORT(FILTER(tblItems[[Supplier]:[OrderQty]],
       tblItems[OrderQty] > 0, "Nothing to reorder"), 1, 1)

Grouped by supplier, it becomes a set of draft purchase orders. Add order value per supplier to check minimum order values for free freight.

ABC Classification

ClassTypical definitionPolicy
AItems making up ~80% of annual valueHigh service level, review weekly
BNext ~15%Medium service level, review fortnightly
CRemaining ~5%Lower service level, larger, less frequent orders
Annual value:      =[@AvgDemand] * 365 * [@UnitCost]
Cumulative share:  sort by annual value descending, then running sum ÷ total
Class:             =IFS([@CumShare] <= 0.8, "A", [@CumShare] <= 0.95, "B", TRUE, "C")

Where AI and Forecasting Fit

For seasonal items, replace the 90-day average with a forecast for the lead-time window; see sales forecasting with FORECAST.ETS. AI can summarise the week's reorder list, flag items whose demand changed sharply, and draft supplier emails. Kitchens and bars use a simpler par-level version, covered in restaurant par levels in Excel.

When Excel Stops Being Enough

If on-hand stock is updated by several people, or receipts and picks need barcode scanning, a shared database keeps inventory position accurate in real time. See our warehouse inventory Access database and Access inventory tracker; the reorder logic above carries straight over into queries.

FAQ

Reorder point FAQ

Safety stock, lead time and order quantity

Reorder point = average daily demand × lead time in days + safety stock. When on-hand plus on-order stock falls to that level, you place an order. The safety stock covers variation in demand and supplier lead time.

A common statistical formula is Safety stock = Z × σ(daily demand) × √(lead time days), where Z comes from the service level you want, for example =NORM.S.INV(0.95) for 95 percent. A combined version also accounts for lead-time variability. Simpler rules, such as a fixed number of days of cover, also work when data is limited.

Use inventory position: on hand plus on order minus allocated to existing customer orders. Comparing only on-hand stock to the reorder point causes duplicate orders while a purchase order is in transit.

The economic order quantity (EOQ) formula, √(2 × annual demand × cost per order ÷ annual holding cost per unit), balances ordering and holding costs. In practice, round to supplier pack sizes and minimum order quantities, and cap by storage space and shelf life.

Monthly for most items, and immediately when lead times or demand patterns change, such as a new supplier or a seasonal peak. Seasonal items may need separate parameters by season.