Skip to content
Français

Retail demand with a hierarchy

  • Case study 3
  • 150 min
  • Optional · 1 case study needed for the certificate

Where we are

In eight modules, we forecast a hierarchy with slow sellers and set stock orders. In this case study, we do it on real open data: an online retailer's sales by country and product. We reconcile the hierarchy and set order quantities from a cost table.

What you will be able to do

Take a real online shop’s invoice lines to a stock order with its cost. You will clean cancellations without hiding demand and build a country → product group → product hierarchy. You will compare slow-seller methods in the course’s backtest and reconcile the hierarchy. Then you set the order, test the choice once, and say where it failed.

Case studyPortfolio piece

Licence
CC BY 4.0
Time
About 2.5 hours
Runs in
Colab, Kaggle or VS Code
Skills used
  • Cleaning cancellations
  • Building a hierarchy
  • Demand classes (ADI, CV²)
  • Croston, SBA and TSB
  • Weekly backtest
  • Bottom-up and MinT
  • Quantile stock order
  • Final test once

Colab and Kaggle open your own copy. To use VS Code, download the notebook from GitHub. Stuck? See the solution notebook.

The notebook runs every step of this page, with the same code and the same output, plus three tasks with checks. Its setup cells install the packages and download the data (15 MB) and the course’s backtest.py. It runs in about one minute after the install.

Before you startWhat to know and to have readyAbout 150 minutes

What you need to know

This case study uses the whole course. These lessons matter most:

What you need on your computer

Nothing: the notebook runs in Colab or Kaggle. To run it locally, use a virtual environment with Python 3.12, pandas 2.3.3, statsforecast 2.1.1 and hierarchicalforecast 1.5.3. The setup cell makes a new folder online-retail-forecasting. To start again, delete that folder.

Tested on macOS with Python 3.12, in Jupyter from a fresh start. Colab, Kaggle, Windows and Linux are not tested.

A UK online shop sells gifts and homeware, many of them to small shops that buy by the dozen. It keeps one warehouse and orders stock every four weeks.

Nadia buys the stock. Owen, a data analyst, builds the forecasts. We invented both people for this case study. The shop and its sales are real.

Each order can go wrong in two ways. The costs are the course’s assumptions, the same as Larkfield’s:

  • A unit short: a customer wanted it, and there was none. The shop loses the margin, 35% of the price.
  • A unit left over: it waits on the shelf and is sold later at a discount. That costs 25% of the price.

Nadia asks Owen: “How much of each product should I order for the next four weeks? And can the country managers and the buyers plan with the same numbers?”

You will build weekly forecasts for 200 products in six country groups, at every level of the hierarchy. They end in a stock order per product with its cost, tested once on four weeks that no step has seen.

The data is Online Retail II from the UCI Machine Learning Repository. It has every invoice line of the shop from 1 December 2009 to 9 December 2011. One line is one product on one invoice.

These are the columns that this case study uses:

Column What it means
Invoice The invoice number. A number that starts with C is a cancellation: the customer took the order back.
StockCode The product code. Some codes are not products, such as POST for postage.
Description The product name, in capitals.
Quantity The units on the line. Negative on a cancellation.
InvoiceDate The date and time of the invoice.
Price The price of one unit, in pounds (£).
Customer ID A number for the customer. We use it only to match a cancellation with its order.
Country The customer’s country.

We compared two other datasets. We could not confirm that the terms of M5, Walmart’s sales data, allow redistribution. The first Online Retail dataset has only one Christmas.

The notebook loads all the lines and counts the problems that matter for demand:

