Top Down, Bottom Up and Middle Out

A small but important new addition to SkuBrain is the ability to specify the reconciliation level when generating hierarchical forecasts.

Reconciliation Levels

The what on the what?

Forecast hierarchies

If you don’t already know, forecast hierarchies let you create a tree (or hierarchy) of forecasts for the products you sell. At the bottom of the hierarchy you have sales histories and forecasts for specific SKUs (e.g. blue-sweater-small). Further up the hierarchy you have the sales histories and forecasts for aggregate product groups such as Product Type (e.g. sweaters)… and at the tippy top you have the sales history and a forecast for the aggregate sales of all of the products that you sell.

So what use is a hierarchy? For one thing, sometimes for business planning purposes it’s much more appropriate to take a birds eye view. Referring to the top level (All Sales) sales vs. forecast gives you a good idea, very generally, of how you’re tracking to your business plan. If things are going much better or worse than expected, then digging down into the details (by department, region, category or brand or whatever) can help you identify your trouble spots.

However from a forecasting perspective, hierarchies can also help you create more accurate forecasts. In a previous article we discussed how choosing an appropriate hierarchy could improve forecast accuracy. In that post we showed how a top down forecast could be used to push trends and seasonal selling patterns down from higher level forecasts to forecasts lower down in the hierarchy (where trend and seasonality was harder to detect).

With the introduction of bottom up and middle out forecasting, you can now specify lower levels in the hierarchy as the reconciliation level. So if you think your brand forecasts or your category forecasts express the trend and seasonal selling patterns of your products better than the root level (all sales) forecast, you can now use a middle out reconciliation strategy. Alternatively, if you’ve got a decent amount of sales history for each individual SKU that you sell, you could use a bottom up reconciliation strategy.

In this article, I’ll describe how bottom up and middle out reconciliation works. Firstly though, let’s got back to basics and see what reconciliation is.

Forecast reconciliation

Assuming you have a forecast hierarchy like All Sales > Brand > SKU, you’re going to end up with a tree of forecasts consisting of three different levels. At the top level you’ll have a single forecast for the aggregate demand of all of the products that you sell. At the second level you’ll have one or more forecasts/branches for each different type of product that you sell and at the bottom level (under each product type forecast) you’ll have the forecasts for individual SKUs.

Forecast reconciliation simply the process of ensuring that the sum of the forecasts for the individual product SKUs for any period in the forecast horizon add up to the forecast for that relevant brand and that the sum of all the brand forecasts add to the All Sales forecast.

For example, imagine you sell death traps for two different brands: Acme and Q. Now assume the following forecasts:

Product Forecast
q-exploding-lighter 5
q-razor-hat 2
acme-boulder 33
acme-shrink-gun 14
acme-magic-hole 27

Bottom up reconciliation

The simplest way to “reconcile” these forecasts is simply to sum them up in order to produce the aggregate forecasts – which is known as bottom up reconciliation. In this case, we end up with the following brand forecasts:

Brand Forecast
Q 5 + 2 = 7 units
Acme 33 + 14 + 27 = 74 units

And the following aggregate (top level) forecast:

Root Forecast
All Sales 7 + 74 = 81 units

Top down reconciliation

Whilst bottom up reconciliation is fine – it doesn’t add much to our forecasting process… it is pretty much just a fancy phrase for “summarizing”.

A more sophisticated reconciliation approach is what’s known as a top down reconciliation. When performing a top down reconciliation, we don’t generate the brand and root level forecasts simply by summing all the child forecasts. Instead we aggregate the child sales histories and produce separate statistical forecasts for each level in the hierarchy based on those sales histories (this process is described in greater detail in the documentation on hierarchies).

The motivation for doing this is that by aggregating sales data we can potentially get much more accurate forecasts. To give an example that demonstrates this, the owner of a corner store might be able to quite accurately forecast how many bottles of milk he is likely to sell in any given day. However, if you ask him to forecast how many bottles of milk he’ll sell in any 5 minute period, he might not be so sure and his may be quite inaccurate.

