Manaz Afzal
Retail & E-commerce · Stock reorder forecasting

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.

83.8%of demand filled with no buffer
92.2%filled by the forecast policy at £427k stock
3.8%less stock at a 92% fill rate
7.2%less stock at a 94% fill rate

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.

83.8%of demand filled by a reorder rule with no safety margin
6.3%less stock for the same 93.8% fill rate
A$126,262a year in holding cost on A$8m of 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
0k50k100k2010-012010-072011-012011-07Units sold a weekPre-Christmaswholesale peak
Demand is seasonal and lumpy: a wholesale peak from September to November and a sharp drop over Christmas. Data: UCI Online Retail II, weekly units for the 300 best-selling stock codes

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.

Sales historyWeekly unitsper stock codeForecastSmoothed weeklydemandBufferSized from recentforecast errorOrderUp to cover leadtime and review
The forecast policy replaces a fixed percentage margin with a buffer sized from each stock code’s own forecast error.
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.

£200k£400k£600k£800k£1000k84%86%88%90%92%94%96%Spreadsheet ruleForecast with error-based bufferRule, 50% bufferForecast, z = 1.28Share of demand filled from stock (fill rate)
At the rule’s 50% buffer, fill is 91.7% with £414k of stock. The forecast policy reaches 92.2% with £427k. The gap widens as the target fill rises.
92.2% fillrule £444k, forecast £427k£17k93.2% fillrule £505k, forecast £483k£23k93.8% fillrule £572k, forecast £536k£36k94.4% fillrule £660k, forecast £612k£47k
At equal fill the forecast policy holds 3.8% less stock at a 92% fill rate and 7.2% less at 94%.

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 settingFill rateStock-code weeks out of stockAverage stock valueUnits not supplied
Rule, buffer 0%83.8%12.0%£235k394,572
Rule, buffer 25%89.0%6.7%£322k266,448
Rule, buffer 50%91.7%3.8%£414k200,701
Rule, buffer 75%93.3%2.4%£511k163,964
Rule, buffer 100%94.1%1.5%£612k142,210
Rule, buffer 150%95.1%0.8%£822k118,220
Rule, buffer 200%95.7%0.5%£1,042k105,632
Forecast, z = 083.5%12.3%£244k399,629
Forecast, z = 0.588.5%6.9%£314k278,595
Forecast, z = 1.091.3%4.0%£386k212,328
Forecast, z = 1.2892.2%3.3%£427k189,327
Forecast, z = 1.6593.2%2.5%£483k166,210
Forecast, z = 2.093.8%1.9%£536k150,821
Forecast, z = 2.594.4%1.3%£612k136,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 classStock codesShare of demand value
A: top 80% of value13780%
B: next 15%7815%
C: last 5%855%

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 rateAverage stock value
A 1.0 / B 1.65 / C 2.3392.5%£444k
A 1.28 / B 1.65 / C 2.3393.0%£469k
A 0.84 / B 1.28 / C 2.0591.9%£410k
A 1.0 / B 2.0 / C 2.592.7%£459k
A 1.28 / B 2.0 / C 2.593.2%£484k

The class split is still useful for governance: buyers review A items weekly and leave C items on automatic ordering.

Stock code 22423 (£12.75)409Weekly units, 2011Stock code 85123A (£2.95)3,329Weekly units, 2011Stock code 23166 (£1.25)74,215Weekly units, 2011
The highest-value stock codes are the most erratic, which is why a buffer sized from each code’s own error helps most on them.

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

What a retail team would do next

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.

Simulation code (Python) · Value-class runs (Python)

Financial impact

About A$505,049 of stock released for every A$8m held, at the same fill rate

The saving is cash released from stock at an unchanged fill rate, and the yearly holding cost on that cash.

InputValueSource
Average stock valueA$8,000,000 mid-market wholesaler or retailer
Target fill rate93.8%Measured from the simulation
Stock needed by the spreadsheet rule at that fill£572kMeasured simulated, cost proxy
Stock needed by the forecast policy£536kMeasured
Holding cost a year25% of stock value capital, space, shrinkage and markdowns

Calculation

  1. Stock reduction at equal fill6.3%
  2. Cash released on A$8m of stockA$505,049
  3. Holding cost avoided a yearA$126,262

Yearly holding cost avoidedA$126,262

Fewer stock-outs on best sellers are a separate gain if the business currently runs a thin margin; they are not counted here.

Implementation and rollout

Ten weeks from sales export to suggested orders the buyer trusts

  1. Weeks 1 to 2

    Export and baseline

    Two years of weekly sales by stock code, supplier lead times and current reorder settings; measure today’s fill rate and stock value.

    Buying lead, Manaz

  2. Weeks 3 to 4

    Draw the curve

    Replay last year under the current rule and the forecast policy; agree a fill-rate target per value class with finance.

    Manaz, finance

  3. Weeks 5 to 8

    Suggested orders

    Each week the forecast order appears beside the buyer’s; differences are discussed and logged.

    Buyer

  4. Weeks 9 to 10

    Switch over

    Forecast orders become the default for B and C items; A items stay buyer-approved. Monthly review of fill and stock.

    Buyer, Manaz on support

Tools

Shopify, Cin7 or Unleashed sales and stock exports, Python simulation and forecast, run weekly, Suggested orders written to a Google Sheet or the inventory system’s purchase-order draft, A monthly fill-rate and stock-value report

Team

  • Supply-chain or operations manager sets the fill-rate target and signs off the switch
  • Buyer reviews suggested orders and supplier constraints
  • Manaz data work, simulation, forecast, integration and monitoring

The timeline is a plan for a typical engagement; it depends on data access and how many systems are involved.

Risks and mitigations

What could go wrong in production, and the control for each

RiskMitigation
Lost sales are missing from the historyFlag weeks where an item hit zero stock and treat their demand as a lower bound.
Supplier lead times varyMeasure lead time per supplier from purchase-order and receipt dates and refresh it quarterly.
Promotions and new lines have no historyBuyer overrides for promoted and new items; the forecast starts once eight weeks of sales exist.
Order minimums and pack sizesRound suggested orders to pack size and minimum order quantity before they reach the buyer.

Key takeaways

Key takeaways

  • With no margin, a spreadsheet rule fills 84% of demand and runs short in 12% of stock-code weeks.
  • Choosing the buffer on a drawn trade-off curve is the first and largest gain.
  • A forecast with an error-based buffer then holds 6% less stock for the same fill at the higher service levels.
  • Value classes help with governance more than with stock on this data.