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 and five-week moving averages for the time series.

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

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

Week	Sale (1000s of Gallons)	three-week	five-week
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

I HOPE ITS HELPFUL TO YOU IF YOU HAVE ANY DOUBTS PLS COMMENTS BELOW..I WILL BE THERE TO HELP YOU ...ALL THE BEST

AS FOR GIVEN DATA..

• 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)

I HOPE YOU UNDERSTAND..PLS RATE THUMBS UP ITS HELPS ME ALOT..

THANK YOU...!!