Prepare a line graph

Assignment Help Financial Management
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.

Reference no: EM131581

Questions Cloud

Red brand canners : Red Brand Canners Gordon asked Myers about the demand for tomato products for the coming year. Myers replied that they could sell all of the whole canned tomatoes they could produce.
Flow assurance technical report : Flow Assurance Technical Report - Scale Risk Assessment
Give both a high-level algorithm and an implementation : Give both a high-level algorithm and an implementation (\bubble diagram") of a Turing machine for the language
Analytical procedures for the cash cycle : Analytical procedures for the cash cycle
Prepare a line graph : Prepare a line graph showing the budgeted total revenues and total expenditures
Assess risks and opportunities in terms of economic : Assess risks and opportunities in terms of economic. A analysis of the case study "AccuForm: Ethical leadership and its challenges in the era of globalization"
Disk scheduling algorithm : Simulation of Elevator and Shortest Seek Time First
Operating system and cpu scheduling : Operating System and CPU Scheduling
Identify a list of matters for consideration : The approach the controller recommended is to compare SUPERVALU's revenue recognition accounting policies to three similar companies, one reporting under US GAAP ( Safeway ) and two reporting under IFRS ( Ahold and Loblaw Companies ).

Reviews

Write a Review

 

Financial Management Questions & Answers

  Foreign company acquisition

Acquisition by a foreign company and the effects of that decision and the results of foreign exchange in Euro and the exchange rate differences.

  Financial management for profit and non profit organizations

In this essay, we are going to discuss the issues of financial management in a non-profit organisation.

  Method for estimating a venture''s value

Evaluate venture's present value, cash and surplus cash and basic venture capital.

  Replacement analysis

This document show the Replacement Analysis of modling machine. Is replacement give profit to company or not?

  Business finance task - capital budgeting

Your company is considering using the payback period for capital-budgeting. Discuss the advantages and disadvantages of this technique.

  Analysis of the investment

In this project, you will focus on one of these: the additional cost resulting from the purchase of an apple press (a piece of equipment required to manufacture apple juice).

  Conduct a what-if analysis

Review the readings and media for this unit, including the Anthony's Orchard case study media. Familiarise yourself with the Anthony's Orchard company and its current situation.

  Determine operational expenditures

Organisations' behaviour is guided by financial data. In the short term, such data will help determine operational expenditures; in the long term, historical data may help generate forecasts aimed at determining strategic plans. In both instances.

  Personal financial management

How much will you have left over each half year if you adopt the latter course of action?

  Sources of finance for expansion into new foreign markets

A quoted company is considering several long-term sources of finance for expansion into new foreign markets.

  Long term financial planning

This assignment is designed for analyze Long term financial planning begins with the sales forecast and the key input in the long term fincial planning.

  Explain the role of fincial manager

This assignment explain the role of fincial manager, function of manger. And what are the motives of financial manager.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd