Question

A tourist center is open on weekends (Friday, Saturday, and Sunday). The owner-manager hopes to improve scheduling of part-time employees by determining seasonal relatives for each of these days. Data on recent traffic at the center have been tabulated and are shown in the following table:

Week 1 2 3 4 5 6

Friday 138 148 148 148 153 158

Saturday 248 248 258 258 273 273

Sunday 158 158 168 168 168 178

a. Develop seasonal relatives for the shop using the centered moving average method. (Round your intermediate calculations and final answers to 2 decimal places.)

X⎯⎯⎯ X ¯

Friday

Saturday

Sunday

b. Develop seasonal relatives for the shop using the SA method. (Round your intermediate calculations and final answers to 4 decimal places.)

SA Index

Friday

Saturday

Sunday

Answer 1

a) Seasonal relatives for the shop using the centered moving average method.

A D E Unadjusted Adjusted Seasonal Number Dt/CMA Day Week 1 Day CMA Served (Dt) relatives Seasonal Friday Saturday Sunday Total Friday Saturday 2 138 0.79 0.79 248 181.33 1.37 1.36 1.35 Sunday 4 158 184.67 0.86 0.86 0.86 Friday 148 184.67 0.80 3.00 3.00 Saturday 6 248 184.67 1.34 Sunday 7 158 184.67 0.86 Friday 3 148 188.00 0.79 Saturday 9 258 191.33 1.35 Sunday 10 168 191.33 0.88 Friday 11 4 148 191.33 0.77 Saturday 12 258 191.33 1.35 Sunday Friday 13 168 193.00 0.87 14 5 153 198.00 0.77 Saturday 15 273 198.00 1.38 Sunday Friday 16 168 199.67 0.84 17 6 158 199.67 0.79 Saturday 18 273 203.00 1.34 Sunday 19 178 20 21 22 LO

EXCEL FORMULAS:

Week	Day	Data (Dt)	CMA	Dt/CMA		Day	Unadjusted Seasonal	Adjusted Seasonal relatives
1	Friday	138				Friday	=AVERAGEIF($B$2:$B$19,G2,$E$2:$E$19)	=H2/AVERAGE($H$2:$H$4)
	Saturday	248	=AVERAGE(C2:C4)	=C3/D3		Saturday	=AVERAGEIF($B$2:$B$19,G3,$E$2:$E$19)	=H3/AVERAGE($H$2:$H$4)
	Sunday	158	=AVERAGE(C3:C5)	=C4/D4		Sunday	=AVERAGEIF($B$2:$B$19,G4,$E$2:$E$19)	=H4/AVERAGE($H$2:$H$4)
2	Friday	148	=AVERAGE(C4:C6)	=C5/D5		Total	=SUM(H2:H4)	=SUM(I2:I4)
	Saturday	248	=AVERAGE(C5:C7)	=C6/D6
	Sunday	158	=AVERAGE(C6:C8)	=C7/D7
3	Friday	148	=AVERAGE(C7:C9)	=C8/D8
	Saturday	258	=AVERAGE(C8:C10)	=C9/D9
	Sunday	168	=AVERAGE(C9:C11)	=C10/D10
4	Friday	148	=AVERAGE(C10:C12)	=C11/D11
	Saturday	258	=AVERAGE(C11:C13)	=C12/D12
	Sunday	168	=AVERAGE(C12:C14)	=C13/D13
5	Friday	153	=AVERAGE(C13:C15)	=C14/D14
	Saturday	273	=AVERAGE(C14:C16)	=C15/D15
	Sunday	168	=AVERAGE(C15:C17)	=C16/D16
6	Friday	158	=AVERAGE(C16:C18)	=C17/D17
	Saturday	273	=AVERAGE(C17:C19)	=C18/D18
	Sunday	178

b) Seasonal relatives for the shop using the SA method.

A B C E G H Seasonal Seasonal Week 5 1 Day Week 1 Week 2 Week 3 Week 4 Week 6 Average Relative 2 Friday 3 Saturday 4 Sunday 138 148 148 148 153 158 148.8333 0.7767 248 248 258 258 273 273 259.6667 1.3552 158 158 168 168 168 178 166.3333 0.8681 Overall Average 191.6111 1.0000 6 7 8 10 F. ш LO

EXCEL FORMULAS:

Friday	138	148	148	148	153	158	=AVERAGE(B2:G2)	=H2/$H$5
Saturday	248	248	258	258	273	273	=AVERAGE(B3:G3)	=H3/$H$5
Sunday	158	158	168	168	168	178	=AVERAGE(B4:G4)	=H4/$H$5
Overall Average =							=AVERAGE(H2:H4)	=AVERAGE(I2:I4)