Question

Using only the California sales data through Excel

Date	Chicago	Seasonal Relatives for Chicago Sales	California	New York
1/4/18	152	0.3	640	456
1/5/18	195	0.4	586	650
1/6/18	653	1.5	420	543
1/7/18	187	0.5	645	632
1/8/18	240	0.6	507	546
1/9/18	207	0.8	623	531
1/10/18	311	0.9	511	544
1/11/18	373	1.2	500	639
1/12/18	225	0.5	576	570
1/13/18	276	0.3	540	544
1/14/18	475	0.7	475	674
1/15/18	755	1.1	645	711
1/16/18	732	1.4	534	594
1/17/18	975	2.2	511	564
1/18/18	170	0.3	650	693
1/19/18	203	0.7	699	645
1/20/18	288	0.8	580	570
1/21/18	378	0.9	520	456
1/22/18	400	1.3	546	609
1/23/18	798	1.6	576	585
1/24/18	1111	1.9	649	609
1/25/18	176	0.5	657	480
1/26/18	213	0.6	600	544
1/27/18	240	0.9	703	560
1/28/18	311	0.7	657	476
1/29/18	456	1	546	656
1/30/18

1a. Forecast sales for January 7^th through January 30^th using a three-day weighted moving average.

The weights are:

Day	Weight
1 day previous	0.50
2 days previous	0.35
3 days previous	0.15

1b. Find the mean absolute deviation of the sales forecasts for January 7^th through January 29^th. You should not include the forecast for January 30^th since there is no corresponding sales figure for that date.

1c. Find the mean squared error of the sales forecasts, again for January 7^th through January 29^th.

1d. Create a scatter scatter-chart for the three-day weighted moving average. Label your chart axes properly.

Answer 1

1a/ b/ c

Date	California	Forecast (WMA-3)		Absolute Deviation		Squared Error
01-Jan-18	640
02-Jan-18	586
03-Jan-18	420
04-Jan-18	645
05-Jan-18	507
06-Jan-18	623
07-Jan-18	511	585.70		74.70		5580.09
08-Jan-18	500	549.60		49.60		2460.16
09-Jan-18	576	522.30		53.70		2883.69
10-Jan-18	540	539.65		0.35		0.12
11-Jan-18	475	546.60		71.60		5126.56
12-Jan-18	645	512.90		132.10		17450.41
13-Jan-18	534	569.75		35.75		1278.06
14-Jan-18	511	564.00		53.00		2809.00
15-Jan-18	650	539.15		110.85		12287.72
16-Jan-18	699	583.95		115.05		13236.50
17-Jan-18	580	653.65		73.65		5424.32
18-Jan-18	520	632.15		112.15		12577.62
19-Jan-18	546	567.85		21.85		477.42
20-Jan-18	576	542.00		34.00		1156.00
21-Jan-18	649	557.10		91.90		8445.61
22-Jan-18	657	608.00		49.00		2401.00
23-Jan-18	600	642.05		42.05		1768.20
24-Jan-18	703	627.30		75.70		5730.49
25-Jan-18	657	660.05		3.05		9.30
26-Jan-18	546	664.55		118.55		14054.10
27-Jan-18		608.40	MAD =	65.93	MSE =	5757.82

1d