Question

Question 1 contains the actual values for 12 periods (listed in order, 1-12). In Excel, create forecasts for periods 6-13 using each of the following methods: 5 period simple moving average; 4 period weighted moving average (0.63, 0.26, 0.08, 0.03); exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53); linear regression with the equation based on all 12 periods; and quadratic regression with the equation based on all 12 periods.  Round all numerical answers to two decimal places.

1. The actual values for 12 periods (shown in order) are:

(1) 45  (2) 52 (3) 48 (4) 59  (5) 55  (6) 54  (7) 64  (8) 59  (9) 72  (10) 66  (11) 67  (12) 78

Using a 5 period simple moving average, the forecast for period 13 will be:

2.  Using the 4 period weighted moving average, the forecast for period 13 will be:

3. With exponential smoothing, the forecast for period 13 will be

4. With linear regression, the forecast for period 13 will be:

5. With quadratic regression, the forecast for period 13 will be:

6. Considering only the forecasts for period 6-12, what is the lowest MAD value for any of the methods?

Answer 1

1. 5 period simple moving average is calculated as

For example the forecast for period 6 is obtained by deviding the sum of values from periods 1 to 5 by 5

The calculations are below

The values are

The forecast for period 13 will be 68.4

2. 4 period weighted moving average with the weights (0.63, 0.26, 0.08, 0.03) is calculated as

Use the following

get the following

Using the 4 period weighted moving average, the forecast for period 13 will be: 74

3. exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53)

The forecast for period t+1 is

The forecast for period 6 is (having given that the forecast for period 5 is 53 and the value of period 5 is 55)

The calculation for the rest is

The values are

With exponential smoothing, the forecast for period 13 will be 66.29

4. linear regression with the equation based on all 12 periods

The linear model that we want ot estimate is

where Y is the value for period t

is the intercept of the regression line

is the slope coefficient for t

is a random disturbance

We use data-->data analysis--regression to setup

get the following

the estimated regression equation is

The forecast for period 6 is

The calculations are below

get the values

With linear regression, the forecast for period 13 will be: 76.08

5.  quadratic regression

the model that we want to estimate is

where Y is the value for period t

is the intercept of the regression line

is the slope coefficient for t

is the slope coefficient for

is a random disturbance

We add a new column for t-square and using data-->data analsysis-->regression setup the regression

get the following

The estimated regression line is

the forecast for period 6 is

The formula for the rest is

The values are

With quadratic regression, the forecast for period 13 will be: 77.52

Mean Absolute Deviation (MAD) is calculated as below

MAD for each of the 5 forecast is below

the values are

The lowest MAD for any methods is 4.07 for quadratic regression