Forecast Pro Tips & Tricks:
Exception Reporting in Forecast Pro TRAC

Forecast Pro TRAC provides a wide array of exception reports, including reports which monitor your archived forecasts (i.e., previously created and saved forecasts) and reports which monitor your current forecasts (i.e., the forecasts you are working with but haven’t yet finalized). Exception reports enable you to quickly find cases where key performance metrics have fallen outside of an acceptable range. In this installment of Forecast Pro Tips & Tricks we look at the Forecasts vs. History Exception Report which is one of the reports available to monitor current forecasts.

Monitoring the Current Forecast
Many companies create forecasts via an iterative periodic forecast process or cycle. This may be a formally defined process which is closely followed each period, or it may be a less formalized process. It may involve input from a single individual or it may involve a collaborative effort. Regardless of the approach, it is useful to think of the in-process forecast as the current forecast. The end result (upon completion of the last iteration) is the final forecast; this is the forecast the company will act upon, using it to drive planning and decision-making.

As the current forecast evolves into the final forecast it can be beneficial for the forecaster(s) to monitor certain conditions in the forecast. For instance:

• Does the current forecast fall within a reasonable range?
• Is the current forecast substantially different than last period’s forecast?
• How does the current forecast compare to the history?

An Example in Forecast Pro TRAC: Forecasts vs. History Exception Report
To illustrate the Forecast vs. History Exception Report we are using an example drawn from the Forecast Pro TRAC User’s Guide which provides detailed tutorial exercises. Accompanying data files are installed when the program is installed. The example comes from a company called 123 Bakery. In the screenshot below, a 12-month forecast for an individual SKU ("12-Count Corn Muffin") to a specific customer ("Stuff-Mart") is shown. Note that the history and the forecast show a distinct seasonal pattern where demand peaks in December and is lowest in the middle of the year.

The screenshot below shows that the forecast for December 2009 is 36,746 cases. Upon visual inspection it appears that this forecast looks reasonable when compared to the previous December’s actual. In general, the 12 forecasts represented on the red line (the point forecast) appear reasonable when compared visually to the historic data for the previous 12 months.

The following screenshot shows that the actual for the previous December was 38,880 cases. Comparing the forecast for December 2009 to December 2008’s actual shows that the forecast of 36,746 is within 5.5% of the previous year’s actual.

While the best way to validate the forecast for December 2009 would be to compare it to December 2009’s actual, that analysis cannot be performed for a number of months until the period rolls around. If, however, we think that the business is in a relatively stable state then perhaps the next best thing would be to compare the forecast to a meaningful point in the history – in this case, comparing it to December 2008.

The full forecast period covers July 2009 through June 2010 – a 12-month forecast. While it would be possible to manually check each month’s forecast against the same-period-previous-year’s actual (i.e., compare the July 2009 forecast vs. the July 2008 actual, compare the August 2009 forecast vs. the August 2008 actual, compare the September 2009 forecast vs. the September 2008 actual, etc.), this quickly becomes cumbersome.

Further, consider that in the 123 bakery example a total of 600 forecasts need to be monitored (50 items being forecasted, each with a 12-month forecast). For many forecasters the number of items being forecasted is in the hundreds, thousands or even tens-of-thousands, making manual review of the forecasts simply untenable.

Setting up the Forecasts vs. History Exception report
Clicking on the exception report icon or selecting View>Exceptions Report from the menu opens the Forecast Exceptions Report window. Right mouse clicking in the Forecast Exceptions Report window brings up the window’s context menu. Choosing Exception Report Settings... from the context menu brings up the Exception Report Settings dialog box shown below. The Forecasts vs. History tab contains the settings for the Forecasts vs. History exception report.

The screenshot above shows the Forecasts vs. History settings tab. Note that the following settings have been selected:

1.) "All Periods" in the "Forecast periods to consider" section is selected. This means that all forecasts across the forecast horizon will be reviewed – in our example 12 forecasts for each item.
2.) In the "Allowable deviation from history" section a "Global Minimum" of +/- 15 is selected.
3.) In the "Comparison Basis" section "Percent" is selected, and "History periods prior" is set to 12.

The resulting report generated via these settings is a report that identifies all forecasts where the forecast for a given period in the future is either greater than 15% lower or 15% higher than the actual for same-period-prior-year. The screenshot below shows the report.

In addition, the identified exceptions are ordered so that the forecast with the greatest deviation is listed first, followed in descending order by the others. Double-clicking directly on each line on the report brings you to that specific position in the Navigator and updates the report in the report window, making it extremely easy to review the results.

For Forecast Pro TRAC users who would like to learn more about exception reporting BFS offers public training classes, WebEx-based training and on-site training. Click here to learn more.

To schedule a live WebEx demonstration of Forecast Pro TRAC click here.