Forecast Reconciliation: Solving the Multi-Hierarchy Puzzle

If you've ever sat in an S&OP meeting where the VP of Sales says we're going to sell 50M, but the operations team's bottoms-up SKU forecast only adds up to42M, you've experienced the coherence problem.

It's one of the most persistent headaches in supply chain planning: different teams forecast at different levels (dollars vs. units, global vs. regional, category vs. SKU), and the numbers simply don't add up.

For years, the "solution" has been political, not mathematical. The loudest voice in the room wins, and their number gets "peanut butter spread" down to the SKUs, destroying accuracy at the execution level. Or, you stick with the bottoms-up number and miss the macroeconomic signals that only appear at the aggregate level.

But forecast reconciliation isn't just about forcing numbers to match. It is a mathematical problem with optimal solutions that can actually improve accuracy at all levels, not just align them.

This article explores why forecasts naturally diverge, why traditional methods like "top-down" are often flawed, and how modern approaches like Minimum Trace (MinT) reconciliation are changing the game.

The "One Number" Problem in Supply Chain

In a perfect world, your supply chain would run on a "Single Source of Truth." Every SKU forecast at every location would sum perfectly to the brand totals, which would sum perfectly to the corporate revenue guidance.

In reality, most organizations operate with "Forecasting Islands":

• Sales forecasts revenue by Customer Account.
• Operations forecasts units by SKU and Production Line.
• Finance forecasts margin by Region and Product Category.

Each of these views is valid. Each captures unique signals. Sales knows about a handshake deal with Walmart that isn't in the data yet. Ops knows that Line 3 is down for maintenance. Finance sees the exchange rate impact.

The problem arises when you need to execute. You can't produce "revenue"; you have to produce SKUs. If the aggregate plan says 10,000 units but the SKU details only sum to 8,000, you have a coherence gap.

Coherence is the mathematical property where forecasts at lower levels sum exactly to forecasts at higher levels. Without it, you are essentially planning for two different realities simultaneously.

Why Forecasts Never Add Up (Naturally)

It is a statistical certainty that independently generated forecasts will not add up. If you run a time-series model on "Total US Sales" and separate models on every individual SKU in the US, the sum of the SKU forecasts will rarely equal the Total US forecast.

Why?

1. Noise vs. Signal: At the SKU level, data is noisy. A random spike in one week looks like a trend. At the aggregate level, that noise cancels out (the "Law of Large Numbers"), revealing cleaner trends and seasonality.
2. Different Drivers: Aggregate forecasts are driven by macroeconomic factors (GDP, inflation, overall brand health). SKU forecasts are driven by micro factors (stock-outs, specific promotions, shelf placement).
3. Model Selection: You might use an ARIMA model for the stable aggregate series but Croston's method for the intermittent SKU series. Different math yields different trajectories.

This divergence isn't a failure of the forecaster; it's a feature of the data. The challenge is how to reconcile these conflicting signals into a single, executable plan.

Traditional Reconciliation: The "Force Fit"

Most legacy planning systems and Excel-based processes rely on one of three basic methods to force coherence. While simple to understand, they all come with significant accuracy penalties.

1. Top-Down Reconciliation

The Method: You forecast only at the aggregate level (e.g., "Total Category Sales") and then disaggregate that number down to the SKUs based on historical proportions.

The Pro: It captures the big picture. If the business is growing 10% year-over-year, top-down ensures every SKU reflects that growth.

The Con: It assumes historical proportions are constant. If SKU A is dying and SKU B is launching, a top-down spread will over-forecast A and under-forecast B. It spreads the "truth" like peanut butter, ignoring local reality.

2. Bottom-Up Reconciliation

The Method: You forecast at the lowest level (SKU/Location) and simply sum them up to get the aggregate.

The Pro: It respects the details. It captures specific cannibalization effects or local stock-outs that an aggregate view misses.

The Con: The Detail Trap. Lower-level data is noisy. Summing up thousands of noisy forecasts often results in a cumulative error that is significant at the executive level. You often miss the forest for the trees.

3. Middle-Out Reconciliation

The Method: You pick a "Goldilocks" level (e.g., Brand or Product Family), forecast there, and then force-fit both upwards (summing to Total) and downwards (disaggregating to SKU).

The Critique: While often a pragmatic compromise, all three of these methods are fundamentally political choices. You are deciding arbitrarily which level of the hierarchy "knows best" and forcing all other levels to submit to it.

Optimal Reconciliation (The Math Part)

What if you didn't have to choose a "winner"? What if you could mathematically combine the stability of the top-down view with the granularity of the bottom-up view?

