Product Mapping Files
In addition to interactive product mapping, Forecast Pro supports file-based product mapping, allowing you to map the history from one item in your historic data (the Map from item) to another (the Map to item).
When you map the history from one item to another, the history used to generate the Map to item’s forecast (the forecast history) is the actual history for the Map to item plus the forecast history for the Map from item. In other words, the Map to item’s history is incremented by the Map from item’s history to generate a forecast history for the Map to item. Please see Working with Product Mapping and Mapped History for more details.
Product Mapping Tutorial- Product Mapping.xlsx, shown below, is installed in the Forecast Pro input directory.
In the example above, a 4-level hierarchy is used. The first columns (A through D in our example) define the attributes and name of the Map to item. The next columns (E through H in our example) show the attributes for the Map from item. For group levels, all attributes still need to be included in the row with “NULL” filled in for lower attribute levels (see the second example below). Assigning forecast histories at a group level requires being in All Levels Product mapping mode.
The attributes for the Map to and Map from items are the only required columns in a product mapping file. The following optional columns may be included in the file as well:
|Indicates the first Map from historic period to map. If missing, the first period with history for the Map from item is used. Use date formats as shown in the Override grid.
|Indicates the last Map from historic period to map. If missing, the last period with history for the Map from item is used. Use date formats as shown in the Override grid.
|Indicates the proportion of the Map from volume to map. The allocation should be greater than 0. If missing, it is assumed to be 1.
|Indicates if a \SET_FH_TO_ZERO modifier should be applied to the Map from item. Applying this modifier effectively removes the item from group level Forecast Histories and sets the forecast (if any) to zero.
|Indicates if a \TOPDOWN modifier should be applied to the Map to item. This is only applicable when using the “All levels” product mapping mode.
Below is an example of a group-level product mapping file which includes all optional columns. Note that the optional columns must be after the Map to and Map from attribute columns but may be included in any order.
Mapped History Files
At times, you may find that the item that you want to Map from is not in your historic data. If so, you may use a mapped history file to import values that you want to map to an item in your historic data. The forecast history for the Map to item will equal the values assigned to that item in the mapped history file plus the item’s history in the historic data file.
The mapped history file is very similar to the external data file structure. The sample file shown below is installed into the Forecast Pro input directory and is named Product Mapping Tutorial Part 2- Mapped History.xlsx.
The first columns (A through D in our example) define the attributes and name of the Map to item (i.e., the hierarchy information). The next column (E in our example) is labeled Description and should read “Mapped History” for all rows in the file. The remaining columns are labeled with dates and contain the data values.
If you plan to import your mapped history using a transaction style spreadsheet, examine the sample file Product Mapping Tutorial Part 2- Mapped History – Transactions.xlsx located in the Forecast Pro input directory. You may also read in data through ODBC using the same transaction style layout shown below.
The format is very similar to the format for historic demand. Notice that the attribute fields are present. Description must be present and read “Mapped History” for all rows. Hist_Year, Hist_Period and Hist_Value are number fields identifying the year, period and values, respectively.
In a spreadsheet, a header row is required. The Description and date fields must also be present and labeled accordingly. The dates in the header row need to be either in an Excel format or in a Year-Period format (for example, 2019-July or 2019-52).
A mapped history file may contain mapped history for end-items or group levels, if you are working in the All levels Product mapping mode. For group levels, all attributes still need to be included in the header row with “NULL” filled in for lower group and item levels.