Trend of Historic Averages Algorithm

The application uses the following algorithm to calculate forecasted values for the Trend of Historic Averages and the BUP - Trend of Historic Averages forecast methods.

The application uses this forecast method to calculate the forecast for each day using the steps below. For example, suppose you are creating a forecast for Monday in week 14 of this year.

1. The application calculates the sum of actuals of last year’s similar days. The last year’s matching day is found by subtracting 364 days. Last year’s similar days are found by successively subtracting an additional 7 days.

   The following formula demonstrates how the application calculates the actuals for last year’s similar days for Monday in week 14:

   

   If actuals are missing for any similar days last year, or if any of those days are tagged as skip special day, then the application skips those days and uses the previous similar day, going back up to 20 weeks before the matching day.

2. The application calculates the sum of this year’s actual or forecasted similar days.

   The following formula demonstrates how the application calculates the actual or forecasted data for this year’s similar days for Monday in week 14:

   

   • If actuals are not available for a similar day this year, the application uses the forecasted value for that day instead.

   • If any of TY days are tagged as skip special day, then the application skips those days and uses the previous similar day, going back up to 53 weeks before the forecasted day. For information about special days, see Special Days and Matching Days.

   • If the application does not find any TY similar days within the 53-week range, it returns a zero value (0). The forecast generation does not fail and you can manually edit the values on the Worksheet after forecast generation.

   • If the system skips a week this year, it also skips the corresponding week in the last year to ensure alignment between the years, and vice versa.

3. The application calculates the ratio of the LY actual data to TY actual or forecast data.

   The following formula demonstrates how the application calculates the ratio of the LY actual data to TY actual or forecast data for Monday in week 14:

   

   • The application always uses an equal number or data points for this year’s actuals and last year’s actuals to create the ratio. If there are more data points for one year than for the other, then the application drops additional data points for the year with more data points, so that each set of values has the same number of data points.

   • The application drops the highest and lowest values only if there are at least 6 data points.

4. The application multiplies the ratio calculated in the previous step by the actuals of last year’s matching day.

   • The application always finds last year’s matching day by subtracting 364 days, which corresponds to the same day of the week 52 weeks ago. Note that in a 53-week year, this is not the corresponding week.

   • The following formula demonstrates how the application multiplies this ratio by the actual’s of last year’s Monday in week 14:

     

5. After the application forecasts data for every day in a week, the applcation aggregates that data into the weekly values displayed on the Worksheet.

To create the forecasted value for week 14, the application creates a forecast for each day in week 14 using the above steps. Then the application aggregates all the daily values for that week to create the weekly value.