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 monitors 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 use 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?
Forecasts vs. History Exception Report
We will illustrate the Forecasts vs. History Exception Report in Forecast Pro TRAC with an example from 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. The history and the forecast show a distinct seasonal pattern where demand peaks in December and is lowest in the middle of the year.
(Note: this example is drawn from the Forecast Pro TRAC User’s Guide which provides detailed tutorial exercises. Accompanying data files are installed along with the program.)
The graph below shows that the forecast for December 2012 is 53,486 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 forecasts) appear reasonable when compared visually to the historic data for the previous 12 months.
As shown below, the actual demand for the previous December was 49,766 cases. Comparing the forecast for December 2012 to December 2011’s actual shows that the forecast of 53,468 is within 6.9% of the previous year’s actual.
While the best way to validate the forecast for December 2012 would be to compare it to December 2011 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, to December 2010.
The full forecast period covers July 2012 through June 2013 – 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 2012 forecast vs. the July 2011 actual, compare the August 2012 forecast vs. the August 2011 actual, compare the September 2012 forecast vs. the September 2011 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 forecast, each with a 12-month forecast). For many forecasters the number of items being forecast is in the hundreds, thousands or even tens-of-thousands, making manual review of the forecasts simply untenable. Using the Forecasts vs. History Exception Report in Forecast Pro TRAC, you can focus on those forecasts that require your attention.
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 as shown below.
In this example, the following settings have been selected:
- “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.
- In the “Allowable deviation from history” section a “Global Minimum” of +/- 15 is selected.
- 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.
To schedule a live WebEx demonstration of Forecast Pro TRAC click here.