Question

Refer to the gasoline sales time series data in Table below to answer the following (Copy the file to Excel when done submit here)

1. Compute Three- week, four-week and five-week moving averages for the time series.

2. Compute the MSE for Three- week, the four-week and five-week moving average forecasts.

3. What appears to be the best number of weeks of past data (three, four, or five) to use in the moving average computation?

4. Plot gasoline sales time series after obtaining the contract with the Vermont state police

5. Plot gasoline sales time series and three-week moving average Forecasts

Week	Sale (1000s of Gallons)
1	17
2	21
3	19
4	23
5	18
6	16
7	20
8	18
9	22
10	20
11	15
12	22
13	31
14	34
15	31
16	33
17	28
18	32
19	30
20	29
22	34
21	33

Answer 1

Week	Sales	3-week MA	Error-square	4-week MA	Error-square	5-week MA	Error-square
1	17
2	21
3	19
4	23	19	16
5	18	21	9	20	4
6	16	20	16	20.25	18.0625	19.6	12.96
7	20	19	1	19	1	19.4	0.36
8	18	18	0	19.25	1.5625	19.2	1.44
9	22	18	16	18	16	19	9
10	20	20	0	19	1	18.8	1.44
11	15	20	25	20	25	19.2	17.64
12	22	19	9	18.75	10.5625	19	9
13	31	19	144	19.75	126.5625	19.4	134.56
14	34	22.66667	128.4444	22	144	22	144
15	31	29	4	25.5	30.25	24.4	43.56
16	33	32	1	29.5	12.25	26.6	40.96
17	28	32.66667	21.77778	32.25	18.0625	30.2	4.84
18	32	30.66667	1.777778	31.5	0.25	31.4	0.36
19	30	31	1	31	1	31.6	2.56
20	29	30	1	30.75	3.0625	30.8	3.24
21	34	30.33333	13.44444	29.75	18.0625	30.4	12.96
22	33	31	4	31.25	3.0625	30.6	5.76
	MSE		21.7076		24.09722		26.15529

MSE : Average of squared errors

3) Since MSE for 3-week MA is the least, it the best in MA computation for the given data.

5) Scatter plot :