Forecast Pro Tips & Tricks: Using Weighting Transformations in Forecast Pro
At the recent Forecasting Summit I had a discussion with an attendee about his forecasting process. He uses Forecast Pro, but rather than using “actual” monthly history he is using a “corrected” monthly history. He explained that his actual history contains prominent spikes due to the fact that the number of business days in a month varies, ranging anywhere from 20 days to 25 days. His company uses a planning calendar which is based on full weeks. As a result, some “months” contain four weeks while others contain five weeks. This is often referred to as a “4-4-5 Calendar”. His solution is to correct the data in Excel before importing it into Forecast Pro.
So why worry about the effects of this planning calendar when forecasting? The answer is found in a basic discussion of statistical forecasting. Statistical forecasting methods seek patterns in data, predictable sources of variation such as level, trend and seasonality. The forecaster in this discussion feels that the spikes in his data, which might otherwise be interpreted solely as seasonality, are at least in part present because specific months in each year are 25% longer. Correcting for the varying length of the planning periods affords a better view of the underlying demand patterns. This makes sense.
I explained to him that Forecast Pro has built-in functionality called weighting transformations that would easily and automatically allow him to correct for the 4-4-5 planning calendar’s impact.
Weighting Transformations in Forecast Pro
Weighting transformations can be used for a variety of purposes. Some common examples include:
It is important to note that weighting transformations happen internally and automatically in Forecast Pro via a straightforward three-step process. In the first step, the actual value for each period in the historical data is divided (de-weighted) by a user-defined “weight” for that period. In the second step, the de-weighted series is forecasted. In the final step, the resulting statistical forecast for each period in the future is multiplied (re-weighted) by a user-defined “weight” for that period. Once this step is completed, we have the “final” forecast.
- 4-4-5 calendar corrections – where a 12-period planning calendar is used and number of weeks per period varies.
- Trading day corrections – where calendar months are used and the number of business days per month varies.
- User defined seasonality – when it is desirable to supply your own estimate of the seasonal profile.
- Analog forecasting – when it is desirable to force the forecast to” mimic” the pattern of another product.
Weighting transformations in Forecast Pro involve de-weighting the data prior to generating the statistical forecast and then re-weighting the forecasted values. Weighting transformations can be used for a variety of purposes, including 4-4-5 Calendars, Trading Day Corrections, User Defined Seasonality and Analog Forecasting.
To illustrate, let’s explore using a weighting transformation to correct for the 4-4-5 calendar. In this case, the objective is to normalize the data so that we can capture the underlying level, trend and seasonality more directly. The screenshot below shows a historical data series (called SALES 3) with roughly seven years of monthly history. Note that, in general, the data spikes every third month, resulting in what is sometimes referred to as a “hockey stick” pattern. This is the pattern imposed by the 4-4-5 calendar. By de-weighting the historical data by the number of weeks in each period we can remove that part of the variation in the data. In effect we are transforming sales-per-month to sales-per-week-per-month.
The above graph shows approximately seven years of “monthly” data starting in January 1996 and running through October 2002. The data has been gathered using a 4-4-5 convention. Note the “hockey stick” pattern and that the data generally spikes every third month.
The Data Components for Weighting Transformations
The key data components for using a weighting transformation in Forecast Pro are:
1.) The historical data
In Forecast Pro the weights necessary to transform the data are dictated by the weighting schedule. The weighting schedule is an additional data series which covers both the historical period and the forecast period. In our example it is simply a series consisting of fours and fives, reflecting whether a specific month is a 4-week month or a 5-week month. The screenshot below shows both the SALES 3 historical data series and the weighting schedule (called _WEEKS) from our example.
2.) The “weighting schedule”
Using a weighting transformation we can remove the 4-4-5 effect before generating the statistical forecast by de-weighting (dividing by either four or five) the historical data and then re-weighting (multiplying by either four or five) the resulting forecast. This is controlled through the weighting schedule.
When we apply the _WEEKS weighting schedule to SALES 3 Forecast Pro does the following:
1.) De-weights the data prior to generating the statistical forecasts by dividing each historical value by the appropriate weight specified in the _WEEKS weighting schedule—either four or five—for that period.
The “\WGT=” Modifier in Forecast Pro
2.) Forecasts the resultant “de-weighted” series.
3.) Re-weights the statistical forecasts by multiplying each forecast value by the appropriate weight specified in the _WEEKS weighting schedule—either four or five—for that period.
To apply the weighting transformation in Forecast Pro you use the modifier “\WGT=_SCHEDULE”, where _SCHEDULE is the name of the weighting schedule. Thus, the modifier for our SALES 3 example is \WGT=_WEEKS. The screenshot below shows the \WGT=_WEEKS modifier being applied in Forecast Pro Unlimited. In the example shown, the transformation will be applied to all items in the data file 445DATA. The way the modifier is applied can vary depending on the edition of Forecast Pro you are using and whether you want to apply the weighting transformation to an individual data series or multiple data series. If in doubt, be sure to consult the User’s Manual or contact BFS Technical Support.
The \WGT=_WEEKS modifier in the above tells Forecast Pro to perform a weighting transformation using the _WEEKS weighting schedule.
Weighting transformations are one of the topics covered in Forecast Pro Product Training Workshops. Click here for a current workshop schedule.
About the author:
Bob Leonard is the Director of Sales at Business Forecast Systems, Inc. (BFS) and the editor of Trends. In his ten plus years with BFS, Bob’s responsibilities have included sales & marketing as well as forecasting consulting, education and training.