Assignment -
This assignment has three parts.
Part I - Cost Analysis
Assume that you are the Budget Director for the City of Hometown. You have been provided with the following information about the City of Hometown's ambulance service:
- The City of Hometown currently uses tax revenues to finance the provision of ambulance services to city residents.
- Currently, ambulance services are provided utilizing city staff.
- The direct cost of providing ambulance services is $500,000 per year.
- Approximately 3,000 people utilize the ambulance services per year (assume one trip per user).
- The city has an indirect cost rate equal to $0.35.
Due to budgetary pressures, the City Council (the city's legislative body) is considering two proposals: (1) to charge residents who utilize ambulance services, and/or (2) to contract with a private firm to provide ambulance services instead of relying on city staff.
1. If the city decided to charge residents who utilize city ambulance services, how high would the charge need to be per trip in order to recover the full cost of providing the ambulance services? (Please show your calculations.)
2. What factors other than revenue generation and cost should the city council consider when evaluating whether to charge residents the full cost of an ambulance trip?
3. A private contractor has submitted a proposal to provide ambulance services to city residents at a cost of $200 per trip. From a cost perspective, would it be cheaper for the city to continue to provide ambulance services using city staff or to contract for this service? Explain. (For this question, you do not need to address whether residents would be charged for the use of an ambulance.)
4. What factors other than cost should the city council consider when evaluating whether to contract with a private firm to provide ambulance services instead of relying on city staff?
Part II - Generally Accepted Accounting Principles
Assume that a city government follows generally accepted accounting principles (GAAP). In which fund or funds should one look to find each of the items listed below? Please list the particular fund, e.g., special revenue fund, rather than the broader category, e.g., governmental funds.
1. The revenues received from a grant that is legally restricted to be used for Homeland Security purposes
2. The costs associated with the capital construction of a new downtown parking garage (assume that the parking operations are financed through user charges)
3. The payment of interest on general obligation bonds
4. Salaries for the mayor's staff
5. Total salaries for all city employees
6. The costs of the building maintenance department (assume that the building maintenance department provides janitorial and repair services to city departments on a user charge basis)
7. Construction costs for a new fire station
8. The total of amount of grants received by the city
Part III - Financial Reporting
For the spreadsheet portions of this assignment (Question #1 part B and Question #2 parts A-D), please use the Small Assignment 3 Excel workbook that is posted in the Small Assignments link from the course menu. Put question #1 in the first worksheet (which currently is blank) and question #2 in the second worksheet (which has headings listed). The worksheets are listed at the bottom of the spreadsheet once you open an Excel file. If you are unfamiliar with worksheets, please feel free to email me for additional clarification.
You will need to turn in (1) the Excel file and (2) a text file. The text file should be used for all of the questions/parts except the ones listed above.
Question 1 - (Part B should be completed in the Excel spreadsheet.)
You have been asked to assist in an analysis of the financial condition of the City of Hometown.
A. Explain why it is helpful to convert current dollars into constant dollars when examining trends in the city's revenues over time.
B. Using an Excel spreadsheet and the data listed below, calculate (i) the amount of revenues per capita in constant dollars for each of the years 2014-2017, and (ii) the annual percentage change in the amount of revenues per capita expressed in constant dollars for each of the years 2015-2017.
|
Year
|
Revenues Per Capita (in current dollars)
|
CPI
|
|
2014
|
$700
|
110
|
|
2015
|
$750
|
113
|
|
2016
|
$790
|
118
|
|
2017
|
$820
|
122
|
C. Given the figures noted in Part B, what concerns might you have about the City of Hometown's revenues per capita? What additional information would you want to obtain to help you further analyze the revenues?
Question 2 - For parts A-D, you should complete the necessary steps within the Excel spreadsheet file.
You will need to get data from a Comprehensive Annual Financial Report (CAFR) for the City of Detroit. The last section of the CAFR, the statistical section, contains 10-year historical data for key financial, economic, and demographic figures.
A) Using the Comprehensive Annual Financial Report for the Year Ended June 30, 2017 for the City of Detroit, Michigan, fill in the following columns shown in the attached Excel spreadsheet file:
- Net general obligation bonded debt outstanding per capita in current dollars, 2008-2017 (Column D in the spreadsheet)
- Debt service expenditures in current dollars, 2008-2017 (Column F in the spreadsheet).
- General government revenues in current dollars, 2008-2017 (Column G in the spreadsheet).
You will find this information towards the end of the report in the Statistical Section. You can refer to the Table of Contents of the report to help you locate this information. (Hint: Useful historical data on revenues and expenditures are available in the statement called "Changes in Fund Balances, Governmental Funds.")
B) Calculate debt service expenditures as a % of general government revenues (Column H in the spreadsheet)
C) Use the Internet to find the Consumer Price Index (CPI) for the years 2008-2017 and input those figures into Column B of the spreadsheet. Although these are in calendar years and the CAFR data are in fiscal years, use calendar year 2008 for fiscal year 2008, calendar year 2009 for fiscal year 2009, and so on.
D) Using the information covered in the lecture, input formulas into the Excel spreadsheet to
- convert the CPI to a CPI with a base year equal to 2017 (Column C)
- convert the net general obligation debt per capita in current dollars into constant dollars (Column E)
E) Prepare two graphs in Excel:
- a line graph that shows the net general obligation debt per capita in constant dollars for the years 2008-2017
- a line graph that shows debt service expenditures as a % of general government revenues for the years 2008-2017
Be sure to properly label your graphs.
F) Describe the trends shown in each of the two graphs. Would you consider the trends to be favorable, unfavorable, or mixed trends? Explain. What additional information would you like to have? Based on these trends, what suggestions would you make for future research, discussion, or action by city officials?
Attachment:- Assignment Files.rar