Question

Refer to the gasoline sales time series data in the given table. Sales (1000s of gallons) 18 Week 18 24 17 15 20 17 21 20 14 4 8 10 12

Answer 1

a) ) A four week moving average averages the actual demand for the previous four weeks to generate the forecast for the next week.This can be calculated as Sum of the actual value of the previous four weeks / 4

Using the above formula the forecast for week 5 through 12 is calculated below:

• For week 5 = (18+22+18+24) / 4 = 82/4 = 20.50
• For week 6 = (22+18+24+17) / 4 = 81/4 =20.25
• For week 7 = (18+24+17+15) / 4 = 74/4 = 18.50
• For week 8 = (24+17+15+20) / 4 = 76/4 = 19.00
• For week 9 = (17+15+20+17) / 4 = 69/4 = 17.28
• For week 10 = (15+20+17+21) / 4 = 73/4 = 18.25
• For week 11 = (20+17+21+20) / 4 = 78/4 = 19.50
• For week 12= (17+21+20+14) / 4 = 72/4 = 18.00

A five week moving average averages the actual demand for the previous five weeks to generate the forecast for the next week.This can be calculated as Sum of the actual value of the previous five weeks / 5

Using the above formula the forecast for week 6 through 12 is calculated below:

• For week 6 = (18+22+18+24+17) / 5 = 99/5 = 19.80
• For week 7 = (22+18+24+17+15) / 5 = 96/5 = 19.20
• For week 8 = (18+24+17+15+20) / 5 = 94/5 = 18.80
• For week 9 = (24+17+15+20+17) / 5 = 93/5 = 18.60
• For week 10 = (17+15+20+17+21) / 5 = 90/5 = 18.00
• For week 11 = (15+20+17+21+20) / 5 =93/5 = 18.60
• For week 12 = (20+17+21+20+14) / 5 = 92/5 = 18.40

b) To calculate the MSE ,we have to calculate the error and squared error for each method for each week first.

Error = Actual value - Forecast value

Squared error = Square of the error

Using the above two formula first the errors and squared errors and then the MSE for each method is calculated below:

Calculation of MSE for four-week moving average

The error and squared error for week 5 through 12 is calculated below:

• For week 5 error = 17-20.50 = -3.5 and squared error = square of -3.5 =12.25
• For week 6 error = 15-20. 25 = -5.25 and squared error = square of -5.25 = 27.56
• For week 7 error = 20-18.50 = 1.5 and squared error = square of 1.5 = 2.25
• For week 8 error = 17-19. 00 =-2 and squared error = 4
• For week 9 error = 21-17.28 = 3.72 and squared error = 13. 84
• For week 10 error = 20-18.25 = 1.75 and squared error = 3.06
• For week 11 error = 14-19.50 = -5.5 and squared error = 30.25
• For week 12 error = 22-18.40 = 3.6 and squared error = 12.96

MSE = Sum of the squared errors for all the weeks / number of weeks

= (12.25+27.56+2.25+4+13.84+3.06+30.28+12.96) / 8

= 106.2 / 8

= 13. 28

Calculation of MSE for five-week moving average

The error and squared error for week 6 through 12 is calculated below:

• For week 6 error = 15-19.80 = -4.18 and squared error = square of -4.18 = 17. 47
• For week 7 error = 20-19.20 = 0.8 and squared error = square of 0.8 = 0.64
• For week 8 error = 17-18.80=-1.8 and squared error = 3.24
• For week 9 error = 21-18.60 = 2.4 and squared error = 5.76
• For week 10 error = 20-18.00 = 2and squared error = 4
• For week 11 error = 14-18.60 = -4.6 and squared error = 21.16
• For week 12 error = 22-18.40 = 3.6 and squared error = 12.96

MSE = Sum of the squared errors for all the weeks / number of weeks

= (17.47+0.64+3.24+5.76+4+21.16+12.96) / 7

= 62.23 / 7

= 8.89