lines = pd.read_csv("online_retail_ii.csv.gz", dtype={"Invoice": str, "StockCode": str}, parse_dates=["InvoiceDate"])
cancel = lines["Invoice"].str.startswith("C")
print(lines.shape, "|", lines["InvoiceDate"].min(), "to", lines["InvoiceDate"].max())
print(f"exact repeated lines: {lines.duplicated().sum():,} | cancellation lines (C...): {cancel.sum():,} | "
f"price 0 or below: {(lines['Price'] <= 0).sum():,}")
print("lines per weekday (Monday = 0):", lines["InvoiceDate"].dt.dayofweek.value_counts().sort_index().to_dict())
(1067371, 8) | 2009-12-01 07:45:00 to 2011-12-09 12:50:00
exact repeated lines: 34,335 | cancellation lines (C...): 19,494 | price 0 or below: 6,207
lines per weekday (Monday = 0): {0: 189084, 1: 196626, 2: 185051, 3: 203149, 4: 153803, 5: 402, 6: 139256}

What this tells us. There are about a million lines over two years. Most of the 34,335 exact repeats come from the nine days that both Excel sheets contain. Saturday has almost no lines: the shop does not work on Saturdays. So a week is a natural unit for the forecast.

Three rules from the segmentation case study

Section titled “Three rules from the segmentation case study”

The first three rules are the same as in Segment online-shop customers, which explains them. Remove the exact repeats, the codes that are not products (postage, fees, vouchers) and the lines with a price of 0 or below.

removed because lines removed lines left
exact repeat of another line 34335 1033036
not a product (postage, fee, voucher) 5801 1027235
price 0 or below 5963 1021272

Here we keep the lines without a customer ID. A guest buyer’s order is still demand.

That leaves 17,915 cancellation lines with 467,740 units. There are two easy answers for them, and both are wrong.

  • Subtract every cancellation from the sales. Then a return in March makes March’s demand smaller. But the customer wanted the goods when they ordered. A returned unit is stock, not negative demand.
  • Ignore every cancellation. Then a mistaken order counts as demand. On 18 January 2011, a customer ordered 74,215 storage jars and cancelled the order 16 minutes later.

So Owen splits the cancellations in two. A cancellation of the same product, the same quantity, by the same customer, within 24 hours of an order undoes that order. That order was never demand, so both lines go. Every other cancellation is a real return: the order stays, and the return is not subtracted.

def undone_orders(clean):
"""Pairs of an order line and a cancellation of the same product, the same quantity and the same
customer, within 24 hours after it. Returns the index labels of the order lines and of the cancellations."""
c = clean[clean["Invoice"].str.startswith("C") & clean["Customer ID"].notna()]
s = clean[~clean["Invoice"].str.startswith("C") & clean["Customer ID"].notna()]
m = c.reset_index().merge(s.reset_index(), on=["Customer ID", "StockCode"], suffixes=("_c", "_s"))
gap = m["InvoiceDate_c"] - m["InvoiceDate_s"]
m = m[(m["Quantity_s"] == -m["Quantity_c"]) & (gap >= pd.Timedelta(0)) & (gap <= pd.Timedelta("1D"))]
m = m.assign(gap=gap).sort_values("gap")
m = m.drop_duplicates("index_c").drop_duplicates("index_s") # one order for one cancellation
return m["index_s"].tolist(), m["index_c"].tolist()
undone orders: 1,331 order lines
StockCode Description Quantity InvoiceDate
1065882 23843 PAPER CRAFT , LITTLE BIRDIE 80995 2011-12-09 09:15:00
587080 23166 MEDIUM CERAMIC TOP STORAGE JAR 74215 2011-01-18 10:01:00
order lines: 1,002,026, 10,973,734 units | other returns, not subtracted: 253,413 units

What this tells us. 1,331 orders with 214,327 units were undone; the two largest are 155,210 of them. The other 253,413 returned units are not subtracted from demand.

Common mistake

The best seller that nobody bought

Symptom: Owen ranks the products by units ordered from December 2009 to mid-April 2011, after removing the cancellation lines. The medium ceramic storage jar is the best seller.

Cause: the cancellation lines were removed, but the order that they undid was kept. All 74,215 jars came from one order, cancelled 16 minutes later.

Fix: remove an order and its cancellation together. With the fourth rule, the jar has no order at all before May 2011, and it is not one of the 200 products.

