HU Statistics Excel Spreadsheet Worksheet

Problem 1WEEK VALUE
1
18
2
13
3
16
4
11
5
17
6
14
FORECAST
FORECAST ERROR
ABSOLUTE
VALUE OF
FORECST
ERROR
18
13
16
11
17
-5
3
-5
6
-3
TOTALS
5
3
5
6
3
22
a.
b.
c.
d.
SQUARED
FORECAST
ERROR
ABSOLUTE
VALUE OF
PERCENTAGE PERCENTAGE
ERROR
ERROR
25
9
25
36
9
104
-38.46%
18.75%
-45.45%
35.29%
-21.43%
-51.30%
MEAN ABSOLUTE ERROR
MEAN SQUARED ERROR
MEAN ABSOLUTE % ERROR
FORECAST for WEEK 7 is F7=Y7=
4.4
20.8
31.88%
14
38.46%
18.75%
45.45%
35.29%
21.43%
159.39%
Problem 2
WEEK
1
2
3
4
5
6
VALUE
18
13
16
11
17
14
FORECAST
FORECAST ERROR
ABSOLUTE
VALUE OF
FORECST
ERROR
18.00
15.50
15.67
14.50
15.00
-5.00
0.50
-4.67
2.50
-1.00
TOTALS
5.00
0.50
4.67
2.50
1.00
13.67
a.
b.
c.
d.
SQUARED
FORECAST
ERROR
PERCENTAGE
ERROR
ABSOLUTE
VALUE OF
PERCENTAGE
ERROR
25.00
0.25
21.78
6.25
1.00
54.28
-38.46%
3.13%
-42.42%
14.71%
-7.14%
-70.20%
38.46%
3.13%
42.42%
14.71%
7.14%
105.86%
MEAN ABSOLUTE ERROR
MEAN SQUARED ERROR
MEAN ABSOLUTE % ERROR
FORECAST for WEEK 7 is F7=Y7=
Y1+Y2+Y3+Y4+Y5+Y6)/6=
2.73
10.86
21.17%
14.83
Problem 13
13.b. in the 13th edition: the first two numbers in
columns 4 and 5 should be shifted one column to the
right so that the Average Forecast 240 for month 2 is
240 and 262 for month 3.
e first two numbers in
shifted one column to the
orecast 240 for month 2 is
MSE(3-Month) = 17,988.52/9 = 1998.72
MSE(a = 0.2) = 27,818.49/11 = 2528.95
Based on the above MSE values, the 3-month moving average appears better.
However, exponential smoothing was penalized by including month 2, which
was difficult for any method to forecast. Using only the errors for months 4 to
12, the MSE for exponential smoothing is as follows:
12, the MSE for exponential smoothing is as follows:
MSE(a = 0.2) = 14,694.49/9 = 1632.72
Thus, exponential smoothing was better considering months 4 to 12.
c. Using exponential smoothing,
appears better.
month 2, which
for months 4 to
Problem 14
20.b. in the 12th and 13th editions: the y
intercept is 4.7167 and should be designated
as bo; the slope is 1.4567 and should be
designated b1
b. The regression estimates for the slope and y-intercept are as follows:
ns: the y
e designated
ould be
MSE = 0.3808
Problem 26
There appears to be a seasonal pattern in the data and perhaps a moderate upward linear trend.
b. After putting the data into the following format:
we can use the LINEST function to find the regression model:
Value = 2491.67 – 711.67 Qtr1 – 1511.67 Qtr2 + 326.67 Qtr3
c. The quarterly forecasts for next year are as follows:
Quarter 1 forecast = 2491.67 – 711.67(1) – 1511.67(0) + 326.67(0) = 1780.00
Quarter 2 forecast = 2491.67 – 711.67(0) – 1511.67(1) + 326.67(0) = 980.00
Quarter 3 forecast = 2491.67 – 711.67(0) – 1511.67(0) + 326.67(1) = 2818.33
Quarter 3 forecast = 2491.67 – 711.67(0) – 1511.67(0) + 326.67(1) = 2818.33
Quarter 4 forecast = 2491.67 – 711.67(0) – 1511.67(0) + 326.67(0) = 2491.67
d. After putting the data into the following format:
we can use the LINEST function to find the regression model:
Value = 2306.67 – 642.29 Qtr1 – 1465.42 Qtr2 + 349.79 Qtr3 + 23.13t
The quarterly forecasts for next year are as follows:
Quarter 1 forecast = 2306.67 – 642.29(1) – 1465.42(0) + 349.79(0) + 23.13(13) = 1965.00
Quarter 2 forecast = 2306.67 – 642.29(0) – 1465.42(1) + 349.79(0) + 23.13(14) = 1165.00
Quarter 3 forecast = 2306.67 – 642.29(0) – 1465.42(0) + 349.79(1) + 23.13(15) = 2011.33
Quarter 4 forecast = 2306.67 – 642.29(0) – 1465.42(0) + 349.79(0) + 23.13(16) = 2676.67
e upward linear trend.
3) = 1965.00
4) = 1165.00
2011.33 3003.33
6) = 2676.67
Problem 1
Problem 2
Problem 6
Problem 7
Problem 10
Problem 13
Problem 17
Problem 24