MEMORANDUM
To: Management Analyst
From: Beverly BunchBB, Budget Director
Subject: Budget Projections for the City of Hometown
Date: September 12, 2012
The City Council will be discussing the budget for the City of Hometown next month. In preparation for this meeting, I would like for you to prepare a memo to me that I can share with the city council members. Please address the items listed below, including the three graphs that I have requested.
I. Revenues and Expenditures
A. Using the numbers shown in Figure 1 (attached), prepare a pie graph that shows a breakdown of the city's FY 2013 budgeted 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 2013 budgeted revenues and expenditures, along with the projected revenues and expenditures for the fiscal years 2014-2018. Calculate the total revenues, total expenditures, and the projected 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, you should still use cells for these assumptions and formulas that refer to the assumption cells to allow for future changes in the assumptions. 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 2013 and the projected total revenues and total expenditures for FY 2014 - FY 2018.
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 2014-2018 to avoid a deficit? Each year the property tax rate should be the lowest level possible that will avoid a deficit. (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 2014-2018 to avoid a deficit? Each year the property tax rate should be the lowest level possible that will avoid a deficit.
C. Prepare a column graph that shows a comparison of the property tax rates that would be needed for fiscal years 2014-2018 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 city council members 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.
Your graphs should be labeled Figure 1, Figure 2, and Figure 3 and should also include a title. Your text should refer to the graphs, e.g., "As shown in Figure 1" or ..... "(see Figure 1)."
Figure 1 - City of Hometown
FY 2013 Budgeted Figures
Revenues
Property Taxes $2,239,862
Sales Taxes $ 960,444
State Revenue $ 605,645
Other $ 350,075
Total $4,156,026
Expenditures
Personal Services $2,916,409
Contractual $ 808,922
Supplies $ 356,700
Capital $ 73,995
Total $4,156,026
Property Tax Data
Market Value $131,800,000
Assessment Ratio 80%
Property Tax Collection Rate 97%
Property Tax Rate $.0219 per $1 assessed value or 2.19%
Original Assumptions - annual % change
Revenues
Sales Taxes 2.0%
State Revenue 0.0%
Other Revenues 0.0%
Expenditures
Personal Services 4.0%
Contractual 3.0%
Supplies 3.0%
Capital 3.0%
Property Tax Data
Market Value 1.5%
Tax Collection Rate 0.0%
Property Tax Rate 0.0%
How to Design Your Spreadsheet File
Please format your Excel file as shown below.
Title
Your Name
Date
Assumptions
Xxxxxx ####
Xxxxxxx ####
Etc.
budgeted --------------- projected ------------------------
Property Tax Data 2013 2014 2015 2016 2017 2018
Market Value ### formulas that you copy through to 2018
Assessment Ratio
Assessed Value
Property Tax Rate
Tax Collection Rate
Revenues
Xxxx
Xxxx
Etc.
Total Revenues
Expenditures
Xxxx
Xxxx
Etc.
Total Expenditures
Surplus / (Deficit)
(xxxx refers to text and ### refers to numerical values that you will need to fill in)
Note: Be sure to be comfortable with using the copy command (both absolute and relative) prior to doing this assignment. You may want to review an Atomic Learning video.