Statistics

FORECASTING - TIME SERIES ANALYSIS IN EXCEL

(Chapter 16 in Excel)

EXERCISE #1: OBTAIN FORECASTED VALUES USING THE MOVING AVERAGE METHOD

Option 1: With Data Analysis Toolpak: Data ( Data Analysis ( Moving Average

In the dialog box: Enter the data range in “INPUT RANGE” and under “INTERVAL” enter the number for your choice of an MA order (e.g., for an MA(5), the number 5 was entered above).

Then under “OUTPUT RANGE”, highlight the cells in which you want to place the output but start with one cell after the first one with the real values. For example, from above, cell F3 was selected as the output range and the output will be as follows:

Option 2: Enter AVERAGE formulas in each cell

For example, to get the MA(5) forecasted value of $21.702 as shown in cell F7 the formula appears below:

EXERCISE #2: OBTAIN FORECASTED VALUES USING THE EXPONENTIAL SMOOTHING METHOD

Option 1: With Data Analysis Toolpak: Data ( Data Analysis ( Exponential Smoothing

In the dialog box: Enter the data range in “INPUT RANGE” and under “DAMPING FACTOR” enter the number for your choice of a weight (W) or smoothing coefficient between 0 and 1.

Excel uses this particular formula where W is the weight:

forecasted Yt = (1- W) Yt – 1 + W forecasted Yt - 1

Then under “OUTPUT RANGE”, highlight the cells in which you want to place the output but place it at the same row as the first observation with the real values. For example, as shown below, the output range was selected to be in cell F2.

The output will be:

Option 2: Enter the formulas in each cell

For example, with a smoothing factor of 0.50 in which the exponential smoothing equation is

forecasted Yt = (1- W) Yt – 1 + W forecasted Yt - 1

to get the exp. smoothing forecasted value of $22.87 in cell F4 the formula appears below:

EXERCISE #3: OBTAIN FORECASTED VALUES USING THE REGRESSION ANALYSIS METHOD

First, define your regression model; any model you wish but explain it using the equation form. For example, we can forecast using a First Degree Autoregressive Model with this equation:

Yt = a + b Yt -1

Note that in Excel you would need to create an extra column for the data at time Yt - 1 as shown below:

Option 1: With Data Analysis Toolpak: Data ( Data Analysis ( Regression

In the dialog box: enter under “Input Y Range” the t data. Under “Input X Range”, enter the t-1 data. And, mark “Line Fit Plots” to get all the forecasted Yt

The output will be as shown below. Notice that by selecting “Line Fit Plots“, the regression output includes the values of each forecasted Yt under the column titled “Predicted Sara Lee at t” in the “Residual Output” section shown in the last section of the output.

From the regression output, using this data as an example, we can see that the intercept is equal to 3.611107174 and the slope is equal to 0.813321907. Thus, our regression equation is:

Yt = 3.611107174 + 0.813321907 Yt-1

Note: because the 95% confidence interval of the slope does not include 0, we can say that Yt is affected by its past values.

Option 2: Enter the formulas in each cell

To calculate the intercept and the slope, enter these formulas separately in a cell:

=INTERCEPT(data range of Yt, data range of Yt-1)

=SLOPE(data range of Yt, data range of Yt-1)

Below, you can see the formula for the intercept in cell I3

Below, you can see the formula for the slope in cell I4

Finally, to compute the forecasted values create one new column and use the regression equation with the values of the intercept and slope. For our sample data, this formula is

Yt = 3.611107174 + 0.813321907 Yt-1

and the Excel column will be as shown below:

EXERCISE #4: COMPARE THE FORECASTED VALUES FROM ALL THREE METHODS AND CHOOSE THE BEST ONE

In this case, we needed to calculate the forecast error under each method for each observation and use MAD = average absolute forecast error. Hence, the first part in Excel is to get the absolute value of each of the forecast errors under each method as follows (see formula in cell C7):

Then, we calculate the average of these forecast errors under each method to get the MAD as follows (see formula in cell C56):

As we can see the MAD is lower under the regression method, which is not surprising since we have an upward time trend in the data because these are stock prices that have been moving up over the years. In the case of an upward time trend it is better to use a regression model.

These are the forecasted values