Reorder point calculation is one of the most important things you can automate in Excel. Get it right and you never run out of stock. Get it wrong — or don't do it at all — and you're always scrambling to place rush orders at premium prices.
The Reorder Point Formula
In Excel, with your data in columns:
Understanding Each Component
Average Daily Sales
The average number of units sold per day, typically calculated over the last 30-90 days. Calculate it from your sales data:
Lead Time
The number of days between placing an order and receiving it. Include supplier processing time AND shipping time. If your supplier says "5 business days," that's 7 calendar days. Add a buffer of 1-2 days for reliability.
Safety Stock
A buffer to protect against demand spikes and supplier delays. The simplest formula:
Building a Reorder Report for All SKUs
Set up your Item Master sheet
One row per SKU with columns: SKU, Item Name, Avg Daily Sales, Lead Time, Max Daily Sales, Safety Stock, Reorder Point.
Calculate Reorder Point for each SKU
In the Reorder Point column: =(C2*D2)+E2. Drag down for all SKUs.
Add a Status column
=IF(QtyOnHand=0,"Out of Stock",IF(QtyOnHand<ReorderPoint,"Reorder Now","OK"))
Add a Priority column
=IF(QtyOnHand=0,"🔴 URGENT",IF(QtyOnHand/ReorderPoint<0.5,"🟡 HIGH","🟢 NORMAL"))
Filter to see what needs ordering
Filter the Status column to show only "Reorder Now" and "Out of Stock". This is your daily purchase list.
Adjusting for Seasonal Demand
If your sales vary by season, use a seasonal average rather than a 90-day average. For a product that sells 3x more in December:
- Calculate average daily sales for each month separately
- Use the upcoming month's average, not the trailing average
- Review and update reorder points monthly during peak season
FAQ
🔁 Get the Pre-Built Reorder Report Template
Free download — no sign-up required.
⬇ Download Free Template