How to Import Overrides and Modifiers from Excel

tipsandtricks

Many Forecast Pro users rely on the software’s expert selection capability to choose the appropriate forecasting techniques and generate the forecasts, and then “tweak” the forecasts as needed. One way to adjust a forecast is to change the forecasting method used; when you do this, a “forecast modifier” appears next to the item in the Navigator to record and maintain your selection. A second option for adjusting the statistical forecast is to apply overrides.

In this edition of Tips and Tricks, we explain how to import overrides and modifiers into Forecast Pro directly from Excel which can save substantial time if you work with large data sets.

Another benefit of this approach is that it allows individuals who are not familiar with Forecast Pro to review and modify forecasts in Excel, and then import the revised forecasts into Forecast Pro without manual re-entry of the changes.

Importing Overrides from Excel

Importing overrides from Excel requires that you set up a spreadsheet containing the overrides in a specific format. The format is simple and will be familiar to anyone who has set up input data for Forecast Pro or worked with numeric output from Forecast Pro.

overrides

Excel Spreadsheet for Importing Overrides

The example above shows an Excel spreadsheet that has been set up to import overrides. Rows 2 and 3 show the overrides to the forecast for the item Total>Muffins>Stuff-Mart>COR-12-11. The first columns (A through D in our example) define the attributes and name of the item (e.g. the hierarchy information). The next column defines the override row to which the changes should be applied, followed by a column for comments (which, naturally, populates the comment field). Finally, the overrides themselves appear from left to right, starting with the first forecast period. If a specific cell is blank, no override will be imported for that period.

The requirements and specifications for the spreadsheet are as follows:

  • A header row is required. The Row and Comment fields must 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, e.g. 2011-July, or 2011-52 (if using weekly data).
  • Overrides can be assigned at any level.
  • The override row can be specified by either the row number or the row name.
  • Overrides can be entered as values or formulas.
  • Multiple overrides to the same item can be imported using multiple rows which will appear as different rows on the override grid.
  • If an item has no overrides, it does not need to be included in the spreadsheet.

Once the spreadsheet is ready, importing it into your Forecast Pro project is simple. With your project open, open the Project menu, and choose Import > Overrides From… as shown below.

From there, choose “All Excel files” as the file type from the bottom-right drop-down menu, and finally, select your specific Excel file containing the overrides.

This will populate your project with the overrides specified in the spreadsheet.

Importing overrides in this way also allows non-forecasters who are not familiar with Forecast Pro to make changes to the forecasts. For example, you can export the forecasts created in Forecast Pro to Excel as a Numeric Output file, allow a salesperson to make changes to that spreadsheet, and then re-import the file into Forecast Pro with minimal formatting changes.

Importing Modifiers from Excel

The format for importing modifiers from Excel is similar to that for importing overrides.

Excel Spreadsheet for Importing Modifiers

Once again–as shown in the example above–the first columns in the spreadsheet define the attributes and names of the items. This is followed by a single column that defines the modifiers to be imported.

The requirements and specifications for the spreadsheet are as follows:

  • A header row is required. The Modifier field must be present and labeled accordingly.
  • The modifiers can be assigned at any level.
  • Multiple modifiers can be assigned in a single cell.
  • If an item has no modifiers, it does not need to be included in the spreadsheet.

Once the spreadsheet is prepared, you can import it into Forecast Pro by opening the Project menu, and choosing Import > Modifiers From…, switching the file type to Excel, and choosing the desired spreadsheet. This will populate your hierarchy with the modifiers specified in the Excel file.

Summary

Importing overrides and modifiers directly from Excel can save substantial time if you work with large data sets. The format is straightforward and the files can easily be prepared from scratch or by modifying Forecast Pro output files.

The ability to import forecast overrides from Excel also facilitates collaboration. Using Excel, your colleagues who do not use Forecast Pro can review and modify the forecasts generated in Forecast Pro and then import those changes directly into a Forecast Pro project.

If you would like to learn more about Forecast Pro, please visit our site, or contact us directly to schedule a live demo via WebEx.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s