Consequence in AI work: a forecast trained on that week would order jars that nobody wants. Data errors become stock on a shelf.

Frame the forecast asks a few questions before any model. Owen’s answers:

  • What: units ordered per product, after the cleaning above. It is close to demand, but with no stock-out flag, a week with no order may be a week with no stock.
  • Unit of time: one week, Monday to Sunday. Only full weeks: 7 December 2009 to the week of 28 November 2011, 104 weeks.
  • Horizon: 4 weeks, the time that one order must cover.
  • Level: the order is per product, because there is one warehouse. The country managers plan per country, and the buyers per product group.
  • Which products: the 200 with the most units before the first test week, so the choice does not look at the future. They carry 39.8% of all the units.

The shop has no product categories. So Owen builds eight product groups from words in the description, for example “BAG” or “SHOPPER” for bags. This is our rule, not the shop’s catalogue.

Most customers are in the United Kingdom. Owen keeps the next four countries (Ireland, Germany, France and the Netherlands) and puts the others together. hierarchicalforecast’s aggregate() builds them from the bottom series, as in Hierarchies that must add up:

{'total': 1, 'total/country': 6, 'total/country/group': 48, 'total/country/group/product': 1048} | rows: 114712
A tree. At the top, the total. Below it, six country boxes with the number of products that sold there: United Kingdom 199, EIRE 181, Germany 174, France 173, Netherlands 128, other countries 193. Below them, eight product groups with their sizes. At the bottom, 1,048 country-product series, and an example path.
The hierarchy: 1 total, 6 countries, 48 groups in a country and 1,048 products in a country. The stock order adds each product's six countries up.
Text description of the diagram

A tree. At the top is the total. Below it are six countries, with the number of the 200 products ordered there before the test: United Kingdom 199, EIRE (Ireland) 181, Germany 174, France 173, Netherlands 128, other countries 193. Below them are the eight product groups in every country, 48 series: paper-and-crafts 40 products, other-gifts 32, bags 30, home-decor 27, party-and-baking 26, lights-and-candles 21, kitchen-and-table 18, christmas 6. At the bottom are the 1,048 country-product series.

A country-product pair with no order before the first test week is left out: its forecast would be zero anyway.

All 200 products, every week. Orders fall to zero in the Christmas closure. Some 2010 weeks have very large one-off orders. The 2011 weeks are lower than the 2010 weeks.
Text description of the chart

The line shows the units ordered per week for the 200 products. 79 of the 104 weeks are between 30,000 and 80,000 units. A few weeks in 2010 have very large orders, up to 104,518 units in the week of 27 September 2010. The weeks of 28 December 2009 and 27 December 2010 are zero. From April to November, the mean week was 50,314 units in 2010 and 36,621 units in 2011.

What this tells us. The shop closes at the end of December, and the weeks before it are busy. Single resellers can double a week with one order. And 2011 is about 27% lower than 2010 for these products. That matters later: they were chosen because they sold well before the test.

In the mean country-product series, 71.9% of the weeks have no order: 17.5% in the United Kingdom, and 84.6% in the other countries.

Think of a corner shop that sells umbrellas. Some weeks it sells none, then ten in one wet week. A forecast that works for bread fails here. Two numbers describe this kind of series, as Syntetos and Boylan proposed:

  • ADI, the average demand interval: the number of weeks divided by the number of weeks with an order. ADI 4 means one order in four weeks.
  • CV², the squared coefficient of variation: how much the order sizes vary, compared with their mean.

With the usual limits, 1.32 for ADI and 0.49 for CV², each series falls into one of four classes. Smooth: orders most weeks, of similar size. Erratic: orders most weeks, very different sizes. Intermittent: many empty weeks, similar sizes. Lumpy: many empty weeks, very different sizes. A tiny example: the weeks 0, 0, 12, 0, 0, 0, 12, 0 have ADI 8 ÷ 2 = 4 and CV² 0, so they are intermittent.

