Question

Suppose that the following are the quarterly sales data for the past 7 years.

1.Construct a time series plot and develop linear trend equation using the original data (with seasonal components).

2.Calculate 4-quarter moving average values for this time series (column E).

3.Calculate centered moving average values and seasonal indexes (column F and G).

4.Calculate seasonal indexes (column J) for the four quarters.

5.Copy seasonal indexes (column J) to column O, and calculate deseasonalized number sold (column P).

6.Construct a time series plot and develop linear trend equation using the deseasonalized data (without seasonal components).

Year	Number Sold
1	35
2	50
3	75
4	90
5	105
6	110
7	130

Year	Quarter	Number Sold
1	1	6
	2	15
	3	10
	4	4
2	5	10
	6	18
	7	15
	8	7
3	9	14
	10	26
	11	23
	12	12
4	13	19
	14	28
	15	25
	16	18
5	13	22
	14	34
	15	28
	16	21
6	13	24
	14	36
	15	30
	16	20
7	13	28
	14	40
	15	35
	16	27

Answer 1

Computations

Result

Yr.	Qtr	Number Sold	Trendline to original data	MA-4	CMA	Seasonal indices	Qtr	Seasonal indices	Seasonal indices (normalized)	Seasonal indices (normalized)	Deseasonalized data	Trendline to deseasonalized data
1	1	6	8.66				1	0.89	0.90	0.90	6.67	7.39
	2	15	9.59				2	1.35	1.36	1.36	11.02	8.44
	3	10	10.53		9.25	1.081	3	1.11	1.12	1.12	8.94	9.50
	4	4	11.46		10.13	0.395	4	0.61	0.62	0.62	6.44	10.55
2	5	10	12.39	8.75	11.13	0.899	1			0.90	11.12	11.60
	6	18	13.32	9.75	12.13	1.485	2			1.36	13.22	12.66
	7	15	14.26	10.50	13.00	1.154	3			1.12	13.41	13.71
	8	7	15.19	11.75	14.50	0.483	4			0.62	11.27	14.77
3	9	14	16.12	12.50	16.50	0.848	1			0.90	15.57	15.82
	10	26	17.05	13.50	18.13	1.434	2			1.36	19.09	16.88
	11	23	17.99	15.50	19.38	1.187	3			1.12	20.57	17.93
	12	12	18.92	17.50	20.25	0.593	4			0.62	19.33	18.99
4	13	19	19.85	18.75	20.75	0.916	1			0.90	21.13	20.04
	14	28	20.78	20.00	21.75	1.287	2			1.36	20.56	21.10
	15	25	21.72	20.50	22.88	1.093	3			1.12	22.35	22.15
	16	18	22.65	21.00	24.00	0.750	4			0.62	28.99	23.21
5	17	22	23.58	22.50	25.13	0.876	1			0.90	24.47	24.26
	18	34	24.51	23.25	25.88	1.314	2			1.36	24.97	25.32
	19	28	25.45	24.75	26.50	1.057	3			1.12	25.04	26.37
	20	21	26.38	25.50	27.00	0.778	4			0.62	33.82	27.42
6	21	24	27.31	26.25	27.50	0.873	1			0.90	26.69	28.48
	22	36	28.24	26.75	27.63	1.303	2			1.36	26.44	29.53
	23	30	29.18	27.25	28.00	1.071	3			1.12	26.83	30.59
	24	20	30.11	27.75	29.00	0.690	4			0.62	32.21	31.64
7	25	28	31.04	27.50	30.13	0.929	1			0.90	31.14	32.70
	26	40	31.97	28.50	31.63	1.265	2			1.36	29.38	33.75
	27	35	32.91	29.50			3			1.12	31.30	34.81
	28	27	33.84	30.75			4			0.62	43.49	35.86

Graph