Simon G. · Excel template · free
Safety stock and reorder point: a free Excel template
The workbook calculates safety stock, reorder point and a proposed order for up to 300 products. Every formula is open, and explained below.
Excel workbook (.xlsx), no macros, no sign-up.
What you fill in
- Average units sold per day
- Standard deviation of daily sales
- Lead time, in days
- Variation of the lead time, in days
- How often you review orders, in days
- Service level
- Stock on hand and stock on order
- Pack size, minimum order quantity and unit cost
If you do not know the average and the standard deviation, the second sheet calculates them from the daily sales of one product.
The formulas
- Safety stock
service factor × √[ (lead time + review period) × σ² of daily sales + (average daily sales)² × σ² of lead time ]The first part covers the variation of demand over the days until the next delivery you can still influence. The second covers a supplier who delivers late.
- Reorder point
average daily sales × lead time + safety stockWhen stock on hand and on order falls to this level, an order is due.
- Order up to
average daily sales × (lead time + review period) + safety stockThe level an order should bring you back to.
- Proposed order
order up to − on hand − on orderNever below zero, rounded up to the pack size and raised to the minimum order quantity.
- Service factor
=NORMSINV(service level)The number of standard deviations that gives the service level. 95% gives 1.64, 98% gives 2.05.
A worked example
A product sells 16 units a day, with a standard deviation of 6. The supplier delivers in 21 days, give or take 4. You review orders every 7 days and want a 97% service level.
| Service factor | 1.88 |
|---|---|
| Safety stock | 1.88 × √(28 × 6² + 16² × 4²) = 1.88 × 71.4 = 135 |
| Reorder point | 16 × 21 + 135 = 471 |
| Order up to | 16 × 28 + 135 = 583 |
| Proposed order | 583 − 96 on hand − 360 on order = 127, rounded up to packs of 12 = 132 |
What a spreadsheet cannot see
- A day when the product was out of stock looks like a day with no demand. The average comes out too low, and the next order is too small.
- Season, trend and day of the week are ignored. One average stands for the whole year.
- Every product is filled in by hand, and the figures go stale the day after.
The free Stock Check does these from your transactions file. It rebuilds the stock of every day, corrects the out-of-stock days, tests several forecasting methods per product and proposes the orders for all products at once. It runs in your browser and the file is not uploaded.
Questions
Which service level should I choose?
A higher level for products that matter more and sell steadily, a lower one for slow and erratic products. 98% for the top sellers and 90% for the slow ones is a common starting point. Each extra point costs more stock than the one before.
What if I do not know the variation of the lead time?
Write 0 and the formula covers demand only. If your supplier is sometimes a week late, a variation of 3 to 4 days is a fair first guess.
Why is the review period in the formula?
If you place orders once a week, an order placed today must last until the delivery of next week's order. The stock has to cover the lead time plus the days between two orders.
Does it work outside Excel?
The workbook uses standard functions and no macros. We tested it in Excel.
May I change it and share it?
Yes, freely, with or without credit.
The other free template: Cash conversion cycle and working capital days