Step 1. Begin De-seasonalizing these data on the basis of months using the Centered Moving Average CMA) approach. This will give you a CMA value for each month from July year 1 to June year 2. (hint: arrange all prices into a single column, calculate CMAs in second column) There as there are 12 months in a year, each CMA will be calculated by adding middle month price to the prices for 5 months before and 5 months after, then dividing that sum by 12.

Step 2. You will now have 24 CMA values for the middle 24 months. Calculate the seasonal ratio for each of those 24 months (calculate seasonal ratios in third column)

Step 3. Calculate the seasonal indexes as the averages of the seasonal ratios for each calendar month and enter them below to two decimal places. (for each month there are two CMA values, so there will be exactly 12 seasonal indexes, one for each month of the year) Repeat these same 12 values into the Seasonal Average column for all three years of historical data so that all 36 months have their appropriate seasonal index value shown on the spreadsheet.

Jul	Aug	Sep	Oct	Nov	Dec
Jan	Feb	March	Apr	May	Jun

Step 4. Divide the actual prices by the appropriate seasonal indexes for each corresponding month to create a column of de-seasonalized prices.

Step 5. Use Excels Data Analysis Regression tool to identify the coefficients of the linear regression equation (intercept and slope coefficients of de-seasonalized data against independent variable (x) of period number) for the 36 months of de-seasonalized data, then plot a new chart showing both the original data series, and the de-seasonalized data together.

Identify the coefficients in: Y = 0 + 1 X

b0= ________ b1= ________

Step 6. You now have deconstructed your data to just trend with random and you have a complete set of seasonal averages, which is enough to make a forecast of period 37 (and possibly beyond). You do this by plugging 37 into the equation above to get the trend prediction which is _________, and then re-seasonalize it by multiplying by the seasonal average for whatever month period 37 lands on. (Ok, its January!) Once you have done that, you will have your forecast value.

Period 37 forecasted gasoline price: _______________

Data for use in TBE#5 Average Monthly Gasoline Prices for past 3 Years - Assume current time is end of December, year 3. Month January February March April May June July August September October November December Year 1 Year 2 Year 3 $ 1.090 $1.246 $1.475 $1.326 $1.400 $1.642 $ 1.637 $1.557 $1.523 $1.429 $1.724 $1.712 $ 1.311 $1.917 $1.943 $1.722 $2.378 $1.822 $2.172 $1.901 $2.147 $1.979 $1.648 $1.730 $1.313 $1.665 $1.935 $ 1.184 $1.160 $1.568 $1.861 $1.464 $1.320 $1.209 $1.435 $1.625 Seasonal Ratio Seasonal Index (average of month's Ratios) Price De-Seasonal Alternate layout,( for your calculations) Price Period Month Actual 1 January $1.090 2 February $1.326 3 March $1.637 4 April $1.429 5 May $1.311 6 June $1.722 7 July $2.172 8 August $1.979 9 September $1.313 10 October $1.184 11 November $1.861 12 December $1.209 13 January $1.246 14 February $ 1.400 15 March $1.557 16 April $1.724 17 May $1.917 18 June $2.378 19 July $1.901 20 August $1.648 21 September $1.665 22 October $ 1.160 23 November $1.464 24 December $1.435 25 January $1.475 26 February $1.642 27 March $1.523 28 April $1.712 29 May 30 June 31 July 32 August 33 September 34 October 35 November 36 December $1.943 $1.822 $2.147 $1.730 $1.935 $1.568 $1.320 $1.625 Data for use in TBE#5 Average Monthly Gasoline Prices for past 3 Years - Assume current time is end of December, year 3. Month January February March April May June July August September October November December Year 1 Year 2 Year 3 $ 1.090 $1.246 $1.475 $1.326 $1.400 $1.642 $ 1.637 $1.557 $1.523 $1.429 $1.724 $1.712 $ 1.311 $1.917 $1.943 $1.722 $2.378 $1.822 $2.172 $1.901 $2.147 $1.979 $1.648 $1.730 $1.313 $1.665 $1.935 $ 1.184 $1.160 $1.568 $1.861 $1.464 $1.320 $1.209 $1.435 $1.625 Seasonal Ratio Seasonal Index (average of month's Ratios) Price De-Seasonal Alternate layout,( for your calculations) Price Period Month Actual 1 January $1.090 2 February $1.326 3 March $1.637 4 April $1.429 5 May $1.311 6 June $1.722 7 July $2.172 8 August $1.979 9 September $1.313 10 October $1.184 11 November $1.861 12 December $1.209 13 January $1.246 14 February $ 1.400 15 March $1.557 16 April $1.724 17 May $1.917 18 June $2.378 19 July $1.901 20 August $1.648 21 September $1.665 22 October $ 1.160 23 November $1.464 24 December $1.435 25 January $1.475 26 February $1.642 27 March $1.523 28 April $1.712 29 May 30 June 31 July 32 August 33 September 34 October 35 November 36 December $1.943 $1.822 $2.147 $1.730 $1.935 $1.568 $1.320 $1.625