Problem 3-16 Obtain estimates of daily relatives for the number of customers at
ID: 360122 • Letter: P
Question
Problem 3-16 Obtain estimates of daily relatives for the number of customers at a restaurant for the evening meal, given the following data.
# served
85
a. Use the centered moving average method. (Hint: Use a seven-day moving average.) (Round your intermediate calculationsand final answers to 4 decimal places.
b. Use the SA method. (Round your intermediate calculationsand final answers to 4 decimal places.)
(Merged part A and B into same table, because you have to find 1's - 7's of both parts. Also, I know there are similar problems/Excel solutions on Chegg already, but it doesn't clearly specify which numbers goes in which blank for the answer.)
Day# served
185
2 81 3 84 4 98 5 134 6 136 7 48 8 86 9 80 10 81 11 93 12 139 13 145 14 46 15 80 16 83 17 83 18 96 19 138 20 148 21 41 22 81 23 78 24 88 25 95 26 141 27 139 28 49Explanation / Answer
a. CMA Method
Centerd moving Average using 7-day moving average is obtained as:
7-day CMAt = (At-3 + At-2 + At-1 + At + At+1 + At+2 + At+3)/7 days
Seasonal Unadjusted Relative = SUt = At/CMAt
Seasonal Adjusted Relative = SAt = (SUt + SUt+7 + SUt+14 + SUt+21)/4 weeks
SA2 = (SU2 + SU9 + SU16 + SU23)/4
CMA Method
Day
Ai
Mi
Number Served
7-day moving average
Ai/Mi
Seasonal relatives
1
85
0.8653
2
81
0.8410
3
84
0.8783
4
98
95.1429
1.0300
0.9993
5
134
95.2857
1.4063
1.4378
6
136
95.1429
1.4294
1.5030
7
48
94.7143
0.5068
0.4722
8
86
94.0000
0.9149
9
80
94.7143
0.8446
10
81
96.0000
0.8438
11
93
95.7143
0.9716
12
139
94.8571
1.4654
13
145
95.2857
1.5217
14
46
95.5714
0.4813
15
80
96.0000
0.8333
16
83
95.8571
0.8659
17
83
96.2857
0.8620
18
96
95.5714
1.0045
19
138
95.7143
1.4418
20
148
95.0000
1.5579
21
41
95.7143
0.4284
22
81
95.5714
0.8475
23
78
96.0000
0.8125
24
88
94.7143
0.9291
25
95
95.8571
0.9911
26
141
27
139
28
49
b. SA method
Simple Average method: Average of demand of day i in each week
Simple average for day i = Si = (Ai + Ai+7 + Ai+14 + Ai+21)/4 weeks
Overall Average = Xi = (Ai)/(7 days x 4 weeks)
Seasonal Relative for ith day = Si/Xi
Simple Average Method
Si
Xi
Si/Mi
Day in a week
Week 1
Week2
Week 3
Week 4
Season Average
Overall average
Seasonal Relatives
1
85.00
86.00
80.00
81.00
83.00
95.57
0.8685
2
81.00
80.00
83.00
78.00
80.50
95.57
0.8423
3
84.00
81.00
83.00
88.00
84.00
95.57
0.8789
4
98.00
93.00
96.00
95.00
95.50
95.57
0.9993
5
134.00
139.00
138.00
141.00
138.00
95.57
1.4439
6
136.00
145.00
148.00
139.00
142.00
95.57
1.4858
7
48.00
46.00
41.00
49.00
46.00
95.57
0.4813
ANS:
Day of week
(part a) x
(b) SA Index
1's
0.8653
0.8685
2's
0.8410
0.8423
3's
0.8783
0.8789
4's
0.9993
0.9993
5's
1.4378
1.4439
6's
1.5030
1.4858
7's
0.4722
0.4813
Copy-paste following table in Excel from start of sheet:
CMA Method
Simple Average Method
Day
Ai
Mi
Si
Xi
Si/Mi
Number Served
7-day moving average
Ai/Mi
Seasonal relatives
Day in week
Season Average
Overall average
Seasonal Relatives
1
85
=AVERAGE(D4,D11,D18,D25)
1
=AVERAGE(B4,B11,B18,B25)
=AVERAGE($B$4:$B$31)
=+H4/I4
2
81
=AVERAGE(D5,D12,D19,D26)
2
=AVERAGE(B5,B12,B19,B26)
=AVERAGE($B$4:$B$31)
=+H5/I5
3
84
=AVERAGE(D6,D13,D20,D27)
3
=AVERAGE(B6,B13,B20,B27)
=AVERAGE($B$4:$B$31)
=+H6/I6
4
98
=AVERAGE(B4:B10)
=+B7/C7
=AVERAGE(D7,D14,D21,D28)
4
=AVERAGE(B7,B14,B21,B28)
=AVERAGE($B$4:$B$31)
=+H7/I7
5
134
=AVERAGE(B5:B11)
=+B8/C8
=AVERAGE(D8,D15,D22,D29)
5
=AVERAGE(B8,B15,B22,B29)
=AVERAGE($B$4:$B$31)
=+H8/I8
6
136
=AVERAGE(B6:B12)
=+B9/C9
=AVERAGE(D9,D16,D23,D30)
6
=AVERAGE(B9,B16,B23,B30)
=AVERAGE($B$4:$B$31)
=+H9/I9
7
48
=AVERAGE(B7:B13)
=+B10/C10
=AVERAGE(D10,D17,D24,D31)
7
=AVERAGE(B10,B17,B24,B31)
=AVERAGE($B$4:$B$31)
=+H10/I10
8
86
=AVERAGE(B8:B14)
=+B11/C11
9
80
=AVERAGE(B9:B15)
=+B12/C12
10
81
=AVERAGE(B10:B16)
=+B13/C13
11
93
=AVERAGE(B11:B17)
=+B14/C14
12
139
=AVERAGE(B12:B18)
=+B15/C15
13
145
=AVERAGE(B13:B19)
=+B16/C16
14
46
=AVERAGE(B14:B20)
=+B17/C17
15
80
=AVERAGE(B15:B21)
=+B18/C18
16
83
=AVERAGE(B16:B22)
=+B19/C19
17
83
=AVERAGE(B17:B23)
=+B20/C20
18
96
=AVERAGE(B18:B24)
=+B21/C21
19
138
=AVERAGE(B19:B25)
=+B22/C22
20
148
=AVERAGE(B20:B26)
=+B23/C23
21
41
=AVERAGE(B21:B27)
=+B24/C24
22
81
=AVERAGE(B22:B28)
=+B25/C25
23
78
=AVERAGE(B23:B29)
=+B26/C26
24
88
=AVERAGE(B24:B30)
=+B27/C27
25
95
=AVERAGE(B25:B31)
=+B28/C28
26
141
27
139
28
49
CMA Method
Day
Ai
Mi
Number Served
7-day moving average
Ai/Mi
Seasonal relatives
1
85
0.8653
2
81
0.8410
3
84
0.8783
4
98
95.1429
1.0300
0.9993
5
134
95.2857
1.4063
1.4378
6
136
95.1429
1.4294
1.5030
7
48
94.7143
0.5068
0.4722
8
86
94.0000
0.9149
9
80
94.7143
0.8446
10
81
96.0000
0.8438
11
93
95.7143
0.9716
12
139
94.8571
1.4654
13
145
95.2857
1.5217
14
46
95.5714
0.4813
15
80
96.0000
0.8333
16
83
95.8571
0.8659
17
83
96.2857
0.8620
18
96
95.5714
1.0045
19
138
95.7143
1.4418
20
148
95.0000
1.5579
21
41
95.7143
0.4284
22
81
95.5714
0.8475
23
78
96.0000
0.8125
24
88
94.7143
0.9291
25
95
95.8571
0.9911
26
141
27
139
28
49
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.