Question

Hudson Marine has been an authorized dealer for C&D marine radios for the past seven years....

Hudson Marine has been an authorized dealer for C&D marine radios for the past seven years. Suppose the quarterly sales values for the seven years of historical data are as follows.

 Year Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total Yearly Sales 1 7 13 9 4 33 2 11 20 16 6 53 3 12 25 25 11 73 4 18 28 26 16 88 5 23 35 27 21 106 6 24 37 31 18 110 7 28 39 37 29 133

a. Compute the centered moving average values (Four-Quarter Moving Average) for this time series (to 3 decimals).

 t Sales Centered Moving Average 1 7 2 13 3 9 4 4 5 11 6 20 7 16 8 6 9 12 10 25 11 25 12 11 13 18 14 28 15 26 16 16 17 23 18 35 19 27 20 21 21 24 22 37 23 31 24 18 25 28 26 39 27 37 28 29

c. Compute the seasonal indexes for the four quarters (to 3 decimals).

 Quarter Adjusted Seasonal Index 1 2 3 4

d. When does Hudson Marine experience the largest seasonal effect?

Hudson Marine experiences the largest seasonal increase in quarter  . The largest seasonal effect is the seasonal decrease in quarter  .

Does this result seem reasonable?

Solve in Excel using the following formulae and steps:

1. Calculate central moving average = (AVERAGE(D26:D29)+AVERAGE(D27:D30))/2
2. Calculate seasonality = D28/E28
3. Calculate unnormalized seasonal index =AVERAGEIF(\$B\$3:\$B\$30,"="&J6,\$F\$3:\$F\$30)
4. Calculate normalized seasonal index = K6*4/\$K\$7
5. Calculate seasonal index =VLOOKUP(B28,\$J\$3:\$L\$6,3)
6. Calcualte seasonally adjusted data = D28/G28

 Year Quarter t Sales Central Moving Average Ratio (Seasonality) Seasonal Index Seasonally Adjusted Sales 1 1 1 7 89.44% 7.83 1 2 2 13 138.26% 9.40 1 3 3 9 8.75 102.86% 114.55% 7.86 1 4 4 4 10.13 39.51% 57.75% 6.93 2 1 5 11 11.88 92.63% 89.44% 12.30 2 2 6 20 13.00 153.85% 138.26% 14.47 2 3 7 16 13.38 119.63% 114.55% 13.97 2 4 8 6 14.13 42.48% 57.75% 10.39 3 1 9 12 15.88 75.59% 89.44% 13.42 3 2 10 25 17.63 141.84% 138.26% 18.08 3 3 11 25 19.00 131.58% 114.55% 21.82 3 4 12 11 20.13 54.66% 57.75% 19.05 4 1 13 18 20.63 87.27% 89.44% 20.13 4 2 14 28 21.38 130.99% 138.26% 20.25 4 3 15 26 22.63 114.92% 114.55% 22.70 4 4 16 16 24.13 66.32% 57.75% 27.71 5 1 17 23 25.13 91.54% 89.44% 25.72 5 2 18 35 25.88 135.27% 138.26% 25.31 5 3 19 27 26.63 101.41% 114.55% 23.57 5 4 20 21 27.00 77.78% 57.75% 36.36 6 1 21 24 27.75 86.49% 89.44% 26.83 6 2 22 37 27.88 132.74% 138.26% 26.76 6 3 23 31 28.00 110.71% 114.55% 27.06 6 4 24 18 28.75 62.61% 57.75% 31.17 7 1 25 28 28.50 98.25% 89.44% 31.31 7 2 26 39 30.63 127.35% 138.26% 28.21 7 3 27 27 114.55% 23.57 7 4 28 39 57.75% 67.53 =(AVERAGE(D26:D29)+AVERAGE(D27:D30))/2 =D28/E28 =VLOOKUP(B28,\$J\$3:\$L\$6,3) =D28/G28
 Quarter Unnormalized Sesonal Index Normalized Seasonal Index 1 88.6% 89.4% 31.7% 2 137.0% 138.3% 48.8% 3 113.5% 114.6% -23.7% 4 57.2% 57.7% -56.8% 396.4% 400.0% =AVERAGEIF(\$B\$3:\$B\$30,"="&J6,\$F\$3:\$F\$30) =K6*4/\$K\$7

a)

 Sales Central Moving Average 7 13 9 8.75 4 10.13 11 11.88 20 13.00 16 13.38 6 14.13 12 15.88 25 17.63 25 19.00 11 20.13 18 20.63 28 21.38 26 22.63 16 24.13 23 25.13 35 25.88 27 26.63 21 27.00 24 27.75 37 27.88 31 28.00 18 28.75 28 28.50 39 30.63 27 39

c)

 Quarter Normalized Seasonal Index 1 89.4% 2 138.3% 3 114.6% 4 57.7%

d)

Hudson Marina experiences largest seasonal increase in quarter 2. The largest seasonal effect is the seasonal decrease in quarter 4.

Yes, result seems reasonable

Earn Coins

Coins can be redeemed for fabulous gifts.