Please HELP

Show transcribed data

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 of the A1 :fx Hospital Financial Statements A2 fx Balance Sheets A1 fx Question 2: Calculate the provided 15 ratios for BOTH hospitals A B C D E Question 2: Calculate the provided 15 ratios for BOTH hospitals - The first ratio calculation has been completed for you as an example. - Areas in which vou are required to input data are shaded in blue. Ratio Calculation Procedure Steps 1. Write the formula. 2. Substitute numbers for the words. * You can skip this step if you use cell references linked to the Hospital Financial Statements worksheet as shown in cells B15-E15 in the example. 3. Do the math. 4. Format the answer. Calculate the given 15 Ratios for BOTH hospitals. \begin{tabular}{l|l|l|l|l|l} \hline \end{tabular} A1 fx Question 3: Provide a summary and analysis for each ratio Question 3: Provide a summary and analysis for each ratio - Ensure that your ratio calculations from Question 2 automatically transfer to this sheet. - Areas in which you are required to input data are shaded in blue. - The first row has been completed for vou as an example. Question 4: Prepare a Common Size Analysis - Eriter the relevant data for for each category. Calegories shaded in yellow have been entered for you. - Complete the Analysis column (column Jj based on your calculations. - Areas in which you are required to input data are shaded in blue - It is a good idea to use Excel cell references and formulas to reduce errors in calculations. A \begin{tabular}{|l} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} 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 of the A1 :fx Hospital Financial Statements A2 fx Balance Sheets A1 fx Question 2: Calculate the provided 15 ratios for BOTH hospitals A B C D E Question 2: Calculate the provided 15 ratios for BOTH hospitals - The first ratio calculation has been completed for you as an example. - Areas in which vou are required to input data are shaded in blue. Ratio Calculation Procedure Steps 1. Write the formula. 2. Substitute numbers for the words. * You can skip this step if you use cell references linked to the Hospital Financial Statements worksheet as shown in cells B15-E15 in the example. 3. Do the math. 4. Format the answer. Calculate the given 15 Ratios for BOTH hospitals. \begin{tabular}{l|l|l|l|l|l} \hline \end{tabular} A1 fx Question 3: Provide a summary and analysis for each ratio Question 3: Provide a summary and analysis for each ratio - Ensure that your ratio calculations from Question 2 automatically transfer to this sheet. - Areas in which you are required to input data are shaded in blue. - The first row has been completed for vou as an example. Question 4: Prepare a Common Size Analysis - Eriter the relevant data for for each category. Calegories shaded in yellow have been entered for you. - Complete the Analysis column (column Jj based on your calculations. - Areas in which you are required to input data are shaded in blue - It is a good idea to use Excel cell references and formulas to reduce errors in calculations. A \begin{tabular}{|l} \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular}