5. Use the Chapter 11, Problem 11 Excel spreadsheet for this problem. ABC Bakery
ID: 367929 • Letter: 5
Question
5. Use the Chapter 11, Problem 11 Excel spreadsheet for this problem. ABC Bakery sells the following number of chocolate pies in a one-week period. ________________________________________________Pies Sold________
Monday 50
Tuesday 60
Wednesday 75
Thursday 50
Friday 90
Saturday 125
a. Prepare a forecast of sales for each day, starting with F1 = 60 and a=.1. b. What is the MAD and Tracking Signal for the data above in each period? c. Using the tracking signal criteria from the text, are the tracking signals within tolerance? d. Recompute parts a and b using a=.2, .3 and .4. Using the Cumulative Error as an indicator, which a gives the best forecast?
Explanation / Answer
Part a.
Exponential Smoothing:
Ft+1 = ()x(At) + (1 – )x(Ft)
0.1
Day
Period (t)
Actual (At)
Forecast
F (t)
Forecast Error
e = A-F
Absolute Deviation
d = |A-F|
Mon
1
50
60.0000
-10.0000
10.0000
Tue
2
60
59.0000
1.0000
1.0000
Wed
3
75
59.1000
15.9000
15.9000
Thus
4
50
60.6900
-10.6900
10.6900
Fri
5
90
59.6210
30.3790
30.3790
Sat
6
125
62.6589
62.3411
62.3411
88.9301
130.3101
Cumulative Error = total forecast error = 88.9301
b.
Mean Absolute Deviation = Total Absolute Deviation/n = 130.3101/6 = 21.7084
Tracking signal = Total of forecast error/MAD = 88.9301/21.7184 = 4.0947
Part c.
Tolerances are not provided
Part d.
0.2
Day
Period
Actual
Forecast
Forecast Error
e = A-F
Absolute Deviation
d = |A-F|
Mon
1
50
60.0000
-10.0000
10.0000
Tue
2
60
58.0000
2.0000
2.0000
Wed
3
75
58.4000
16.6000
16.6000
Thus
4
50
61.7200
-11.7200
11.7200
Fri
5
90
59.3760
30.6240
30.6240
Sat
6
125
65.5008
59.4992
59.4992
87.0032
130.4432
Cumulative Error for = 0.2 = 87.0032
0.3
Day
Period
Actual
Forecast
Forecast Error
e = A-F
Absolute Deviation
d = |A-F|
Mon
1
50
60.0000
-10.0000
10.0000
Tue
2
60
57.0000
3.0000
3.0000
Wed
3
75
57.9000
17.1000
17.1000
Thus
4
50
63.0300
-13.0300
13.0300
Fri
5
90
59.1210
30.8790
30.8790
Sat
6
125
68.3847
56.6153
56.6153
84.5643
130.6243
Cumulative Error for = 0.3 = 84.5643
0.4
Day
Period
Actual
Forecast
Forecast Error
e = A-F
Absolute Deviation
d = |A-F|
Mon
1
50
60.0000
-10.0000
10.0000
Tue
2
60
56.0000
4.0000
4.0000
Wed
3
75
57.6000
17.4000
17.4000
Thus
4
50
64.5600
-14.5600
14.5600
Fri
5
90
58.7360
31.2640
31.2640
Sat
6
125
71.2416
53.7584
53.7584
81.8624
130.9824
Cumulative Error for = 0.4 = 81.8624
Cumulative error by considering = 0.4 gives lowest cumulative error, thus select the forecast based on exponential smoothing of 0.4.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.