Bottom-up, top-down and middle-out

Today let's understand what to do when forecasts made one series at a time do not add up.

Priya is a demand planner at a bakery chain. The chain has 9 stores in 3 regions: North, Central and South. She has to give finance the forecast of monthly units sold for 2025, for the whole chain, for each region and for each store. That's 13 series in all, and finance will add them up.

So she forecasts each of the 13 series on its own. But when finance adds up the store forecasts, the region forecasts and the total forecast, the three do not match. The same question, how many units will we sell, has three different answers.

There are three standard ways to fix this: bottom-up, top-down and middle-out. We will build all three on the same data and see what each one costs in accuracy.

The sales are simulated to look like real bakery data. The chart shows the chain's total units for each month from January 2019 to December 2024.

Switch between line and point. Every December is the highest month of its year, and sales trend upward over the years.

Why forecasts made series by series do not add up

Let's build Priya's data first, because every number from here on comes from it.

The 9 stores roll up into a hierarchy with three levels. At the bottom are the 9 stores. In the middle are the 3 regions, and each region is the sum of its 3 stores. At the top is the total, the sum of all 9 stores.

The sales are simulated for 84 months, January 2019 to December 2025. Each store has its own starting level, its own growth per month and its own noise, and all stores share one seasonal shape that peaks in December. N3 grows the fastest, at 1.5% a month.

Each column is a store and each row is a month, so these are the first three months of 2019.

Now we add the stores up to regions and a total. A region or a total is just the sum of the stores under it.

The last line confirms that the actual sales add up in every one of the 84 months. Nothing forces the same to hold for forecasts.

Months 1 to 72, January 2019 to December 2024, are the training months. Months 73 to 84 are the year 2025. That year is the holdout: data that no model is fit on, kept back so we can measure each forecast against what was really sold.

Next, one model per series. We fit an ETS model to each of the 13 training series and forecast the 12 months of 2025. ETS is exponential smoothing with an error term, a trend and a seasonal term.

Setting model = "MAM" fixes every series to multiplicative error, additive trend and multiplicative seasonality, and damped = FALSE keeps the trend going in a straight line instead of flattening it out over the forecast. With the same model form for all 13 series, the only thing that will differ between the methods later is how the forecasts are combined.

A forecast made for one series with its own model, without any regard for the other series, is called a base forecast. To see whether the 13 base forecasts agree with each other, we add up the base forecasts of each series' children, the series directly below it, and compare the sum with the series' own base forecast.

Read the Total row first. For January 2025 the three region forecasts add up to 2,856.0, but the total's own forecast is 2,873.7. That is a gap of 17.7 units. Inside North, the three stores add up to 1,126.1 while North's forecast is 1,117.0, a gap of -9.2.

A set of forecasts is coherent when every region equals the sum of its stores and the total equals the sum of the regions. These base forecasts are not coherent. The 13 models were fit separately, so nothing made them agree.

The gaps do not go away over a year. Let's add the 12 forecast months at each level.

The total's model says 39,612 units for 2025. The region models add up to 39,604 and the store models add up to 39,720. What was actually sold is 39,779. So the same year has three different forecasts.

Finance cannot plan against three numbers. Priya needs one set of forecasts that is coherent. Turning base forecasts into a coherent set is called reconciliation, and there are three standard rules for it.