Measuring Validity for Moving Average Forecasts

 Introduction.  Again we are going to work at developing more objective measures for the quality of a forecast.  We are again going to focus on APE = Average Percentage Error.  Due to some previous familiarity I will start by showing the spreadsheet computations for APE for the two-period and three-period moving average forecasts we did on the Whistle While We Work data. The spreadsheet for the two-period moving average is below.

 Notice that the average error is about 21.85%, which is up from many of the error estimates we got for exponential smoothing.  Though none of these approaches is giving a very good forecast. The spreadsheet for the three-period moving average forecast is next.

 Notice that this error is actually worse than that for the two-period.  This shouldn't be too surprising because the major thing going on in this data is a trend and we have yet to study any forecasting methods that work well for trends! But we need to arrive at some general statements about computing our error terms and computing their averages. If there are n periods of historical data and we are doing an m-period moving average forecast there will be n - m periods for making comparisons between past observations and "past predictions" we need m periods of data for each forecast there are m blank periods that correspond to our earliest historical data where it wasn't possible to make any predictions.  These all influence the nature of our loops when we compute past predictions and errors. The code for computing the APEMovingAverage function follows. Function APEMovingAverage(Historical, NumberOfPeriods) As Single ' Declaring and initializing variables Dim Item As Variant Dim Counter As Integer Dim Accumulation As Single Dim NumberOfPredictions As Integer Dim ErrorCounter As Integer Dim ErrorPercentage As Single Dim ErrorAccumulation As Single Dim PredictionCounter As Integer Dim HistoricalSize As Integer Dim Prediction(100) As Single ' Initializing variables Counter = 1 Accumulation = 0# ' Determining the size of Historical array HistoricalSize = Historical.Count ' We can determine the number of predictions and comparisons we can perform NumberOfPredictions = HistoricalSize - NumberOfPeriods For PredictionCounter = 1 To HistoricalSize - NumberOfPeriods For Counter = 1 To NumberOfPeriods ' Accumulating the appropriate number of most recent previously observed values Accumulation = Accumulation + Historical(PredictionCounter + Counter - 1) Next Counter Prediction(PredictionCounter) = Accumulation / NumberOfPeriods Accumulation = 0# Next PredictionCounter ' Determining the errors For ErrorCounter = 1 To HistoricalSize - NumberOfPeriods ErrorPercentage = Abs(Historical(ErrorCounter + NumberOfPeriods) - _ Prediction(ErrorCounter)) / Historical(ErrorCounter + NumberOfPeriods) ErrorAccumulation = ErrorAccumulation + ErrorPercentage Next ErrorCounter APEMovingAverage = ErrorAccumulation / (HistoricalSize - NumberOfPeriods) End Function The code will be discussed in class. You want to check this function on your spreadsheet against other things we've developed.  Make sure you choose the number of periods in the moving average the same for both the prediction and the error computation.  For the WWWW three period moving average the spreadsheet follows.