A 2 by 2 grid. Rows: orders most weeks (ADI up to 1.32) and many empty weeks (ADI above 1.32). Columns: sizes vary little and sizes vary a lot (CV² limit 0.49). Smooth 49 series (UK 49), erratic 106 (UK 106), intermittent 427 (UK 5), lumpy 466 (UK 39).
The four classes of the 1,048 series, with the UK count under each. 893 series (85%) are intermittent or lumpy; all smooth and erratic series are in the UK.
Text description of the diagram

A grid of four boxes. Top row, orders in most weeks (ADI 1.32 or less): smooth, 49 series, and erratic, 106, all in the UK. Bottom row, many empty weeks: intermittent, 427 series (5 in the UK), and lumpy, 466 (39 in the UK). The left column has sizes that vary little (CV² 0.49 or less). A note says that Croston, SBA and TSB are made for the bottom row, 893 series (85%).

What this tells us. The shop is two businesses in one table. The UK sells most products most weeks. Abroad, a product is ordered now and then, often in large amounts by one reseller. So this case study needs the slow-seller methods of Intermittent demand.

Owen uses the course’s backtest.py, with weeks. There are 7 origins, four weeks apart, from 18 April to 3 October 2011. Each forecast covers 4 weeks, and each method is refit at every origin. The four weeks after 31 October are kept for the final test.

Six methods, each with its reason:

  • Always 0: the floor, and a reminder of how MASE behaves on slow sellers.
  • Seasonal naive (52 weeks): the same week last year. The course’s baseline, and here it knows about Christmas.
  • ETS without a season: statsforecast’s ETS cannot take a 52-week season, so it smooths the level only.
  • Croston, SBA and TSB, the slow-seller methods. TSB uses a weight of 0.1 for both the order size and the chance of an order.

Each method forecasts the 1,048 bottom series. The stock order is per product, so the notebook adds the six countries up and scores the order with bt.stock_cost(). The costs are in pounds per 4-week order for the 200 products. (The helper’s column is named cost $; here it holds pounds.)

for name, make in METHODS.items():
forecasts[name] = bt.backtest(bot, stats(make), h=H, origins=ORIGINS)
row = bt.score(forecasts[name], bot, name, season=52)
product_row = bt.score(to_products(forecasts[name]), prod_hist, name, season=52, cost=cost)
row["product WAPE %"], row["cost"] = product_row["WAPE %"], product_row["cost $"]
rows.append(row)
method MASE WAPE % bias % product WAPE % cost
always 0 0.986 100.0 -100.0 100.0 70921
seasonal naive (52 weeks) 3.318 127.7 40.0 110.6 40858
ETS (no season) 1.515 88.0 10.7 70.8 25926
Croston 1.900 104.0 21.9 86.8 34243
SBA 1.847 101.2 15.8 84.4 34009
TSB 1.253 81.3 0.7 64.8 25035
The stock order's cost per 4-week order, 7 origins, 200 products. TSB is the cheapest (£25,035), just ahead of ETS. Always 0 is the most expensive.
Text description of the chart

Six columns: always 0 about £71,000, seasonal naive £41,000, ETS £26,000, Croston £34,000, SBA £34,000, and TSB £25,000, the lowest, highlighted.

Which column decides? The cost of the order, because it is the decision.

  • MASE prefers “always 0” (0.986), as in Intermittent demand. In 69.7% of the test weeks a series has no order, and a forecast of 0 is right on those weeks. Its order costs £70,921.
  • The seasonal naive forecast is far too high: +40% bias. Many of the 200 products had a good year before the test that did not repeat.
  • Croston and SBA are too high too (+21.9% and +15.8%). They change only in a week with an order, so they keep forecasting a product that stopped selling (failure case below).
  • TSB is the best on cost, WAPE and bias: £25,035, 81.3% and +0.7%. Its forecast falls in every week without an order.

