Reorder points that keep best sellers in stock without tying up cash
A year of weekly orders for an online wholesaler, replayed under the spreadsheet rule and under a forecast with an error-based buffer.
Executive summary
Setting the buffer deliberately moves fill from 84% to above 92%; a forecast with an error-based buffer then holds the same fill with 6% less stock.
Situation
Reorder points set in a spreadsheet from a recent average and a fixed margin. Best sellers run out before deliveries while slow lines tie up cash, and nobody can say which costs more.
Approach
A year of weekly ordering replayed for 300 stock codes of a UK online wholesaler, under the spreadsheet rule and under a forecast with a buffer sized from each code’s forecast error, each at several buffer settings.
Result
The buffer choice is the larger lever: fill rises from 83.8% to 91.7% with a 50% margin. At equal fill the forecast policy holds 6.3% less stock at 93.8% fill.
The business problem
A wholesaler usually sets reorder points in a spreadsheet: a few weeks of average sales plus a flat buffer for every line. Best sellers run out and slow lines sit in the warehouse. Distributors that move to buffers set per line from forecast error report higher fill rates on their important lines with the same or less stock.
I replayed a year of weekly orders under the spreadsheet rule and under a forecast with an error-based buffer, and measured the fill rate and stock each one needs.
- Team
- Buyers, demand planner, supply-chain manager; finance for cash
- Systems
- MYOB, Xero with Unleashed or Cin7, Netstock or a spreadsheet
- Decision
- How much to order for each stock code each week
- Measure
- Fill rate (share of demand supplied from stock) and average stock value
The approach
I rebuilt a full year of weekly ordering for the 300 best-selling stock codes of a UK online giftware wholesaler and replayed it twice. Each week the simulation receives deliveries ordered two weeks earlier, supplies what it can, records what it could not, and places the next order.
- Spreadsheet rule
- Order up to three weeks of the last four-week average, plus a fixed percentage buffer
- Forecast policy
- Order up to three weeks of a smoothed forecast, plus a buffer of z times the recent forecast error
- Lead time and review
- Two-week supplier lead time, weekly ordering
- Unmet demand
- Lost, not back-ordered, as with most online orders
Results
Running each policy at several buffer settings draws its trade-off between stock held and demand filled. The forecast policy sits below the rule across the useful range: the same fill rate for less stock.
The larger lever is the buffer itself
The choice of buffer moves the result far more than the choice of forecast. With no buffer the rule fills 83.8% of demand and 12.0% of stock-code weeks run short. A 50% buffer lifts fill to 91.7% and cuts unsupplied units from 394,572 to 200,701, for 77% more stock. A buyer who has never seen this curve is choosing a point on it blind.
| Policy and setting | Fill rate | Stock-code weeks out of stock | Average stock value | Units not supplied |
|---|---|---|---|---|
| Rule, buffer 0% | 83.8% | 12.0% | £235k | 394,572 |
| Rule, buffer 25% | 89.0% | 6.7% | £322k | 266,448 |
| Rule, buffer 50% | 91.7% | 3.8% | £414k | 200,701 |
| Rule, buffer 75% | 93.3% | 2.4% | £511k | 163,964 |
| Rule, buffer 100% | 94.1% | 1.5% | £612k | 142,210 |
| Rule, buffer 150% | 95.1% | 0.8% | £822k | 118,220 |
| Rule, buffer 200% | 95.7% | 0.5% | £1,042k | 105,632 |
| Forecast, z = 0 | 83.5% | 12.3% | £244k | 399,629 |
| Forecast, z = 0.5 | 88.5% | 6.9% | £314k | 278,595 |
| Forecast, z = 1.0 | 91.3% | 4.0% | £386k | 212,328 |
| Forecast, z = 1.28 | 92.2% | 3.3% | £427k | 189,327 |
| Forecast, z = 1.65 | 93.2% | 2.5% | £483k | 166,210 |
| Forecast, z = 2.0 | 93.8% | 1.9% | £536k | 150,821 |
| Forecast, z = 2.5 | 94.4% | 1.3% | £612k | 136,718 |
Buffers by value class
A common refinement is to hold different service levels by value class. I grouped the stock codes by their share of demand value in the warm-up period:
| Value class | Stock codes | Share of demand value |
|---|---|---|
| A: top 80% of value | 137 | 80% |
| B: next 15% | 78 | 15% |
| C: last 5% | 85 | 5% |
Giving the low-value C class a higher buffer and the A class a lower one moves along the same curve rather than below it on this data. The settings tested:
| Buffers by class (A / B / C) | Fill rate | Average stock value |
|---|---|---|
| A 1.0 / B 1.65 / C 2.33 | 92.5% | £444k |
| A 1.28 / B 1.65 / C 2.33 | 93.0% | £469k |
| A 0.84 / B 1.28 / C 2.05 | 91.9% | £410k |
| A 1.0 / B 2.0 / C 2.5 | 92.7% | £459k |
| A 1.28 / B 2.0 / C 2.5 | 93.2% | £484k |
The class split is still useful for governance: buyers review A items weekly and leave C items on automatic ordering.
Data and method
- Data
- UCI Online Retail II (Chen, 2019, CC BY 4.0): 1,067,371 invoice lines from a UK online giftware wholesaler, December 2009 to December 2011. Returns and non-product codes removed
- Scope
- The 300 best-selling stock codes by units, 102 weeks of demand
- Test period
- 48 weeks from 2011-01-03 to 2011-11-28; 2010 is warm-up for forecasts and error estimates
- Stock value
- Units on hand valued at 60% of the median selling price, a cost proxy
- Forecast
- Exponentially weighted average, weight 0.3, updated weekly
- Buffer
- z times the standard deviation of the last 26 weeks of forecast error, scaled by the square root of lead time plus review
Limitations
- Recorded sales stand in for demand. When an item was out of stock in the real business, lost demand is missing from the history, so true demand is somewhat higher.
- Lead time is fixed at two weeks for every supplier. Real lead times vary and should be measured per supplier.
- Stock value uses a cost proxy, so the pound figures follow that proxy; the percentage differences do not depend on it.
- Order minimums, pack sizes and supplier discounts are not modelled, and they often decide the real order.
What a retail team would do next
- Export two years of weekly sales by stock code and current supplier lead times
- Draw this curve on their own data to see where today’s margin sits
- Agree a fill-rate target per value class with finance
- Run the forecast policy as a suggested order beside the buyer’s for a season, then switch
Technical depth
The simulation runs all stock codes in parallel as arrays, so a full year replays in under a second and the trade-off curve is cheap to redraw for new settings. Inventory, the order pipeline and forecast error are tracked per code; fill rate and stock-out weeks are counted only in the test year.