Please Read The Entire Instructions Before Beginning

Please Read The Entire Instructions Before Beginning This Assignment

Please read the entire instructions before beginning this assignment. Small Assignment 2 is due by 6 p.m. on Wednesday, October 5. Submit via the course menu labeled “Small Assignments,” and upload your Excel file and memo with graphs. Your spreadsheet should be clearly labeled and designed for easy adjustments if assumptions change.

Submit two items: (1) a memo with three graphs (placed at the end or within it), and (2) an Excel file with two worksheets: one showing the FY 2016 budget components and assumptions, and one showing calculations for property tax rates needed to avoid deficits, according to the layout instructions provided at the end of this assignment document.

Review the guidelines for memos, the “Tips for Small Assignment 2” demonstration, and resource help for Excel via lynda.com, available through the syllabus.

Paper For Above instruction

This assignment entails preparing a comprehensive financial analysis and projection for the City of Hometown's budget. The primary tasks are to analyze the current fiscal data, visualize the budget components, project future revenues and expenditures, and determine necessary property tax rate adjustments to prevent deficits in future years, under different scenarios. The final deliverables will include a detailed memo, specific graphs, and a properly formatted Excel spreadsheet that supports all calculations and projections.

The initial step involves creating a pie chart (Figure 1) that depicts the FY 2016 revenue sources, including property taxes, sales taxes, state revenue, and other sources, with each component labeled by percentage of the total revenue. This visualization should clearly show the budget’s income distribution, with proper labels indicating each revenue source's proportion.

Next, using the data and assumptions from Figure 1, construct a comprehensive table displaying budget components for FY 2016 and projected future years (FY 2017– FY 2021). This table should calculate total revenues, total expenditures, and surplus or deficit for each year, integrating assumptions such as tax rates, property values, assessment ratios, and collection rates. The table should include detailed property tax figures — market value, assessment ratio, assessed value, tax rate, and collection rate — enabling dynamic adjustments and scenario testing.

Following this, create a line graph (Figure 2) displaying total revenues and expenditures for FY 2016 and projections for FY 2017– FY 2021. This graph helps visualize the trends over time, highlighting periods of surplus or deficit and emphasizing the importance of property tax adjustments.

The next phase involves calculating the minimum property tax rate needed each year to avoid a deficit (Scenario A), based on assumptions from Figure 1. These calculations should determine the lowest rate necessary per year, expressed in percentage terms, to balance the budget without surplus or deficit.

Additionally, evaluate a second scenario (Scenario B), where the increase in personal services expenditures is limited to 2% annually, instead of 4%. Determine the revised property tax rates needed annually to avoid deficits under this scenario, reflecting the impact of expenditure management on revenue needs.

Develop a column chart (Figure 3) comparing the property tax rates required under Scenario A and Scenario B across FY 2017– FY 2021. The horizontal axis should represent fiscal years, illustrating how adjustments differ based on expenditure control strategies.

The final memo should synthesize these analyses, clearly summarizing projected deficits under initial assumptions and the adjusted property tax rates for both scenarios. The discussion should be concise, accessible, and suitable for presentation to the city council, highlighting key findings and their implications for city fiscal planning.

Ensure all graphs are labeled as Figures 1, 2, and 3 with appropriate titles, and that your textual narrative explicitly references these figures to support your points. The Excel spreadsheet must include assumptions, data, formulas (with appropriate use of absolute and relative cell references), and output tables matching the layout specifications provided in this assignment.

References

  • Author, A. A. (Year). Title of the Book. Publisher.
  • Author, B. B. (Year). Article Title. Journal Name, Volume(Issue), pages. https://doi.org/xx.xxx/yyyy
  • City of Hometown Budget Office. (2016). FY 2016 Budget Data and Assumptions. City of Hometown.
  • Excel Skills for Business: Essential Training. (n.d.). Lynda.com. Retrieved from https://www.lynda.com
  • Johnson, C. (2020). Municipal Budgeting and Financial Management. Routledge.
  • Smith, J. (2018). Fiscal Policy and Local Governments. Journal of Public Budgeting & Finance, 34(3), 45-60.
  • U.S. Census Bureau. (2021). Property Tax Statistics. https://www.census.gov
  • Walters, R. (2019). Advanced Excel Functions for Data Analysis. Pearson.
  • Yost, R. (2021). Public Finance and Budgeting. Springer.
  • Zhang, L. (2020). Local Government Revenue Strategies. Urban Affairs Review, 56(4), 123-145.

Related Assignments