The cheapest order still costs about 12% of what the 200 products sell in four weeks (about £203,000 at their median prices). Lumpy demand is expensive to plan, whatever the method.

The country managers and the buyers plan with the upper levels. Owen forecasts all 1,103 series with ETS. At every origin, the total’s own forecast is 7.5% to 16.5% lower than the sum of its 1,048 bottom forecasts.

Then he reconciles the ETS forecasts with bottom-up and MinT, and adds TSB’s bottom forecasts up as a fourth option. Over the 7 origins (about 30 seconds in the notebook):

WAPE % by level, 7 origins:
total country group in a country product in a country
ETS base (not coherent) 16.3 29.7 45.4 88.0
ETS bottom-up 17.9 29.4 45.3 88.0
ETS MinT 17.3 29.0 44.5 86.9
TSB bottom-up 15.7 27.4 42.1 81.3

What this tells us. TSB added up is the best at every level, even at the total (15.7% against ETS’s own 16.3%). MinT improves the ETS forecasts a little below the total; at Larkfield, it was best only at the product level. It cannot fix forecasts that are too high at the bottom. So Owen chooses TSB, bottom-up: one set of numbers for Nadia, the buyers and the country managers.

The order must also decide how much risk to take. The newsvendor idea gives the level (From forecast to order and roster). Order the quantile at the critical ratio, 0.35 ÷ (0.35 + 0.25) = 0.583. Then the order runs out in about 42% of the cycles, and the expected cost is lowest.

That needs a range for each product’s 4-week demand. Owen tries two:

  • Normal: the forecast total, plus or minus the spread of the last 26 weeks, as if the weeks were independent and the demand symmetric.
  • Empirical: the product’s own past 4-week totals in the last 52 weeks, scaled so that their mean equals the forecast total. A lumpy product has many small totals and a few big ones, and this range keeps that shape.
def empirical_order(point, history, origin, ratio=RATIO, weeks=52):
"""The `ratio` quantile of the product's past 4-week totals, scaled to the forecast total."""
tail = history[(history["ds"] <= origin) & (history["ds"] > origin - pd.Timedelta(weeks=weeks))]
sums = tail.sort_values("ds").groupby("unique_id")["y"].apply(lambda s: s.rolling(H).sum().dropna().to_numpy())
total = point.groupby("unique_id")["yhat"].sum()
out = {}
for u, t in total.items():
v = sums.get(u, np.array([]))
out[u] = t * np.quantile(v, ratio) / v.mean() if len(v) and v.mean() > 0 else t
return pd.Series(out).clip(lower=0)

For TSB’s forecasts:

critical ratio 0.583
TSB, the forecast total: cost 25035, runs out 0.386
TSB, normal 58.3% order: cost 25780, runs out 0.339, 80% range holds 0.654
TSB, empirical 58.3% order: cost 25968, runs out 0.422, 80% range holds 0.771

What this tells us. The normal range is too narrow: its 80% range holds only 65.4% of the real totals (see Score the range). The empirical range is close to honest: 77.1%, and its order runs out in 42.2% of the cycles, close to the expected 41.7%. But both quantile orders cost more than the plain forecast total.

The notebook moves the quantile of the empirical order from 0.3 to 0.8:

  • Empirical order at this quantile
  • The forecast total as the order (£25,035)
The cost of the empirical order is lowest near the critical ratio (0.583 to 0.6), as the theory says. The plain forecast total is cheaper still.
Text description of the chart

The line shows the cost of the empirical order for seven quantiles: 0.3 £32,038; 0.4 £28,628; 0.5 £26,811; 0.583 £25,967; 0.6 £25,896; 0.7 £27,094; 0.8 £32,373. Its lowest point is at 0.6. A dashed flat line at £25,035, the forecast total, is below every point.

What this tells us. With this range, the cheapest level is next to 0.583, as the theory says. But for a lumpy product, one large past order changes the range a lot. And the two costs are close (35% and 25%), so the best quantile is near the middle anyway. Here, the quantile costs about £930 more per order. So Owen recommends the forecast total as the order. With costs further apart, run the test again.

