FINANCE GEN PAD 504

Small Assignment 3 This assignment is due by 6 p.m. on Wednesday, November 9. Please submit this assignment through the course menu item labeled “Small Assignments.” Mouse click on Assignment 3; it is a link to the area where you can browse to and upload your files. This assignment has three parts. Please put your text answers to all three parts in one file and your spreadsheet answers to Part III in one Excel workbook. Be sure to include your name within each file. 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 $300,000 per year. • Approximately 2,000 people utilize the ambulance services per year (assume one trip per user). • The city has an indirect cost rate equal to $.40. 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 $180 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. l. 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. Explain why it is helpful to convert current dollars into constant dollars when examining trends in the city’s revenues over time. 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 2012-2015, and (ii) the annual percentage change in the amount of revenues per capita expressed in constant dollars for each of the years 2013-2015. Revenues Per Capita year (in current dollars) CPI 2012 $700 110 2013 $750 113 2014 $790 118 2015 $820 122 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 Chicago. 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 December 31, 2015 for the City of Chicago, Illinois http://www.cityofchicago.org/content/dam/city/depts/fin/supp_info/CAFR/2015CAFR/2015CityCAFR.pdf, fill in the following columns shown in the attached Excel spreadsheet file: o General net bonded debt per capita in current dollars, 2006-2015 (Column D in the spreadsheet) o the ratio of debt service expenditures for general obligation debt to total governmental expenditures, 2006-2015 (Column F in the spreadsheet). You will find this information towards the end of the report in the Statistical Section.Part III would suggest that you refer to the Table of Contents of the report to help you locate this information. (Hint: you do not need to go to the table for the Debt Service Funds.) B) Use the Internet to find the Consumer Price Index (CPI) for the years 2006-2015 and input those figures into Column B of the spreadsheet. One place where you can get the CPI is https://www.minneapolisfed.org/community/teaching-aids/cpi-calculator-information/consumer-price-index-and-inflation-rates-1913. C) Using the information covered in the lecture, input formulas into the Excel spreadsheet to o convert the CPI to a CPI with a base year equal to 2015 (Column C) o convert the general net bonded debt per capita in current dollars into constant dollars (Column E). D) Prepare two graphs in Excel: o a line graph that shows the general net bonded debt per capita in constant dollars for the years 2006-2015 o a line graph that shows the ratio of debt service for general obligation bonded debt to general governmental expenditures for the years 2006-2015 Be sure to properly label your graphs. E) 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?