Configuring Key Figures, Disaggregation, and Excel Planning Views for the Demand Cycle

Explanation

Once the planning model exists, the intermediate-level work in demand planning is configuring how key figures behave in day-to-day planning: how values calculated at an aggregate level are pushed down (disaggregated) to detail level, how planners interact with the data in Excel, and how calculated key figures support forecast error tracking and consensus adjustments. Disaggregation matters because planners rarely want to enter every value at the lowest planning level. A regional sales manager might adjust a monthly total for a product family, and that adjustment needs to flow down to individual product-location-week combinations in a sensible way. IBP supports disaggregation methods such as proportional distribution based on a reference key figure (commonly historical sales proportions or a prior statistical forecast), and even distribution across periods or members when no reference is available. Choosing the wrong disaggregation basis is a common source of planner distrust in the tool - if a top-down override disaggregates using stale or seasonal-blind proportions, the resulting detail numbers can look obviously wrong to a planner who knows the business, and that damages adoption more than almost any other configuration issue. Calculated key figures extend the model with formulas evaluated at runtime or during batch jobs. A typical demand planning model includes calculated key figures such as Forecast Error (Consensus Demand Plan minus Actual, or the reverse depending on sign convention), Forecast Accuracy percentage, and Bias metrics tracked over rolling periods. These calculations are defined once in the key figure formula and then apply consistently across every planning view that includes them, which is valuable for governance - planners across regions see the same accuracy calculation rather than ad hoc spreadsheet formulas that can diverge. The statistical forecast itself is typically generated by a forecasting job (batch or on-demand) that reads historical key figures, applies a selected model (exponential smoothing variants, Croston's method for intermittent demand, or an automatic best-fit selection), and writes results into the Statistical Forecast key figure. Intermediate practitioners need to understand forecast profiles - the configuration object that defines which statistical models to try, the history window to use, and outlier correction settings - because forecast quality issues often trace back to a poorly tuned forecast profile rather than a data problem. On the UX side, most demand planners still work primarily through the Excel-based add-in, where a planning view combines a planning level, a set of key figures, and filters, with drag-and-drop dimension arrangement similar to a pivot table. Planners can enter overrides directly into cells, and the add-in sends those changes back to the planning area as save operations. Understanding save behavior matters operationally: a save typically triggers the disaggregation logic and any dependent calculated key figures, so large bulk overrides can be slow if the disaggregation basis or planning level cardinality is very high. In production, planners are often trained to work at a summarized level and only drill into detail when investigating an anomaly, both for performance and for cognitive load reasons. Finally, the consensus demand process usually layers multiple key figures - Sales Forecast, Marketing Forecast, Statistical Forecast, Consensus Demand Plan - with a defined override hierarchy so that a documented consensus step (rather than the last person to touch the spreadsheet) determines the final number handed to supply planning.

Code example

Example calculated key figure formula (IBP formula key figure, conceptual syntax): ForecastErrorPct :=  IF(ActualsQty <> 0,     ABS(ConsensusDemandPlan - ActualsQty) / ActualsQty * 100,     NULL) Example disaggregation setup for a top-down override: Key Figure: CONSENSUSDP  Disaggregation Method: Proportional  Reference Key Figure: STATFCST (prior period statistical forecast)  Fallback (if reference is zero for all members): Even distribution

Real project scenario

During a consensus demand review, a regional planning manager overrode a monthly aggregate forecast upward by 15 percent to reflect a new promotion, expecting the increase to disaggregate mainly to the two SKUs actually being promoted. Because the disaggregation reference key figure was the general statistical forecast (which spread proportionally across all SKUs in the product family), the override incorrectly inflated forecasts for unrelated SKUs. The team resolved this by creating a separate promotion-specific key figure disaggregated using a dedicated promotion-lift reference, keeping the base consensus disaggregation logic unaffected for non-promotional overrides.

Common mistakes

• Using a single generic disaggregation reference for all types of overrides, causing promotional or exceptional changes to spread incorrectly across unrelated members • Defining calculated key figures with inconsistent sign conventions for forecast error, leading to confusing positive/negative interpretation across reports • Ignoring forecast profile history window settings, so seasonal products get statistical forecasts based on too little history • Allowing planners to work at the lowest planning level by default, causing slow saves and planner frustration on large models • Not documenting the override hierarchy, so it becomes unclear which key figure represents the final approved consensus number

Best practices

• Match the disaggregation reference key figure to the nature of the override being made, using dedicated references for promotional or exceptional changes • Define calculated key figures with clear, documented sign conventions and review them with planners before rollout • Tune forecast profiles per product segment (e.g., separate profiles for seasonal vs stable demand items) rather than using one global profile • Encourage planners to work at summarized planning levels for routine review, drilling to detail only for exception investigation • Maintain a documented override hierarchy showing which key figure is authoritative at each stage of the consensus process

Interview angle

Candidates are often asked how they would design disaggregation logic for a specific business scenario, such as a promotional uplift or new product introduction with no history. A strong answer discusses using different reference key figures per override type, handling the zero-history fallback case explicitly, and validating disaggregated results against planner expectations before go-live rather than assuming the default proportional method is always correct.