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

SOLUTION

1) and 2)

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

3 )

MSE is lowest for Three- week

formulas : just copy paste below in excel

week	sale	MA 3	MA 4	MA 5	MSE 3	MSE 4	MSE 5
1	17
=1+A2	21
=1+A3	19
=1+A4	23	=AVERAGE(B2:B4)			=(B5-C5)^2
=1+A5	18	=AVERAGE(B3:B5)	=AVERAGE(B2:B5)		=(B6-C6)^2	=(B6-D6)^2
=1+A6	16	=AVERAGE(B4:B6)	=AVERAGE(B3:B6)	=AVERAGE(B2:B6)	=(B7-C7)^2	=(B7-D7)^2	=(B7-E7)^2
=1+A7	20	=AVERAGE(B5:B7)	=AVERAGE(B4:B7)	=AVERAGE(B3:B7)	=(B8-C8)^2	=(B8-D8)^2	=(B8-E8)^2
=1+A8	18	=AVERAGE(B6:B8)	=AVERAGE(B5:B8)	=AVERAGE(B4:B8)	=(B9-C9)^2	=(B9-D9)^2	=(B9-E9)^2
=1+A9	22	=AVERAGE(B7:B9)	=AVERAGE(B6:B9)	=AVERAGE(B5:B9)	=(B10-C10)^2	=(B10-D10)^2	=(B10-E10)^2
=1+A10	20	=AVERAGE(B8:B10)	=AVERAGE(B7:B10)	=AVERAGE(B6:B10)	=(B11-C11)^2	=(B11-D11)^2	=(B11-E11)^2
=1+A11	15	=AVERAGE(B9:B11)	=AVERAGE(B8:B11)	=AVERAGE(B7:B11)	=(B12-C12)^2	=(B12-D12)^2	=(B12-E12)^2
=1+A12	22	=AVERAGE(B10:B12)	=AVERAGE(B9:B12)	=AVERAGE(B8:B12)	=(B13-C13)^2	=(B13-D13)^2	=(B13-E13)^2
=1+A13	31	=AVERAGE(B11:B13)	=AVERAGE(B10:B13)	=AVERAGE(B9:B13)	=(B14-C14)^2	=(B14-D14)^2	=(B14-E14)^2
=1+A14	34	=AVERAGE(B12:B14)	=AVERAGE(B11:B14)	=AVERAGE(B10:B14)	=(B15-C15)^2	=(B15-D15)^2	=(B15-E15)^2
=1+A15	31	=AVERAGE(B13:B15)	=AVERAGE(B12:B15)	=AVERAGE(B11:B15)	=(B16-C16)^2	=(B16-D16)^2	=(B16-E16)^2
=1+A16	33	=AVERAGE(B14:B16)	=AVERAGE(B13:B16)	=AVERAGE(B12:B16)	=(B17-C17)^2	=(B17-D17)^2	=(B17-E17)^2
=1+A17	28	=AVERAGE(B15:B17)	=AVERAGE(B14:B17)	=AVERAGE(B13:B17)	=(B18-C18)^2	=(B18-D18)^2	=(B18-E18)^2
=1+A18	32	=AVERAGE(B16:B18)	=AVERAGE(B15:B18)	=AVERAGE(B14:B18)	=(B19-C19)^2	=(B19-D19)^2	=(B19-E19)^2
=1+A19	30	=AVERAGE(B17:B19)	=AVERAGE(B16:B19)	=AVERAGE(B15:B19)	=(B20-C20)^2	=(B20-D20)^2	=(B20-E20)^2
=1+A20	29	=AVERAGE(B18:B20)	=AVERAGE(B17:B20)	=AVERAGE(B16:B20)	=(B21-C21)^2	=(B21-D21)^2	=(B21-E21)^2
=1+A21	34	=AVERAGE(B19:B21)	=AVERAGE(B18:B21)	=AVERAGE(B17:B21)	=(B22-C22)^2	=(B22-D22)^2	=(B22-E22)^2
=1+A22	33	=AVERAGE(B20:B22)	=AVERAGE(B19:B22)	=AVERAGE(B18:B22)	=(B23-C23)^2	=(B23-D23)^2	=(B23-E23)^2
					=AVERAGE(F5:F23)	=AVERAGE(G5:G23)	=AVERAGE(H5:H23)