Reference no: EM131581

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.