Measuring Prediction Validity for Exponential Smoothing
Now we need to focus on trying to get some sense of validity for our
prediction. In many respects this entire notion is ludicrous, but
that has failed to stop a lot of efforts. Sit back and think about
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
Then you can copy this down the column where comparisons exist.
|The formula for cell C13 where you take the average of
the percentage errors is
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
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.