Public finance

profilecarfito
BT2eModule28Exercise28.12.xlsx
Home>Business & Finance homework help>Accounting homework help>Public finance

Questions

Type your name here, Last Name First
Forecasting Concepts Exercises
28.1. The State of Arizona has asked you to examine tax burdens for residents in preparation for the next forecasting effort. Use the data in Table 28.3.
(a) Prepare a graph showing the total state and local per capita taxes paid per year. Identify any outliers.
(b) Calculate the level, trend and growth, and their averages.
Table 28.5 State of Arizona: State and Local Per Capital Taxes
Year Total State and Local Per Capita Taxes Paid ($)
1977 729.10
1978 792.06
1979 871.42
1980 928.18
1981 960.51
1982 1014.01
1983 1058.44
1984 1226.99
1985 1372.09
1986 1470.50
1987 1559.85
1988 1631.95
1989 1718.13
1990 1804.99
1991 1870.55
1992 1878.01
1993 1951.56
1994 2017.41
1995 2105.29
1996 2133.51
1997 2147.02
1998 2230.04
1999 2366.24
2000 2498.91
2001 2553.06
2002 2592.34
2003 2679.79
2004 2787.50
2005 3128.02
2006 3368.10
2007 3750.00
2008 3582.34
2009 3202.74
2010 3006.35
28.2. In the Arizona tax information in assignment 1, a typographical error in the data has been discovered. The tax per capita in 2006 was actually $6,368.10 rather than $3,368.10.
(a) Prepare a graph showing the total state and local per capita taxes paid per year. Identify any outliers.
(b) Using the Windsorizing technique, adjust the data and prepare a graph showing the original information and the adjusted information.
28.3. Northland is developing a forecast for its special revenue fund that does not conform to a trend. Use the data in Table 28.4 to calculate a 5-year moving average and then use that result to project revenues for this fund for the next 3 years.
Year
1996 53421417.00
1997 64600858.00
1998 67053747.00
1999 24316124.10
2000 66110642.25
2001 93389137.00
2002 49686022.50
2003 20706410.20
2004 81238032.40
2005 49055130.40
2006 124202746.80
2007 86126169.60
2008 27862519.00
2009 85704726.16
2010 81483729.60
2011 45459604.80
2012 22672731.80
2013 58226034.00
28.4. Prepare a memo to the budget director of Northland providing a brief explanation of the revenue forecasts developed for FY 2014 to FY 2016. Based on the results of assignment 3, provide a recommendation.
28.5. River County receives revenue through property taxes. The budget director has asked you to build a forecast for the next 3 years. Use the data in Table 28.5.
(a) Prepare a forecast using a 7-, 5-, and 3-year offset moving average of level and trend.
(b) Utilize an analysis of errors to determine which forecast provides the most accurate information.
Fiscal Year Table 28.5 River County: Property Tax Revenue
(in thousands)
1992 34,762.30
1993 32,354.05
1994 30,971.67
1995 31,010.82
1996 32,382.92
1997 33,211.58
1998 36,407.76
1999 38,930.54
2000 43,246.20
2001 48,342.35
2002 45,042.55
2003 45,322.70
2004 44,165.63
2005 50,550.37
2006 62,733.41
2007 67,096.62
2008 70,422.87
2009 74,674.33
2010 74,996.80
2011 68,019.59
2012 62,401.17
2013 54,584.58
2014 39,842.99
Type your name here, Last Name First
Please enter your name on the first page

Put Graph answers here

This page for graphs only. Other pages are self-checking
Year Total State and Local Per Capita Taxes Paid
1977 729.1 Graph for Exercise 1
1978 792.06
1979 871.42
1980 928.18
1981 960.51
1982 1014.01
1983 1058.44
1984 1226.99
1985 1372.09 Put Graph Exercise 1 Here
1986 1470.5
1987 1559.85
1988 1631.95
1989 1718.13
1990 1804.99
1991 1870.55
1992 1878.01
1993 1951.56
1994 2017.41
1995 2105.29
1996 2133.51
1997 2147.02
1998 2230.04
1999 2366.24
2000 2498.91
2001 2553.06
2002 2592.34
2003 2679.79
2004 2787.5
2005 3128.02
2006 3368.1
2007 3750
2008 3582.34
2009 3202.74
2010 3006.35

