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) 58 (7) 64 (8) 61 (9) 71 (10) 66 (11) 69 (12) 77 Using a 5 period simple moving average, the forecast for period 13 will be:

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

Answer 1

Let t=1,2,...,12 indicate the period and indicate the actual values for period t, be the forecast value for period t

1. Forecast using simple moving average is

For example for period 6, the forecasted moving average is

The 4 period weighted moving average (0.63, 0.26, 0.08, 0.03) is calculated as

For example the forecast for period 6 is

Forecast using exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53)

For example the forecast for period 6 (knowing that ) is

linear regression with the equation based on all 12 periods;

The regression line that we want to estimate is

where is the intercept

is the slope of the regression line

is a random error

We estimate the regression using data-->data analysis-->regression

get this

The estimated regression equation is

For example forecast for period 6 is

quadratic regression with the equation based on all 12 periods

the regression equation that we want to estimate is

where is the intercept

are the slope coefficients

is a random error

First we create a new column t2 which is the square of period t.

We estimate the regression using data-->data analysis-->regression

get this

the estimated regression equation is

For example the forecast for period 6 is

Prepare the following sheet

get these values

ans: The forecast for period 13 using

• a 5 period simple moving average is 68.80
• 4 period weighted moving average is 73.86
• exponential smoothing is 66.66
• Linear regression is 76.67
• Quadratic regression is 76.70

2) MAD is calculated as

We prepare the following sheet

get this

Lowest MAD is for linear and quadratic regressions.

Since linear regression has one less term and the slope coefficient of quadratic term is not significant, we will prefer the liner regression.