Prevent Stockouts With a Reorder Point Spreadsheet

A stockout isn’t just a missed sale. It’s a customer who ordered from your competitor, possibly for the last time. For small businesses where every customer relationship matters, running out of product is an expensive problem — and it’s almost always preventable with a little math.

Here’s how to build a spreadsheet that tells you exactly when to reorder, before you’re scrambling.

The Three Numbers You Need

Every reorder system is built on three inputs:

1. Average Daily Usage (ADU) How many units do you sell per day on average? Pull your last 90 days of sales and divide total units by 90. For seasonal products, use the relevant season’s data.

ADU = Total Units Sold ÷ 90 days

2. Lead Time How many days does it take from the moment you place an order to when the inventory arrives and is ready to sell? Include processing time, shipping, and your own receiving/inspection process. Track this per supplier — it varies.

3. Safety Stock The buffer you hold to absorb variability in demand or supplier delays. Without it, your reorder point assumes everything goes perfectly, and things don’t always go perfectly.

Safety Stock = (Max Daily Usage - Avg Daily Usage) × Max Lead Time

Max Daily Usage is your peak single-day sales in the last 90 days. Max Lead Time is the worst-case delivery you’ve experienced from that supplier.

Calculating the Reorder Point

Once you have these numbers, the reorder point formula is:

Reorder Point = (ADU × Lead Time) + Safety Stock

This tells you the inventory level at which you must place a new order. If you have 150 units in stock and your reorder point is 120, you’re safe. The moment your stock count hits 120, you place the order.

Example:

• ADU: 15 units/day
• Lead Time: 7 days
• Safety Stock: 25 units (based on peak day of 18 units, max lead time of 10 days)

Reorder Point = (15 × 7) + 25 = 130 units

Building the Spreadsheet

Create one row per SKU with these columns:

Column	Formula / Input
SKU / Product Name	Manual entry
Current Stock	Update weekly or sync from POS
Avg Daily Usage (90-day)	=Units Sold / 90
Max Daily Usage	=MAXIFS(daily_sales, product, A2)
Lead Time (days)	Manual entry per supplier
Max Lead Time (days)	Manual entry (historical worst case)
Safety Stock	=(F2-C2)*G2
Reorder Point	=(C2*E2)+H2
Reorder Quantity	Manual entry (your preferred order size)
Status	=IF(B2<=I2,"ORDER NOW","OK")

The Status column is your daily checklist. Filter to “ORDER NOW” every morning and those are the products you need to buy today.

Apply conditional formatting to the Status column: red background for “ORDER NOW” so it’s impossible to miss.

Supplier Lead Time Tracking

Lead time is the most underestimated variable in reorder planning. Most businesses assume a fixed number, but real lead times vary. Add a separate Supplier Tracking tab with:

Column	What to Track
Supplier Name
Product / SKU
Order Date
Expected Arrival
Actual Arrival
Lead Time (days)	=Actual Arrival - Order Date
Variance	=Lead Time - Expected Lead Time

After 5-10 orders per supplier, you’ll have real data. Calculate the average and maximum lead times:

• =AVERAGEIF(Supplier, "SupplierName", LeadTime)
• =MAXIFS(LeadTime, Supplier, "SupplierName")

Feed these back into your main reorder calculator. If a supplier’s average lead time creeps from 7 days to 9 days, your reorder point should go up automatically.

Handling Variable Demand

For products with highly variable demand — seasonal items, products you promote heavily, items tied to other businesses’ needs — the static ADU method can fail. A few adjustments:

Weighted average demand: Give more weight to recent weeks. Last 4 weeks get 50% weight, weeks 5-8 get 30%, weeks 9-13 get 20%. This makes your ADU respond faster to demand shifts.

Seasonal demand multiplier: If you know January demand is typically 60% of your annual average, adjust your ADU down in November-December (ordering for January stock) accordingly. Use your historical monthly index from your seasonal analysis.

Promotional stock: When you’re running a sale or promotion, calculate expected demand lift and order extra before the promo starts. This is a judgment call, but a spreadsheet makes it easy to model: if your promo last quarter lifted sales 40%, order 40% more product in advance of the next one.

What a Healthy Reorder System Looks Like in Practice

You open your spreadsheet Monday morning. Two products show “ORDER NOW.” You check both: one is genuinely low, one was a data entry error (you got a delivery Friday and forgot to update the count). You fix the count, place one order, and you’re done in 10 minutes.

That’s what this is supposed to feel like. Not reactive panic, not constant stockouts, not over-ordering that ties up cash. Just a system that tells you what to do and when.

Next Step

Pick your top 10 fastest-moving SKUs. Pull 90 days of sales data. Calculate ADU and reorder points for all 10 using the formula above. Check current stock levels against your calculated reorder points. Anything below the threshold — order today. Then set a calendar reminder to check stock levels against reorder points every Monday morning.