An inventory reorder point spreadsheet gives you a repeatable way to decide when to buy more stock before you run out. Instead of relying on instinct, you can combine average demand, supplier lead time, safety stock, and an acceptable level of stockout risk into a simple Excel or Google Sheets model. The result is practical: a clearer reorder trigger for each SKU, fewer emergency purchases, and a spreadsheet you can revisit whenever sales patterns or supplier performance change.
Overview
If you manage inventory in a small business, operations role, or growing team, reorder decisions often become inconsistent long before they become formal. One person orders early because they remember the last stockout. Another delays because cash is tight. A supplier says lead time is "usually" two weeks, but sometimes it stretches further. Over time, those small judgment calls create excess stock in some items and shortages in others.
An inventory reorder point spreadsheet helps standardize that decision. At its simplest, the reorder point is the inventory level where you should place a new order. A useful spreadsheet does more than store a formula. It should show the assumptions behind the reorder trigger, make service-level tradeoffs visible, and let you test what happens when demand or lead time changes.
For most teams, the core logic is:
Reorder Point = Demand During Lead Time + Safety Stock
That equation is simple enough to maintain in a spreadsheet and flexible enough to improve over time. You can begin with average daily or weekly demand, then add variability measures once you have better history. You can also add extra fields such as order quantity, supplier minimums, and unit cost, but the reorder point itself should stay easy to audit.
This article focuses on building a practical model in Excel or Google Sheets. The aim is not to create a mathematically elaborate inventory system. It is to create a reliable working file that can support routine purchasing decisions and be updated as your business changes.
How to estimate
To build a reorder point model, start with one item or SKU per row and a small set of inputs in columns. Keep raw inputs separate from formulas so the sheet stays readable. A good starting layout looks like this:
- SKU
- Product name
- Average demand per day or per week
- Lead time in days or weeks
- Demand variability measure
- Lead time variability measure
- Target service level
- Safety stock
- Demand during lead time
- Reorder point
- Current on-hand inventory
- On-order inventory
- Reorder flag
If you want the fastest possible version, use a basic approach first:
Demand during lead time = Average demand × Average lead time
Reorder point = Demand during lead time + Safety stock
In Excel or Google Sheets, if average daily demand is in C2, lead time in days is in D2, and safety stock is in H2, the reorder point formula in J2 can be:
=C2*D2+H2
Then your reorder flag can compare current available inventory against that threshold. If on-hand inventory is in K2 and on-order inventory is in L2, you might calculate inventory position as:
=K2+L2
And use a simple reorder flag such as:
=IF((K2+L2)<=J2,"REORDER","OK")
That gives you a functioning inventory planning template. The next step is making safety stock more defensible.
There are two practical ways to estimate safety stock in a spreadsheet:
- Rule-of-thumb safety stock: a fixed number of days or weeks of extra demand.
- Variability-based safety stock: a formula tied to demand variation, lead time variation, and target service level.
The rule-of-thumb method is often enough for a small catalog. For example, you might hold 7 extra days of average demand as a buffer. If average daily demand is 12 units, safety stock is 84 units. This is easy to explain and maintain.
The more analytical method is useful when demand is uneven or supplier lead times are inconsistent. A common spreadsheet-friendly version is:
Safety Stock = Z × Std Dev of Demand × SQRT(Lead Time)
In Excel or Google Sheets, if the standard deviation of daily demand is in E2, average lead time in days is in D2, and your Z-score for service level is in G2, then:
=G2*E2*SQRT(D2)
Many teams store a small lookup table elsewhere in the workbook for service level and Z-score, for example:
- 90% service level → 1.28
- 95% service level → 1.65
- 97.5% service level → 1.96
- 99% service level → 2.33
You do not need to overcomplicate this. Pick one service level policy by product type if possible. For example, high-priority items may target 97.5%, while low-priority items may target 90%.
If both demand and lead time are volatile, a more complete formula can be used:
Safety Stock = Z × SQRT((Lead Time × Demand Std Dev²) + (Average Demand² × Lead Time Std Dev²))
In spreadsheet form, if average demand is C2, average lead time is D2, demand standard deviation is E2, lead time standard deviation is F2, and Z-score is G2:
=G2*SQRT((D2*(E2^2))+((C2^2)*(F2^2)))
This version is especially helpful if supplier performance changes often. It can support a more realistic stockout risk spreadsheet because it recognizes that uncertainty comes from more than one place.
Whichever method you choose, keep one rule in mind: it is better to use a slightly simpler model that your team actually updates than a technically better one no one trusts.
Inputs and assumptions
The quality of your reorder point depends less on advanced formulas than on clean assumptions. A spreadsheet can only guide decisions well if the inputs are consistent and current.
1. Demand period must match lead time period.
If demand is measured per week, lead time should also be in weeks. If demand is daily, lead time should be daily. Mixing weekly demand with daily lead time is one of the easiest ways to create misleading reorder points.
2. Use recent demand, but not only the last few days.
A common method is a rolling average over the last 8 to 12 weeks, or last 60 to 90 days. If demand is highly seasonal, you may want to compare recent demand to the same period last year rather than using a single short window.
3. Separate average demand from peak demand.
Do not use an unusually strong month as your default average unless that level has become normal. Peak demand belongs in scenario testing, not in the base assumption for every SKU.
4. Define lead time from order placement to stock availability.
Lead time should include the whole practical process: supplier processing, shipping, receiving, and put-away if that delays item availability. Teams often underestimate lead time because they only count transit time.
5. Safety stock is a policy choice, not just a formula output.
Your sheet may calculate a number, but the number still reflects a business decision. A higher service level reduces stockout risk but ties up more cash. A lower service level improves cash efficiency but increases the chance of missed sales or disrupted operations.
6. Treat low-volume and erratic items carefully.
For products with intermittent demand, averages can be misleading. In those cases, use a simpler manual review flag, a minimum stocking rule, or a separate class of assumptions instead of forcing every SKU through the same formula.
7. Track inventory position, not just shelf count.
If you have open purchase orders, returns, reservations, or production commitments, on-hand inventory alone may not be enough. A practical reorder decision should consider what is available plus what is already on order, minus what is committed.
8. Add validation rules to prevent bad entries.
Use data validation for service levels, nonnegative values, and SKU lists. Error-proofing matters here because one incorrect lead time or decimal place can distort buying decisions across multiple weeks. If you want to harden the file, the guide on spreadsheet error-proofing: validation rules and templates to prevent costly mistakes is a useful companion.
A simple sheet can also include SKU segmentation. For example:
- A items: high value or business critical, higher service level
- B items: moderate importance, standard service level
- C items: low value or low urgency, lower service level
That structure keeps your safety stock calculator Excel model aligned with actual business priorities instead of applying one buffer rule to every item.
If your inventory spending is under pressure, it is worth pairing this file with a cash planning model. Reorder timing affects liquidity, especially if many items hit their thresholds at once. For that, see Cash Flow Forecast Spreadsheet Guide: Weekly, Monthly, and 13-Week Models.
Worked examples
Examples make the model easier to trust, so here are three practical setups you can recreate in Excel or Google Sheets.
Example 1: Basic reorder point with fixed safety stock
You sell a packaging supply item with average demand of 20 units per day. Supplier lead time is 10 days. You want a fixed safety stock equal to 5 days of demand.
- Average daily demand = 20
- Lead time = 10 days
- Safety stock = 20 × 5 = 100
- Demand during lead time = 20 × 10 = 200
- Reorder point = 200 + 100 = 300
Spreadsheet formulas:
- Safety stock:
=C2*5 - Reorder point:
=C2*D2+H2
If available inventory drops to 300 or below, you place the order.
Example 2: Reorder point using demand variability
You manage a consumable item with average daily demand of 15 units, standard deviation of daily demand of 4 units, average lead time of 12 days, and a target service level of 95% with Z = 1.65.
- Demand during lead time = 15 × 12 = 180
- Safety stock = 1.65 × 4 × SQRT(12)
- SQRT(12) ≈ 3.46
- Safety stock ≈ 22.8, which you may round to 23
- Reorder point = 180 + 23 = 203
Spreadsheet formula for safety stock:
=1.65*E2*SQRT(D2)
This is a good middle-ground model when supplier lead times are reasonably stable but daily sales move around.
Example 3: Demand and lead time both vary
You have a higher-risk SKU with average daily demand of 30 units, demand standard deviation of 6 units, average lead time of 14 days, lead time standard deviation of 3 days, and service level target of 97.5% with Z = 1.96.
Safety stock formula:
=1.96*SQRT((14*(6^2))+((30^2)*(3^2)))
Working through it:
- 14 × 36 = 504
- 30² = 900
- 3² = 9
- 900 × 9 = 8,100
- Total inside square root = 8,604
- Square root ≈ 92.76
- Safety stock ≈ 1.96 × 92.76 = 181.8
- Rounded safety stock = 182
Demand during lead time:
- 30 × 14 = 420
Reorder point:
- 420 + 182 = 602
This result is much higher than a simple average-demand model would suggest, which is exactly the point. Uncertain lead time can drive stockout risk even when average demand looks manageable.
Once you have these calculations, you can add a dashboard layer with conditional formatting:
- Red for inventory position below reorder point
- Amber for inventory position within 10% above reorder point
- Green for healthy coverage
If you want to summarize reorder exposure by category, supplier, or buyer, pivot tables are helpful. The article Master pivot tables: a friendly pivot table tutorial with ready-to-use templates can help turn a row-level reorder sheet into a weekly review view.
You can also connect this inventory model to profitability analysis. A product with frequent stockouts but low margin may deserve a different replenishment policy than a high-margin core item. For related thinking, see Profitability per product: a financial modeling spreadsheet template for small retailers and Profit Margin Calculator Spreadsheet for Products, Services, and Mixed Revenue.
When to recalculate
The value of a reorder point spreadsheet is not the initial setup. It is the habit of revisiting it when conditions change. Inventory assumptions drift quietly. A product starts selling faster, a supplier misses two deliveries, or order minimums change. If the spreadsheet is static, the reorder point becomes less useful even if the formula is still correct.
Recalculate your model when any of the following happens:
- Demand pattern changes: a promotion, new channel, season, or product launch changes average volume or volatility.
- Supplier lead time shifts: vendors extend processing windows, shipping slows, or receiving delays increase.
- Service level expectations change: critical items may need higher protection during busy periods.
- Unit economics change: carrying more stock may become less attractive when cash is tight or product margins compress.
- Portfolio mix changes: new SKUs, discontinued items, or packaging changes can alter stocking policy.
- Benchmark assumptions move: your preferred target buffers or internal standards are updated.
As a practical operating rhythm, consider this review schedule:
- Weekly: refresh on-hand and on-order inventory, review reorder flags
- Monthly: update average demand and recent lead time
- Quarterly: revisit service levels, SKU segmentation, and formula choices
- After major events: promotions, supplier disruptions, or major pricing changes
To make the sheet more actionable, add a final tab called Review Queue with these fields:
- SKU
- Current inventory position
- Reorder point
- Gap to reorder point
- Recommended action
- Owner
- Review date
This turns the spreadsheet from a calculator into a lightweight operating tool. You can sort by the largest gap, filter by supplier, and assign follow-ups without needing a larger system.
If your team already reviews operating metrics in one place, you can feed reorder status into an operations dashboard template or KPI dashboard for small teams. And if purchasing decisions affect broader planning, a rolling budget view such as Rolling 12-Month Budget vs Actual Spreadsheet for Small Business Reporting can help connect inventory choices to spending plans.
The simplest next step is this: build the first version with ten important SKUs, not your full catalog. Use one demand period, one lead time definition, and one safety stock method. Test it for a month, compare the reorder flags with actual purchasing decisions, and adjust where needed. Once the logic feels dependable, scale it outward. That approach keeps your reorder point formula Google Sheets or Excel file grounded in real operations rather than abstract inventory theory.
A good reorder point spreadsheet should not feel finished. It should feel maintainable. When the inputs change, the model should give you a quick, calm way to update assumptions and make the next buying decision with more confidence.
