1 2 A B HCA 230 Project Part 3 3 Instructions 4 Congratulations! You are almost finished with your internship and your hard work has been noticed! For the third part of your project, you have been promoted to Senior Intern. The hospital Chief Financial Officer wants to know how Pima Medical Center is doing compared to its primary competitor, Tuscon Regional. You have been asked to prepare a Ratio Analysis and Common Size Financial Statement for the CFO to present at the next Board of Trustees meeting. The presentation needs to cover both quantitative measures and qualitative analysis. You have been provided with a list of ratios that the CFO wants you to compute and the necessary financial statements for both facilities. You are to calculate the ratios for Pima Medical Center for the last three fiscal years and for Tucson Regional for the last year. In addition, you need to write a description of what each ratio means, as this will help the CFO answer any questions the Board may have. The ratio analysis and common size financial statements will serve as the quantitative portion of the presentation. For the qualitative portion, you have been asked to analyze the numbers and make assessments based on those numbers. For example, because you have three years of data for Pima Medical Center, you should be able to comment on trends over those three years and because you have the last fiscal year for both Pima Medical Center and Tuscon Regional, you can comment on which is doing better and why. The name and description of each worksheet you will use for this project are below. Please be sure to complete data entry on each 5 of the four worksheets that requires your input. 6 Worksheet Title 7 Ratio List Hospital Financial Statements Q1 ID Financial Statements Needed 9 Q2 Ratio Calculations 10 Q3 Summary and Analysis 11 Q4 Common Size Financial Statements 2 How To Use Reference for answering questions Reference for answering questions Identify which financial statements are needed. Use the drop-down menu to make selections. Calculate the ratios provided using the data on the Hospital Financial Statement tab and your answers to Q1. Review the summary to ensure that your work automatically transferred to this sheet. Make quantitative and qualitative comments. What is the trend? Is it good or bad? How is Pima Medical Center doing compared to Tucson Regional? Prepare a common size income statement and balance sheet. Make quantitative and qualitative comments. What is the trend? Is it good or bad? How is Pima Medical Center doing compared to Tucson Regional? 13 C