The $50,000 Stockout That Could Have Been Prevented
Your bestselling product went out of stock on Monday. By the time new inventory arrived Thursday, you'd lost 127 orders. At $395 average order value, that's $50,165 in lost revenue.
The worst part? You had sales data showing you'd hit reorder point two weeks ago. You just didn't calculate it.
Manual inventory management doesn't scale. "Reorder when it looks low" doesn't work when you have 50+ SKUs. You need a system.
Reorder points are that system. They tell you exactly when to place your next order, automatically accounting for lead time, sales velocity, and demand variability.
Let's build it in Excel.
The Science of Reorder Points
A reorder point is the inventory level that triggers a new purchase order. Hit that level, order more. Simple.
But calculating the right level requires understanding three components:
1. Average Daily Sales Rate
How many units you sell per day on average.
Formula: Total Units Sold ÷ Number of Days
Example: 450 units sold in 90 days = 5 units/day
2. Lead Time
Days from when you place an order until inventory arrives and is ready to sell.
Includes: Order processing + manufacturing + shipping + receiving
Example: 3 days to process + 12 days shipping + 2 days receiving = 17 days total lead time
3. Safety Stock
Buffer inventory to protect against:
- Demand spikes (suddenly selling 10 units/day instead of 5)
- Supply delays (shipment takes 20 days instead of 17)
- Seasonal variability
Formula: Safety Stock = (Max Daily Sales × Max Lead Time) - (Average Daily Sales × Average Lead Time)
Or use the statistical method: Safety Stock = Z-score × Standard Deviation of Daily Sales × √Lead Time
The Complete Reorder Point Formula
Reorder Point = (Average Daily Sales × Lead Time) + Safety Stock
For our example:
- Average Daily Sales: 5 units/day
- Lead Time: 17 days
- Safety Stock: 15 units (calculated)
Reorder Point = (5 × 17) + 15 = 100 units
When inventory hits 100 units, place your next order.
Building Your Reorder Point Calculator in Excel
-
3
Create Your Product Master Table
Set up columns: SKU, Product Name, Current Stock, COGS, Supplier, Average Lead Time (days). This is your foundation. Export from Shopify or build manually.
-
3
Add Lead Time Data
Create a lead time column. If unknown, start with estimates: domestic suppliers 10-14 days, international 20-30 days. Track actual lead times from POs and update monthly.
-
3
Calculate Safety Stock (Simple Method)
Safety Stock = Daily Sales Rate × (Maximum Lead Time - Average Lead Time). If average is 17 days but max ever was 25 days, safety stock = 5 units/day × 8 days = 40 units.
-
3
Calculate Safety Stock (Statistical Method)
For variable demand: Safety Stock = 1.65 × STDEV(daily_sales_range) × SQRT(lead_time). The 1.65 factor gives 95% service level. Increase to 2.33 for 99% service level.
-
3
Calculate Final Reorder Point
Reorder Point = (Daily Sales Rate × Lead Time) + Safety Stock. Add a column: =B2*C2+D2. When Current Stock ≤ Reorder Point, trigger alert.
-
3
Add Conditional Formatting Alerts
Highlight cells where Current Stock < Reorder Point in red. Yellow when within 20% of reorder point. Green when well-stocked. This gives you at-a-glance inventory health.
-
3
Calculate Economic Order Quantity (EOQ)
EOQ determines how much to order. Formula: √((2 × Annual Demand × Order Cost) / Holding Cost per Unit). Balances ordering costs vs. storage costs. Order this amount when you hit reorder point.
Economic Order Quantity: How Much to Order
Knowing when to reorder is half the battle. The other half is knowing how much.
The EOQ Formula
EOQ = √((2 × D × S) / H)
Where:
- D = Annual Demand (units per year)
- S = Cost per Order (shipping + admin + processing)
- H = Holding Cost per Unit per Year (storage + insurance + opportunity cost)
Real Example
Product: Coffee Mug
- Annual Demand: 1,825 units (5/day × 365)
- Order Cost: $50 per shipment
- Holding Cost: $2 per unit per year (storage fees)
EOQ = √((2 × 1825 × 50) / 2) = √(182,500 / 2) = √91,250 ≈ 302 units
Order 302 units each time you hit your reorder point.
The Total Cost Check
With EOQ, you'll order about 6 times per year (1,825 / 302).
- Ordering Costs: 6 orders × $50 = $300/year
- Holding Costs: (302/2) × $2 = $302/year
- Total: ~$600/year
Compare this to ordering 50 units at a time (37 orders/year):
- Ordering Costs: 37 × $50 = $1,850/year
- Holding Costs: (50/2) × $2 = $50/year
- Total: $1,900/year
EOQ saves you $1,300/year on this one SKU.
Stop manual reorder point calculations. Get automated inventory reports with reorder alerts built-in.
Automate Inventory TrackingNever stock out again
Common Mistakes to Avoid
Learn from these common pitfalls to ensure success
Using total sales instead of SKU-specific sales to calculate reorder points
Solution: Every SKU needs its own reorder point. A t-shirt and a watch have completely different sales velocity and lead times.
Not updating reorder points when sales velocity changes
Solution: Recalculate quarterly. A product going viral needs a higher reorder point immediately, not next quarter.
Forgetting to account for supplier minimum order quantities (MOQs)
Solution: If your EOQ is 302 but supplier MOQ is 500, order 500. Adjust safety stock to account for the overage.
Setting reorder point based on supplier lead time, not total lead time
Solution: Add receiving time, quality checks, and Shopify restocking time. 12-day shipping becomes 17-day total lead time.
Using same safety stock multiplier for all products
Solution: High-variability products need higher safety stock. Stable sellers need less. Analyze demand variability per SKU.
How to Verify Your Numbers
Ensure accuracy with these verification steps
Your reorder point for at least one SKU has prevented a stockout in the last 30 days
You can explain why your reorder point is X units (daily sales × lead time + safety stock)
Your EOQ calculation shows lower total cost than your current ordering pattern
You've caught and fixed at least one product with incorrect reorder point
You have conditional formatting showing reorder alerts automatically
Frequently Asked Questions
What if I can't get 90 days of sales history for a new product?
Should I use the same safety stock for all products?
My supplier's lead time varies wildly. How do I handle that?
What if my product has lumpy demand (big orders sporadically)?
How do I handle products in multiple warehouses?
Ready to Transform Your Reporting?
Start with a free report and see exactly what you'll get every time.