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.

QUESTION 1

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:

QUESTION 2

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

QUESTION 3

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

QUESTION 4

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

QUESTION 5

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

   QUESTION 6

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

PLEASE HIGHLIGHT AND NUMBER ANSWERS

Answer 1

1. Go to Data -> Data Analysis -> Moving Average -> Insert values of B column and put interval 5 -> Ok

Value of 13th period = 70.75

2. The formula to calculate 4 weighted moving average is

X_t = (4 * X_t-1 + 3 * X_t-2 + 2 * X_t-3 + X_t-4) / 10

Value of 13th period = 71.7

3. Go to Data ->Data Analysis -> Exponential Smoothing -> Insert input range of value and put damping factor 0.5 -> Ok

Value of 13th period = 72.19

4. Formula for linear regression is FORECAST(period of forecast,given values,given years)

Here it will FORECAST(A14,B2:B13,A2:A13)

Hence value of 13th period is 76.08