Designing Product Hierarchies for Better Forecasts

If you've ever spent a Friday afternoon manually reconciling a spreadsheet because Finance's revenue number didn't match Operations' unit count, you've felt the pain of a broken product hierarchy.

It is the hidden foundation of every demand plan. When it works, you don't notice it. When it's broken, it manifests as forecast error, communication breakdowns, and hours of wasted time fighting over whose data is "correct."

Most demand planners inherit a hierarchy designed years ago—usually by an ERP implementation team that didn't understand supply chain trade-offs. It might be rigid, outdated, or cluttered with "Do Not Use" buckets.

In this guide, we'll walk through how to design a product hierarchy that actually supports decision-making. We will move beyond the theory of perfect data trees and tackle the messy reality of attributes, new product introductions, and the conflict between financial and operational views.

What is a Product Hierarchy?

A product hierarchy is the structure used to organize your items (SKUs) into logical groups for reporting, planning, and forecasting. It acts as the "map" that allows your organization to navigate from high-level strategy down to execution.

At its simplest, it is a parent-child relationship where every child (SKU) belongs to exactly one parent.

A Typical Structure

While every industry differs, a standard CPG (Consumer Packaged Goods) hierarchy often looks like this:

1. Division: Beverages
2. Category: Carbonated Soft Drinks
3. Brand: SparkleSoda
4. Pack Type: Cans
5. Size: 12oz
6. SKU: SparkleSoda 12oz Can 12-pack

In this structure, you can easily answer questions like "How many cans of SparkleSoda did we sell?" by rolling up the data from Level 6 to Level 3.

However, the modern definition of a product hierarchy is evolving. As we'll discuss later, relying solely on a rigid "tree" often fails when you need to answer cross-cutting questions like "How are all our Lemon-flavored products performing across all brands?"

Core Design Principles

Before you start moving cells in Excel or configuring your planning system, you need to adhere to three core design principles. Violating these is the primary cause of "ghost inventory" and reporting errors.

1. The MECE Principle

MECE stands for Mutually Exclusive, Collectively Exhaustive.

• Mutually Exclusive: A SKU can only live in one branch of the hierarchy at a time. It cannot be both a "Snack" and a "Beverage." If it is, you will double-count revenue.
• Collectively Exhaustive: Every single active SKU must have a home. You cannot have "orphan" products floating outside the structure, or they will disappear from the total company forecast.

2. The Goldilocks Zone (Depth vs. Breadth)

How many levels should you have?

• Too few (2-3 levels): You lose granularity. You might see that "Beverages" are up, but you won't know if it's water or soda driving the trend.
• Too many (8+ levels): The system becomes unmanageable. Planners spend more time scrolling through folders than analyzing data.

For most planning implementations, 4 to 6 levels is the sweet spot. This provides enough detail for root cause analysis without overwhelming the user interface.

3. Stability Over Precision

A common mistake is designing a hierarchy around the current organizational chart (e.g., "John's Division"). When John leaves or the sales team reorganizes, your historical data becomes useless.

Design your hierarchy around the product attributes, which are permanent, rather than the people managing them, who are temporary.

Forecasting: The Aggregation Dilemma

Why do we bother grouping products? Why not just forecast every SKU individually?

The answer lies in the Law of Large Numbers. Individual SKUs are noisy. A single stock-out, a one-time bulk order, or a data entry error can make a SKU-level history look chaotic.

When you aggregate those SKUs into a Family or Category, the noise cancels out. The underlying trend and seasonality become clear.

The "Middle-Out" Approach

This creates a dilemma:

• Top-Down: Forecasting at the Category level is accurate but doesn't tell the factory what specific SKUs to make.
• Bottom-Up: Forecasting at the SKU level gives the factory detail but is often wrong.

The best practice is often a Middle-Out approach. You generate the statistical forecast at a "manageable" level (like Brand or Product Line) where the signal is strong. Then, you disaggregate that number down to the SKU level based on recent sales ratios.

For a deeper dive on this trade-off, read our guide on Bottom-Up vs. Top-Down Forecasting.

Handling the "Messy Reality"

If you read a marketing textbook, product hierarchies are clean. In the real world, they are messy. Here is how to handle the edge cases that break most planning systems.

1. New Product Introductions (NPI)

When you launch a new "Cherry Vanilla" soda, where does it go?

• The Trap: Creating a "New Products" bucket. This isolates the product from its peers, meaning it won't inherit the seasonality of other sodas.
• The Fix: Slot the new product into its correct functional home immediately (e.g., under "Carbonated Soft Drinks"). Use "Like Modeling" to borrow history from a predecessor product so the statistical engine generates a baseline immediately.

2. Discontinued Items

Never delete a discontinued SKU from your database. If you delete the SKU, you delete the history.

• The Fix: Mark the status as "Inactive" or "Discontinued" but keep it in the hierarchy. When analyzing Year-over-Year growth, you need that history to explain why last year's sales were higher.

3. The "Misc" Bucket

Avoid categories named "Miscellaneous," "Other," or "General." These are black holes for data. If a product doesn't fit your structure, it often indicates your structure is missing a limb, or the product requires a specific "Accessories" or "Service Parts" category.

Hierarchy vs. Attributes: The Modern Approach

Traditionally, if you wanted to report on "Flavor," you had to build it into the tree: Category -> Brand -> Flavor -> SKU

But what if you also want to report on "Pack Size"? You can't have two parents.

This is where the debate between Hierarchies (rigid trees) and Attributes (flexible tags) begins. Modern demand planning leans heavily on attributes.

The Attribute-First Strategy

Instead of building a deep, complex tree, keep your hierarchy simple (e.g., Category -> Brand -> SKU). Then, tag every SKU with rich metadata:

• Flavor: Lemon
• Pack Type: Can
• Life Cycle: Mature
• Sourcing: Domestic

This allows you to create virtual hierarchies on the fly. You can pivot your data to see "Lemon" sales across all brands, or "Can" shortages across all categories, without restructuring your master data.

How DemandPlan Handles This

We built DemandPlan to solve the rigidity problem. We use an engine we call AdaptiveHierarchy™.

Instead of forcing you to choose one static view, DemandPlan allows you to slice and dice by any attribute dynamically.

• Finance can view the plan by Revenue Category.
• Sales can view the plan by Customer Channel.
• Operations can view the plan by Production Line.

All three views are reconcilable because they are aggregations of the same granular data, just summed up through different attribute paths. This eliminates the need for "offline spreadsheets" to translate between departments.

Conclusion

Your product hierarchy is more than just a list of folders; it is the lens through which your company sees its business. A blurry lens leads to bad decisions.

If you are redesigning your hierarchy, follow this checklist:

1. Audit: Ensure every active SKU has a home (MECE).
2. Simplify: Reduce levels to 4-6 max.
3. Tag: Move descriptive details (Flavor, Color, Size) out of the hierarchy and into attributes.
4. Preserve: Keep discontinued items for historical accuracy.
5. Align: Ensure the hierarchy supports both Financial reporting and Operational execution.

Getting this structure right is the highest-ROI activity you can do before implementing a new planning tool.

---

Ready to stop fighting with spreadsheets? See how DemandPlan's AdaptiveHierarchy™ can unify your planning process. Schedule a demo today.