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). 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}\). What is \(\overline{diff}\)?

Quick answer:

vec.plano <- c(2068592, 867387, 791878, 1731316, 911839, 909258, 1826999, 964868, 1020941, 1881435, 1075607, 964977, 2699324, 884494, 1035007, 1930143, 1124814, 1098136, 1812798, 1095294, 1163039, 1920424, 1000743, 1075763, 2341127, 1062449, 1120898, 1939866, 1316907, 1284888, 2098891, 1375423, 1201251, 2165295, 1301110, 1251165, 2986796, 1271028, 1228055, 2349629, 1385267, 1537452, 2576586, 1642938, 1577049, 2765401, 1940847, 1640531, 3271545, 1383909, 1495825, 2772734, 1592051, 1560732, 2773904, 1523255, 2013622, 2957306, 1789103, 1848972, 3507801, 1821378, 1930585, 2823010, 1970356, 1970534, 2982305, 1795240, 2145180, 3021075, 1908781, 1957956, 3955970, 2119970, 2208176, 3063504, 2190613, 2197082, 3085586, 2642591, 2550586, 3230872, 2482466, 2315274, 4388396, 2335249, 1956240, 3183566, 2421722, 1879301, 3094563, 2599894, 2320012, 3518486, 2407487, 2291118, 4813948, 2380134, 2223477, 3378416, 2876314, 2650942, 3788448, 2651506, 2450710, 4118992, 2434040, 2763878, 5227962, 2762093, 2528931, 4040412, 2883152, 3100274, 4149743, 3061236, 2805394, 3962285, 3197688, 3149649, 5401137, 3393528, 2852524, 4708691, 3567883, 3405732, 4885709, 4142396, 3564755, 4794159, 3459785, 3600702, 5789400, 3283596, 3411052, 4783941, 3706871, 3756080, 4318154, 3201376, 3502712, 4864603, 3108517, 3357796, 5904823, 2951480, 3185525, 4729624, 3282329, 3271971, 4559047, 3350292, 3286394, 4566940, 2863028, 3049842, 5780438, 3286533, 3016081, 4533575, 3296881, 3535071, 5290070, 3323063, 3318144, 5206490, 3240679, 3673046, 6166054, 3573983, 2999256, 5177550, 3845943, 3492933, 4975878, 3531498, 3611446, 5145814, 3260597, 3715755, 6239931, 3730730, 3431157, 5404423, 4049371, 3648390, 5394527, 3968853, 3970771, 5384216)
ts.plano <- ts(vec.plano,start=c(1990,2), freq = 12)
ts.plano
##          Jan     Feb     Mar     Apr     May     Jun     Jul     Aug
## 1990         2068592  867387  791878 1731316  911839  909258 1826999
## 1991  964977 2699324  884494 1035007 1930143 1124814 1098136 1812798
## 1992 1075763 2341127 1062449 1120898 1939866 1316907 1284888 2098891
## 1993 1251165 2986796 1271028 1228055 2349629 1385267 1537452 2576586
## 1994 1640531 3271545 1383909 1495825 2772734 1592051 1560732 2773904
## 1995 1848972 3507801 1821378 1930585 2823010 1970356 1970534 2982305
## 1996 1957956 3955970 2119970 2208176 3063504 2190613 2197082 3085586
## 1997 2315274 4388396 2335249 1956240 3183566 2421722 1879301 3094563
## 1998 2291118 4813948 2380134 2223477 3378416 2876314 2650942 3788448
## 1999 2763878 5227962 2762093 2528931 4040412 2883152 3100274 4149743
## 2000 3149649 5401137 3393528 2852524 4708691 3567883 3405732 4885709
## 2001 3600702 5789400 3283596 3411052 4783941 3706871 3756080 4318154
## 2002 3357796 5904823 2951480 3185525 4729624 3282329 3271971 4559047
## 2003 3049842 5780438 3286533 3016081 4533575 3296881 3535071 5290070
## 2004 3673046 6166054 3573983 2999256 5177550 3845943 3492933 4975878
## 2005 3715755 6239931 3730730 3431157 5404423 4049371 3648390 5394527
##          Sep     Oct     Nov     Dec
## 1990  964868 1020941 1881435 1075607
## 1991 1095294 1163039 1920424 1000743
## 1992 1375423 1201251 2165295 1301110
## 1993 1642938 1577049 2765401 1940847
## 1994 1523255 2013622 2957306 1789103
## 1995 1795240 2145180 3021075 1908781
## 1996 2642591 2550586 3230872 2482466
## 1997 2599894 2320012 3518486 2407487
## 1998 2651506 2450710 4118992 2434040
## 1999 3061236 2805394 3962285 3197688
## 2000 4142396 3564755 4794159 3459785
## 2001 3201376 3502712 4864603 3108517
## 2002 3350292 3286394 4566940 2863028
## 2003 3323063 3318144 5206490 3240679
## 2004 3531498 3611446 5145814 3260597
## 2005 3968853 3970771 5384216
ts.plano.subset <- ts(ts.plano[12:179],start=c(1991,1),freq=12)
ts.plano.subset
##          Jan     Feb     Mar     Apr     May     Jun     Jul     Aug
## 1991  964977 2699324  884494 1035007 1930143 1124814 1098136 1812798
## 1992 1075763 2341127 1062449 1120898 1939866 1316907 1284888 2098891
## 1993 1251165 2986796 1271028 1228055 2349629 1385267 1537452 2576586
## 1994 1640531 3271545 1383909 1495825 2772734 1592051 1560732 2773904
## 1995 1848972 3507801 1821378 1930585 2823010 1970356 1970534 2982305
## 1996 1957956 3955970 2119970 2208176 3063504 2190613 2197082 3085586
## 1997 2315274 4388396 2335249 1956240 3183566 2421722 1879301 3094563
## 1998 2291118 4813948 2380134 2223477 3378416 2876314 2650942 3788448
## 1999 2763878 5227962 2762093 2528931 4040412 2883152 3100274 4149743
## 2000 3149649 5401137 3393528 2852524 4708691 3567883 3405732 4885709
## 2001 3600702 5789400 3283596 3411052 4783941 3706871 3756080 4318154
## 2002 3357796 5904823 2951480 3185525 4729624 3282329 3271971 4559047
## 2003 3049842 5780438 3286533 3016081 4533575 3296881 3535071 5290070
## 2004 3673046 6166054 3573983 2999256 5177550 3845943 3492933 4975878
##          Sep     Oct     Nov     Dec
## 1991 1095294 1163039 1920424 1000743
## 1992 1375423 1201251 2165295 1301110
## 1993 1642938 1577049 2765401 1940847
## 1994 1523255 2013622 2957306 1789103
## 1995 1795240 2145180 3021075 1908781
## 1996 2642591 2550586 3230872 2482466
## 1997 2599894 2320012 3518486 2407487
## 1998 2651506 2450710 4118992 2434040
## 1999 3061236 2805394 3962285 3197688
## 2000 4142396 3564755 4794159 3459785
## 2001 3201376 3502712 4864603 3108517
## 2002 3350292 3286394 4566940 2863028
## 2003 3323063 3318144 5206490 3240679
## 2004 3531498 3611446 5145814 3260597
yearly <- aggregate(ts.plano.subset,FUN=sum)
yearly
## Time Series:
## Start = 1991 
## End = 2004 
## Frequency = 1 
##  [1] 16729193 18283868 22512213 24774517 27725217 31685372 32420190
##  [8] 36058045 40483048 47325948 47327004 45309249 46876867 49453998
yearly.diff <-diff(yearly,1)

