Problem 8-12 (Algorithmic)

Many forecasting models use parameters that are estimated usingnonlinear optimization. The basic exponential smoothing model forforecasting sales is

F_{t + 1} = ?Y_t + (1 –?)F_t

where

Ft + 1	=	forecast of sales for period t + 1
Yt	=	actual value of sales for period t
Ft	=	forecast of sales for period t
?	=	smoothing constant 0 ? ? ? 1

This model is used recursively; the forecast for time periodt + 1 is based on the forecast for period t,F_t; the observed value of sales in periodt, Y_t and the smoothing parameter ?.The use of this model to forecast sales for 12 months isillustrated in the table below with the smoothing constant ? = 0.3.The forecast errors, Y_t -F_t, are calculated in the fourth column. Thevalue of ? is often chosen by minimizing the sum of squaredforecast errors, commonly referred to as the mean squared error(MSE). The last column of Table shows the square of the forecasterror and the sum of squared forecast errors.

EXPONENTAL SMOOTHING MODEL FOR ?=0.3
Week ()	Observed Value ()	Forecast	Forecast Error ()			Squared Forecast Error
1	16	16.00		0.00			0.00
2	20	16.00		4.00			16.00
3	18	17.20		0.80			0.64
4	24	17.44		6.56			43.03
5	21	19.41		1.59			2.53
6	16	19.89		-3.89			15.13
7	19	18.72		0.28			0.08
8	21	18.80		2.20			4.84
9	24	19.46		4.54			20.61
10	22	20.82		1.18			1.39
11	12	21.17		-9.17			84.09
12	19	18.42		0.58		0.34
SUM=188.68

In using exponential smoothing models, we try to choose thevalue of ? that provides the best forecasts. Build an Excel Solveror LINGO optimization model that will find the smoothing parameter,?, that minimizes the sum of squared forecast errors. You may findit easiest to put table into an Excel spreadsheet and then useSolver to find the optimal value of ?. If required, round youranswer for ? to three decimal places and the answer for theresulting sum of squared errors to two decimal places.

The optimal value of ? is  and the resulting sum ofsquared errors is .