Exercise 28.1

To show the cell checking, put a check here: x Percent Correct 0%
Grade Graph 1
Exercise 28.1
Arizona Years of Data: 34
1977 to 2010
Year Total State and Local Per Capita Taxes Paid Level Trend Growth Check Grade
1977 729.1 0
1978 792.06 0 0 0
1979 871.42 0 0 0
1980 928.18 0 0 0
1981 960.51 0 0 0
1982 1014.01 0 0 0
1983 1058.44 0 0 0
1984 1226.99 0 0 0
1985 1372.09 0 0 0
1986 1470.5 0 0 0
1987 1559.85 0 0 0
1988 1631.95 0 0 0
1989 1718.13 0 0 0
1990 1804.99 0 0 0
1991 1870.55 0 0 0
1992 1878.01 0 0 0
1993 1951.56 0 0 0
1994 2017.41 0 0 0
1995 2105.29 0 0 0
1996 2133.51 0 0 0
1997 2147.02 0 0 0
1998 2230.04 0 0 0
1999 2366.24 0 0 0
2000 2498.91 0 0 0
2001 2553.06 0 0 0
2002 2592.34 0 0 0
2003 2679.79 0 0 0
2004 2787.5 0 0 0
2005 3128.02 0 0 0
2006 3368.1 0 0 0
2007 3750 0 0 0
2008 3582.34 0 0 0
2009 3202.74 0 0 0
2010 3006.35 0 0 0
0
Average 0 0 0 103

Exercise 28.2

To show the cell checking, put a check here: x Grade Graph 2 Percent Correct 0%
Please enter your name on the first page Grade in Points (All Sheets) 0.00
Exercise 28.2:
Arizona
1977 to 2010
Year Total State and Local Per Capita Taxes Paid Winsorizing Check Grade
1977 729.1 0
1978 792.06 0
1979 871.42 0
1980 928.18 0
1981 960.51 0
1982 1014.01 0
1983 1058.44 0
1984 1226.99 0
1985 1372.09 0
1986 1470.5 0
1987 1559.85 0
1988 1631.95 0
1989 1718.13 0
1990 1804.99 0
1991 1870.55 0
1992 1878.01 0
1993 1951.56 0
1994 2017.41 0
1995 2105.29 0
1996 2133.51 0
1997 2147.02 0
1998 2230.04 0
1999 2366.24 0
2000 2498.91 0
2001 2553.06 0
2002 2592.34 0
2003 2679.79 0
2004 2787.5 0
2005 3128.02 0
2006 6368.1 0
2007 3750 0
2008 3582.34 0
2009 3202.74 0
2010 3006.35 0
Average 0
Standard Deviation 0
0 0
Winsorizing 2006 0 38

Exercise 28.3 & 28.4

To show the cell checking, put a check here: x Percent Correct 0%
Please enter your name on the first page Grade Graph 3 Grade in Points (All Sheets) 0.00
Exercise 28.3:
Year Special Revenue 5yrMA Forecast based on 5YrMA
1996 53421417
1997 64600858
1998 67053747 Grade
1999 24316124.1 Check 0
2000 66110642.25 0 0
2001 93389137 0 0
2002 49686022.5 0 0
2003 20706410.2 0 0
2004 81238032.4 0 0
2005 49055130.4 0 0
2006 124202746.8 0 0
2007 86126169.6 0 0
2008 27862519 0 0
2009 85704726.16 0 0
2010 81483729.6 0 0
2011 45459604.8 0 0
2012 22672731.8 0 0
2013 58226034 0
2014 0 0
2015 0 30
2016
Exercise 28.4: Put Text Answer here Grade Text Answer

