Akiba Tools › Guides

Reorder Level Formula: The Excel Method Where You Set the Rules and Stay in Control

Free Excel method for stores and spare parts · 30 October 2026 · about 10 minutes to read

The reorder level formula you find everywhere is average daily usage × lead time + safety stock. It was written for shops that sell every day. A store issues spare parts a few times a year, buys some locally and some from abroad, and holds parts it hopes never to use. This guide gives you the full method, in Excel, from the yearly issue quantities in a normal stock export. You choose the lead times, the cover and the safety stock; the formulas only make suggestions, and every final level is your decision.

What the basic formula misses

Usage × lead time is the core, but on its own it gets many store items wrong. The method below also allows for:

• Stock on order: what is already coming, so you don't order twice
• Local and foreign supply: different lead times and how many months of stock to hold
• How steady the use is: steady items need less safety stock than variable ones
• Falling use: an average of past years overstates what you will need next year
• Items that should not get a level at all: no use, erratic use, too little history
• Critical spares: held for breakdowns, set by decision rather than by formula
• Very slow movers and pack sizes: rounding that keeps the levels practical
• Your current levels: comparing old and new shows the cash each change frees or needs

What you need, and how to lay out the sheet

One stock export, one row per item, with the issue quantities for the last 3 completed years (2 is the minimum; many systems show these as yearly columns), the supply source (Local or Foreign), stock on hand, stock on order and unit cost. Useful if you have them: current reorder level and maximum, pack size or minimum order quantity, and a critical-spare flag.

Set the sheet up once with the layout below. The formulas in this guide use these column letters. The white columns come from your export; the shaded ones are formulas you add.

Adapt it to your store. The layout is a starting point. Change the lead times, cover and safety % to match your suppliers and your risk, overwrite a value for one item when you know better, and add your own columns after Z (category, location, buyer) without breaking the formulas.

Step 1: Sort the items before you calculate

Not every item should get a calculated reorder level. Sort first:

Step 2: Work out the annual usage

A plain average treats a year from three years ago the same as last year. Weight the recent years more. With the three years in columns C, D and E (E = the latest):

With only two years: =0.6*E2+0.4*D2.

If use is clearly falling, the average still overstates it. When last year is below 70% of the earlier years, use last year only:

This is column J, "Annual usage". The spread in step 3 is column K.

Step 3: How steady is the use?

Divide the spread of the yearly quantities by their average (the coefficient of variation). It tells you how much buffer an item needs:

These cut-offs and percentages are a practical starting point, not a rule. Adjust them to your store.

The "Use" column (L) and the safety % (O):

Step 4: Lead time and cover, local or foreign

Lead time decides when to order; cover decides how much to hold at most. Starting points:

Lead time (M) and cover (N), in months:

Use the real lead time where you know it, and overwrite it for a single item when a supplier is slower than usual.

Step 5: The formulas

These columns are your suggested levels. With annual usage in J, lead time in M, cover in N and the safety % in O:

1. Use during the lead time (P): =J2/12*M2
2. Safety stock (Q): =ROUNDUP(O2*P2,0)
3. Reorder level (R): =ROUNDUP(P2+Q2,0)
4. Maximum (S): =MAX(ROUNDUP(J2/12*N2,0),R2+1). The MAX keeps the maximum above the reorder level for low-use items.
5. Order quantity (T): =IF(X2>0,CEILING(S2-Q2,X2),S2-Q2), rounded up to the pack size in X when there is one

Two practical rules on top:

• Very slow movers: if the use during the lead time is less than 1 unit, set reorder level 1, maximum 2. The formulas would otherwise give 0 or 1 and leave you with nothing on the shelf.
• Long lead times: make sure the cover is longer than the lead time plus the safety stock, plus a little margin. Otherwise the stock arrives just as you hit the maximum.

Step 6: When to order, and how much

This is where most stores go wrong: they look at stock on hand only. Count what is already on order too. With stock on hand in G and on order in H:

In words: when on hand + on order is at or below the reorder level, order enough to bring it back up to the maximum.

A worked example

Six items from a made-up store. Issues are for the last three completed years, oldest first.

Made-up data. The grease uses last year only (150), because use fell below 70% of the earlier years. The seal kit keeps a floor of 1 and 2 as a critical spare.

Now the decisions, with stock on order and the current levels:

Made-up data. Cash freed compares the average stock value with the current and the new levels (average stock ≈ safety stock + half the order quantity). Total for these four items: +1,551.

Three things stand out. The filter is the only item where the new level is higher: with a 3-month lead time, the old level of 20 was below the 27 filters used during one lead time, so the shelf would run empty before the next delivery arrived. The grease had its level set when use was much higher, so 185 cartridges are now surplus. And without counting stock on order, the filter would have been ordered twice.

Before you load the new levels: review and confirm

The suggestions are a starting point for your decision, not the decision. Go through them and record your call in the Decision column (Z): Approve, Change (type your own value) or Keep current. Only approved or changed values go into your system.

• Check the big changes by hand, especially the largest cash amounts in both directions.
• Ask the people who use the parts. A planned overhaul, a new machine or a retired one changes next year's use in ways the history can't show.
• Set dead items to zero. An item with no use that still carries a reorder level will be bought again.
• Make sure every maximum is above its reorder level.
• Keep the old values next to the new ones, with the date and who approved them.

Review every quarter

Use changes, suppliers change and equipment is retired. Recalculate every quarter with the new export, and straight away when a lead time or a supplier changes. The first time takes the longest; after that, you only look at what moved.

← Back to the Dead Stock Finder