The excel spreadsheet has historical property tax revenue for a locality. In the first worksheet labeled "Property Tax Data" you are given property tax revenue for the years 1980-2006. Based on this data, your job is to estimate property revenue for years 2007-2012 using the Simple Moving Average (SMA) technique, the Transformation Moving Average (TMA) technique, and the Exponential Smoothing Technique (EXS). As a result, you will have three different estimations for property tax revenue for years 2007-2012. These revenue estimations have to be done in excel by using the "excel equation option." Next, you have to determine which estimation technique generates the most accurate estimate. In the second worksheet labeled "Property Tax Data Actual 1980-2012" you are given the actual property tax data for years 1980-2012. With the actual property tax data from years 2007-2012, calculate the percentage error of both of your estimates annually for years 2007-2012. These percentage error estimations have to be done in excel by using the "excel equation option." Third, generate three "line charts" (also called scatterplots) in excel that show the actual property tax data compared to the estimated property tax data for years 2007-2012 for the SMA, TMA, AND EXS techniques. For this simulation, I want you to turn in via Blackboard one excel document with: the SMA, TMA, and EXS estimates for years 2007-2012, . the percent errors for the SMA, TMA, and EXS estimates for years 2007-2012, . and the three line charts showing the SMA estimate compared to the actual property tax data for years 1980- 2012, the TMA estimated compared to the actual property tax data for years 1980-2012, and the EXS estimated compared to the actual property tax data for years 1980- 2012 Note: In order to do well on this simulation, you should structure your excel document so that I can easily follow your equations and calculations. Property Tax Data Property Tax Actual 1980-2012 Property Tax Revenue 31000 3957607 27277000 Property Tax Data Property Tax Actual 1980-2012 Property Tax Revenue 3.190.000 497691 53822 73181300 2 7474111 750123 11.6161806 13.711.833 2010 2011 2012 DOOR 14.500.000 14,945,770 15.894,445 1. Examine the 'overview of the city document, especially pages 22-27 From those pages, calculate the following: (a) Whether your department ran a surplus or deficit for Years I-V. Calculate the amount of the surplus/deficit for each year and construct a line graph in excel to show this amount. (b) The percentage of your department's surplus/deficit in relation to the entire size of the budget for Year's I-V. To do this, show a calculation for your departmental revenues and the total revenues contained in the budget, and show a calculation for your departmental expenditures and the total expenditures contained in the budget (i.e. see Wang pages 23-24). (c) Your department's per capita ratio in terms of revenues and expenditures. To calculate this, first determine the city's population in Years I-V. Each specific year of population will be the denominator in a division calculation with the numerator being revenues and in a separate calculation, expenditures). You should calculate ten ratios: five ratios for departmental revenues (for Years I-V), and five ratios for your departmental expenditures (for Years I-V). Notes Show your calculations in excel. I should be able to follow all calculations in your excel spreadsheet. You do not have to recreate the entire list of expenditures and revenues in your excel spreadsheet. However, it is probably a good idea to list the expenditures and revenues contained in your department. A few departments will have no revenues to show. That is okay. Report this as a 0 in your calculations. Some departments will have many revenues to show. That is, you will have multiple line items to show and calculate in your excel spreadsheet. G EXPENDITURE DATA ation 1.1.1.1.it L...ll.is.l.si Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 Property Tax Revenue 3,196,000 3,298,501 3,603,099 3,787 298 3,957,667 4,227,000 4,600.341 4,976.9741 5,382.822 5,942,087 6,543.555 7.251.555 7.818.800 7.886,005 7,773,195 7,474,111 7,100.000 7.291,000 7.239.844 7,631,777 7,850,123 8,246,335 8,761.623 10,063,836 11,582,111 11,616,906 12,636,052 2007 2008 2009 2010 2011 2012 The excel spreadsheet has historical property tax revenue for a locality. In the first worksheet labeled "Property Tax Data" you are given property tax revenue for the years 1980-2006. Based on this data, your job is to estimate property revenue for years 2007-2012 using the Simple Moving Average (SMA) technique, the Transformation Moving Average (TMA) technique, and the Exponential Smoothing Technique (EXS). As a result, you will have three different estimations for property tax revenue for years 2007-2012. These revenue estimations have to be done in excel by using the "excel equation option." Next, you have to determine which estimation technique generates the most accurate estimate. In the second worksheet labeled "Property Tax Data Actual 1980-2012" you are given the actual property tax data for years 1980-2012. With the actual property tax data from years 2007-2012, calculate the percentage error of both of your estimates annually for years 2007-2012. These percentage error estimations have to be done in excel by using the "excel equation option." Third, generate three "line charts" (also called scatterplots) in excel that show the actual property tax data compared to the estimated property tax data for years 2007-2012 for the SMA, TMA, AND EXS techniques. For this simulation, I want you to turn in via Blackboard one excel document with: the SMA, TMA, and EXS estimates for years 2007-2012, . the percent errors for the SMA, TMA, and EXS estimates for years 2007-2012, . and the three line charts showing the SMA estimate compared to the actual property tax data for years 1980- 2012, the TMA estimated compared to the actual property tax data for years 1980-2012, and the EXS estimated compared to the actual property tax data for years 1980- 2012 Note: In order to do well on this simulation, you should structure your excel document so that I can easily follow your equations and calculations. Property Tax Data Property Tax Actual 1980-2012 Property Tax Revenue 31000 3957607 27277000 Property Tax Data Property Tax Actual 1980-2012 Property Tax Revenue 3.190.000 497691 53822 73181300 2 7474111 750123 11.6161806 13.711.833 2010 2011 2012 DOOR 14.500.000 14,945,770 15.894,445 1. Examine the 'overview of the city document, especially pages 22-27 From those pages, calculate the following: (a) Whether your department ran a surplus or deficit for Years I-V. Calculate the amount of the surplus/deficit for each year and construct a line graph in excel to show this amount. (b) The percentage of your department's surplus/deficit in relation to the entire size of the budget for Year's I-V. To do this, show a calculation for your departmental revenues and the total revenues contained in the budget, and show a calculation for your departmental expenditures and the total expenditures contained in the budget (i.e. see Wang pages 23-24). (c) Your department's per capita ratio in terms of revenues and expenditures. To calculate this, first determine the city's population in Years I-V. Each specific year of population will be the denominator in a division calculation with the numerator being revenues and in a separate calculation, expenditures). You should calculate ten ratios: five ratios for departmental revenues (for Years I-V), and five ratios for your departmental expenditures (for Years I-V). Notes Show your calculations in excel. I should be able to follow all calculations in your excel spreadsheet. You do not have to recreate the entire list of expenditures and revenues in your excel spreadsheet. However, it is probably a good idea to list the expenditures and revenues contained in your department. A few departments will have no revenues to show. That is okay. Report this as a 0 in your calculations. Some departments will have many revenues to show. That is, you will have multiple line items to show and calculate in your excel spreadsheet. G EXPENDITURE DATA ation 1.1.1.1.it L...ll.is.l.si Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 Property Tax Revenue 3,196,000 3,298,501 3,603,099 3,787 298 3,957,667 4,227,000 4,600.341 4,976.9741 5,382.822 5,942,087 6,543.555 7.251.555 7.818.800 7.886,005 7,773,195 7,474,111 7,100.000 7.291,000 7.239.844 7,631,777 7,850,123 8,246,335 8,761.623 10,063,836 11,582,111 11,616,906 12,636,052 2007 2008 2009 2010 2011 2012