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 October | Reorder at 240 | Reorder at 287 |
|---|---|---|
| Orders that ran short | 4 | 0 |
| Boxes short | 74 | 0 |
| Sales turned away | £1,591 | £0 |
Scope.
AVERAGE,STDEV.S,SQRT,ROUNDUP,NORM.S.INV,NORM.S.DIST,COUNTIFandMAXall 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.
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).
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.
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.
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% | Formula | Boxes | Reorder point |
|---|---|---|---|
| Square root of lead time | =1.645*9*SQRT(10) | 47 | 287 |
| Lead time itself | =1.645*9*10 | 149 | 389 |
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.
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 level | z | Safety stock | Reorder point |
|---|---|---|---|
| 50% | 0.000 | 0 | 240 |
| 90% | 1.282 | 37 | 277 |
| 95% | 1.645 | 47 | 287 |
| 98% | 2.054 | 59 | 299 |
| 99% | 2.326 | 67 | 307 |
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, inH8.
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.
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 entered | Lead time entered | Reorder point at 95% |
|---|---|---|
| 24 a working day | 10 working days | 287 |
| 24 a working day | 14 calendar days | 392 |
| 120 a week | 10, read as weeks | 1,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.
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.
08Five Traps
- Reordering at the average. It is a 50% service level: about every other order runs short.
- Multiplying the daily spread by the lead time. Use the square root of the lead time; the straight multiple triples Calder's safety stock.
- Measuring demand on days the shelf was empty. Stockout days record zero sales, which pulls the average down and pushes the spread up.
- Mixing working days and calendar days. 24 a working day over 14 calendar days adds 105 boxes for nothing.
- Rounding safety stock with
ROUND. At 90% it gives 36 boxes, below the 36.47 that level needs.ROUNDUPgives 37.
09Four Exercises
- Reproduce it. Build the eight-order table and confirm that 4 orders ran short at 240, 74 boxes in all.
- Size it. With 24, 10, 9 and 95% in
H1:H4, confirm thatH5returns 47 andH6returns 287. - Replay it. Add column E and confirm that
=COUNTIF(E2:E9,">0")returns 0. - Stretch it. Put a lead-time spread of 2 days in
H8and 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.