This is the domain of Hierarchical Forecast Reconciliation (HFR).

OLS (Ordinary Least Squares)

In an optimal reconciliation framework, we treat the coherence problem as a system of linear equations. We want to find a set of revised forecasts that:

1. Add up correctly (coherence).
2. Minimize the total change from the original base forecasts.

Ordinary Least Squares (OLS) attempts to do this by adjusting every forecast slightly until they align, assuming that every forecast at every level is equally reliable (or unreliable).

MinT (Minimum Trace): The Game Changer

The breakthrough in modern forecasting (popularized by Rob Hyndman and others) is MinT (Minimum Trace) reconciliation.

MinT doesn't treat all forecasts equally. It looks at the variance (error) of each forecast.

• If your Top-Level forecast has a huge error margin, MinT will adjust it significantly to match the sub-levels.
• If your SKU-level forecasts are extremely volatile but the Category forecast is rock-solid, MinT will barely touch the Category number and force the adjustments down to the SKUs.

In plain English: MinT trusts the level that is performing best. It is a weighted average of all possible forecasts, where the weights are determined by the forecast accuracy (covariance matrix) of each level.

Why this matters: Studies show that MinT reconciliation often yields reconciled forecasts that are more accurate than the original base forecasts at every level. You aren't just making the numbers add up; you are using the hierarchy to denoise the signal.

Solving the "Cross-Hierarchy" Puzzle (Matrix View)

The problem gets harder. Most supply chains don't just have one hierarchy (Brand -> Category -> SKU). They have multiple, intersecting hierarchies.

• Product Hierarchy: Total -> Brand -> SKU
• Geographic Hierarchy: Global -> Region -> Country
• Sales Hierarchy: Channel -> Key Account -> Store

You need a number for "Brand X sold in Region Y."

If you reconcile the Product Hierarchy, you get one set of numbers. If you reconcile the Geographic Hierarchy, you get another.

This is known as Grouped Time Series forecasting. It requires a more complex matrix approach (often called the "Summing Matrix" or S matrix) that represents every possible aggregation.

Trying to solve this in Excel is impossible. The matrix for a company with 1,000 SKUs and 50 locations can easily involve millions of correlations. This is where computational power becomes non-negotiable.

Automated Reconciliation in Practice

So, how do you actually implement MinT or optimal reconciliation? You certainly don't do it manually.

The Role of AdaptiveHierarchy™

At DemandPlan, we built AdaptiveHierarchy™ to handle this specific challenge. Instead of forcing you to pick a "primary" hierarchy (e.g., forcing Sales to plan by Product when they want to plan by Customer), the system generates base forecasts at all relevant intersections.

It then uses a reconciliation engine to:

1. Detect constraints: (e.g., "The User specifically locked the forecast for Walmart to 50k units").
2. Calculate variance: Assess the historical accuracy of the aggregate vs. detail models.
3. Solve for coherence: Distribute the difference optimally across the non-locked nodes.

This allows for Dynamic Slicing. A user can update a forecast at the "Region" level, and the system instantly reconciles that change down to the SKUs and up to the Global view, respecting the statistical relationships between them.

Best Practices for Implementation

If you are moving away from simple spreadsheets toward a reconciled forecasting process, follow these rules:

1. Measure Accuracy at All Levels

Don't just measure WAPE (Weighted Absolute Percentage Error) at the SKU level. Measure it at the Brand, Category, and Regional levels. This helps you identify which level of your hierarchy has the strongest signal, informing your reconciliation strategy.

2. Don't Reconcile Too Frequently

Reconciliation changes numbers. If you run it every hour, your execution teams will get whiplash. Reconcile on a cadence (e.g., weekly or monthly) that aligns with your decision cycles.

3. Treat "Human Overrides" as Constraints

When a human planner manually changes a number, that shouldn't just be another input into the weighted average. In a modern system, a manual override acts as a constraint. The math must solve around it.

If I lock SKU A at 100, the reconciliation engine must adjust SKU B and C to match the Category total, without touching A.

Conclusion

Forecast reconciliation is the bridge between strategy and execution. It transforms a collection of disjointed guesses into a single, cohesive operating plan.

While "Top-Down" and "Bottom-Up" are easy concepts to grasp, they leave accuracy on the table. By adopting optimal reconciliation methods like MinT—supported by platforms that can handle the heavy matrix algebra—you can stop fighting about whose number is right and start executing on a number that is mathematically proven to be the most accurate.

---

Ready to stop the spreadsheet wars? See how DemandPlan automates reconciliation, design your product hierarchy, or choose your forecast dimensions for better results.