Purpose: To learn how to To learn how to use an stable seasonal pattern (SSP) model to forecast a seasonal time series (the Plano Sales Tax data set) and to test for the presence or absence of seasonality based on a test of equal proportions. This exercise is due Tuesday, September 5. Use the Excel spreadsheet plano_transposed.xlsx on the class website to finish this exercise.

Question 1

Using the complete years data from 1991 - 2004, obtain the 1991 - 2004 yearly totals. Then calculate the 13 year-to-year differences in the yearly totals and take the average of these differences. Call the average of these differences \(\overline{diff}\).

Question 2

Then compute your estimate of the 2005 total by adding \(\overline{diff}\) to the 2004 year total.

Question 3

Given your projected 2005 year total, you should get the December 2005 number by subtracting the sum of the monthly numbers from January, 2005 through November, 2005 from your projected 2005 total. (The hope here is that this estimated December number makes some sense in that the implied proportion that December takes up of the 2005 year total is not too far from the overall proportion that you have calculated for December using your whole year calculations. The nice thing about this approach for filling in the December, 2005 number is that the monthly numbers for 2005 are forced to add up to the total you have projected for 2005.) Recall we calculate the monthly proportions by summing up the numbers by month over all complete years and the dividing these monthly totals by an overall total obtained by summing up the yearly totals over all complete years (or for that matter summing up the monthly totals).

Question 4

Adding \(\overline{diff}\) to your estimated 2005 total you can get an estimate of the 2006 total of tax revenues that the city of Plano could expect to garner for that year. By applying your previously determined whole-year monthly proportions to your estimated 2006 total, you can correspondingly get monthly estimates of sales tax revenues for 2006.

Question 5

Finally, using the above information, calculate the expected percentage growth in sales tax revenue for Plano in going from 2005 to 2006.


In addition to implementing this in MS Excel, write annotated R codes to 1. - 5.