On this page
Two questions every warehouse asks: when should we reorder, and how much extra should we keep in case things go wrong? Excel can answer both.
What you need
- Daily demand for an item, for at least a few weeks. Put it in a column, for example B2:B91 for 90 days.
- The supplier lead time in days, which is the time from order to delivery. Say it is in cell E1.
- The service level you want. A 95% service level means you accept running out in about 5% of order cycles. Say it is in E2 as 0.95.
Step 1: Average daily demand
=AVERAGE(B2:B91)Step 2: How much demand varies
=STDEV.S(B2:B91)This is the standard deviation. A bigger number means demand jumps around more, so you need more safety stock.
Step 3: Safety stock
=NORM.S.INV(E2) * STDEV.S(B2:B91) * SQRT(E1)NORM.S.INV(0.95) gives about 1.645, a number that turns your chosen service level into a multiplier. The square root of lead time adjusts for the length of the wait.
Step 4: Reorder point
=AVERAGE(B2:B91) * E1 + NORM.S.INV(E2) * STDEV.S(B2:B91) * SQRT(E1)The reorder point is the demand you expect during the lead time, plus the safety stock. When your stock falls to this number, place the order.
Worked example
- Average demand: 40 units a day
- Standard deviation: 12 units
- Lead time: 9 days
- Service level: 95% (multiplier 1.645)
Safety stock = 1.645 x 12 x 3 (the square root of 9) = 59.2, so round up to 60 units.
Reorder point = 40 x 9 + 59.2 = 419.2, so round up to 420 units. Wrap the formula in ROUNDUP(..., 0) to round up automatically.
Limits of this method
This formula assumes that the lead time is fixed and that demand is roughly even from day to day. If your supplier is often late, or demand is seasonal, you need more stock than this suggests. Use it as a starting point, and compare it with what actually happened over the last few months.
Also check your data for blanks and one-off spikes before using it. See how to clean messy data.