Owen has chosen TSB, bottom-up, with the forecast total as the order. Now he runs it once on the four weeks after 31 October 2011, in November, which no step has seen. He runs the other methods too, and changes nothing after.

product WAPE % bias % cost
always 0 100.0 -100.0 102729
seasonal naive (52 weeks) 89.8 44.6 43590
ETS (no season) 57.5 -11.3 37163
SBA 73.5 -9.4 52370
TSB 56.3 -21.0 41594
The final test, November 2011. ETS has the cheapest order (£37,163); the chosen TSB costs £41,594, because it is 21% too low.
Text description of the chart

Five columns, the cost of the November order: always 0 about £103,000, seasonal naive £44,000, ETS £37,000 (the lowest, highlighted), SBA £52,000, TSB £42,000.

What this tells us. The chosen method lost the final test. TSB has the best WAPE (56.3%) but a bias of −21.0%, and ETS’s order is £4,431 cheaper. The real total was 192,581 units; ETS forecast 163,672, and TSB only 152,097. November is the start of the Christmas season. TSB gives each new week a weight of only 0.1, so it follows a fast rise slowly. The comparison weeks, April to October, had no such rise, so the backtest could not show this.

Owen reports this result as it is. If he switched to ETS now, November would become a second validation set, with no test left.

The last step writes the order for the next four weeks from all the weeks up to 28 November 2011. Then it reads the newest lines, 5 to 9 December 2011, with the same cleaning:

December order: 165,642 units for 200 products
description order
product
22197 SMALL POPCORN HOLDER 7329
84077 WORLD WAR 2 GLIDERS ASSTD DESIGNS 4597
85099B JUMBO BAG RED RETROSPOT 4494
new lines 5-9 December: 17,446; undone orders: 81,162 units

What this tells us. First, the cleaning works on new data. On 9 December, a customer ordered 80,995 paper crafts and cancelled 12 minutes later, and the fourth rule removes both lines. That is more than the 200 products sell together in a typical week.

Second, the order ignores the calendar. TSB forecasts 41,388 units for each of the four weeks. But last year, the week of 20 December sold only 30.7% of the first week of December, and the week of 27 December nothing: the shop closes. A planner’s rule applies these shares to the last two weeks. With it, the order falls from 165,642 to 95,590 units, or from about £242,000 to £140,000 at the median prices. This rule comes from the calendar, as in Calendars, holidays and events. It is not tested: the data ends on 9 December.

Common mistake

Croston and the mug that stopped selling

Symptom: in the backtest, Croston is 21.9% too high, and its order costs £9,208 more than TSB’s.

Cause: Croston changes its forecast only in a week with an order. The black and white paisley flower mug (£1.25) had orders in 12 weeks, the last on 5 April 2010. One was 19,152 mugs from a reseller in Denmark. Croston’s average order size remembers it. At every origin, 18 months later, Croston still forecasts 1,129 mugs a week.

Fix: use a method that also learns from the weeks without an order. TSB lowers its forecast in every empty week: from 2.1 mugs a week at the first origin to 0.2 at the last. Its order for the mug costs £1 per cycle; Croston’s costs £1,412. 16 of the 200 products had no order in the 12 weeks before the last origin.

Consequence in AI work: a forecast that cannot see a product die keeps ordering it. Check the slow sellers’ last order date, not only their average.

  • Mugs ordered per week (0 since April 2010)
  • Croston: 1,129 a week at every origin
  • TSB: from 2.1 down to 0.2 a week
The paisley mug sold nothing after April 2010. Croston forecasts 1,129 a week in every test window; TSB's forecast is close to zero.
Text description of the chart

The mug’s weekly orders are zero from April 2010 to October 2011. In the test weeks, Croston is flat at 1,129 mugs a week, and TSB falls from 2.1 to 0.2.