answer.Q1 <- mean(yearly.diff)
answer.Q1
## [1] 2517293

Question 2

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

Quick answer:

answer.Q2 <- yearly[length(yearly)] + mean(yearly.diff)
answer.Q2
## [1] 51971291

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).

Quick answer:

answer.Q3 <- answer.Q2 - sum(ts.plano[180:190])
answer.Q3
## [1] 3033167

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.

Quick answer:

answer.Q4 <- answer.Q2 + answer.Q1
answer.Q4
## [1] 54488583

Question 5

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

Quick answer:

answer.Q5 <- 100*(answer.Q4-answer.Q2)/answer.Q2
answer.Q5
## [1] 4.843622
print(answer.Q5, digit = 3)
## [1] 4.84

R Codes

  1. [OPTIONAL] In addition to implementing this in MS Excel, write annotated R codes to 1. - 4.

Well we have just done that above.

Some Visualizations

Longer answer:

vec.plano <- c(2068592, 867387, 791878, 1731316, 911839, 909258, 1826999, 964868, 1020941, 1881435, 1075607, 964977, 2699324, 884494, 1035007, 1930143, 1124814, 1098136, 1812798, 1095294, 1163039, 1920424, 1000743, 1075763, 2341127, 1062449, 1120898, 1939866, 1316907, 1284888, 2098891, 1375423, 1201251, 2165295, 1301110, 1251165, 2986796, 1271028, 1228055, 2349629, 1385267, 1537452, 2576586, 1642938, 1577049, 2765401, 1940847, 1640531, 3271545, 1383909, 1495825, 2772734, 1592051, 1560732, 2773904, 1523255, 2013622, 2957306, 1789103, 1848972, 3507801, 1821378, 1930585, 2823010, 1970356, 1970534, 2982305, 1795240, 2145180, 3021075, 1908781, 1957956, 3955970, 2119970, 2208176, 3063504, 2190613, 2197082, 3085586, 2642591, 2550586, 3230872, 2482466, 2315274, 4388396, 2335249, 1956240, 3183566, 2421722, 1879301, 3094563, 2599894, 2320012, 3518486, 2407487, 2291118, 4813948, 2380134, 2223477, 3378416, 2876314, 2650942, 3788448, 2651506, 2450710, 4118992, 2434040, 2763878, 5227962, 2762093, 2528931, 4040412, 2883152, 3100274, 4149743, 3061236, 2805394, 3962285, 3197688, 3149649, 5401137, 3393528, 2852524, 4708691, 3567883, 3405732, 4885709, 4142396, 3564755, 4794159, 3459785, 3600702, 5789400, 3283596, 3411052, 4783941, 3706871, 3756080, 4318154, 3201376, 3502712, 4864603, 3108517, 3357796, 5904823, 2951480, 3185525, 4729624, 3282329, 3271971, 4559047, 3350292, 3286394, 4566940, 2863028, 3049842, 5780438, 3286533, 3016081, 4533575, 3296881, 3535071, 5290070, 3323063, 3318144, 5206490, 3240679, 3673046, 6166054, 3573983, 2999256, 5177550, 3845943, 3492933, 4975878, 3531498, 3611446, 5145814, 3260597, 3715755, 6239931, 3730730, 3431157, 5404423, 4049371, 3648390, 5394527, 3968853, 3970771, 5384216)

Let’s plot the data first

plot(vec.plano,type="l",las=1,col="blue",main="Plano Tax Data")
grid()