Question

The following time series shows the sales of a particular product over the past 12 months. can be found in the below excel file.

https://drive.google.com/file/d/1gIpr0IBYUGgCIRSO7g3mqZ84F89vJU_P/view?usp=sharing

1. Using the three-month moving average, what is the Mean Squared Error (MSE)?

(Round your answer to 2 decimal places. Negative values should be indicated by a minus sign.)

2.  Using the three-month moving average, what is the forecast for month 13 ?

(Round your answer to 2 decimal places. Negative values should be indicated by a minus sign.)

3. Using the exponential smoothing approach for ? .3 or .7, what is the Mean Squared Error (MSE)?  

(Round your answer to 2 decimal places. Negative values should be indicated by a minus sign.)

4. Using the exponential smoothing approach for , .3 or .7, what is the forecast for month 13?

(Round your answer to 2 decimal places. Negative values should be indicated by a minus sign.)

5. Using the exponential smoothing approach for , .5 or .5, what is the Mean Squared Error (MSE)?

(Round your answer to 2 decimal places. Negative values should be indicated by a minus sign.)

6. Using the exponential smoothing approach for , .5 or .5, what is the forecast for month 13?

(Round your answer to 2 decimal places. Negative values should be indicated by a minus sign.)

7. Does a smoother constant of .3 or .5 provide more accurate forecasts based on MSE ?

	.3
	.5

Answer 1

Given Following data:

Week Shipments on Time

1 105

2 135

3 120

4 105

5 90

6 120

7 145

8 140

9 100

10 80

11 100

12 110

1.) Please find the solution in below table:

Week	Shipments on Time	3 Month Moving Average	Error	Squared(Error)
1	105
2	135
3	120
4	105	120.00	-15.00	225.00
5	90	120.00	-30.00	900.00		Mean Squared Error
6	120	105.00	15.00	225.00		811.41975308642
7	145	105.00	40.00	1,600.00
8	140	118.33	21.67	469.44		Root Mean Squared Error(RMSE)
9	100	135.00	-35.00	1,225.00		28.4854305406539
10	80	128.33	-48.33	2,336.11
11	100	106.67	-6.67	44.44
12	110	93.33	16.67	277.78

2.) Please find forecast for month 13 in below table:

Week	Shipments on Time	3 Month MA
1	105
2	135
3	120
4	105	120.00
5	90	120.00
6	120	105.00
7	145	105.00
8	140	118.33
9	100	135.00
10	80	128.33
11	100	106.67
12	110	93.33
13		96.67

3.) Please find Exponenetial smoothening with alpha = 0.3 in below table:

Week	Shipments on Time	Exponenetial smoothening	Error	Squared Error		?
1	105	105.00	0.00	0.00		0.30
2	135	114.00	21.00	441.00
3	120	115.80	4.20	17.64
4	105	112.56	-7.56	57.15
5	90	105.79	-15.79	249.39		Mean Squared Error(MSE)
6	120	110.05	9.95	98.91		229.20
7	145	120.54	24.46	598.39
8	140	126.38	13.62	185.60		Root Mean Squared Error(RMSE)
9	100	118.46	-18.46	340.91		15.14
10	80	106.92	-26.92	724.93
11	100	104.85	-4.85	23.50
12	110	106.39	3.61	13.01

4.) Using alpha = 0.3, forecast for 13 month is given below:

Week	Shipments on Time	Exponenetial smoothening
1	105	105.00
2	135	114.00
3	120	115.80
4	105	112.56
5	90	105.79
6	120	110.05
7	145	120.54
8	140	126.38
9	100	118.46
10	80	106.92
11	100	104.85
12	110	106.39
13		107.48

5.) With alpha = 0.5, Please find exponential smoothening below:

Week	Shipments on Time	Exponenetial smoothening	Error	Squared Error		?
1	105	105.00	0.00	0.00		0.50
2	135	120.00	15.00	225.00
3	120	120.00	0.00	0.00
4	105	112.50	-7.50	56.25
5	90	101.25	-11.25	126.56		Mean Squared Error
6	120	110.63	9.38	87.89		123.88
7	145	127.81	17.19	295.41
8	140	133.91	6.09	37.13		Root Mean Squared Error
9	100	116.95	-16.95	287.41		11.13
10	80	98.48	-18.48	341.38
11	100	99.24	0.76	0.58
12	110	104.62	5.38	28.95

5.) Forecast for month 13 is given below:

Week	Shipments on Time	Exponenetial smoothening
1	105	105.00
2	135	120.00
3	120	120.00
4	105	112.50
5	90	101.25
6	120	110.63
7	145	127.81
8	140	133.91
9	100	116.95
10	80	98.48
11	100	99.24
12	110	104.62
13		107.31

6.) With alpha = 0.3, RMSE is: 15.14

With alpha = 0.5, RMSE is: 11.13

Since RMSE is less for alpha = 0.5, so alpha = 0.5 gives better results.

Hence alpha = 0.5 gives accurate results.