Question

1. Demand data for thirty days have been provided, along with computed weighted moving average forecasts for days six to thirty, using weights of 0.5, 0.3, 0.1, 0.05, and 0.05, with the largest weight on most recent demand. Please compute the Mean Suared Error (MSE) for the 25 forecasts, and then use SOLVER to find the five weights that will minimize the MSE. Note that your weights must sum to one, and it is OK for some of the weights to be zero.

Report your new (optimized) value for the MSE, to two decimals.

		0.05
		0.05
		0.1
		0.3
		0.5
Week (t)	Demand (Dt)	WMA Forecast (Ft)
1	686
2	700
3	692
4	695
5	681
6	699	687.50
7	675	692.90
8	683	684.65
9	681	682.70
10	693	681.90
11	702	687.80
12	697	694.90
13	704	696.60
14	707	700.00
15	698	704.15
16	695	701.45
17	683	697.65
18	688	690.35
19	694	688.65
20	679	691.35
21	688	685.40
22	670	685.65
23	681	678.40
24	679	678.95
25	673	679.15
26	688	676.20
27	670	681.35
28	672	676.70
29	666	673.40
30	673	669.65

Answer 1

Please refer the images

The pink part in excel sheet ...is the question

Final answer: MSE = 72.86 and New weights = 0.084, 0, 0, 0.554 ,0.350 (Cell I2 to I6) GREEN

Please see the blue cell and the formula tab to know the formula of the blue cell.

Similarly, forecast value for all weeks.

Look at the blue cell and the formula bar

Use data solve from DATA and fill the dialog box like given below.

Fill the details and click run, use GRG non linear and untick the unconstrained variable non-negative box as we have already put the constraints.

Now you get all the values required i.e. the weights and the minimum MSE.