How to calculate a reorder point (with the Excel formulas)

Every product you sell has a number like this: the stock level at which you have to order today, or run out before the next delivery arrives. Getting it right is most of what stock control is. Set it too low and you run out while the order is still at sea. Set it too high and cash sits on the shelf for months.

This guide works through the formula on one example product, step by step, then shows the exact Excel formulas to do the same for every product you carry. The numbers come from the sample data in our free reorder point calculator, so you can open the file and check every one of them.

What a reorder point is (and isn't)

A reorder point is a trigger, not a quantity. It answers one question: is it time to order this product? How much to order is a separate decision, covered further down.

It has two parts:

• Demand during lead time — what you expect to sell between placing the order and having the goods on the shelf.
• Safety stock — a buffer for the times you sell more than usual, or the supplier is slower than usual.

The reorder point formula

• d — average daily demand, in units per day.
• LT — lead time, in days, from the day you place the order to the day the stock can be sold.
• safety stock — the buffer. The most complete standard formula is Z × √(LT × σd² + d² × σLT²), explained below and in detail in our safety stock guide.

Keep every input in the same unit of time. Most mistakes with this formula are a monthly number multiplied by a lead time in days.

Worked example, step by step

The product is SKU-0012, a 10 mm fuel filter. Here are its last twelve months of sales:

Step 1 — Average daily demand

The twelve months average 209.7 units. A month is 30.4 days on average (365 ÷ 12), so:

Use six to twelve months if you have them; three is the minimum worth using. A month with no sales is a 0, not a blank — leaving it blank makes the average look higher than it is. And if you were out of stock for part of a month, that month understates demand: note it, and don't let it pull the average down.

Step 2 — Lead time

Say the supplier quotes 14 days, but their recent deliveries actually took 21 days on average, give or take about 4. Use 21. Lead time runs from the day you send the order to the day the goods are on the shelf and sellable — production, shipping, customs, and your own receiving all count.

Step 3 — Safety stock

Monthly sales vary by 32.3 units (standard deviation), which is 5.85 a day (32.3 ÷ √30.4). With a 95% service level, Z = 1.645:

The first term covers demand being higher than average; the second covers the supplier being slower than average. Round safety stock up — you can't stock a third of a filter.

Step 4 — Put it together

Without the safety stock, the reorder point would be 145 — and because demand during the lead time is above average about half the time, you would run out in roughly one cycle in two.

Compare it with stock on order, not just stock on hand

This is the mistake that causes double orders. Compare the reorder point with your inventory position:

Looking only at the shelf, both rows say "order". Only the first one should.

How much to order when you hit it

A simple, robust rule: order enough to get back above the reorder point and cover your normal order cycle — say 30 days of sales.

Then apply the supplier's rules: raise it to the minimum order quantity if it's below, and round up to whole cartons. The reorder point itself doesn't change — MOQs and pack sizes affect how much, never when.

The reorder point formula in Excel

One row per product. Lead time in column B, its variation in C, stock on hand and on order in D and E, twelve months of sales in F to Q, and the service level (95%) in cell Z1. Then, in row 2:

R2  Avg per day    =AVERAGE(F2:Q2)/30.4
S2  SD per day     =STDEV.S(F2:Q2)/SQRT(30.4)
T2  Safety stock   =ROUNDUP(NORM.S.INV($Z$1)*SQRT(B2*S2^2+R2^2*C2^2),0)
U2  Reorder point  =ROUNDUP(R2*B2+T2,0)
V2  Status         =IF(D2+E2<=U2,"Order now","OK")
W2  Order qty      =IF(V2="Order now",ROUNDUP(U2+R2*30-(D2+E2),0),0)

Copy row 2 down. With the fuel filter's numbers these return 6.90, 5.85, 64, 209, "Order now" and 270 — the same as the steps above. In Excel 2007 or older, use STDEV and NORMSINV instead of STDEV.S and NORM.S.INV.

Six mistakes that break reorder points

1. Using the quoted lead time. Measure the real one from your last deliveries. Suppliers quote their best case.
2. Mixing months and days. Monthly demand × lead time in days gives a number thirty times too big.
3. Comparing with stock on hand only. You'll reorder things that are already on the way.
4. Blank months. No sales is a zero. A blank inflates the average.
5. Setting it once. Demand and suppliers change. Recalculate monthly, and weekly for your best sellers.
6. One service level for everything. 99% on a slow, expensive part ties up a lot of cash for very little benefit. See the service level table.

When the simple formula isn't enough

Seasonal products. A twelve-month average under-orders before the peak and over-orders after it. Use the average of the same months last year, or the last three months if demand is trending.

Slow and lumpy movers. A part that sells 0, 0, 3, 0, 1, 0 a month doesn't follow the bell curve the safety stock formula assumes. In a parts warehouse this is most of the catalogue. For these, a simple minimum–maximum rule (for example: order up to 3 when you reach 1) is usually more honest than a statistical answer.

You only order on fixed days. If you place orders every Monday rather than the moment stock hits the line, add the review period to the lead time: a product reviewed weekly with a 21-day lead time needs cover for 28 days.

New products. No history means no average. Borrow the numbers from a similar product for the first few months, then switch to its own.

Frequently asked questions