Stock Replenishment Planner (Forecast & Safety Stock)

About the Stock Replenishment Planner (Forecast & Safety Stock)

Tells you what to order now, item by item. Open your sales history — a sales, POS or marketplace export with a date, a code and a quantity — and your stock file with each item's stock on hand (and, if you have them, the stock on order, supplier, lead time, minimum order quantity, case pack and cost), or take both from the Stock Register in this browser.

Every SKU gets its own forecast. Five methods are tried — a moving average, exponential smoothing, smoothing with a damped trend, Holt-Winters for a season, and Croston's method for slow, lumpy sellers — and the one that best forecast the last part of the item's own history is used, with its accuracy shown (WAPE, MASE and bias). Days the item was out of stock are left out, so lost sales do not pull its forecast down.

From the forecast, the forecast error, your service level and the lead time, the plan works out the safety stock, the level to order up to and the order quantity, rounded up to whole case packs and the MOQ, and groups the orders by supplier, with the days of cover, the day each item runs out, and the overstocked and dead items.

Without a pass the plan is a free preview: how many SKUs to order, are out of stock or will run out, and the first rows of the plan with the forecasts and quantities hidden. The workbook, the CSV files and the purchase order files unlock with a Premium pass. Your files are read in your browser and never uploaded.

Common uses

• The weekly or monthly order to each supplier for a shop, pharmacy, distributor or online seller.
• Finding the items about to run out before it is too late to order them.
• Spotting overstock and dead stock to stop ordering, return or clear.
• Checking how forecastable each item is before setting service levels.

How each item is forecast

Sales are added up per period — by day, week or month (daily sales are planned by week once there are eight weeks of them) — from each item's first sale. Then the methods of Forecasting: Principles and Practice are tried:

• Moving average of the last 3 to 12 periods.
• Exponential smoothing: a level that follows recent sales.
• Damped trend: a level and a trend that flattens out, so growth is not carried on for ever.
• Holt-Winters: level, trend and a season (the days of the week for daily planning, the year for weekly or monthly), once there are more than two seasons of history.
• Croston's method for items that sell in some periods and not in others: the size of the sales and the gaps between them are smoothed separately (Croston).

The last fifth of each item's history is held back, every method forecasts it from the rest, and the one with the smallest MASE (the mean absolute error divided by the error of simply repeating the previous period) wins. The plan also shows the WAPE (absolute errors as a share of sales) and the bias (above zero: the method forecast too much). The winner is then fitted to the whole history for the forecast. An item with fewer than six periods of history gets an average of what there is.

Stock-outs and part periods

Sales on days an item was out of stock are not demand: customers wanted it and could not buy it. Days marked out of stock (an out-of-stock column, a closing stock of zero, or a Stock Register with no stock that day) are left out, and the period is scaled up from the days in stock: 28 sold in 4 days in stock counts as 49 for the week. A period with fewer than half its days in stock is left out. The first period of an item that starts mid-week is scaled the same way, and a last week or month only partly in the file is left out.

Safety stock and the order

The plan follows the periodic-review, order-up-to policy of Silver, Pyke & Thomas: you order every review period, and the order has to last until the next order arrives — the lead time plus the review period, P.

• Safety stock = z × √(P × σ² + d² × σL²), where z is the normal value for your cycle service level (1.645 for 95%), σ the item’s one-step forecast error, d its demand per period and σL the variation of the lead time in periods. With a fixed lead time it is z × σ × √P.
• Order-up-to level = the forecast demand over P + the safety stock.
• Order = the order-up-to level − (stock on hand + stock on order), when above zero, rounded up to whole case packs and to at least the MOQ.

The cycle service level is the chance of not running out before the next order arrives: 95% means about one review cycle in twenty runs short. The reorder point for ordering whenever stock falls low (demand over the lead time plus its own safety stock) is in the workbook too.

Running out, overstock and dead stock

Each item’s stock is played forward day by day at its forecast, with the stock on order arriving on its due date (or one lead time away): that gives the days of cover and the day it runs out. Items that run out before an order placed today could arrive are listed first. An item with more days of cover than you set (120 to start with) is overstocked; one with stock and no sale for longer than you set (90 days) is dead stock and is never ordered.

Purchase orders

The orders of each supplier save as a file the Purchase Order Generator opens with “Open file”: the supplier, the items with quantities, units and costs, and a required-by date one lead time away. Add your details, check the prices and tax rates there, and send it.

Limitations

• A forecast from past sales: promotions, price changes, new competitors and one-off orders are not known to it. Adjust the quantities with what you know.
• An item’s history starts on its first row in the file; an item that was in stock earlier without selling is not known to have been.
• The safety stock assumes forecast errors that are roughly normal and independent from period to period; for very slow sellers it is an approximation.
• Quantities are in the units of your files; the plan does not convert between units or check prices and taxes.

Privacy

Your sales and stock files are read in this browser and never uploaded; only the settings you choose are remembered on this device.