Forecast Pro TRAC Version 3 introduced a feature to import “external” data into Forecast Pro, including elements such as open orders, inventory and other forecasts. This enhancement offers new opportunities to create more meaningful and customized worksheets in Forecast Pro. Initially, external data could only come in at the bottom level of the hierarchy, but with the release of Forecast Pro TRAC Version 3.1 users can now bring in and work with data at any level of the hierarchy. In this edition of Tips and Tricks, we will explore how to work with external data and calculated rows at different hierarchy levels.
Not all data will be meaningful at the lowest level of the hierarchy, and likewise not all data will be meaningful at an aggregate level. For example, although our lowest level may show customer demand by SKU, if we want to bring in inventory levels we are likely to import the data at the SKU level. Alternatively, if management gives us forecasts at a higher level, we will import them at that higher level and have Forecast Pro allocate them through the hierarchy. Let’s take a look at some of examples.
Example 1: Importing Inventory at a Group Level
In this example, we will use the 123 Bakery data set that ships with Forecast Pro. The data represents sales for a commercial bakery, where our hierarchy is organized as: Total > Category > SKU > Customer. In other words, our lowest level of detail is SKU-by-customer, with each combination being rolled up by SKU.
If we maintain our inventory data by SKU, we would want to import that information at the SKU level, instead of the SKU-by-customer level. Thanks to Forecast Pro TRAC v3.1, that is now possible. Here is a screenshot of our hierarchy, as described above, and the grid view before importing data:
Using the menu option Project > Import > External data from… we can import the inventory data at the SKU level:
Once imported, the data can be manipulated using calculated rows and can also be graphed, as with any other external data row. In this case, the inventory row will be visible at the SKU level and then aggregated to higher levels. At the bottom (SKU-by-customer) level, the inventory row will not be available and values will be displayed as “NA.” In this example, we have added a calculated row that displays “projected inventory,” based on the current inventory and forecasted demand.
The file we use to import this information is identical to a file that we would have used in Forecast Pro TRAC Version 3, except we now use the keyword “NULL” to fill in hierarchy attribute fields that are not relevant:
The first four columns describe the levels in the hierarchy where the imported data will reside; the “Description” fields provide a label for the new row we create; and last, the imported data are associated with a date in the far right column.
Example 2: Importing a Higher-level Forecast
When we bring in external data at higher levels, we need to indicate if we want to aggregate or disaggregate that information throughout the current hierarchy. In the previous example, we imported inventory data at a middle level, and allowed it to roll up to higher levels but not disaggregate down to the lowest ones. In our next example, we will again use the bakery data but instead will bring in a management forecast at the category level (muffins/cakes) and let Forecast Pro disaggregate those forecasts for us.
Here we have imported the Management Forecast row at the category (muffins) level:
By default, this forecast will roll up to higher levels but not disaggregate down to lower levels. Using the “Overrides Design” dialog box shown below, which can be accessed by right-clicking on the override grid itself, we have a variety of options for the newly imported row:
In this example, we have checked “Aggregate” and “Disaggregate” for the new row, so that the information will both roll up to higher levels and be disaggregated down through our lower hierarchy levels proportionally, based on the Forecast row. More details on the options available in this box are described in the Forecast Pro TRAC User’s Guide, which can be accessed via the Help menu. Thanks to the “Disaggregate” setting, the category-level Management Forecast has now been allocated down to both the customer and SKU levels:
These aggregation and disaggregation options are also available for calculated rows.
The ability to import external data into Forecast Pro TRAC has transformed the software into a much more comprehensive tool by allowing users a greater degree of flexibility and customization for designing views and worksheets. With the release of Forecast Pro TRAC Version 3.1, data can be imported and calculated at any level of the hierarchy.
For technical details on how to set up data formats and create the override view, please consult the Forecast Pro User’s Guide, which is accessible via the Help menu in Forecast Pro TRAC.
For more information on the latest release of Forecast Pro TRAC, check out What’s New in Forecast Pro TRAC v3.1.