01 / 10The Reorder Point Was Average Lead-Time Demand

StatisticsSTDEVFormulas

Reorder Point and Safety Stock in Excel With NORM.S.INV

The Reorder Point Was Right on Average. Calder Fixings Ran Out of M8 Bolts on Four Orders in Eight, Because an Average Is Beaten Half the Time

21 Nov 20268 min read

UsesSTDEV, VARAVERAGEROUNDMIN, MAX

Calder Fixings reorders M8 bolts when stock falls to 240 boxes: 24 a day for the 10 working days the supplier takes. Across eight orders since June, four ran out before the delivery arrived.

Eight orders, June to OctoberReorder at 240Reorder at 287
Orders that ran short40
Boxes short740
Sales turned away£1,591£0

Scope. AVERAGE, STDEV.S, SQRT, ROUNDUP, NORM.S.INV, NORM.S.DIST, COUNTIF and MAX all work in Excel 2010 and later.

The method assumes daily demand is roughly normal and days are independent. Section 5 shows what to add when the supplier's lead time also varies.

01The Reorder Point Was Average Lead-Time Demand

Calder's sheet sets the reorder point to the demand expected while an order is on its way. That is average daily demand times lead time, and nothing else:

H1:  =AVERAGE(Sales!B2:B61)      → 24      boxes a working day
H2:  10                          lead time, working days
H6:  =H1*H2                      → 240     reorder point

Sales!B2:B61 holds 60 working days of boxes sold. When stock on hand drops to 240, the buyer raises a purchase order, and 10 working days later the delivery lands.

The logic is sound for an average fortnight. It says nothing about a busy one.

Scenario: Put 24 in H1 and 10 in H2, then enter =H1*H2 in H6. Check that it returns 240, the reorder point Calder has used since spring.

Try it in the grid

02Half the Orders Ran Short of the Average

An average sits in the middle of the spread, so lead-time demand beats it in roughly half of all cycles. Calder's last eight orders split exactly that way.

Calder Fixings: M8 Bolt Reorders, June to October 2026

Eight purchase orders for boxes of M8 bolts. B is the day stock fell to the reorder point, C the boxes sold in the 10 working days until the delivery arrived, D the boxes the shelf could not supply with a reorder point of 240, =MAX(0,C2-240), and E the same with a reorder point of 287, =MAX(0,C2-$H$6).

ABCDE
1
Order
Reorder date
Lead-time demand
Short at 240
Short at 287
2
Order 1
15/06/2026
251
11
0
3
Order 2
06/07/2026
228
0
0
4
Order 3
24/07/2026
262
22
0
5
Order 4
12/08/2026
219
0
0
6
Order 5
01/09/2026
247
7
0
7
Order 6
21/09/2026
236
0
0
8
Order 7
09/10/2026
274
34
0
9
Order 8
28/10/2026
233
0
0

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

Column C is the boxes sold between the reorder and the delivery. Column D is what the shelf could not supply, =MAX(0,C2-240), and two formulas sum up the damage:

=COUNTIF(D2:D9,">0")     → 4      orders that ran short
=SUM(D2:D9)              → 74     boxes short

At £21.50 a box, the 74 boxes were £1,591 of trade orders that went to another merchant.

Nothing in the sheet is miscalculated: 240 is exactly what the history says. A reorder point at the mean is a 50% service level, chosen by accident.

Scenario: Type 251, 228, 262, 219, 247, 236, 274 and 233 into C2:C9. Enter =MAX(0,C2-240) in D2, fill it down to D9, and check that =SUM(D2:D9) returns 74.

Try it in the grid

03Measure the Daily Spread With STDEV.S

Safety stock is sized from how much demand moves, so the first number to add is the standard deviation of daily demand:

H3:  =STDEV.S(Sales!B2:B61)      → 9

Use STDEV.S, not STDEV.P. Sixty days are a sample of the demand Calder will see, not all of it, though on 60 values the two differ by less than 1%.

Daily sales of 24 with a spread of 9 means a typical day lands between 15 and 33 boxes. A 10-day lead time adds ten of those days together.

Take the history from days the item was in stock. A day the shelf was empty records zero sales, not zero demand, and drags down the average while inflating the spread.

Scenario: Type 18, 31, 24, 15, 33, 22, 27 and 20 into B2:B9. Enter =STDEV.S(B2:B9) and check that it returns 6.27, then =AVERAGE(B2:B9) and check 23.75.

Try it in the grid

04The Spread Grows With the Square Root of Lead Time

Ten days of demand do not vary ten times as much as one day. Busy and quiet days partly cancel, so the spread over the lead time is the daily spread times the square root of the days:

=H3*SQRT(H2)        → 28.46

Variances add when days are independent. Ten days of 81 is 810, and the square root of 810 is 28.46. Multiplying by 10 instead assumes every day of the fortnight is a busy one.

Safety stock at 95%FormulaBoxesReorder point
Square root of lead time=1.645*9*SQRT(10)47287
Lead time itself=1.645*9*10149389

