Contributors: Ruvisha Pillay, Bill Tonetti, Tracy Roche, Perry Britt
In this article, we aim to give you an introductory overview of Aggregation and Proration.
When working with groups of detailed items, you are working with “aggregates” or
“aggregations.” Pivot forecasting makes it possible for users to efficiently navigate from detail to groups, and to make changes at group levels and synchronize them dynamically to the detail items. Changes to aggregations, when saved, are prorated (disaggregated) to the detail items and stored in the “Synchronized Forecast” measure. Forecasting adjustments in Pivot forecasting are top-down, bottom-up, or middle-out. This is aggregation and proration.
Top-down: forecast adjustments on a group level, which are then prorated down the lower levels of detail.
Bottom-up: forecast adjustments at the lowest level are aggregated up to all group levels.
Middle-out: forecast adjustments at a mid-level on the hierarchy are both aggregated up the higher group levels and prorated down the lower levels.
Hierarchies are made by picking attributes in the upper left portion of the Pivot Forecasting user interface. They must have a minimum of 2 levels, including at least one attribute, plus the base level where most Pivot Forecasting data resides.
Planning a hierarchy typically starts with identifying the attributes to support a forecasting objective. When selecting a hierarchy, start with broader groupings like product families or customer channels, then add specificity by selecting additional attributes like items, sizes and customers.
The attributes that make up the hierarchy are represented as a Tree in Pivot Forecasting. The top, or first selected attribute, forms the trunk of the tree; it represents the whole hierarchy. The top-most group is always “All Items.” Any additional attributes in the hierarchy are branches on the tree trunk. The base level of the hierarchy are leaves on the tree. They represent the most detailed level of data in the model. In Pivot Forecasting, branches always have a folder next to them, and the base level “leaves” have a “dot”.
When you click on a node, data will populate in the dashboard. If you click on an aggregate node, the system will sum up all of the history, promotions and forecasts from the base level nodes in that group and produce a forecast using the summed histories and promotions. This is aggregation in Pivot Forecasting. The dynamically generated aggregate forecast is the “Top Down” or “Middle Out” forecast.
If you make an adjustment while you’re on an aggregate node, then each of the base level nodes in your group will be affected proportionally. In other words, if you increased the forecast by 10%, then each of the base level forecasts will be increased by 10%. This is proration in Pivot Forecasting. The only exception to this is if there are “Locked Forecasts” in that group, in which case the changes made at the aggregate level will be prorated only to the unlocked items.
Here’s an example of a top-down forecast adjustment. For this example, the top level is named Category Furniture. Within that group, there are two groups at the middle level representing two stores, Customer Store ABC and Customer Store XYZ. Each store has two items within the group which represents the base level (i.e. lowest level of detail. These are the Item Chairs and Item Tables.
Adjustments made to the top level will prorate to the lower levels. In this example, the Calculated forecast for Jan was 40 units. An adjustment was made to the forecast to increase that Jan forecast to 80 units and then saved, the results of which are stored in the Synchronized Forecast row.
As an example, let’s say that a change is made to double demand at a level that contains two stores, Store ABC and Store XYZ. If there are no locked forecasts, then each of the stores will receive a doubling of their demand. So, if the adjustment is 100 percent on the aggregate level, then each store will receive a 100 percent increase.
Things get a little trickier with locked forecasts. Let’s say the initial total forecast for the group is 100. Beneath that level, Store ABC accounts for 75 percent of that demand, and Store XYZ accounts for 25 percent. Now, let’s also assume that Store ABC’s forecast is locked. If the group level forecast is increased from 100 units to 150, then the entire 50 unit increase will be added to Store XYZ, increasing its forecast from 25 units to 75 units, which is a 300 percent increase! Because of this, it is recommended that locked forecasts are used sparingly.
If adjustments are made to forecasts in multiple periods, then each period is prorated. Since some of the items may be trending upward or downward, their portions of the aggregate changes may be different from one period to the next.
Remember, once you click save, the forecast will be saved into the Synchronized Forecast measure, but it will not be passed back into your inventory planning app until it’s finalized.
You may also be interested in the below articles: