Forecast Pro TRAC only

The unit of measure used in the historic data file is the default unit of measure. Forecast Pro allows you to define conversion factors (i.e., multipliers) which can be used to display the history and forecasts in alternative units (e.g., dollars, kilos, etc.).

Forecast Pro allows for point conversions, where the same conversion factor is used across all time periods, or time series conversions, where the conversion factors may vary across time. Point conversions and time series conversions have different file layouts, as described below. You may not combine point and time series conversions in the same input file, but you may use both point and time series conversions in the same Forecast Pro project by reading in multiple files.

If you use the same conversion unit (e.g., Dollars) in both the point conversions and time series conversions files, the point conversion will be read in first, providing a conversion factor for all historical and forecast periods. The time series conversions will then be read in, replacing the point conversion values for any items and periods where a time series conversion value is provided.

By default, a conversion unit must include a value for every end item and time period, but these values may be provided in either a point conversion or a time series conversion file. If Forecast Pro is missing values after reading in all conversion input files, you will receive an error message and the conversion factor will not be read into the project. If you want to allow conversion factors with a value of zero or missing conversion factors, you may do so by activating the Allow zero valued conversion factors option on the Formats tab of the Options dialog box.

If the Allow zero valued conversion factors option is selected, Forecast Pro will allow missing or zero values in the conversions file(s) specified in the Data Manager. Forecast Pro will give warnings about the missing data and changing project units using the Units drop-down on the Operations tab will not be enabled. However, the converted rows may be added to the Override Report and Graph views as well as other reports. If a conversion factor is not provided for a given item in a given period, the converted value will appear blank, and the item will not be included in the totals for those conversion factors.

Point Conversions

Examine the file 123 Bakery – Conversions.xlsx.

Notice that the attribute fields are present. In our example, columns E through G contain the conversion factors.

You can also read in point conversions using a table or query. Shown below is the Conversions table in the file 123 Bakery – ODBC.mdb.

Notice that the attribute fields are present (ItemId0–ItemId3). Description is a text field containing the conversion factor’s name, and NumValue is a number field for the conversion factor value.

By default, Forecast Pro will not read in conversion files with missing or zero-valued conversions. To allow missing or zero-valued conversions, select Allow zero-valued conversions factors on the Formats tab of the Options dialog.

Time series conversions

You may read in time series conversion using a row format or a transactional format.

The file 123 Bakery – Conversions – Time series.xlsx shows the row format.

Note that the attribute and variable name fields are present. Following the variable name column is a required column, Units. The Units field is used to specify the name of the conversion unit factor you are defining in the row. In the example above, the conversion factor is named “Dollars”. Following the required Units field is a column for each period for which you are reading in a conversion factor value. In the example above, the Dollars conversion factor value for Total>Cakes>Food-King>BU-20-02 in July 2019 is $11.62.

Note that the 123 Bakery – Conversions – Time series.xlsx does not provide values for all historic periods. To view Dollars in the override grid and graph, you would need to activate the Allow zero valued conversion factors option on the Formats tab of the Options dialog. To view the whole project in Dollars, you would need to also read in the point conversions file to provide values for the time periods not included in the time series file.

You can also read in time series conversions using a transactional format. This transactional format may be used in Excel or as a table in a database. Below is the Time_Series_Conversions table from 123 Bakery – ODBC.db.

Notice that the attribute fields (ItemId0–ItemId3) are in the first four columns. Following the attributes field are four additional required columns: Units, FPYear, FPPeriod and FPValue. The Units column contains the name of the conversion factor. FPYear, FPPeriod and FPValue are number fields identifying the year, period and conversion value, respectively.

The Excel transactional format is the same as the database table format, except the attribute names defined in the historic data file should be used in the attribute columns, as shown below in 123 Bakery – Conversions – Time Series – Transactions.xlsx.