The second version never runs out in this history either. It just keeps 102 more boxes on the shelf, £1,306 of stock at a cost of £12.80 a box, against a fortnight that is busy every single day.

Scenario: Enter =1.645*9*SQRT(10) and =1.645*9*10 side by side. Check that they return 46.81 and 148.04: the same service level, three times the stock.

Try it in the grid

05Let NORM.S.INV Size the Safety Stock

The 1.645 is not a constant. It is the z-score for a 95% chance of not running out in a cycle, and NORM.S.INV turns any service level into one:

H4:  95%
H5:  =ROUNDUP(NORM.S.INV(H4)*H3*SQRT(H2),0)    → 47
H6:  =H1*H2+H5                                  → 287

ROUNDUP because boxes are whole, and one box short is the expensive way to be wrong.

Service levelzSafety stockReorder point
50%0.0000240
90%1.28237277
95%1.64547287
98%2.05459299
99%2.32667307

The last points cost the most. Going from 50% to 90% adds 37 boxes; going from 95% to 99% adds 20 boxes for four points.

Service level here means cycle service level: the chance that an order cycle ends without a stockout. It is not the share of demand filled, which is higher.

If the supplier's lead time also varies, add its spread: =NORM.S.INV(H4)*SQRT(H2*H3^2+H1^2*H8^2), with the standard deviation of the lead time, in working days, in H8.

Scenario: Put 9 in H3, 95% in H4, and enter =ROUNDUP(NORM.S.INV(H4)*H3*SQRT(H2),0) in H5. Check that it returns 47, then change H4 to 99% and check 67.

Try it in the grid

06Keep Demand and Lead Time in the Same Units

Average demand and lead time must count the same days. Demand per working day needs a lead time in working days. The supplier quotes two weeks, which is 10 working days or 14 calendar days.

Demand enteredLead time enteredReorder point at 95%
24 a working day10 working days287
24 a working day14 calendar days392
120 a week10, read as weeks1,305

Converting is one division for the average and a square root for the spread. Weekly demand of 120 is 24 a day; a weekly standard deviation of 20.12 is =20.12/SQRT(5), which is 9 a day.

Label the units in the cell beside each input. A lead time typed as 2 or 14 or 10 looks equally plausible in an unlabelled cell.

Scenario: Enter =24*14+ROUNDUP(1.645*9*SQRT(14),0) and check that it returns 392, which is 105 boxes more than the working-day figure for the same supplier.

Try it in the grid

07Replay the Reorder Point Against Past Orders

Before trusting a new reorder point, replay it against the orders you already have. Column E repeats the shortfall at 287:

E2:  =MAX(0,C2-$H$6)
=COUNTIF(E2:E9,">0")     → 0

The busiest lead time on record, order 7 at 274 boxes, now ends with 13 boxes still on the shelf.

Eight orders cannot prove a 95% level: at that rate a shortfall is still expected about once in 20 cycles. The replay catches a reorder point that is plainly too low, not one that is precisely right.

To read off the service level any reorder point implies, run the formula backwards. =NORM.S.DIST((H6-H1*H2)/(H3*SQRT(H2)),TRUE) returns 95.1% at 287 and 50% at 240.

Scenario: Enter =NORM.S.DIST((240-240)/(9*SQRT(10)),TRUE) and check that it returns 0.5, the service level Calder had been running since spring. Then change the first 240 to 287 and check 0.951.

Try it in the grid

08Five Traps

  1. Reordering at the average. It is a 50% service level: about every other order runs short.
  2. Multiplying the daily spread by the lead time. Use the square root of the lead time; the straight multiple triples Calder's safety stock.
  3. Measuring demand on days the shelf was empty. Stockout days record zero sales, which pulls the average down and pushes the spread up.
  4. Mixing working days and calendar days. 24 a working day over 14 calendar days adds 105 boxes for nothing.
  5. Rounding safety stock with ROUND. At 90% it gives 36 boxes, below the 36.47 that level needs. ROUNDUP gives 37.

09Four Exercises

  1. Reproduce it. Build the eight-order table and confirm that 4 orders ran short at 240, 74 boxes in all.
  2. Size it. With 24, 10, 9 and 95% in H1:H4, confirm that H5 returns 47 and H6 returns 287.
  3. Replay it. Add column E and confirm that =COUNTIF(E2:E9,">0") returns 0.
  4. Stretch it. Put a lead-time spread of 2 days in H8 and confirm that the fuller formula at 95% rounds up to 92 boxes of safety stock.

What to Take Away

Calder's reorder point was the right average and the wrong decision. An average is beaten about half the time, so four of eight orders ran out.

Add safety stock with =ROUNDUP(NORM.S.INV(H4)*H3*SQRT(H2),0), keep demand and lead time in the same days, and replay the result against the orders you already have.

Share this article:
Back to Blog
Daily challenge · Day 110

Price a Balloon Finance Deal with PMT's Fourth Argument

A new exercise every day, solved in a real grid.

Solve today’s challenge