Demand data can almost always be organized into several levels of aggregation. Suppose that the lowest-level forecasts you need to generate represent SKUs (Stock Keeping Units). SKU-level forecasts are often needed to support production planning and inventory control. The corporation might aggregate these SKUs first into products and then into product lines for marketing and sales. These might be aggregated further into geographical regions for the benefit of top management.
For the purposes of this discussion, we will be referring to end-items and groups. End-items are the lowest-level data in your hierarchy. In the above example, each SKU would be an end-item. Groups are aggregations of end-items. In the above example, products, product lines and geographical territories would all be examples of groups.
Forecast Pro allows you to define product hierarchies and create one set of self-consistent forecasts. It allows top-down, bottom-up or user-defined reconciliation, seasonal adjustment based upon aggregate data and model selection at the aggregate level.
It is not necessary that the end-item histories begin and end at the same time. Thus group-level data may consist of end-items that have been retired or replaced by new end-items. Obsolete end-items will contribute to the group-level history but will not themselves be forecasted. You will notice that the starting and ending dates for the overall historic data consist of the starting date for the oldest end-item and the ending date for the newest. Forecasts will be prepared for all end-items and groups that are “alive” at the end of the data set. Those whose histories terminate before that time are considered discontinued—they contribute to the historic aggregates (and therefore influence aggregate forecasts), but they are not themselves forecasted.
Using Attribute Fields to Define a Hierarchy
If you do not include any attribute fields, when you read the data into Forecast Pro, it will create a one-level hierarchy (i.e., there will be no group totals).
If your data file, table or query includes attribute fields prior to the Variable Name field, Forecast Pro will automatically build a multiple-level hierarchy. The first level of the hierarchy will correspond to the first attribute field, the second level of the forecasting hierarchy will correspond to the second attribute field, etc.
Let’s illustrate this with an example. The spreadsheet 123 Bakery – Historic Data.xlsx is a sample data file shipped with Forecast Pro. It is shown below.
In our example, our first attribute field is Total. Notice that the value for the field Total is “Total” for all items. Since all items have the same value for the first attribute field, when we read this spreadsheet into Forecast Pro the first level of our forecasting hierarchy will provide a grand total for all items in our spreadsheet. Notice that the next attribute field is Category, which contains both Cakes and Muffins. Thus, when we read this spreadsheet into Forecast Pro the second level of our forecasting hierarchy will breakdown total sales into Cakes and Muffins. The next attribute field is Customer. Thus, the third level of our forecasting hierarchy will breakdown the Cakes and Muffins into Customers. The next field is the Variable Name (labeled “SKU”) so the final level of our hierarchy will breakdown the Category-by-Customer into SKUs.
By default, the Automatically sort option on the Formats tab of the Options dialog box is selected, and Forecast Pro sorts the data alphabetically prior to reading it in. If Automatically sort is not selected, the order of appearance on the spreadsheet dictates the structure of the hierarchy and you’ll want to take care when constructing your spreadsheet.
ODBC attribute fields
The file 123 Bakery – ODBC.mdb is a sample data file shipped with Forecast Pro. It is shown below.
If you are using ODBC the first attribute field must be named ItemId0, the second attribute field must be named ItemId1, etc. All attribute fields must be text fields. Other than that, the operation is identical to the spreadsheet format described above.