Open 123 Bakery - Historic Data.xlsx in Excel.
Note that the historic data is on the first worksheet of the Excel workbook. Forecast Pro assumes the data reside in the first (topmost) worksheet unless an alternate sheet has been specified by naming it “BFSData”.
Row 1 of the worksheet 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.
Subsequent rows contain the data for each end item to be forecasted. Notice that group-level data is NOT included in the spreadsheet. The group-level data is created by Forecast Pro when you read in the end items.
The columns in 123 Bakery - Historic Data.xlsx are structured as follows:
A-D Attributes in the defined hierarchy. When the data is read into Forecast Pro, the attributes define the item names and the hierarchy displayed in the Navigator. For example, the first item (row 2) will be identified as Total>Cakes>Food-King>BU-20-02. Attributes are described in more detail in the Forecast Pro Reference Manual.
E Variable description. This column must be labeled Description.
F Starting year. This field must be an integer. The Bakery data starts in 2015 for all data rows.
G Starting period. This field must be an integer. The Bakery data starts in January, so the column is 1 for all data rows.
H Periods per year. This field must be an integer. Since the Bakery data is monthly, this is 12 for all data rows.
I Periods per seasonal cycle. The length of the seasonal pattern. This field must be an integer. For monthly and weekly data, this is typically the same as periods per year.
J-BW Historic Data values from January 2015-June 2020. Column J contains the first data point in the series which corresponds to January 2015, based on the values in columns F and G (not the column label in J1). Each row is the historic data series for the item described by the attributes in columns A-D. For example, sales for Total>Cakes>Food-King>CA-20-01 was 20,829 in January 2015.
When Forecast Pro reads in a spreadsheet it uses the starting year, starting period and the presence of historic data to determine when each end item starts. Notice that 123 Bakery - Historic Data.xlsx uses the same starting year and starting period for all end items and then uses blank cells to “pad” the end items that don’t start on January of 2015. This is not the only way you can establish the starting dates for each series but is the recommended way when using a spreadsheet.
When Forecast Pro reads in data it determines the latest period that contains data for any item and uses this as the global ending date for the project. In our example June 2020 (column BW) is our global ending date. If data exists for a given end item on the global ending date, then that end item is considered active and will be forecasted. If data does not exist for a given item on the global ending date, then that item will be considered discontinued and will not be forecasted. In our example, data exists for all end items on June 2020 and therefore all items will be considered active.
There is a big difference between a product not being available (e.g., the product hasn’t launched yet or has been discontinued) and a product being available but having zero demand in a given period (e.g., the product was available—we didn’t sell any). Therefore, it is very important that you understand how Forecast Pro interprets blank cells and cells containing zeroes when setting up your data.
In the next section we will explore some of the settings in Forecast Pro that pertain to reading in data. We will see that some of these settings control how blank cells and zeroes are interpreted, allowing you to set up your data in different ways.