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