Owen writes to Nadia in her words:

  • Order each product’s 4-week forecast from TSB, added up over the countries. In the comparison, it cost £25,035 per order, against £25,926 for ETS and £40,858 for “the same weeks last year”.
  • Use the same numbers for every level. The country and group totals are sums of the product forecasts, so every plan agrees. They were also more accurate than separate forecasts.
  • Before Christmas, be careful. In November, TSB was 21% too low, and ETS would have been £4,431 cheaper. For the October and November orders, compare both forecasts and last year’s weeks.
  • Apply the shop’s calendar to the December order. The shop closes over Christmas, and no method here knows it. With last year’s shares, the December order is 95,590 units, not 165,642.
  • Keep the order at the forecast, not at a quantile, for now. The quantile order was well calibrated, but it cost about £930 more per order.

And what not to use it for:

  • Not for new products. A product with no history gets no forecast here. See New products with no history.
  • Not for the thousands of smaller products without a new backtest. The 200 products carry only 39.8% of the units.
  • Not as a promise to a supplier. It is a forecast with a known error (WAPE 64.8% per product), not a contract.
  • Sales are not demand. With no stock-out flag, the forecast learns weeks with no stock as low demand.
  • The product groups are ours, from words in the descriptions. A real catalogue would group some products differently.
  • The choice of products looks backwards. A real shop would forecast every product it plans to keep.
  • One season in the comparison. The 7 origins run from April to October. With two years of data and a 52-week season, a backtest in last year’s November was not possible.
  • The cleaning rule is a judgement. “The same quantity within 24 hours” catches clear mistakes. A mistake cancelled after two days, or a part cancelled, stays as demand.
  • Assumed costs, old data. 35% and 25% are the course’s numbers, not the shop’s. The sales are from one UK shop in 2009 to 2011.

The course’s Watch the forecast shows how a bias alarm would catch the November problem in production.

  • The data is about real businesses. Customer ID is a pseudonymous number, used here only to match a cancellation with its order. Do not try to identify a customer, and do not publish analyses of single customers.
  • Credit the source. CC BY 4.0 allows you to share and change the data, with the attribution text above and a note of your changes.
  • An order affects other people. Suppliers plan their work from it, and left-over stock may be thrown away. State the error and the bias next to every forecast you hand over.
  • Label the assumptions. The costs, the product groups and the Christmas rule are choices. Say so wherever a number depends on them.

This case study shows an employer that you can take messy transaction data to a stock decision. It also shows that you report a result that goes against you. Make it yours with one change, for example other costs, more products or a seasonal adjustment for November. Then make it public and easy to read in five minutes.

What to put in the repository:

  • README.md, the page that people read first (template below).
  • The notebook, run from top to bottom, with its outputs.
  • cleaning.md: the four cleaning rules, with the lines and units that each one removed.
  • backtest_table.csv: the six methods with MASE, WAPE, bias and cost; and final_test.csv, kept apart.
  • The data source and licence, with the attribution text above. CC BY 4.0 lets you share the data with credit, but a link and a checksum are enough.

A README template:

# Weekly stock orders for an online gift shop, with a hierarchy
## Problem
One warehouse orders 200 products every 4 weeks. A unit short costs 35% of its price,
a unit left over 25% (assumptions). Country managers and buyers need numbers that add up.
## Data and licence
UCI "Online Retail II" (Chen), invoice lines Dec 2009 - Dec 2011, CC BY 4.0,
https://doi.org/10.24432/C5CG6D. Cleaning: repeats, non-products, price <= 0, and
orders cancelled within 24 hours (same customer, product, quantity) removed.
## Approach
Weekly series: total, 6 countries, 48 groups, 1,048 country-products (85% intermittent
or lumpy). Backtest: 7 origins Apr-Oct 2011, 4 weeks ahead. Always 0, seasonal naive,
ETS, Croston, SBA, TSB. Reconciliation: bottom-up, MinT. Order: forecast total vs quantile.
## Results
TSB bottom-up: lowest cost (25,035 per order vs 25,926 ETS), best WAPE at every level.
Quantile order calibrated but 4% more expensive. Final test (Nov 2011): TSB -21% bias,
ETS cheaper. Reported, not tuned.
## Limits
No stock-out flag; our product groups; products chosen from past sales; no Christmas in
the comparison; assumed costs.

