Moving Average Forecasting

 Periods in Moving  Average Forecast Forecast Calculation Forecast 1 89 89 2 (73 + 89)/2 81 3 (85 + 73 + 89)/3 82.33

 Now, hopefully you can see the pattern.  Which do you believe is the most accurate? Whistle While We Work.  Now we return to our new cleaning company started by your estranged half sister called Whistle While We Work.  You have some past sales data represented by the following section from a spreadsheet.  We first present the data for a three period moving average forecast.

 The entry for cell C6 should be =(B3 + B4 + B5)/3 Now you can copy this cell formula down to the other cells C7 through C11. Notice how the average moves over the most recent historical data but uses exactly the three most recent periods available for each prediction.  You should also notice that we don't really need to make the predictions for the past periods in order to develop our most recent prediction.  This is definitely different from the exponential smoothing model.  I've included the "past predictions" because we will use them in the next web page to measure prediction validity. Now I want to present the analogous results for a two period moving average forecast.

 The entry for cell C5 should be =(B3 + B4)/2 Now you can copy this cell formula down to the other cells C6 through C11. Notice how now only the two most recent pieces of historical data are used for each prediction.  Again I have included the "past predictions" for illustrative purposes and for later use in forecast validation. Some other things that are of importance to notice. For an m-period moving average forecast only the m most recent data values are used to make the prediction.  Nothing else is necessary. For an m-period moving average forecast, when making "past predictions", notice that the first prediction occurs in period m + 1. Both of these issues will be very significant when we develop our code. Developing the Moving Average Function.  Now we need to develop the code for the moving average forecast that can be used more flexibly.  The code follows.  Notice that the inputs are for the number of periods you want to use in the forecast and the array of historical values.  You can store it in whatever workbook you want. Function MovingAverage(Historical, NumberOfPeriods) As Single ' Declaring and initializing variables Dim Item As Variant Dim Counter As Integer Dim Accumulation As Single Dim HistoricalSize As Integer ' Initializing variables Counter = 1 Accumulation = 0 ' Determining the size of Historical array HistoricalSize = Historical.Count For Counter = 1 To NumberOfPeriods ' Accumulating the appropriate number of most recent previously observed values Accumulation = Accumulation + Historical(HistoricalSize - NumberOfPeriods + Counter) Next Counter MovingAverage = Accumulation / NumberOfPeriods End Function The code will be explained in class. You want to position the function on the spreadsheet so that the result of the computation appears where it should like the following.

 Now we need to develop the code for the prediction validity in the next web page.