Question

A company has sales of automobiles in the past three years as given in the table below. Using trend and seasonal components, predict the sales for each quarter of year 4.

Year	Quarter	Sales
1	1	71
	2	49
	3	58
	4	78
2	1	68
	2	41
	3	60
	4	81
3	1	62
	2	51
	3	53
	4	72

Answer 1

intercept: =   62.55 --> INTERCEPT(D2:D13,C2:C13)
slope=    -0.08 --> SLOPE(D2:D13,C2:C13)
regression line: sales = 62.55 - 0.08*t

Year	Quarter	t	Actual sales	Trendline = 62.55 - 0.08*t	Seasonal Ratio = actual/trendline	Seasonal Index	Forecast = trend*seasonal index
1	1	1	71	62.47	1.1365	1.078	67.33
	2	2	49	62.39	0.7854	0.757	47.25
	3	3	58	62.31	0.9308	0.919	57.29
	4	4	78	62.23	1.2534	1.244	77.39
2	1	5	68	62.15	1.0941	1.078	66.99
	2	6	41	62.07	0.6605	0.757	47.00
	3	7	60	61.99	0.9679	0.919	56.99
	4	8	81	61.91	1.3084	1.244	76.99
3	1	9	62	61.83	1.0027	1.078	66.64
	2	10	51	61.75	0.8259	0.757	46.76
	3	11	53	61.67	0.8594	0.919	56.70
	4	12	72	61.59	1.1690	1.244	76.59
4	1	13		61.51		1.078	66.30
	2	14		61.43		0.757	46.52
	3	15		61.35		0.919	56.40
	4	16		61.27		1.244	76.20

excel sheet: