Contributors: Ruvisha Pillay, Bill Tonetti, Tracy Roche

With Pivot Forecasting, you can forecast at any level of aggregation, along any hierarchy, using any attributes that are provided with the data in the model. This is the heart and soul of Pivot Forecasting. It’s unique and very powerful, but it can be a little challenging to fully understand.

This article aims to give you a deeper understanding about how forecasting works with aggregation and proration, at aggregate nodes. If you haven’t already done so, have a look at our article called Introduction to Aggregation and Proration as a pre-read. As we continue, we will answer the below questions:

Let’s begin!

Firstly, it’s important to know how to identify an aggregate node. Aggregate nodes have folders next to them on the Tree, whereas base level nodes have a dot. In the example below, Category Furniture, Customer Store ABC and Item Chairs are aggregate nodes. Item Chairs-Customer Store ABC is a base level node.

Navigating to an aggregate node for forecasting starts with loading a hierarchy, selecting a dashboard view and selecting the planned node for the forecast review. When an aggregate node is clicked, most measures for the products and customers below your selection are summed. Actual History, historic and future Promotions, previous historic and future Final Forecasts, and reference measures like the Sales Forecast are all summed. Synchronized Forecasts and Saved Adjustments are summed as well.

However, the Calculated Forecasts and Fitted Forecasts are not. The expert Calculated Forecast is dynamically produced based on the summed histories at the aggregate node selected. The Fitted Forecast values are produced along with the aggregated Calculated Forecast. For aggregate nodes, this Calculated Forecast is called a top-down forecast. The Synchronized Forecast is the sum of all the Synchronized Forecasts for the items in the group; and it is called a bottom-up forecast.

So, whenever you click on an aggregate node, you will be presented with both the sum of the bottom-up forecasts (in the Synchronized Forecast measure), as well as a dynamically calculated top-down forecast (the Calculated Forecast measure).

When an adjustment is made at a base level node and saved, you will be able to see this change on the Synchronized Forecast measure of the aggregate node. That is a bottom-up adjustment. The change was dynamically propagated to the group.

When an adjustment is made at an aggregate level and saved, you will see that the Synchronized Forecast measure has changed. The adjustment was spread proportionately over the items in the group i.e. it was prorated. This is a top-down adjustment. For example, if you wish to adjust the base level forecasts to sum to the top-down calculated forecast, you can simply copy the Calculated Forecast into either the Adjusted Forecast or the Saved Adjustment measure, then save.

Locked Forecasts

If any of the items in the group where the top-down changes were made were “Locked”, the changes would have been prorated to the unlocked items only. Because of this, Locked Forecasts can magnify changes to unlocked items. Remember: Adjusted Forecasts and Saved Forecasts override Calculated Forecasts, but Locked Forecasts override all of them.

Zero “Proration Basis”

What if you enter a forecast adjustment for a group where all items have a forecast of zero? In that case, the adjustment would be split evenly among the items in the group.

Prorating Promotions

Changes to future Promotions are prorated in the same way as forecast adjustments, according to their Synchronized Forecasts. But, since Promotions effect changes to the Calculated Forecasts, the items in that group will automatically be recalculated when you save the change. Historic Promotions on aggregate groups are prorated as well. Positive historic changes are prorated according to Actual History. A negative historic change is prorated according to Fitted Forecasts, before the promotion is saved.

You may opt to make changes directly to items without prorating them. You can do this by navigating to the base of your hierarchy and making the relevant changes there. However, if you plan to make a lot of changes, this method can be cumbersome and time-consuming compared to having the forecast prorated.

Mass Updates

The “Tree Menu” includes several functions for making mass updates to base level values and settings. To access the Tree Menu - right click on the Tree. This will give you access to functions for groups of items. Your level of access will depend on your user setup.

Most users will have access to the these Tree Menu functions:

Have you read the related articles? Check out our Pivot Forecasting collection!