• We have a column of historical observations.
• We have used a model to create a column of predictions
• If we pretend that we move to different points in the past we can see what the model would have predicted.  Then we can use these values to see how they compare to what actually occurred.

This will be the basis of our validation efforts.  We will focus on APE = Average Percentage Error.  What we need to do is take the differences between what the model would have predicted if it was used in the past and what was actually observed.  Then we divide each of these differences by the actual historical value.  Then we add all of these error percentages up and average them.  This corresponds to the following formula.

While there is no perfect measurement of the validity of a prediction, this should help us to compare validity to other forecasts.  Now we need to develop our spreadsheet to illustrate this development.

The formula for cell D4 should be

=ABS(B4-C4)/B4

Then you can copy this down the column where comparisons exist.

=SUM(D4:D10)/COUNT(D4:D10)

Notice that are percentage errors for each prediction are quite bad.  At this point, all I can say is hopefully we will do better.  We need to take the absolute value of each difference to make sure we average error terms which should all be positive.

Modifying Our Code.  There are a couple ways we can modify our code to calculate these error terms and determine the average.  Since we have all of our entries in arrays I will do these calculations in a separate code segment, though I will write out the entire function on this page.  

Function APEExponentialSmoothing(Weight, Historical) As Single
' Declaring and initializing variables
Dim Counter As Integer
Dim ErrorCounter As Integer
Dim HistoricalSize As Integer
Dim Item As Variant
Dim ErrorTerm As Single
Dim TotalError As Single
Dim Predictions(1 To 100) As Single

' Initializing values

Counter = 1
TotalError = 0#

For Each Item In Historical

If Counter = 1 Then

Predictions(1) = Historical(Counter)

Counter = Counter + 1

Else

Predictions(Counter) = (Weight * Historical(Counter)) + ((1 - Weight) * Predictions(Counter - 1))

Counter = Counter + 1

End If

Next Item

HistoricalSize = Counter - 1

' Calculating the Average Percentage Error

For ErrorCounter = 1 To HistoricalSize - 1

' Doing the calculations by breaking out each term in hopes it makes this clearer

ErrorTerm = Abs(Historical(ErrorCounter + 1) - Predictions(ErrorCounter)) / Historical(ErrorCounter + 1)
TotalError = TotalError + ErrorTerm

Next ErrorCounter

APEExponentialSmoothing = TotalError / (HistoricalSize - 1)

End Function

Notice that we have used a different function to compute the errors.  You should also notice that we had to recalculate the predictions in order to determine the errors.  This code will be discussed in class.

Make sure you test this code by comparing it to your results for the spreadsheet calculations.

You are likely to want to develop your spreadsheet like the following so that it automatically recalculates both the forecast and the error as you change the weight.