Question

Many forecasting models use parameters that are estimated using nonlinear optimization. For instance, the basic exponential smoothing model for forecasting sales is

     ŷ_{(t + 1)} = αy_t + (1 - α) ŷ_t

     where

     ŷ_{t + 1} = forecast of sales for period t + 1

     y_t = actual sales for period t

     ŷ_t = forecast of sales for period t

     α = smoothing constant, 0 ≤ α ≤ 1

Click on the datafile logo to reference the data.

This model is used recursively; the forecast for time period t + 1 is based on the forecast for period t, ŷ_t, the observed value of sales in period t, y_t, and the smoothing parameter α. The use of this model to forecast sales for 12 months is illustrated in the table below with the smoothing constant α = 0.3. The forecast errors, y_t - ŷ_t, are calculated in the fourth column. The value of α is often chosen by minimizing the sum of squared forecast errors. The last column of the table below shows the square of the forecast error and the sum of squared forecast errors. In using exponential smoothing models, one tries to choose the value of α that provides the best forecasts. The file ExpSmooth contains the observed data shown below.

Week	Observed Value	Forecast	Forecast Error		Squared Forecast Error
(t)	yt	ŷt	yt - ŷt		(yt - ŷt)2
1	17	17.00	0.00		0.00
2	21	17.00	4.00		16.00
3	19	18.20	0.80		0.64
4	23	18.44	4.56		20.79
5	18	19.81	-1.81		3.27
6	16	19.27	-3.27		10.66
7	20	18.29	1.71		2.94
8	18	18.80	-0.80		0.64
9	22	18.56	3.44		11.83
10	20	19.59	0.41		0.17
11	15	19.71	-4.71		22.33
12	22	18.30	3.70		13.69
Sum =					102.86

Construct this table using the formula above. Note that we set the forecast in period 1 to the observed in period 1 to get started (ŷ₁ = y₁ = 17), then the formula above for ŷ _{t + 1} is used starting in period 2. Make sure to have a single cell corresponding to α in your spreadsheet model. After confirming the values in the table below with α = 0.3, try different values of α to see if you can get a smaller sum of squared forecast errors. Use Excel Solver to find the value of α that minimizes the sum of squared forecast errors.

If required, round your answer to four decimal places. Do not round intermediate calculations.

α =

Answer 1

Formulas

Using Excel

data -> data analysis -> solver

alpha = 0.1744

Please give me a thumbs-up if this helps you out. Thank you! :)