Question

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?

Answer 1

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