Exercise 28.5

To show the cell checking, put a check here: x ME RMSE Percent Correct 0%
Please enter your name on the first page MA3 Grade in Points (All Sheets) 0.00
MA5
Exercise 28.5 MA7
How many times the trend? 4 How many times the trend? 3 How many times the trend? 1
1000 ME MSE ME MSE ME MSE
RMSE RMSE RMSE
Property Tax Levy Property Tax Levy Property Tax Levy
Fiscal Year Tax Levy Tax Levy Trend 7 year offset Level Offset Trend Forecast Error Error Sq Fiscal Year Tax Levy Tax Levy Trend 5 year offset Level Offset Trend Forecast Error Error Sq Fiscal Year Tax Levy Tax Levy Trend 3 year offset Level Offset Trend Forecast Error Error Sq
1992 34762.30 1992 34762.30 1992 34762.30
1993 32354.05 1993 32354.05 1993 32354.05
1994 30971.67 1994 30971.67 1994 30971.67
1995 31010.82 1995 31010.82 1995 31010.82
1996 32382.92 1996 32382.92 1996 32382.92
1997 33211.58 1997 33211.58 1997 33211.58
1998 36407.76 1998 36407.76 1998 36407.76
1999 38930.54 1999 38930.54 1999 38930.54
2000 43246.20 2000 43246.20 2000 43246.20
2001 48342.35 2001 48342.35 2001 48342.35
2002 45042.55 2002 45042.55 2002 45042.55
2003 45322.70 2003 45322.70 2003 45322.70
2004 44165.63 2004 44165.63 2004 44165.63
2005 50550.37 2005 50550.37 2005 50550.37
2006 62733.41 2006 62733.41 2006 62733.41
2007 67096.62 2007 67096.62 2007 67096.62
2008 70422.87 2008 70422.87 2008 70422.87
2009 74674.33 2009 74674.33 2009 74674.33
2010 74996.80 2010 74996.80 2010 74996.80
2011 68019.59 2011 68019.59 2011 68019.59
2012 62401.17 2012 62401.17 2012 62401.17
2013 54584.58 2013 54584.58 2013 54584.58
2014 39842.99 2014 39842.99 2014 39842.99
2015 2015 2015
2016 2016 2016
2017 2017 2017
Check
Grade
0 0 0
0 0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0
375

Answer 1

Exercise xx-1
Arizona Years of Data: 34
1977 to 2010
Year Total State and Local Per Capita Taxes Paid Level Trend Growth
1977 729.1 729.1
1978 792.06 792.06 62.96 0.086353038
1979 871.42 871.42 79.36 0.1001944297
1980 928.18 928.18 56.76 0.0651350669
1981 960.51 960.51 32.33 0.0348316059
1982 1014.01 1014.01 53.5 0.0556995763
1983 1058.44 1058.44 44.43 0.0438161359
1984 1226.99 1226.99 168.55 0.1592437928
1985 1372.09 1372.09 145.1 0.1182568725
1986 1470.5 1470.5 98.41 0.0717227004
1987 1559.85 1559.85 89.35 0.0607616457
1988 1631.95 1631.95 72.1 0.0462223932
1989 1718.13 1718.13 86.18 0.0528079904
1990 1804.99 1804.99 86.86 0.0505549638
1991 1870.55 1870.55 65.56 0.0363215309
1992 1878.01 1878.01 7.46 0.0039881318
1993 1951.56 1951.56 73.55 0.0391637957
1994 2017.41 2017.41 65.85 0.033742237
1995 2105.29 2105.29 87.88 0.0435608032
1996 2133.51 2133.51 28.22 0.0134043291
1997 2147.02 2147.02 13.51 0.0063322881
1998 2230.04 2230.04 83.02 0.0386675485
1999 2366.24 2366.24 136.2 0.0610751377
2000 2498.91 2498.91 132.67 0.0560678545
2001 2553.06 2553.06 54.15 0.0216694479
2002 2592.34 2592.34 39.28 0.015385459
2003 2679.79 2679.79 87.45 0.0337340009
2004 2787.5 2787.5 107.71 0.040193448
2005 3128.02 3128.02 340.52 0.1221596413
2006 3368.1 3368.1 240.08 0.0767514274
2007 3750 3750 381.9 0.1133873697
2008 3582.34 3582.34 -167.66 -0.0447093333
2009 3202.74 3202.74 -379.6 -0.105964258
2010 3006.35 3006.35 -196.39 -0.0613193703
Average 2029.0294117647 69.0075757576 0.0451276273

