The charts of Marvin's diet in this chapter were generated from an Excel worksheet that's included to allow you to experiment further on your own and get a better feel for how moving averages identify the overall trend among data that subject to large short-term variations.
To use this model, load the worksheet SMOOTH.XLS into Excel. You should see a something like this on your screen.
Depending on your monitor and graphics board, you may have to resize the window to see the entire worksheet. The chart shows the true trend line as a thin red line. This trend is masked by random variations from day to day, resulting in daily measurements drawn as green diamonds connected by yellow lines. The trend extracted by the selected moving average is drawn as a thick blue line. The closer the blue line approximates the red line indicating the true trend, the more effective the moving average has been in filtering out the short term random variations in the measurements.
You can control the moving average model by entering values in the following boxes of the control panel.
This parameter selects the type of moving average and its degree of smoothing. If positive, an exponentially smoothed moving average with smoothing constant equal to Smoothing is used. Only smoothing constants between 0 and 1 are valid. If negative, a simple moving average over the last -Smoothing days is used. To see the effects of a 20 day simple moving average, enter ``-20'' in the Smoothing cell.
The Noise value specifies the day to day random perturbation of
the basic trend. If you set Noise to 10, the measured values
will be randomly displaced ± 5 from the true trend. The random
displacement of points in the primary trend changes every time the
worksheet is recalculated. To show the effects of a different random
displacement of the current trend, press
to force recalculation.
Since a moving average looks back at prior measurements, it lags the current trend. You can shift the moving average backward in time to cancel this lag by entering the number of days of displacement in the Shift cell. This allows you to compare the shape of the trend curve found by various moving averages with the original trend. A Shift value of zero disables displacement and produces a moving average that behaves, with respect to the actual trend, just as one calculated daily from current data. For a simple moving average, a Shift of half the days of Smoothing will generally align the trend and moving average. For an exponentially smoothed moving average, a Smoothing value of 0.9 can be aligned with a Shift of about 10.
The trend used in this model is generated by a cosine function. Amplitude controls the extent of the trend; the peak to peak variation is twice the value of Amplitude.
Rate controls the period of the primary trend, specified as the number of days from trough to peak and vice versa. As you decrease Rate, the trend varies more rapidly, requiring a shorter-term moving average to follow.
By John Walker