finance homework ( budget analysis, tax, Revenues and Expenditures)

Please read the entire instructions before beginning this assignment. Small Assignment 2 This assignment is due by 6 p.m. on Wednesday, October 5. Please submit this assignment through the course menu item labeled “Small Assignments.” Mouse click on Assignment 2; it is a link to the area where you can browse to and upload your files. 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).”

Figure 1 – City of Hometown FY 2016 Budgeted Figures Revenues Property Taxes $1,984,946 Sales Taxes $1,215,360 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% Homestead Exemptions $12,000,000 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% Homestead Exemptions 0.0%

How to Design Your Spreadsheet File Please format your Excel file as shown below. Title Your Name Date Assumptions Xxxxxx #### Xxxxxxx #### Etc. Budget --------------- projected ------------------------ Property Tax Data 2016 2017 2018 2019 2020 2021 Market Value ### formulas that you copy through to 2021 Assessment Ratio Assessed Value Homestead Exemptions 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 learn how to use the absolute and relative copy commands prior to doing this assignment. Otherwise, this assignment will take you many, many hours. If you need help with the copy command, please see the lynda.com video (see information on the syllabus about lynda.com).