Prepare a line graph showing the budgeted total revenues

Reference no: EM131217581

Assignment

You will need to use Excel for this assignment. Your spreadsheet should be clearly labeled and designed so that it is easy to determine how the results change if any of the assumptions change.

You should turn in the following items:

(1) A file that contains your memo, along with the three graphs (The graphs can be put at the end of your memo or inserted into your memo.)

(2) An Excel file that contains two worksheets: (a) one that shows the Part I B table with the original assumptions and resulting surpluses or deficits for each year, and (b) one that shows for Part II how you derived the property tax rates needed to avoid a deficit.

SEE THE END OF THIS ASSIGNMENT for more instructions about how to lay out the Excel file.

You should review the following items: (1) guidelines for memos in the Readings folder,

(2) a short demonstration titled "Tips for Small Assignment 2" in the Lectures folder, and

(3) information on the syllabus about Resource for Help with Excel, specifically the information about how to access lynda.com. Lynda.com provides a series of short videos on how to use various software products, such as Excel.

MEMORANDUM

To: Budget Analyst

From: Richard Funderburg, Budget Director Subject: Budget Projections for the City of Hometown Date: September 21, 2016

Next month, the City Council will discuss the budget for the City of Hometown. Please prepare a memo to me that addresses the items listed below. Be sure to include the related graphs. I need this information before 6:00 p.m. October 5 so I may prepare for the meeting.

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 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.

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)."

Attachment:- MEMORANDUM.pdf

Reference no: EM131217581

Questions Cloud

Country ability to pass comprehensive tax reform : Based on the article : "Top 10 Reasons There Is No Corporate Tax Reform". Do you think any of these obstacles can be overcome? If so, which ones and how? How does the influence of special interest groups affect the country’s ability to pass comprehen..

Illustrate mick consumption choice in a diagram : Illustrate Mick's consumption choice in a diagram with health club usage on the horizontal axis and other recreational activities on the vertical axis.

Impediment to the international convergence of accounting : Various attempts have been made to reduce the accounting diversity that exists internationally. Which factor do you believe represents the greatest impediment to the international convergence of accounting? Which factor do you believe creates the sma..

Explain in your presentation what may affect negotiations : Explain in your presentation what may affect negotiations between business people from those nations. Investigate issues such as oculesics, proxemics, kinesics, chronemics and paralanguage, but also do not forget that historic or recent socio-poli..

Prepare a line graph showing the budgeted total revenues : 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.

About the liabilities : "Liabilities" Please respond to the following: For this week's eActivity, research one (1) publicly traded company in which you are interested using the Internet and/or Strayer databases. Locate the company Website and focus on the types of bonds the..

Determine the kvar rating of the capacitors : A bank of capacitors is connected in delta across the supply terminals and p.f. raised to 0.9 lagging.- Determine the kVAR rating of the capacitors connected in each phase.

Key recent global economic events : Outline what you think are some of the key recent global economic events which have impacted, or are impacting, on the domestic economy. These could be events which occurred up to 5 years ago but are still influencing the domestic economy.

Summarizes the meeting and offers some recommendations : Identification of the difficulties the managers may be having in establishing self-directed, high-performing teams and Identification of the successful characteristic of establishing self-directed, high-performing teams.

User Account

All Pages