Spare parts reorder points: min/max levels with Excel examples

A spare parts reorder point tells you when to buy. The order-up-to level tells you how much to buy. For a regularly used part, start with expected usage during the supplier's lead time, then add a safety-stock allowance. Before placing the order, check what you already hold, what is coming, and when it will be usable.

This guide follows an oil filter from a reorder decision through a partial delivery, then tests the same approach against a late supplier and a rarely used spare. All quantities are illustrative; the buffers and stocking decisions are assumptions, not measured service-level guarantees.

Use the existing spreadsheet: download the free inventory workbook or read its setup guide. It has Items, Movements, Purchase orders and Reorder sheets. The examples below show what to enter; they are not preloaded in the download, which contains sample plumbing and electrical stock.

Jump to: set the rules · oil-filter example · Excel steps · supplier delays · rare spares · review checklist.

1. Set the reorder point and order-up-to level

For reasonably steady demand, the starting calculation is:

Reorder point =
  (daily usage × lead time in days)
  + safety stock in units

Safety stock allows for uncertainty in demand or delivery. More variation can require more protection; a fixed buffer does not establish a particular probability of avoiding a stockout. NC State's safety-stock tutorial explains that distinction.

Keep three quantities separate:

Here, inventory position means on-hand stock plus outstanding orders. More capable systems can also account for open demand and which stock is eligible to supply it; Oracle documents both net-demand and non-net-demand calculations. The workbook uses the simpler on-hand-plus-on-order calculation. It does not subtract reservations or backorders.

Once the trigger is reached:

Base order quantity = MAX(0,
  order-up-to level − inventory position
)

Choose the target above the trigger using the replenishment quantity you can justify, available space, cash tied up and supplier constraints. “Max” is a planning target, not a promise that exactly that many units will sit on the shelf after delivery: parts may be used while you wait.

Before using the formula, check the inputs:

• Usage: use a representative period and include days with no demand. Separate routine issues from transfers, count corrections and an unusual one-off job. Record requests you could not fill; zero issues during a stockout do not prove zero demand.
• Lead time: measure from placing the order until the part is usable, including receiving or inspection delays. Use the same working-day or calendar-day basis as the usage rate.
• Review frequency: these examples assume movements are recorded promptly and the reorder list is checked each working day. A batch issue can cross the trigger between checks.
• Stock accuracy: if the shelf and the record disagree, follow the parts stocktake and reconciliation process before treating the balance as a purchasing fact.

If you only buy weekly, account for that wait too. A periodic-review target normally covers the review interval plus replenishment lead time, with a buffer; it is not interchangeable with a continuously watched reorder point. See NC State's periodic-review explanation.

2. Worked example: a regularly used oil filter

Suppose a workshop uses three filters per working day. Delivery takes two working days. The buyer chooses a four-filter buffer and an order-up-to target of 30 after reviewing shelf space and purchasing needs.

The target of 30 is a chosen policy, not an optimized order quantity. With eight filters on hand and none on order:

Inventory position = 8 + 0 = 8
8 ≤ 10 → review for purchase
Base order = 30 − 8 = 22 filters

If exactly six filters are used while waiting and all 22 arrive together, the shelf reaches 8 − 6 + 22 = 24, not 30. The order restored inventory position to 30 when placed; subsequent usage reduced it.

Now suppose four filters were already on order. The position would be 8 + 4 = 12, above the trigger, so the workbook would show OK and omit the item from Reorder. Check their arrival date before accepting that decision. An order due next week cannot supply tomorrow's repair.

What if the supplier sells only packs of six?

In a separate version of the 22-filter order, full packs require rounding up to 24. The position after ordering becomes 8 + 24 = 32, exceeding the chosen target by two. Under the same six-filter usage assumption, the delivery leaves 26 on the shelf.

Approve that tradeoff deliberately. The workbook does not apply supplier pack sizes or minimum order quantities automatically. Order multiples and minimum quantities can change a base replenishment proposal; Microsoft documents these as order modifiers. Record the actual quantity agreed with the supplier on the PO.

3. Enter the policy and receipts in the workbook

Use a working copy and follow Start here to replace sample input records while retaining formulas. Keep one SKU and unit consistent across sheets. For this example, use FILTER-A, measured in individual filters, and enter both policy values explicitly.

1. Items: enter the SKU, description, unit and preferred supplier. Set Reorder point (I) to 10 and Order up to (J) to 30. Keep calculated columns L–P intact.
2. Movements: establish the verified opening eight filters once, using the correct location, Type = Counted, In = 8, and an opening-balance note. If your working file already contains the movements that produce eight, do not enter a second opening receipt.
3. Read back Items: On hand (L) should be 8 and On order (M) should be 0. Reorder should suggest 22. The workbook triggers at on hand + on order ≤ reorder point, including equality.
4. Place and record the approved order: after buying 22 individually supplied filters, enter one Purchase orders line with the PO reference, dates, supplier, SKU, Qty ordered (F) = 22, Qty received (I) = 0, and Expected (K). This records an order; it does not send it to the supplier.
5. Read back again: On hand remains 8; On order becomes 22. The item should leave Reorder because the position is now 30. Do not record ordered stock as a receipt before it arrives.

Record a partial delivery in both places

Suppose eight of those 22 arrive, with no other stock movements between these steps:

• Add one Movements row: SKU FILTER-A, the receiving location, Type = Received, In = 8, and the PO reference in Job / customer / PO.
• Update the existing PO line's Qty received to the cumulative total of 8. Its calculated status becomes Part received. Do not replace Qty ordered with the outstanding quantity.

When the remaining 14 arrive, record a new receipt movement for 14 and change the PO's cumulative Qty received to 22, not 14. With no intervening issues, the result is 30 on hand and zero on order. If parts were issued meanwhile, the on-hand result will be lower.

Why both entries matter: updating only the movement leaves received stock counted again as outstanding supply. Updating only the PO removes incoming supply without adding it to stock. Read both balances after every delivery.

What the workbook does not check

The stored formulas total movements across locations for each SKU, then add total ordered quantity minus total received quantity, floored at zero. The reorder decision does not use PO Expected dates, reservations, or a separate sellable-stock status. Supplier lead-time information does not calculate the threshold for you.

That means:

• A distant location's stock can clear the overall trigger even when the workshop shelf is empty.
• An overdue PO can clear it even when the promised receipt is no longer credible.
• A part physically held for another job, damaged or quarantined needs a separate availability check.
• Typing a cancellation note does not remove outstanding supply; neither does editing the calculated Status label.

Keep cancellation evidence and reconcile the quantities under your purchasing procedure. Never enter a fictitious receipt to clear an order. For replenishment that must respect locations, reservations or arrival dates automatically, this workbook's reorder total is insufficient.

4. Test what happens when a supplier is late

Consider a different consumable used at two units per working day. With a three-day planning lead time and four-unit buffer, its trigger is 2 × 3 + 4 = 10.

Assume an order is placed immediately at exactly that on-hand balance, no other supply is pending, and demand stays at two per day. Six working days to delivery require 12 units. Ten on the shelf leave two units of demand unfilled before replenishment; the four-unit buffer did not cover the delay.

The second row illustrates a reviewed six-day assumption, not a recommended universal increase. Check actual order-to-usable-stock history and why deliveries slipped. Demand could rise too; neither row establishes a 95% service level.

Check the timing of existing supply separately. With eight on hand and a late PO for four, the workbook sees 12. But three days of demand at three per day require nine: there is a one-unit gap if nothing arrives in that period. Oracle's supply-cutoff controls illustrate why dated supply matters; the spreadsheet has no equivalent cutoff calculation.

Before making an emergency purchase, check whether you can expedite, transfer suitable stock, or change the original order. If replacement supply is approved, reconcile both orders so the delayed one does not arrive later as an unintended duplicate.

5. Treat rare and critical spares as a separate decision

A part used once in two years has intermittent demand. Its small average does not tell you whether carrying none, one or several is sensible. Intermittent-demand forecasting distinguishes the size of non-zero demand from the gaps between demands; see Forecasting: Principles and Practice. That still does not decide the operational consequence of having no spare.

Review the equipment it serves, compatible substitutes, repair or loan options, replenishment time, storage life and the consequence of downtime. A maintenance owner should approve the stocking policy; “slow-moving” alone is not a reason to remove a needed spare.

Suppose that review approves one on-site spare for a workshop label printer, with replenishment after use. In this workbook, a reorder point of 0 and order-up-to level of 1 represents that particular one-for-one policy:

The last row is a purchasing signal, not confirmation that the workshop has its spare back. Track the uncovered period until receipt. One spare is an assumed decision here, not a general rule for critical equipment or every slow mover. In this workbook, setting the trigger to 1 would flag an item already holding one; it does not simply mean “keep one.”

6. Review the policy against what happens

Keep a short record for each policy change: SKU and unit, usage period, actual lead times, chosen buffer, trigger, target, supplier constraints, reason, reviewer and review date.

After replenishment, check:

• Did the part run out before usable stock arrived, and was the cause demand, delay or a wrong balance?
• Did an overdue or duplicate PO hide the need to buy?
• Did pack rounding or an uncollected job leave persistent excess?
• Did maintenance plans, equipment compatibility or supplier availability change?
• Are receipts, issues and cumulative PO receipts still consistent?

Use those observations to revise one assumption at a time. Do not keep raising every minimum after a single shortage without finding its cause.

For the wider workflow (part identities, bins, job issues, returns and special orders), use the auto-parts inventory management guide. This article supplies the replenishment decision; accurate movements and timely purchasing are what make it usable.

Common questions

Is the reorder point the same as safety stock?

No. Safety stock is the uncertainty allowance. The reorder point also covers expected usage while waiting for replenishment. In the filter example, safety stock is four and the trigger is ten.

Should I subtract reserved parts before reordering?

Check which stock can serve the job in question, but do not silently change the workbook formula or count the same demand twice. It does not subtract reservations. In a separate dated plan, distinguish unmet commitments from issues already recorded, and avoid adding booked demand again if your forecast already includes it. A reservation-aware system is a better fit when this is routine.

Can the workbook choose the best minimum and maximum for me?

No. You enter Reorder point and Order up to. The workbook uses them to flag items and suggest quantities; it does not estimate a service level, optimize stocking costs or evaluate criticality. Enter both values explicitly and investigate a Reorder row with a zero suggestion rather than assuming the settings are sensible.