Demand forecasting & reorder simulation for an online retailer

The problem

An online retailer reorders stock from last month’s sales and a spreadsheet. The result is predictable: the products that sell out were under-ordered, and the ones gathering dust tie up cash that could have gone into the winners. Neither mistake shows up on a single invoice; they show up later, as lost sales and as stock that has to be discounted.

The question this project answers is narrower and more useful than “can we predict demand?”: how much should be reordered each week, for each product, and what does it cost, in stock held and in sales lost, to be more or less careful?

The data

Two years of a real UK online retailer: 1,067,371 raw transaction rows → 1,003,246 after cleaning (cancellations −19,494, fees/adjustments −4,699), 4,897 SKUs over 106 weeks, £19.6M revenue. The seasonal spike is Christmas: most of this catalogue is giftware.

It is a good stand-in for a real client export precisely because it is messy: cancellations mixed in with sales, postage and fee lines coded like products, a long tail of items that sell a few units a week, and a Christmas peak that dwarfs the rest of the year. Every cleaning rule lives in the code, and the rows each one removed are counted above.

Approach

Method, in brief. The analysis is fully scripted and reproducible with one command:

Weekly grain, SKU level; every series densified so zero-demand weeks count as demand signal, not gaps.
ABC segmentation by revenue share crossed with demand pattern (smooth vs intermittent).
Modeling universe: the 2,487 SKUs still commercially alive (≥60 weeks of history, sold in the last 12), covering 78% of total revenue.
Four models (Naive, 4-week moving average, AutoETS, Croston) backtested with a rolling-origin walk-forward: 6 origins × 4-week horizon, expanding window. No random splits on time series.
Error metric: WMAPE (weekly SKU series are zero-heavy; plain MAPE would be meaningless).
The reorder replay sizes safety stock on a separate calibration window (the 24 weeks before the evaluation), so no error from a scored week ever informs the policy.

The models are deliberately simple and transparent: the goal is a forecast a buyer can trust and explain, not a leaderboard score. A client version would add what this dataset lacks: promotions, price changes and web-analytics traffic as signals, supplier lead times and minimum order quantities in the reorder policy, and a seasonal model once enough peaks of history exist.

Results

On the segment that matters most (A-smooth, 940 SKUs driving the bulk of revenue), the best models cluster at WMAPE 0.65 vs 0.74 for naive: a ~12% error reduction. The honest fine print: the 4-week moving average ties AutoETS on WMAPE, but AutoETS wins on bias (−6% vs −9%), and bias is what inflates or starves stock. Croston, the textbook model for intermittent demand, loses on exactly that segment (0.94, bias −41%).

The retailer’s top-revenue product (a three-tier cake stand, £331k over the two years), shown over its final year: actual weekly units vs the AutoETS backtest forecast (dashed) across the six evaluation origins. A live view of what a 4-week-ahead forecast gets right and what it smooths over.

From forecast to reorder decisions

The 24 evaluation weeks replayed with a weekly order-up-to policy on the 763 A-smooth SKUs; each point is a safety-stock factor z. Safety stock is sized on forecast errors from the 24 weeks before the replay window, never on the weeks being scored. Forecast alone (z=0) fills 64.7% of demand and loses £1.12M of sales; z=1.5 lifts service to 85.7% holding ~£178k of average stock; z=2.5 reaches 89.9% at £283k. Note the flattening: buffers sized on January–June errors come up short in a replay window (late June to early December) that runs into the Christmas ramp. The curve’s shape, not any single point, is the deliverable: it lets an owner pick the service level they can finance, and shows where extra stock stops buying service.

Try it: pick your own service level

Move the safety factor and the replay re-runs in your browser, using the same policy, data and code path that produced the chart above and reproducing its numbers exactly. Then pick a product to see what that choice means week by week: when stock ran out, and how much demand walked away.

Loading the simulator…

This is the conversation the curve is for. Moving from z=1.0 to z=1.5 raises service by 3.8 points (81.8% to 85.7%) for about £50k more stock held on average; the same half-step from z=2.0 to z=2.5 buys only 1.7 points for about £53k. Where to stop is a business decision (margin, cash, and what a stockout costs you with your customers), and the curve puts a price on each option before any stock is bought.

Limitations & what didn’t work

Real caveats from this build, the parts a sales pitch would hide:

Survivorship by design: the universe keeps only SKUs still selling; a production system must also decide when a product is dead (that’s a policy, not a forecast).
No seasonal model was tested in this build. With two years of data each SKU has at most one prior Christmas to learn from; seasonality is the first thing a client version with longer history should add.
Croston lost on intermittent demand. The method exists for exactly that pattern, and a 4-week moving average beat it throughout. Textbook defaults are hypotheses, not answers.
An earlier version of this simulation leaked. Safety stock was sized on forecast errors from the same weeks the policy was then scored on, which flattered every point of the curve: at z=2.5 it showed 96.8% service where the leak-free replay gets 89.9%. Fixed by sizing on an earlier calibration window; the numbers above are the corrected ones.
Sizing buffers on a calm period under-protects the peak. The calibration weeks carry smaller errors than the replay weeks that run into Christmas, so service flattens below 90% even at z=2.5. A production policy would size safety stock by season, or on a window that includes the previous peak.
Sales ≠ demand: the data records what sold, not what was demanded during stockouts (censored demand). Fill rates in the simulation are therefore optimistic at the margin.
Holding cost proxy: on-hand stock is valued at selling price (the dataset has no purchase costs); a client version would use real unit economics and add lead times, MOQs and pack sizes.

None of these caveats is exotic: they are the questions any stock decision built on this analysis has to answer first, which is why they are written down rather than left out.

What this would mean for your business

If you sell physical products online, the same work on your own order history gives you three things: a weekly reorder quantity for each product, the price, in stock held and sales lost, of every service level you might choose, and code you own. It runs as a fixed-scope project: see Services, or get in touch.