Finance homework ( budget analysis, tax, Revenues and Expenditures)

Revenues and Expenditures A. Using the numbers shown in Figure 1 (attached), prepare a pie graph that shows a breakdown of the city’s budgeted FY 2016 revenues by source (e.g., property taxes, sales taxes, etc.). The labels on the graph should indicate the percentage of total revenue that is attributable to each component. B. Using the data and assumptions shown in Figure 1, prepare a table that shows the components of the FY 2016 budgeted revenues and expenditures, along with the projected revenues and expenditures for the fiscal years 2017-2021. Calculate the projected total revenues, total expenditures, and surplus or deficit for each of the future years. You should also show the following property tax figures for each year: the market value of property, the assessment ratio, the assessed value of property, the property tax rate, and the property tax collection rate. (Note: Even though some assumption values are equal to zero, those assumptions could change in the future. Therefore, you should still use cells for these assumptions and formulas that refer to the assumption cells. However, you do not need to include formulas for the assessment ratio which is set by state law and therefore will not change.) C. Prepare a line graph showing the budgeted total revenues and total expenditures for FY 2016 and the projected total revenues and total expenditures for FY 2017 – FY 2021. II. Estimation of Property Tax Rates Needed in the Future to Avoid a Deficit A. Scenario A Given the figures and assumptions listed in Figure 1, what would the property tax rate have to be in each of the fiscal years 2017-2021 to avoid a deficit? Each year the property tax rate should be the lowest level possible that will avoid a deficit. Express the property tax rates as percentages. (You should use the spreadsheet to calculate the new property tax rates.) B. Scenario B Another option being considered is to hold the increase in personal services to 2% rather than 4%. If we do this, what would the property tax rate have to be in each of the fiscal years 2017-2021 to avoid a deficit? Each year the property tax rate should be the lowest level possible that will avoid a deficit. Express the property tax rates as percentages. C. Prepare a column graph that shows a comparison of the property tax rates that would be needed for fiscal years 2017-2021 under Scenario A and Scenario B. Express the property tax rates as a percentage. The horizontal axis should show the fiscal years. III. Summarize the results of your analysis in a memo to me. Within the text of your memo, be sure to summarize your major findings, such as how large the projected deficits will be under the original assumptions and what property tax rates (expressed as a percentage) will be needed to avoid a deficit under Scenario A and Scenario B. Do not assume that the reader of your memo will look at your tables and graphs to identify what information is important. Instead you should include that information in the text of your memo. I may decide to share your memo with city council members and so please write it in a way that they would be able to clearly follow your points.