Flexible Data Hierarchies in Forecast Pro Unlimited V5

Forecast Pro Unlimited V5 allows you to easily define hierarchies through the use of attribute fields. Unlike other desktop forecasting applications, there are no limits on the number of levels or structure of the hierarchy (e.g., customer-based, product grouping-based, geography-based, etc.). Forecasts are generated for all levels of your hierarchy and can be reconciled using any combination of top-down, bottom-up and middle-out approaches. Once you’ve completed your forecasts, Unlimited V5 will export your forecasts and defined hierarchy in a tabular format, allowing for easy forecast integration, analysis and reporting.

Below are a few examples of the types of hierarchies that can be defined in Forecast Pro Unlimited V5. In the program, only the lowest-level records (e.g., SKU in sample hierarchy 1, Size in sample hierarchy 2, Product in sample hierarchy 3) are imported. All group levels are created on-the-fly as the program reads the attributes associated with each record. Attributes typically come directly from the corporate database, for instance from the Item Master or Customer Master files.

In the screenshot below, the forecast hierarchy for the company “ABC Bakery” is shown. This data is used in the Forecast Pro Unlimited tutorial and is installed with the program. The ABC Bakery hierarchy starts at the company total level and drills all the way down to the Item-by-Customer level. Item-by-Customer is the level that the data comes in to Forecast Pro Unlimited. The complete hierarchy is:

Company Total>Product Category>Customer>Sub-Category>SKU

The above screenshot shows the hierarchy for ABC Bakery. Note how the hierarchy is depicted using a tree structure in the Navigator on the left side of the screen. The full hierarchy includes: Company Total> Category>Customer>Sub-Category>SKU. In the example, the forecast for 12 count Corn Muffins to Stuff-Mart is currently displayed.

Attribute Fields in the Input File
As previously noted, only the lowest-level data are included in the data file. In the ABC Bakery example this is the Item-by-Customer level data. The groupings and hierarchy structure are defined by attribute fields.

The screenshot below shows the Excel spreadsheet used to create the ABC Bakery hierarchy. Forecast Pro Unlimited automatically defines the first level as the sum of all records. Our first attribute field is Category which contains both Muffins and Cakes. Thus, when we read this spreadsheet into Forecast Pro Unlimited, the second level of our forecasting hierarchy will breakdown total sales into Muffins and Cakes. The next attribute field is Customer. Thus, the third level of our forecasting hierarchy will breakdown the Muffins and Cakes by Customer. The next attribute field is Sub-Category so the Category-by-Customer will then breakdown by Sub-Category. The next field is SKU. So the final level of our hierarchy will breakdown the Category-by-Customer-by-Sub-Category by SKU.

The above screenshot shows ABC Bakery.xls, the input file used to define the ABC Bakery hierarchy. The sum of all records represents the highest level – ABC Bakery. Subsequent levels are defined by the attribute fields in columns A-D. Thus, the record in row 2 is 12 count Corn Muffins to Stuff-Mart. This SKU falls within the Grain Sub-Category which in turn falls within the Muffins Category.

Attribute Fields in the Forecast Output File
Once the final forecast has been established, the results can be exported to a variety of formats including spreadsheet files, text files and XML. The user has complete flexibility in specifying which forecasts are exported. For instance, all forecasts can be saved or perhaps just the forecasts for Category or Sub-Category.

It is often very useful to include the attribute fields in the output. In addition to making it extremely easy to import the forecasts back into corporate systems, the inclusion of attributes allow for further analysis and reporting. The screenshot below shows a sample output file followed by examples of an Excel Pivot Table and an Excel List directly derived from the file (Note: a link to download a file containing the original Forecast Pro attribute-based forecast table, the Excel Pivot Table and the Excel List is available at the end of this article).

The above screenshot shows the attribute-based forecast output table. This forecast output format allows you to leverage Excel data reporting and analysis capabilities such as Pivot Tables, Lists and Groupings.

In the screenshot above, the Excel Pivot Table is derived directly from the Forecast Pro attribute-based forecast output table. In the example, subtotals for all nodes in the hierarchy have been included. Dragging and dropping the column headings in the Pivot Table allows you to change the hierarchy and analyze the forecasts on-the-fly. The screenshot below shows the hierarchy with Customer moved to the highest level.

By dragging and dropping the Customer column heading in the Pivot Table we now have a report where the hierarchy is Customer>Category>Sub-Category>Item.

The screenshot above shows an Excel List which is derived directly from the Forecast Pro attribute-based forecast output table. The List allows for on-the-fly filtering for analysis and reporting.

In addition to exporting forecast output tables, Forecast Pro Unlimited exports both override/adjustment tables containing all information about changes to the forecast and outlier tables containing all information about outlier detection and correction. These tables include the attribute fields, allowing you to utilize the same Excel data tools we’ve illustrated above.

Conclusion
Forecast Pro Unlimited V5 allows you to easily create forecasting hierarchies that reflect your business and the way that you need to see your data. There are no limits on the number of levels or structure of the hierarchy. Attribute-based output tables allow you to leverage Excel’s built-in data tools for easy and powerful forecast analysis and reporting.

Click here to download the Excel file containing ABC Bakery sample output table and Excel Pivot Table and List.