Question

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.

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

(1) 45  (2) 52 (3) 48 (4) 59  (5) 55  (6) 57  (7) 64  (8) 63  (9) 72  (10) 66  (11) 73  (12) 73

1. 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. Enter data in EXCEL-Data-Data Analysis-Moving average-enter range in the Input range (A1:A12)-type 5 in Interval-type C1 as output range. Click OK. The forecast for period 13 is 67.2.

2. Using the weighetd moving average, type theweights in cell E1, E2, E3, and E4 respectively. For forecast of period 13, type formula: =SUMPRODUCT(A9:A12, E1:E4). result is:67.773.

3. Using Exponential smoothing, select Exponential smoothing from Data Analysis-select input range (A1:A12)-enter Dumping factor-0.23-input output range G1-click OK.

The forecast for period 13 is 68.30617

4. Using regression [Data analysis-Regression-select Input X range (periods), and Y range (actual values)-type output range, I1-click OK.

Value=44.439+2.343 Period

Substitute Period with 13 and compute predicted value: 74.898.