Question

Problem 6-07 (Algorithmic)

Refer to the gasoline sales time series data in the given table.

Week	Sales (1000s of gallons)
1	17
2	22
3	19
4	22
5	19
6	16
7	22
8	17
9	23
10	21
11	14
12	22

1. Compute four-week and five-week moving averages for the time series. If required, round your answers to two decimal places.
   

   Week	Sales	4 Period Moving Average	5 Period Moving Average
   1	17
   2	22
   3	19
   4	22
   5	19
   6	16
   7	22
   8	17
   9	23
   10	21
   11	14
   12	22

2. Compute the MSE for the four-week and five-week moving average forecasts. If required, round your intermediate calculations to four decimal places and round your final answers to three decimal places.
   
   MSE for four-week moving average =
   
   MSE for five-week moving average =
   
3. What appears to be the best number of weeks of past data (three, four, or five) to use in the moving average computation? Recall that MSE for the three-week moving average is 12.138.
   
   

Answer 1

Please refer below table .

We place all the data provided in excel table

We calculate four week and five week moving average as per following formula :

Four week moving average :

Ft = ( At-1 + At-2 + At-3 + A t-4) / 4

Five week moving average :

Ft = ( At-1 + At-2 + At-3 + A t-4+ At-5 ) / 5

Ft = forecast for week t, At , At-1 etc = Actual data for period t, t-1 etc

Squared error = Calculate square of absolute difference forecast data and actual data

Week	Actual data	Four week moving average	Squared error	Five week moving average	Squared error
1	17
2	22
3	19
4	22
5	19	20	1
6	16	20.5	20.25	19.8	14.44
7	22	19	9.00	19.6	5.76
8	17	19.75	7.56	19.6	6.76
9	23	18.5	20.25	19.2	14.44
10	21	19.5	2.25	19.4	2.56
11	14	20.75	45.56	19.8	33.64
12	22	18.75	10.56	19.4	6.76
		Total =	116.44		84.36
		Mean Square error (MSE)	14.55		12.05

Basis above,

Mean squared error (MSE) :

For 4 week moving average case = Sum of squared error/ 8 i.e. number of data = 14.55

For 5 week moving average case = Sum of squared error /7 i.e. number of data = 12.05

MSE for 3 week moving average = 12.1138

A lower MSE is always desirable since it indicates least difference between actual and forecast data.

Hence 5 week moving average case is the best and is to be used for moving average calculation