How to describe it in an interview, in two sentences: “I built weekly stock orders for 200 products of a real online shop, with a reconciled country and product-group hierarchy, and chose TSB by the cost of the order in a rolling backtest. The final test showed that it fell 21% short in the Christmas ramp, and I reported that with a fix for the next cycle instead of tuning on the test.”

Go further:

  • Add one origin in November 2010 with ETS and TSB only, and see whether the comparison would have warned you.
  • Scale TSB by each group’s change from October to November last year, and backtest it.
  • Compare with the other case studies, Bike-share demand and fleet planning and Electricity load for many customers: where is the cost ratio far from one half?

Before you run it, predict: is ETS’s empirical order at the median (0.5) cheaper than TSB’s (£26,811)? In the notebook, section 7, compute the cost of the empirical order at ratio=0.5 for forecasts["ETS (no season)"], and put it in ets_median_cost.

Show the answer
ets_median_cost, ets_runs_out = order_cost(to_products(forecasts["ETS (no season)"]), prod_hist, empirical_order, 0.5)
print(ets_median_cost, ets_runs_out)
27657 0.47

ETS’s median order costs £27,657, more than TSB’s (£26,811), and it runs out in 47% of the cycles. ETS is 10.7% too high in the comparison weeks, and the median order keeps that bias.

Check your understanding

Owen removes an order and its cancellation together when they match within 24 hours, but he keeps other orders that were returned later, without subtracting the return. Explain both choices for a demand forecast.

Show the answer

An order that is cancelled within hours, with the same product and quantity, was a mistake: nobody wanted those goods. Removing only the cancellation line keeps that false demand, for example 74,215 storage jars in one week. A later return is different: the customer wanted the goods when they ordered, so the order was real demand. A returned unit changes the stock, not the demand. Subtracting it would make the demand of the return week too low.

Check your understanding

In the backtest, always 0 has the best MASE and TSB the lowest cost. Which one should Nadia use, and why do the two measures disagree here?

Show the answer

Nadia should use TSB. The decision is the stock order, and its cost is the measure that matches it: £25,035 against £70,921 for always 0. The measures disagree because 69.7% of the test weeks of a country-product series have no order. On those weeks, a forecast of 0 has no error, so MASE is low. But an order of zero loses every sale in the weeks with orders. On slow sellers, choose by the cost of the decision and look at the bias.

Check your understanding

The chosen method, TSB, lost the final test in November to ETS. Should Owen now switch to ETS? What should he do instead?

Show the answer

No. The final test is used once, to report how the choice performs. If Owen switches because of it, November becomes a second validation set, and no honest test is left. He should report the result as it is: TSB was 21% too low and £4,431 more expensive than ETS in the Christmas ramp. For the next cycle, he can compare ETS and TSB before the October and November orders. He can backtest a seasonal adjustment on new data, and watch the bias with an alarm.

Recap

  • You cleaned real invoice lines without hiding demand: orders undone within 24 hours went (214,327 units), and later returns were not subtracted.
  • You built a hierarchy of 1,103 weekly series; 85% of the bottom series are intermittent or lumpy.
  • In the backtest, MASE preferred “always 0”; the order cost chose TSB (£25,035 per order). TSB added up was the best at every level, better than ETS with MinT.
  • The quantile order at the critical ratio 0.583 was well calibrated but cost about £930 more than the forecast total.
  • The final test went against the choice: in November, TSB was 21% too low. The December order needs the shop’s calendar.

Next, the final assignment: a new region and 40 new products for Larkfield, from the forecast frames to the quarter’s stock order and roster.

Next: Final assignment