Answer 2

Exercise xx-1
Arizona
1977 to 2010
Year Total State and Local Per Capita Taxes Paid Winsorizing
1977 729.1 729.1
1978 792.06 792.06
1979 871.42 871.42
1980 928.18 928.18
1981 960.51 960.51
1982 1014.01 1014.01
1983 1058.44 1058.44
1984 1226.99 1226.99
1985 1372.09 1372.09
1986 1470.5 1470.5
1987 1559.85 1559.85
1988 1631.95 1631.95
1989 1718.13 1718.13
1990 1804.99 1804.99
1991 1870.55 1870.55
1992 1878.01 1878.01
1993 1951.56 1951.56
1994 2017.41 2017.41
1995 2105.29 2105.29
1996 2133.51 2133.51
1997 2147.02 2147.02
1998 2230.04 2230.04
1999 2366.24 2366.24
2000 2498.91 2498.91
2001 2553.06 2553.06
2002 2592.34 2592.34
2003 2679.79 2679.79
2004 2787.5 2787.5
2005 3128.02 3128.02
2006 6368.1 5433.5678294186
2007 3750 3750
2008 3582.34 3582.34
2009 3202.74 3202.74
2010 3006.35 3006.35
Average 2117.2647058823
Standard Deviation 1105.4343745121
3
Winsorizing 2006 5433.5678294186

Answer 3

Special Revenue
5yrMA Forecast based on 5YrMA
1996 53421417
1997 64600858
1998 67053747
1999 24316124.1
2000 66110642.25 55100557.67
2001 93389137 63094101.67 55100557.67
2002 49686022.5 60111134.57 63094101.67
2003 20706410.2 50841667.21 60111134.57
2004 81238032.4 62226048.87 50841667.21
2005 49055130.4 58814946.5 62226048.87
2006 124202746.8 64977668.46 58814946.5
2007 86126169.6 72265697.88 64977668.46
2008 27862519 73696919.64 72265697.88
2009 85704726.16 74590258.392 73696919.64
2010 81483729.6 81075978.232 74590258.392
2011 45459604.8 65327349.832 81075978.232
2012 22672731.8 52636662.272 65327349.832
2013 58226034 58709365.272 52636662.272
2014 58709365.272
2015 58709365.272
2016 58709365.272

Answer 5

