Forecast Pro can read Excel (XLS, XLSX) files and CSV (Comma Separated Values) files saved from Excel.
You must create your spreadsheet file from within your spreadsheet program. Forecast Pro checks data types as it reads the spreadsheet. If it sees text where it expects a number, or a floating-point number where it expects an integer, an error message will be displayed when the data is read in. By default, a blank cell is interpreted as a missing value. This default can be adjusted for leading and trailing blanks on the Formats tab of the Options dialog box. Do not use zeros to represent missing values¾they will be interpreted as numbers and will probably distort your forecasts badly.
Row format
If your data are already stored by rows, you will want to consider the row format first. You can probably alter your spreadsheet to the Forecast Pro row layout in just a few minutes. In row format each time series occupies a single row on the spreadsheet.
The data are assumed to reside in the leftmost (first) spreadsheet in the workbook unless an alternate sheet has been specified by naming it BFSDATA.
Row 1 of the spreadsheet is devoted to column headings. The keyword “description” must be used as the column heading for the description column. The other cells in row 1 are ignored by Forecast Pro. You can use them for titles, calendar information, etc.
Here is an example of a row format spreadsheet. The file is installed with the program and is named Sample Historic Data – Horizontal.xlsx.
The above example does not use attribute fields to define product groupings. This is often desirable and is discussed in the Defining the Hierarchy.
In our example, each data record consists of six header items in columns A through F, followed by the historic data in the remainder of the row, beginning in column G. Each item is placed in a separate cell.
Columns A through F contain the following six required items.
- Variable name.
- Variable description. Up to 256 characters.
- Starting year. Must be an integer.
- Starting period. Must be an integer.
- Periods per year. Must be an integer.
- Periods per seasonal cycle. Must be an integer.
See Setting up the Historic data for more details on these items.
The time series data begin in column G. The starting year (column C) and starting period (column D) refer to the year and period for column G, whether or not that cell actually contains data. Thus, in the example spreadsheet, the first value for PG1-204-2 is for January 2017 in column I, even though the column C defines the starting year as 2016, and column D defines the starting period as 11.
Column format
If your data are already stored by columns, you will want to consider the column format first. You can probably alter your spreadsheet to the Forecast Pro row layout in just a few minutes. In column format each time series occupies a single column on the spreadsheet.
The data are assumed to reside in the leftmost (first) spreadsheet in the workbook unless an alternate sheet has been specified by naming it BFSDATA.
Column A of the spreadsheet is devoted to row headings. The keyword “description” must be used as the row heading for the description row. The other cells in column A are ignored by Forecast Pro. You can use them for titles, calendar information, etc.
Here is an example of a column format spreadsheet. The file is installed with the program and is named Sample Historic Data – Vertical.xlsx.
The above example does not use attribute fields to define product groupings. This is often desirable and is discussed in the Defining the Hierarchy.
In our example, each data record consists of six header items in rows 1 through 6, followed by the historic data in remainder of the rows, beginning in row 7. Each item is placed in a separate cell.
Rows 1 through 6 contain the following six required items.
- Variable name.
- Variable description. Up to 256 characters.
- Starting year. Must be an integer.
- Starting period. Must be an integer.
- Periods per year. Must be an integer.
- Periods per seasonal cycle. Must be an integer.
See Setting up the Historic data for more details on these items.
The time series data begin in row 7. The starting year (row 3) and starting period (row 4) refer to the year and period for row 7, whether or not that cell actually contains data. In the example spreadsheet, the first value for PG1-204-2 is for January 2017 in row 9, even though the header defines the starting period as 11 and the starting year as 2016.
Transaction format
When demand history is stored in a database, it often consists of individual records for each transaction (e.g., each order or shipment) that includes the quantity and the date. If your data are currently available in Excel in this format, you will want to consider the transaction format. The advantage is that it may require less manipulation than other formats to bring the data into Forecast Pro. The disadvantage is that it is harder for a person to view and manipulate the data in Excel.
The data are assumed to reside in the leftmost (first) spreadsheet in the workbook unless an alternate sheet has been specified by naming it BFSDATA.
Row 1 of the spreadsheet is devoted to column headings. The keyword “description” must be used as the column heading for the description column. The other cells in row 1 are ignored by Forecast Pro. You can use them for titles, calendar information, etc.
Here is an example of a transaction format spreadsheet. The file is installed with the program and is named Sample Historic Data – Transactions.xlsx.
The above example does not use attribute fields to define product groupings. This is often desirable and is discussed in the Defining the Hierarchy.
In our example, each data record consists of six header items in columns A through F, followed by the historic data in column G. Each item is placed in a separate cell.
Columns A through F contain the following six items required items.
- Variable name.
- Variable description. Up to 256 characters.
- Year. Must be an integer.
- Period. Must be an integer.
- Periods per year. Must be an integer.
- Periods per seasonal cycle. Must be an integer.
See Setting up the Historic data for more details on these items.