Question

Marie Bain is the production manager at a company that manufactures hot water heaters. Marie needs a demand forecast for the next few years to help decide whether to add new production capacity. The company's sales history (in thousands of units) is shown in the table below. Construct a forecast for period 7 in two ways. First, use exponential smoothing where the initial forecast for period 1 was 11 units and the smoothing constant is α = 0.5. For the second forecast, use linear regression. Which forecasting method is likely to give a better forecast for period 7 and why? Please show all work in excel!

Period	Actual	Forecast
1	12	11.00
2	16
3	14
4	17
5	16
6	18

Answer 1

	Exponential Smoothing:
	Ft+1=Ft+alpha*(At-Ft)
	Ft+1=alpha*At+(1-alpha))*Ft
	Ft+1=Forecast in period (t+1)
	Ft=Forecast in period t
	At=Actual Value in period t
	alpha=smoothing constant=0.5
	Ft+1=alpha*At+(1-alpha))*Ft
	Ft+1=0.5*At+(1-0.5)*Ft				0
	Ft+1=0.5*At+0.5*Ft	At	Ft
	Period	Actual	Forecast	Forecast calculation
	1	12	11.00
	2	16	11.5	0.5*12+0.5*11
	3	14	13.75	0.5*16+0.5*11.5
	4	17	13.875	0.5*14+0.5*13.75
	5	16	15.4375	0.5*17+0.5*13.875
	6	18	15.71875	0.5*16+0.5*15.4375
	7		16.85938	0.5*18+0.5*15.71875
	Forecast for period 7	16.85938
	Linear Regression
	X Variables=Period
	Y Variables=Demand
	Using Regression Tool of Data Analysis of excel
	Input Y range=Demands
	Input X range=Periods
	SUMMARY OUTPUT
	Regression Statistics
	Multiple R	0.813638
	R Square	0.662006
	Adjusted R Square	0.577508
	Standard Error	1.409154
	Observations	6
	ANOVA
		df	SS	MS	F	Significance F
	Regression	1	15.55714	15.55714286	7.834532	0.04886
	Residual	4	7.942857	1.985714286
	Total	5	23.5
		Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
	Intercept	12.2	1.311851	9.29983844	0.000744	8.557718	15.84228	8.557718	15.84228
	X Variable 1	0.942857	0.336852	2.799023468	0.04886	0.007606	1.878109	0.007606	1.878109
	Regression Equation:
	Y=12.2+0.942857*X
	Period =X=7
	Forecast Demand=Y=12.2+0.942857*7=	18.8
	Forecast for Period 7	18.8
	Regression method gives a better forecast
	The error between forecast and actual is minimized under this method