Often spreadsheets created for Forecast Pro are output from a corporate database or ERP system. At times, this leads to the user having limited control on how the spreadsheets are formatted and how they use zeroes and blank cells. To accommodate this, Forecast Pro provides a variety of options to allow you to control how the spreadsheet is interpreted when it is read into the Forecast Pro project.
In this section we will examine how some of these options work.
Start Forecast Pro. On the Startup page, click “New Project”. From the Home tab, click on the Data Manager icon to open the Data Manager.
Click the Add drop-down on the Historic Data row, select Excel and select Format Options – Historic Data.xlsx to add it to the Historic Data row. Click on the Read & Forecast button and then on the Exit button to close the Data Manager.
Right click on the Navigator to invoke the Navigator’s context menu. Select Expand All to fully expand the Navigator. Select Total>SKU02. From the Home ribbon, click the Options icon to invoke the Options dialog box. Select the Formats tab. Uncheck the option labeled “Set trailing blanks to zero.”
The settings should now match the screenshot below.
All options on this tab are documented in detail in The Formats Tab. We will examine some of the ones that impact how data is read into the program. Make sure your settings match the ones above—with Ignore leading zeros unchecked, Ignore trailing zeros unchecked and Set trailing blanks to zero unchecked—and click OK. Note that changing this option has triggered a reforecast.
Open Format Options – Historic Data.xlsx in Excel. (The default location for all Forecast Pro’s sample historic and optional input files is \Forecast Pro\Forecast Pro TRAC v8\Input\ in your Public Documents folder.) This is the file we just read into Forecast Pro.
Let’s now examine how some of the Format Options impact how data are read into Forecast Pro.
Consider SKU01 and SKU02. The demand history in Excel looks very similar, but the first four data cells (columns H, I, J and K) are blank in SKU01 and contain zeroes for SKU02. Notice that in Forecast Pro, SKU01 starts in May 2018—Forecast Pro treated the leading blank cells as place holders (the data haven’t started yet). Notice that in Forecast Pro, SKU02 starts in January 2018—Forecast Pro treated the leading zeroes as data points (the product was available, but none were sold).
Ignore leading zeroes: If your spreadsheet uses leading zeroes to pad the data prior to the first actual demand period, then Ignore leading zeroes setting will be important. In our example, if Ignore leading zeroes had been checked then the four leading zeroes in SKU02 would be ignored, and the starting point would be May 2018. Notice that SKU01 will always begin on May 2018 regardless of how Ignore leading zeroes is set. This is why using blank cells to pad the data prior to the first data set is the recommended practice.
Consider SKU03 and SKU04. The demand history in Excel looks very similar with the exception that the final six data cells (columns BP, BQ, BR, BS, BT and BU) are blank in SKU03 and contain zeroes for SKU04. Notice that in Forecast Pro, SKU03 is marked as discontinued and is not forecasted. This is because SKU03 does not contain any data for the project’s global ending date (June 2023) and Set trailing blanks to zero is not selected. Notice that Forecast Pro is not discontinuing SKU04. It is treating the zeroes as valid demand observations (the product was available, but none were sold).
Ignore trailing zeroes: If your spreadsheet contains discontinued items and uses zeroes in the discontinued periods (rather than blank cells), then the Ignore trailing zeroes setting will be important. In our example, if Ignore trailing zeroes had been checked, then the six trailing zeroes in SKU04 would be ignored and SKU04 would be treated as discontinued. Notice that Forecast Pro will treat SKU03 as discontinued regardless of how Ignore trailing zeroes is set. This is why using blank cells to indicate when a series is discontinued is the recommended practice.
Consider SKU05 and SKU06. The data represents a product that mostly sells in the winter months and has lots of zero-demand periods. SKU05 uses zeroes for the zero demand periods and SKU06 uses blank cells. Notice that Forecast Pro is reading in SKU05 properly and is marking SKU06 as discontinued since it does not contain data on the project’s global ending date (June 2023).
Missing and Set trailing blanks to zero: If your spreadsheet contains zero-demand periods and uses blank cells (rather than zeroes), then the Missing and Set trailing blanks to zero options will be important. Forecast Pro uses the Missing option to determine how to treat blank cells that appear in between cells containing data (for example columns I, J, K, L, M and N in SKU06). For SKU06 we want to interpret these “missing” data points as zeroes (which is the default setting). SKU06 also has blank cells in columns BR, BS, BT and BU. If Set trailing blanks to zero is not selected then SKU06 will be read as discontinued in March 2023 and not forecasted. If Set trailing blanks to zero is selected (the default), then the four final blank cells before the global ending date will be set to zero and SKU06 will end in June 2023, be read as active and forecasted. In general, hard coding zero-demand periods with zeroes will be less reliant on the program settings and is the recommended practice.
Consider SKU07. It has no historic demand. Forecast Pro is including it on the Navigator and expert selection has set the forecasts to zero. If you wanted to, you could choose an alternative forecasting method for SKU07 or override the default forecast using the Override grid.
Experiment with the options on the Format tab of the Options menu until you are comfortable with their operation. When you are done, exit Forecast Pro.