Similarly, just as sales can be grouped temporally (by day, week, month etc.), they can also be grouped by other characteristics such as brand, product category or any number of other attributes. For example, if you sell clothing and you have different collections such as Summer, Autumn, Winter and Spring, it would probably make sense to consider the selling patterns of the aggregate sales for each of these collections when trying to gauge demand for individual products.

The difficulty with this approach is that the forecasts for individual product SKUs won’t always (initially) add up to the forecasts, for the same period, produced by the analyzing aggregate sales for groups that these products belong to (such as all the sales of a particular brand). As such, some kind of reconciliation is required.

For example, when running separate forecast analysis against SKUs, brands and global sales, the following resulting forecasts would be entirely possible:

Root Forecast
All Sales 125

Brand Forecast
Q 11
Acme 96

Product Forecast
q-exploding-lighter 5
q-razor-hat 2
acme-boulder 33
acme-shrink-gun 14
acme-magic-hole 27

Note, in particular, that the sum of the SKU level forecasts for all of the Acme products does not add up to the brand level forecast for Acme products. Similarly, the sum of the brand level forecasts does not equal the root level forecast (11 + 96 ≠ 125).

Reconciled

When performing a top down reconciliation this is allocating or distributing parent forecasts to children proportionately. So, for example, the Root level forecast (for sales of all products) is for 125 units. The sum of the brand forecasts is 11 + 96 = 107 units. We expect Q products to make up 11/107ths of the total and Acme products to make up 96/107ths. So, reconciling the two:

Brand Forecast
Q 11 ÷ 107 × 125 = 12.85 units
Acme 96 ÷ 107 × 125 = 112.15 units

And finally we do the same for the product level forecasts vs each of the brand forecasts:

Product Forecast
q-exploding-lighter 5 ÷ 7 × 12.85 = 9.18 units
q-razor-hat 2 ÷ 7 × 12.85 = 3.67 units
acme-boulder 33 ÷ 74 × 112.15 = 50.01 units
acme-shrink-gun 14 ÷ 74 × 112.15 = 21.22 units
acme-magic-hole 27 ÷ 74 × 112.15 = 40.92 units

And now the SKU level forecasts and the brand level forecasts both sum to 125 units (matching the root level forecast).

Middle out forecasting

Middle out reconciliation is a mix of both the top down and the bottom up strategy. A middle out strategy would be where we perhaps generated statistical forecasts for the individual SKUs and the separate brands, but didn’t generate a root level statistical forecast for all products.

So for example, take the same product/brand forecasts we had above:

Brand Forecast
Q 11
Acme 96

Product Forecast
q-exploding-lighter 5
q-razor-hat 2
acme-boulder 33
acme-shrink-gun 14
acme-magic-hole 27

Reconciled

The aggregate root level forecasts are produced in the same manner as a bottom up forecast (so simply add all the brand forecasts together):

Root Forecast
All Sales 11 + 96 = 107 units

Whereas the SKU level forecasts get reconciled as with a top down forecast (disaggregated from the brand):

Product Forecast
q-exploding-lighter 5 ÷ 7 × 11 = 7.86 units
q-razor-hat 2 ÷ 7 × 11 = 3.14 units
acme-boulder 33 ÷ 74 × 96 = 42.81 units
acme-shrink-gun 14 ÷ 74 × 96 = 18.16 units
acme-magic-hole 27 ÷ 74 × 96 = 35.03 units

Now, once again, the sum of all the product forecasts equal the individual brand forecasts which add up to the root level forecast… so everything reconciles. However, this time the character of the forecasts gets pushed out from the brand level (middle out) rather than down from the root level or up from the individual SKUs.

This is more apparent when you look at forecasts over multiple periods (where trend and seasonality can be visualized) and, again, this is illustrated in detail in the documentation on hierarchies… so if you haven’t read this already, you definitely should.

Comments