Reorder Point Formula: How to Calculate It in Excel

Running out of a best seller costs you sales. Ordering too early ties up cash and shelf space. The reorder point sits between the two: it is the stock level at which you should place a new order so that the delivery arrives before you run out.

This guide covers the formula, how to get each input from your own data, three ways to set safety stock, and a complete Excel setup with alerts and suggested order quantities.

The reorder point formula

Reorder point (ROP) = Average daily usage × Lead time (days) + Safety stock

• Average daily usage — how many units you sell or use on a typical day.
• Lead time — the number of days from placing an order to having the stock on the shelf, ready to sell. Include the days it takes you to place the order, the supplier to ship and you to receive and put it away.
• Safety stock — a buffer for days when sales run higher or the delivery runs late.

The first part, usage × lead time, is the stock you expect to sell while waiting for the delivery. Safety stock covers the surprises. When on-hand stock falls to the reorder point, you order.

A quick example

You sell about 12 units a day of a phone case. Your supplier takes 7 days from order to delivery. You keep 30 units of safety stock.

ROP = 12 × 7 + 30 = 114 units

When on-hand drops to 114, place the order. If sales stay average, you will have
about 30 units left when the new stock arrives.

Getting the inputs from your data

Average daily usage

Pull sales history from your POS or marketplace as a list of dates, SKUs and quantities, and put it in an Excel table named Sales. Then calculate usage for the last 30 or 90 days:

Average daily usage, last 90 days (SKU in A2):
=SUMIFS(Sales[Qty],Sales[SKU],A2,Sales[Date],">="&TODAY()-90)/90

Pick a window that fits the item. A longer window is more stable; a shorter one reacts faster to trends. For seasonal products, use the same season from last year rather than the last 90 days.

For ingredients and supplies you use rather than sell, usage comes from purchases and counts instead: beginning count + received − ending count, divided by the days between counts. Received quantities come straight from supplier invoices — the invoice to inventory spreadsheet converter gives you qty received and unit per line, so you can SUMIFS them by item.

Lead time

Don't use the lead time the supplier quotes; use the one you actually experience. Log order date and received date for each purchase order, then average:

Lead time per PO (D2):          =C2-B2      (received date − order date)
Average lead time by supplier:   =AVERAGEIFS(POs[Lead Days],POs[Supplier],"Acme Supply")
Longest lead time by supplier:   =MAXIFS(POs[Lead Days],POs[Supplier],"Acme Supply")

If your purchase orders are PDFs or paper, the purchase order to Excel converter extracts PO number, date, supplier and line items so you can build this log without retyping.

Three ways to set safety stock

Safety stock is the part people overthink. Start simple and refine only for items where stockouts are expensive.

Method 1: Fixed number of days

Hold a set number of days of extra usage. Easy to explain and good enough for most small businesses.

Safety stock = Average daily usage × Safety days
Example: 12 units/day × 3 days = 36 units
Excel:   =B2*E2

Method 2: Max minus average

Uses your own worst-case history: the stock you would need on a bad day with a slow delivery, minus what you would need on an average one.

Safety stock = (Max daily usage × Max lead time) − (Avg daily usage × Avg lead time)
Example: (20 × 10) − (12 × 7) = 200 − 84 = 116 units
Excel:   =(F2*G2)-(B2*C2)

This method is conservative. One freak sales day in your history inflates it, so check the max usage figure looks realistic before trusting the result.

Method 3: Standard deviation (service level)

If daily sales vary a lot and lead time is fairly stable, you can size safety stock to a target service level — the chance of not running out during a lead time:

Safety stock = Z × σ(daily usage) × √(Lead time)

Z for a chosen service level:   =NORM.S.INV(0.95)      → about 1.65
σ of daily usage (range of daily totals):   =STDEV.S(DailyQty)
Excel (Z in H2, σ in I2, lead time in C2):   =ROUNDUP(H2*I2*SQRT(C2),0)

Example: 1.65 × 4 × √7 ≈ 17.5  → 18 units

This assumes daily demand is roughly normally distributed and lead time is constant, which is a simplification. For slow-moving items that sell zero most days, Method 1 is usually more sensible.

Putting it together in Excel

A reorder sheet with one row per SKU:

With Avg daily in B, Lead days in C, Safety stock in D, ROP in E, On hand in F, On order in G and a review period of 14 days in $K$1:

Safety stock (D2):     =ROUNDUP(B2*3,0)                       (3 safety days)
Reorder point (E2):    =ROUNDUP(B2*C2+D2,0)
Status (H2):           =IF(F2<=0,"OUT",IF(F2+G2<=E2,"REORDER",IF(F2<=E2,"ON ORDER","OK")))
Order up to (target):  =ROUNDUP(E2+B2*$K$1,0)
Suggested order (I2):  =IF(H2="REORDER",MAX(0,ROUNDUP(E2+B2*$K$1,0)-F2-G2),IF(H2="OUT",MAX(0,ROUNDUP(E2+B2*$K$1,0)-G2),0))

The status formula compares on hand plus on order to the reorder point, so an item you already ordered doesn't get ordered twice. The suggested quantity tops you up to the reorder point plus one review period of usage, so you aren't placing a new order every few days. Round up to the supplier's case pack if needed:

Round to case pack of 24 (pack size in J2):   =CEILING.MATH(I2,J2)

Conditional formatting for alerts

Select the data rows (for example A2:I200), then Home > Conditional Formatting > New Rule > Use a formula, and add one rule per status:

Out of stock (red fill):        =$F2<=0
Needs reorder (amber fill):     =AND($F2>0,$F2+$G2<=$E2)
Below ROP, on order (blue):     =AND($F2>0,$F2<=$E2,$F2+$G2>$E2)

Put the red rule first in the rule manager so it wins when more than one applies. Add a filter on the Status column and your purchase list for the week is one click away.

Reorder point vs. par level

Restaurants and bars often use par levels instead: on a fixed delivery schedule, you order whatever brings each item back up to par. That is essentially an order-up-to target with a fixed review period, and it fits businesses that get deliveries on set days. Reorder points fit businesses that can order any day and want to order only when needed. See restaurant inventory and food cost for the par method.

Keep it current

• Recalculate average usage monthly, or weekly for fast movers.
• Update lead times whenever a supplier changes or a shipment is late.
• Make sure on-hand numbers are trustworthy — reorder points are only as good as the count. Our guide on how to do an inventory count covers cycle counting.
• Review safety stock after every stockout and after any item sits well above its reorder point for months.

Larger catalogs with several suppliers may prefer the warehouse inventory template or the wholesale distributor template, which add supplier and lead time columns per SKU.