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 two- week , three-week and four-week moving averages for the time series.

2. Compute the MSE for the two- week, three-week and four-week moving average forecasts. (Look over the powerpoint)

3. Create a new column to show the trend line( y= mx+b) for the table below

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

5. Plot gasoline sales time series after obtaining the contract with the Vermont State Police

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

7.  Plot gasoline sales time series and add the trend line to graph

GASOLINE SALES TIME SERIES AFTER OBTAINING THE CONTRACT WITH THE VERMONT STATE POLICE

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

Answer 1

(1) The moving averages are calculated in column C, D and E respectively for 2 weeks, 3 weeks and 4 weeks as follows:

(2) The MSE are calculated in cells F25, G25 and H25 respectively for 2 weeks, 3 weeks and 4 weeks in the table above.

(3) The trend line is calculated in column I, by using the regression of sales (column B) on trend (column A). The regression results is as follows.

The trend line is calculated as (sales) = 0.928 (trend) + 14.052 = mX + b, where m = slope = 0.928 and b = intercept = 14.052.

(4) Since MSE of 2 weeks moving average = 3.17, which is least among all, 2-weeks moving average seems to be the best.

(5) The gasoline sales time series can be shown as below.

(6) The gasoline sales time series and three-week moving average Forecasts can be shown below.

(7) The gasoline sales time series and the trend line to graph is shown below.

_______________________________________________________________________

For your reference, the formula view of the calculations can be found below.