Question

The Longmont company in Charlette, North Carolina, asked you to develop quarterly forecasts of combine sales for next year. Combine sales are seasonal, and the data on the quarterly sales for the last four years are as follows:

Quarter	Year 1	Year 2	Year 3	Year 4
1	55	85	178	256
2	37	23	101	193
3	89	130	145	209
4	110	156	167	167

Chad Johnson estimates the total demand for the next year (Year 5) at Longmont. Use the seasonally adjusted exponential smoothing model to develop the forecast for each quarter. Use the appropriate assumptions to intialize the model. Required column headings in Excel are Periods, Years, Quarters, Demand (sales data), Base Value, Seasonality Index, Trend, and Forecast. Use 3 decimal places. Set up your solution using α = 0.2, β= 0.2, γ = 0.8, MAD = 54.845, and Bias = 4.926.

The table will consist of 8 colums and 20 rows for the 20 periods.

Answer 1

Quarter	Year 1	Year 2	Year 3	Year 4	Period Average	SI = Period Average/Overall Average
1	55	85	178	256	143.5	1.0928
2	37	23	101	193	88.5	0.6740
3	89	130	145	209	143.25	1.0909
4	110	156	167	167	150	1.1423
			Overall Average		131.3125

		Actual	SI	Deseasonlise = Actual/SI
Year 1	Q1	55	1.0928	50.329
	Q2	37	0.6740	54.899
	Q3	89	1.0909	81.583
	Q4	110	1.1423	96.296
Year 2	Q1	85	1.0928	77.781
	Q2	23	0.6740	34.126
	Q3	130	1.0909	119.167
	Q4	156	1.1423	136.565
Year 3	Q1	178	1.0928	162.882
	Q2	101	0.6740	149.859
	Q3	145	1.0909	132.917
	Q4	167	1.1423	146.195
Year 4	Q1	256	1.0928	234.258
	Q2	193	0.6740	286.365
	Q3	209	1.0909	191.583
	Q4	167	1.1423	146.195

	X	Deseasonal data,y	X2	Y2	xy
	1	50.329	1	2532.991	50.329
	2	54.899	4	3013.901	109.798
	3	81.583	9	6655.840	244.750
	4	96.296	16	9272.888	385.183
	5	77.781	25	6049.872	388.905
	6	34.126	36	1164.612	204.758
	7	119.167	49	14200.694	834.167
	8	136.565	64	18649.999	1092.520
	9	162.882	81	26530.678	1465.942
	10	149.859	100	22457.859	1498.595
	11	132.917	121	17666.840	1462.083
	12	146.195	144	21372.856	1754.335
	13	234.258	169	54876.735	3045.352
	14	286.365	196	82004.978	4009.112
	15	191.583	225	36704.174	2873.750
	16	146.195	256	21372.856	2339.113
Total	136	2101	1496	344527.8	21758.69
Average	8.5	131.3125	93.5	21532.99	1359.918

Y = mx+b

m = ((16*21758.69) - (136*2101))/((16*1496)-(136*136)) = 11.47115

b = (2101 - (11.47115*136))/16 = 33.808

y = 11.471x + 33.808

		alpha =	0.20
		beta =	0.20
period	month	sales	level	trend	forecast	error	ABS(error)	Squared Error	Percent Error
1	Q1	50.329	50.33	0.00	-	-	-	-	-
2	Q2	54.899	50.33	0.00	50.33	4.57	4.57	20.89	0.08
3	Q3	81.583	51.24	0.18	51.43	30.16	30.16	909.48	0.37
4	Q4	96.296	57.46	1.39	58.85	37.45	37.45	1402.47	0.39
5	Q1	77.781	66.34	2.89	69.22	8.56	8.56	73.23	0.11
6	Q2	34.126	70.93	3.23	74.16	-40.04	40.04	1603.03	1.17
7	Q3	119.167	66.16	1.63	67.78	51.38	51.38	2640.12	0.43
8	Q4	136.565	78.06	3.68	81.74	54.82	54.82	3005.33	0.40
9	Q1	162.882	92.71	5.88	98.58	64.30	64.30	4134.24	0.39
10	Q2	149.859	111.44	8.45	119.89	29.97	29.97	898.06	0.20
11	Q3	132.917	125.89	9.65	135.53	-2.62	2.62	6.84	0.02
12	Q4	146.195	135.01	9.54	144.55	1.64	1.64	2.70	0.01
13	Q1	234.258	144.88	9.61	154.49	79.77	79.77	6363.32	0.34
14	Q2	286.365	170.44	12.80	183.24	103.13	103.13	10634.77	0.36
15	Q3	191.583	203.87	16.92	220.79	-29.21	29.21	852.95	0.15
16	Q4	146.195	214.95	15.76	230.70	-84.51	84.51	7141.67	0.58
						20.63	29.59	1336.04	0.33
						BIAS	MAD	MSE	MAPE

y = 11.471x + 33.808

		x	Forecast	SI	Reseasonlise = Forecast*SI
Year 5	Q1	17	228.817	1.093	250.054
	Q2	18	240.288	0.674	161.946
	Q3	19	251.760	1.091	274.647
	Q4	20	263.231	1.142	300.692