INTERPRETACIÓN Y ANÁLISIS DE LA EXPERIENCIA

Moving averages are employed as forecasting tools in applications ranging from stock market predictions to estimations of sales and inventory trends. The calculation assumes that a forecast value of the variable under consideration may be made as a simple arithmetic average of the preceding actual values over a selected number of time periods. The number of periods is chosen to fit the situation. In many cases, moving averages are charted using several calculation intervals to gain comparative insights into the specific trends.

The formula for the moving average calculation is

Ft = (1/n) (3.4)

or

Ft+1 = (1/n) (3.5)

where

Ft = forecast value of the variable at time t

n = number of previous time periods over which the average is to be computed (Excel uses a default value of 3 periods if some other number is not specified) At = actual value of the variable at time t

Thus, for n = 4 time intervals, we would have forecast values at times t = 6 and 7 of F6 = (A5 + A4 + A3 + A2 )/4

F7 = (A6 + A5 + A4 + A3)/4

Excel performs the calculation for a set of specified At values and presents a graph of the forecast values Ft along with the actual values for comparison. It is an easy matter to

At-i i n =

∑

∑

Charts and Graphs 51

change the number of periods for the moving average calculation to examine the influence of this selection on forecasting trends.

Example 3.1: Weather Temperature Trends

Figure 3.18 displays three types of weather temperature data as indicated in the nomen- clature for the figure: (a) TV fifth day future forecasts for high and low temperatures, (b) actual high and low temperatures, and (c) long-term average or normal high and low temperatures. We will present results of moving average calculations for 10-, 30-, and 60- d intervals over a 220-d total time period. The calculations will be made for the following:

1. The TV fifth day future forecast for daily high temperature in °F. 2. The long-term average high temperature in °F.

The moving average calculation is CALLED by clicking TOOLS/DATA ANALYSIS/ Moving average, which results in the display of the Moving Average dialog box as shown in Figure 3.19. Entries are made in this box as described in the following paragraphs.

The input range is specified for the TV forecast data as in column C3:C223. These worksheet data are not displayed because of the large number of entries. The first two cells of the column are labels, so the actual data points are in C3:C223. If a label is in the first row of the data column selected, that box should be checked. If not, Excel will label the variables as Values and the abscissa as Data Point when the graph is displayed. The abscissa in this case will be labeled Days, and we will insert the proper titles for the graph coordinates in the editing process. The interval for this problem is 10, 30, or 60 d.

Specifying the upper-left cell of the output table automatically sets the output range for the forecast values. For the case shown in the dialog box, AD3 is chosen for the 10-d averaging. If the standard error box is checked, a second result column adjacent to the output forecast values will be reserved. We will discuss the equation used for the standard error calculation later in Section 3.18.1.

A chart output should be selected. The chart will appear embedded on the calculation worksheet. In many cases, this chart will require considerable editing to bring it to accept- able visual proportions. The chart will contain plots of both the actual values At given in the specified data column and the computed forecast values Ft. The forecast plot will not start until t equals the interval value.

Figure 3.20 and Figure 3.21 show the 10- and 30-d moving averages for the TV forecast data. Clearly, the 10-d average follows the actual data more closely than the 30-d average. The actual temperature rises through spring and summer, and the moving average lags this advance, with the lag increasing as the averaging interval is increased. If the process were carried into the fall and winter season, we would find that the moving averages would still lag the actual temperatures and, thus, fall above the actual temperatures on the chart.

Figure 3.22 presents 10-, 30-, and 60-d moving averages for the long-term average normal high temperatures, along with the actual temperature values upon which the averaging calculations were based. The jagged nature of the actual temperature curve results from data rounded to the nearest degree. It should be a smooth curve. The

Charts and Graphs 53

FIGURE 3.20

FIGURE 3.21

Ten Day Moving Average

50 60 70 80 90 100 110 0 50 100 150 200 250 Day Forecast Hi Temperature Actual Forecast

Thirty Day Moving Average

50 60 70 80 90 100 110 0 50 100 150 200 250 Day Forecast Hi Temperature Actual Forecast

moving average curves exhibit the lag behavior shown previously for the forecast temperatures in Figure 3.20 and Figure 3.21. The larger the number of time intervals, the greater the lag.

Some stock market enthusiasts claim that when the charted price of a stock breaks through a 30- or 60-d moving average, the future trend will be in the direction of the breakthrough. If the stock breaks on the upside, it should be bought. If it breaks on the downside it should be sold or shorted. Reliable data on this effect are difficult to obtain.

3.18.1 Standard Error

The standard error for the moving average function is defined by:

S(t +1) = {∑[(At+1-I - Ft+1-I)2/n]}1/2 (3.6)

This function has the same form as a population standard deviation.

The standard error for the 10-d moving average of Figure 3.20 is plotted in Figure 3.23. The decreasing trend with the approach of summer indicates less volatility in temperature as the calendar progresses. This just means that Texas is predictably hot in the summer — day after day.

FIGURE 3.22 50 55 60 65 70 75 80 85 90 95 0 50 100 150 200 250 Days Moving Average Normal Hi Temperature

Ten Day Avg

Charts and Graphs 55