The number of girls who attend a summer basketball camp has been recorded for th
ID: 3261461 • Letter: T
Question
The number of girls who attend a summer basketball camp has been recorded for the 10 years the camp has been offered. The attendance numbers are 17, 20, 25, 32, 34, 40, 43, 47, 49 and 53.
1. Use a 3-year moving average to smooth the time series and calculate the associated mean absolute deviation (MAD) and mean squared error (MSE). Forecast attendance for year 11.
2. Use exponential smoothing with a constant of 0.6 to forecast attendance for year 11.
3. For this problem, does the exponential smoothing method provide a better forecast than the 3-year moving average method? Explain.
Explanation / Answer
Attendance
3-year MA
Forecast Error
Absolute Error
Squared Error
Exponential Smoothing
Forecast Error
Squared Error
Absolute Error
17
#N/A
20
17
-3
9
3
25
18.8
-6.2
38.44
6.2
32
20.66666667
11.33333333
11.33333333
128.4444444
22.52
-9.48
89.8704
9.48
34
25.66666667
8.333333333
8.333333333
69.44444444
28.208
-5.792
33.547264
5.792
40
30.33333333
9.666666667
9.666666667
93.44444444
31.6832
-8.3168
69.16916224
8.3168
43
35.33333333
7.666666667
7.666666667
58.77777778
36.67328
-6.32672
40.02738596
6.32672
47
39
8
8
64
40.469312
-6.530688
42.64988575
6.530688
49
43.33333333
5.666666667
5.666666667
32.11111111
44.3877248
-4.6122752
21.27308252
4.6122752
53
46.33333333
6.666666667
6.666666667
44.44444444
47.15508992
-5.84491008
34.16297384
5.84491008
49.66666667
50.66203597
MAD
8.19047619
MAD
6.233710364
MSE
70.0952381
MSE
42.0155727
1.
Steps for 3-year moving average :
Step 1: Click the “Data” tab and then click “Data Analysis.”
Step 2: Click “Moving average” and then click “OK.”
Step 3: Click the “Input Range” box and then select the data. If we include column headers, we make sure we check the Labels in first Row box.
Step 4: We type an interval into the box. An interval is how many prior points we want Excel to use to calculate the moving average. Here, it is “3”.
Step 5: Click in the “Output Range” box and select an area on the worksheet where we want the result to appear. Or, click the “New worksheet” button.
Step 6: Check the “Chart Output” box if we want to see a chart of our data set .
Step 7: Press “OK.” Excel will return the results in the area we specified in Step 6.
MAD (mean absolute deviation) = Sum of the absolute values of individual errors divided by the number of periods of data
MSE (mean squared error) = Average of the squared differences between the forecast and observed values.
Here, the forecasted value for year 11 = 49.66666667
MAD = 8.19047619 and MSE = 70.0952381.
2. Steps for Exponential Smoothing :
Here, the forecasted value for year 11 = 50.66203597
MAD = 6.233710364 and MSE = 42.0155727.
.
3. Exponential smoothing method provides a better forecast than the 3-year moving average method since the MSE and MAD are both less for the former than the latter. Since MAD and MSE are measures of accuracy, the lesser their value, better is the forecast.
Attendance
3-year MA
Forecast Error
Absolute Error
Squared Error
Exponential Smoothing
Forecast Error
Squared Error
Absolute Error
17
#N/A
20
17
-3
9
3
25
18.8
-6.2
38.44
6.2
32
20.66666667
11.33333333
11.33333333
128.4444444
22.52
-9.48
89.8704
9.48
34
25.66666667
8.333333333
8.333333333
69.44444444
28.208
-5.792
33.547264
5.792
40
30.33333333
9.666666667
9.666666667
93.44444444
31.6832
-8.3168
69.16916224
8.3168
43
35.33333333
7.666666667
7.666666667
58.77777778
36.67328
-6.32672
40.02738596
6.32672
47
39
8
8
64
40.469312
-6.530688
42.64988575
6.530688
49
43.33333333
5.666666667
5.666666667
32.11111111
44.3877248
-4.6122752
21.27308252
4.6122752
53
46.33333333
6.666666667
6.666666667
44.44444444
47.15508992
-5.84491008
34.16297384
5.84491008
49.66666667
50.66203597
MAD
8.19047619
MAD
6.233710364
MSE
70.0952381
MSE
42.0155727
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.