ME RMSE
MA3 -1924.5783111111 6760.0004971124
MA5 291.3748 8624.553564004
MA7 -2404.2039672619 10594.9939858546
How many times the trend? 4 How many times the trend? 3 How many times the trend? 1
1000 ME -2404.2039672619 112253897.560296 MSE ME -1924.5783111111 74382924.1783736 MSE ME 291.3748 45697606.7209595 MSE
10594.9939858546 RMSE 8624.553564004 RMSE 6760.0004971124 RMSE
Property Tax Levy Property Tax Levy Property Tax Levy
Fiscal Year Tax Levy Tax Levy Trend 7 year offset Level Offset Trend Forecast Error Error Sq Fiscal Year Tax Levy Tax Levy Trend 5 year offset Level Offset Trend Forecast Error Error Sq Fiscal Year Tax Levy Tax Levy Trend 3 year offset Level Offset Trend Forecast Error Error Sq
1992 34762.3 1992 34762.3 1992 34762.3
1993 32354.051 -2408.249 1993 32354.051 -2408.249 1993 32354.051 -2408.249
1994 30971.672 -1382.379 1994 30971.672 -1382.379 1994 30971.672 -1382.379
1995 31010.815 39.143 1995 31010.815 39.143 1995 31010.815 39.143 32696.0076666667 -1895.314 30800.6936666667 210.1213333333 44150.9747217805
1996 32382.924 1372.109 1996 32382.924 1372.109 1996 32382.924 1372.109 31445.5126666667 -1250.495 30195.0176666667 2187.9063333333 4786934.12344012
1997 33211.578 828.654 1997 33211.578 828.654 32296.3524 -594.844 30511.8204 2699.7576 7288691.0987578 1997 33211.578 828.654 31455.137 9.6243333333 31464.7613333333 1746.8166666667 3051368.46694445
1998 36407.76 3196.182 1998 36407.76 3196.182 31986.208 -310.1444 31055.7748 5351.9852 28643745.581019 1998 36407.76 3196.182 32201.7723333333 746.6353333333 32948.4076666667 3459.3523333333 11967118.5661387
1999 38930.541 2522.781 33014.4428571429 274.2433333333 34111.4161904762 4819.1248095238 23223963.9297678 1999 38930.541 2522.781 32796.9498 810.7418 35229.1752 3701.3658 13700108.7854096 1999 38930.541 2522.781 34000.754 1798.9816666667 35799.7356666667 3130.8053333333 9801942.03522838
2000 43246.2 4315.659 33609.9058571429 595.463 35991.7578571429 7254.4421428571 52626930.8040616 2000 43246.2 4315.659 34388.7236 1591.7738 39164.045 4082.155 16663989.4440249 2000 43246.2 4315.659 36183.293 2182.539 38365.832 4880.368 23817991.815424
2001 48342.351 5096.151 35165.9271428571 1556.0212857143 41390.0122857143 6952.3387142857 48335013.598156 2001 48342.351 5096.151 36835.8006 2447.077 44177.0316 4165.3194 17349885.7040164 2001 48342.351 5096.151 39528.167 3344.874 42873.041 5469.31 29913351.8761001
2002 45042.553 -3299.798 37647.4527142857 2481.5255714286 47573.555 -2531.002 6405971.12400404 2002 45042.553 -3299.798 40027.686 3191.8854 49603.3422 -4560.7892 20800798.1268366 2002 45042.553 -3299.798 43506.364 3978.197 47484.561 -2442.008 5963403.07206401
2003 45322.696 280.143 39651.9867142857 2004.534 47670.1227142857 -2347.4267142857 5510412.17894222 2003 45322.696 280.143 42393.881 2366.195 49492.466 -4169.77 17386981.8529 2003 45322.696 280.143 45543.7013333333 2037.3373333333 47581.0386666667 -2258.3426666667 5100111.6000871
2004 44165.629 -1157.067 41500.5255714286 1848.5388571429 48894.681 -4729.052 22363932.818704 2004 44165.629 -1157.067 44176.8682 1782.9872 49525.8298 -5360.2008 28731752.6163207 2004 44165.629 -1157.067 46235.8666666667 692.1653333333 46928.032 -2762.403 7630870.33440903
2005 50550.367 6384.738 43065.39 1564.8644285714 49324.8477142857 1225.5192857143 1501897.51965763 2005 50550.367 6384.738 45223.8858 1047.0176 48364.9386 2185.4284 4776097.29152652 2005 50550.367 6384.738 44843.626 -1392.2406666667 43451.3853333333 7098.9816666667 50395540.7036692
2006 62733.411 12183.044 45085.7624285714 2020.3724285714 53167.2521428571 9566.1588571428 91511395.2800926 2006 62733.411 12183.044 46684.7192 1460.8334 51067.2194 11666.1916 136100026.447911 2006 62733.411 12183.044 46679.564 1835.938 48515.502 14217.909 202148936.332281
2007 67096.622 4363.211 48486.1724285714 3400.41 62087.8124285714 5008.8095714286 25088173.3228345 2007 67096.622 4363.211 49562.9312 2878.212 58197.5672 8899.0548 79193176.333403 2007 67096.622 4363.211 52483.1356666667 5803.5716666667 58286.7073333333 8809.9146666667 77614596.4339485
2008 70422.87 3326.248 51893.3755714286 3407.2031428571 65522.1881428571 4900.6818571429 24016682.6649292 2008 70422.87 3326.248 53973.745 4410.8138 67206.1864 3216.6836 10347053.3825089 2008 70422.87 3326.248 60126.8 7643.6643333333 67770.4643333333 2652.4056666667 7035255.8205654
2009 74674.333 4251.463 55047.7354285714 3154.3598571429 67665.1748571429 7009.1581428571 49128297.8715805 2009 74674.333 4251.463 58993.7798 5020.0348 74053.8842 620.4488 384956.713421438 2009 74674.333 4251.463 66750.9676666667 6624.1676666667 73375.1353333333 1299.1976666667 1687914.57707213
2010 74996.804 322.471 59280.8468571429 4233.1114285714 76213.2925714286 -1216.4885714286 1479844.44441631 2010 74996.804 322.471 65095.5206 6101.7408 83400.743 -8403.939 70626190.715721 2010 74996.804 322.471 70731.275 3980.3073333333 74711.5823333333 285.2216666667 81351.3991361099
2011 68019.592 -6977.212 63520.0051428571 4239.1582857143 80476.6382857143 -12457.0462857143 155178002.164428 2011 68019.592 -6977.212 69984.808 4889.2874 84652.6702 -16633.0782 276659290.407315 2011 68019.592 -6977.212 73364.669 2633.394 75998.063 -7978.471 63655999.4978408
2012 62401.172 -5618.42 66927.7141428571 3407.709 80558.5501428571 -18157.3781428571 329690381.022706 2012 62401.172 -5618.42 71042.0442 1057.2362 74213.7528 -11812.5808 139537065.156529 2012 62401.172 -5618.42 72563.5763333333 -801.0926666667 71762.4836666667 -9361.3116666667 87634156.1204695
2013 54584.578 -7816.594 68620.6862857143 1692.9721428571 75392.5748571429 -20807.9968571429 432972733.206867 2013 54584.578 -7816.594 70102.9542 -939.09 67285.6842 -12701.1062 161318098.703678 2013 54584.578 -7816.594 68472.5226666667 -4091.0536666667 64381.469 -9796.891 95979073.2658811
2014 39842.985 -14741.593 67456.5672857143 -1164.119 62800.0912857143 -22957.1062857143 527028729.013583 2014 39842.985 -14741.593 66935.2958 -3167.6584 57432.3206 -17589.3356 309384726.849427 2014 39842.985 -14741.593 61668.4473333333 -6804.0753333333 54864.372 -15021.387 225642067.403769
2015 63563.1905714286 -3893.3767142857 47989.6837142857 2015 59969.0262 -6966.2696 39070.2174 2015 52276.245 -9392.2023333333 42884.0426666667
2016 59669.8138571428 -3893.3767142857 44096.307 2016 53002.7566 -6966.2696 32103.9478 2016 42884.0426666667 -9392.2023333333 33491.8403333333
2017 55776.4371428571 -3893.3767142857 40202.9302857143 2017 46036.487 -6966.2696 25137.6782 2017 33491.8403333333 -9392.2023333333 24099.638

Grade Worksheet

4 Total Points Available Item Grade Weight x A mark here makes the numbers in the grade calculation visible.
0.25 Base Points (for trying this exercise)
0.6 Manual Points
3.15 Points to be graded
0 Percent all sheets
0 Graded Points
0 Graph 1 0.15
0 Graph 2 0.15
0 Graph 3 0.15
0 Problem 4 0.15
0.25 Initial Grade 0.6
0.35 Minimum Earned Points
0.6 Threshold
0 Grade Grade Table
A 100%
B 90%
0 C 80%
546 D 70%
F 0%