Question 1 contains the actual values for 12 periods (listed in order, 1-12). In

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 10 periods; and quadratic regression with the equation based on all 10 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) 57 (7) 64 (8) 58 (9) 68 (10) 66 (11) 69 (12) 75 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?

Explanation / Answer

1. 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) 58 (9) 68 (10) 66 (11) 69 (12) 75

This problem we can done in MINITAB.

Dataset :

period   actual
1   45
2   52
3   48
4   59
5   55
6   57
7   64
8   58
9   68
10   66
11   69
12   75

Using a 5 period simple moving average :

steps :

ENTER data into MINITAB sheet --> STAT --> Time series --> MOving average --> Variable : select data column --> MA length : 5 --> Center the moving averages --> Generate forecasts --> number of forecasts : 1 --> Results :select third option --> ok --> ok

Moving Average for actual

Data actual
Length 12
NMissing 0

Moving Average

Length 5

Accuracy Measures

MAPE 7.3482
MAD 4.8750
MSD 32.3150

Time actual MA Predict Error
1 45 * * *
2 52 * * *
3 48 51.8 * *
4 59 54.2 * *
5 55 56.6 51.8 3.2
6 57 58.6 54.2 2.8
7 64 60.4 56.6 7.4
8 58 62.6 58.6 -0.6
9 68 65.0 60.4 7.6
10 66 67.2 62.6 3.4
11 69 * 65.0 4.0
12 75 * 65.0 10.0

Forecasts

Period Forecast Lower Upper
13 65 53.8583 76.1417

Forecast for period 13 = 65

3. With exponential smoothing :

ENTER data into MINITAB sheet --> STAT --> Time series --> Single exponential smoothing --> Variable : select data column --> Weight to use in smoothing --> Use : 0.23 --> Generate forecasts : 1 --> Results : select third option --> ok --> ok

Single Exponential Smoothing for actual

Data actual
Length 12

Smoothing Constant

Alpha 0.23

Accuracy Measures

MAPE 10.4991
MAD 6.4614
MSD 55.3339

Time actual Smooth Predict Error
1 45 50.9033 52.6667 -7.6667
2 52 51.1556 50.9033 1.0967
3 48 50.4298 51.1556 -3.1556
4 59 52.4009 50.4298 8.5702
5 55 52.9987 52.4009 2.5991
6 57 53.9190 52.9987 4.0013
7 64 56.2376 53.9190 10.0810
8 58 56.6430 56.2376 1.7624
9 68 59.2551 56.6430 11.3570
10 66 60.8064 59.2551 6.7449
11 69 62.6909 60.8064 8.1936
12 75 65.5220 62.6909 12.3091

Forecasts

Period Forecast Lower Upper
13 65.5220 49.6918 81.3523

Forecast for the period 13 = 65.5220

4. With linear regression :

STAT --> Regression --> Regression --> Response : select data variable --> Predictors : period --> Results : select second option--> ok --> ok

Regression Analysis: actual versus period

The regression equation is
actual = 44.4 + 2.34 period

Predictor Coef SE Coef T P
Constant 44.439 2.021 21.98 0.000
period 2.3427 0.2747 8.53 0.000

S = 3.28446 R-Sq = 87.9% R-Sq(adj) = 86.7%

Analysis of Variance

Source DF SS MS F P
Regression 1 784.79 784.79 72.75 0.000
Residual Error 10 107.88 10.79
Total 11 892.67

Now we can forecast value using regression equation :

The regression equation is
actual = 44.4 + 2.34 period

Plug period = 13

actual = 44.4 + 2.34*13 = 74.82

5. With quadratic regression :

In quadratic regression we want one more column.

period   actual   period^2
1   45   1
2   52   4
3   48   9
4   59   16
5   55   25
6   57   36
7   64   49
8   58   64
9   68   81
10   66   100
11   69   121
12   75   144

STAT --> Regression --> Regression --> Response : select data variable --> Predictors : period and period^2 --> Results : select second option--> ok --> ok

Regression Analysis: actual versus period, period^2

The regression equation is
actual = 45.0 + 2.12 period + 0.0170 period^2

Predictor Coef SE Coef T P
Constant 44.955 3.572 12.59 0.000
period 2.122 1.263 1.68 0.127
period^2 0.01698 0.09460 0.18 0.861

S = 3.45594 R-Sq = 88.0% R-Sq(adj) = 85.3%

Analysis of Variance

Source DF SS MS F P
Regression 2 785.18 392.59 32.87 0.000
Residual Error 9 107.49 11.94
Total 11 892.67

Now find forecast value when period = 13

The regression equation is
actual = 45.0 + 2.12 period + 0.0170 period^2

   = 45.0 + 2.12*13 + 0.0170*13^2

= 75.433

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.