AutoSave on Q9 Cl?) 3 Rev ed 0822(6) Q waH 6 7 HCA 230 Pro ect Part 3 I Instructions Congratulations! You are almostnished 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 Ofcer 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 Statementfor the CFO to present atthe next Board of Tnistees 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 nancial statements for both facilities. You are to calculate the ratios for Pima Medical Center for the lastthree scal 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 nancial statements will serve as the quantitative portion ofthe 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 (or Pima Medical Center. you should be able to comment on trends over those three years and because you have the lastscal 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 Worksheet Title How To Use Ratio List Reference for answering questions 8 Hospital Financial Statements 01 ID Financial Statements Needed 10 11 12 13 14 15 16 17 18 Reference for answering questions Identify which nancial statements are needed. Use the drop-down menu to make selections. ()2 Ratio Calculations Calculate the ratios provided using the data on the Hospital Financial Statement tab and your answers to Q1. 03 Summary and Analysis Review the summary to ensure that yourwork 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? Q4 Common Size Financial Statements 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? 9 Instructions Ratio List Hospital Financial Statements Ready :3 Accessibility: Investigate 01 ID Financial Stmts Needed ()2 Ratio Calculations 03 Summary and Analysis 04 Common Size Fin Statement + __ .+ Home Insert Draw Page Layout Formulas Data Review View 9 Tell me a Share Cl Comments v A Aria! v 14 v Wrap Text Number v ' l ' v L v v V ' p v [l3 v l l l v Paste 5 . , , , . , Merge & Came, ; V Conditlonal Format Cell Insert Delete Format Sort 3. Find & Analyze * Formatting as Table Styles Fllter Select Data A1 : e HCA230 Project Part 3 A a c D E F G | J K L M N 100% AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (6) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial v 12 AA = al Wrap Text v Number Paste BIU V DVAv E Merge & Center v $ ~ % 9 08 20 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data C10 X V fx 9481 A B C D E F H J K L M N 0 4 Income Statements Pima Medical Center Tucson Regional 2019 2020 2021 2021 Revenues $51,336 $51,533 58,752 11,789 Salaries and Benefits 23,560 23,874 26,779 5,411 Supplies 8,481 8,369 9,481 1,963 10 Other Operating Expenses 9,481 9.307 9,961 2.857 11 Depreciation/Amortization 2,596 2,721 2,853 458 12 Interest Expense 1,824 1,584 1,566 1,031 13 Total Operating Expenses 15,942 15,855 50,640 1 1,720 14 Operating Income 5,394 5,678 8,112 69 15 Income Taxes 1,099 1,043 2,112 14 16 Net Income 4,295 4,635 6,000 55 17 18 19 20 Note: Amounts in 1,000s of dollars 21 22 Balance Sheets 23 2019 2020 2021 202 24 Assets 25 Cash 621 1,793 1,451 1,676 26 Short-Term Investments 147 116 103 27 ounts Receivables, Net 7,333 7,159 8,095 1,927 28 Inventories 1,849 2,025 1,986 335 29 Other Current Assets 1,190 1.340 1,896 519 30 Total Current Assets 11,140 12,433 13,531 4.457 Net Property, Plant & Equipment 24,549 25,223 26,176 5,322 Long-Term Note Receivable 37 31 44 59 Intangible Assets 8,269 8,578 9,540 4,219 Total Assets 43,995 46,265 49,291 14,057 36 Liabilities & Shareholders' Equity 38 counts Payable 2,357 2,581 2,842 383 Short Term Debt 145 209 237 980 Current Portion of Long Term Debt 4,905 5,535 6,111 533 Other Current Liabilities 2,303 2,267 2,461 150 42 rued Pay 2,054 2,314 2,381 637 43 Total Current Liabilities 1,764 12,906 14,032 2,683 44 Long-Term Debt 23,107 20,301 23,765 11,033 45 Total Liabilities 34,871 33,207 37,797 13,716 46 47 A Instructions Ratio List Hospital Financial Statements Q1 ID Financial Stmts Needed Q2 Ratio Calculations Q3 Summary and Analysis Q4 Common Size Fin Statement + Ready iT Accessibility: Investigate + 100%AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (6) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial v 12 AA = de Wrap Text v Number LIX V V LY AP - O. V Z Paste E Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data C10 X V fx 9481 A B C D E G H J K L M N 0 Salaries and Benefits 23,560 23,874 26,779 5,41 Supplies 8,481 8,369 9,481 1,963 10 Other Operating Expenses 9,481 9,307 9,961 2,857 11 Depreciation/Amortization 2,596 2,72 2,853 458 12 nterest Expense 1,824 1,584 1,566 1,031 13 Total Operating Expenses 15,942 15,855 50,640 11,720 14 Operating Income 5,394 5,678 8,112 69 15 ne Taxes 1,099 1,043 2,112 14 16 Net Income 4,295 4,635 6,000 55 17 18 19 20 Note: Amounts in 1,000s of dollars 21 22 Balance Sheets 23 2019 2020 2021 2021 24 Assets 25 Cash 621 1,793 1,451 1,676 26 Short-Term Investments 147 116 103 27 Accounts Receivables, Net 7,333 7,159 8,095 1.927 28 nventories 1,849 2,025 1,986 335 29 Other Current Assets 1,190 1,340 1,896 519 30 Total Current Assets 11,140 12,43 13,531 4,457 31 Net Property, Plant & Equipment 24,549 25,223 26,176 5,322 Long-Term Note Receivable 37 31 44 59 Intangible Assets 8,26 8,578 9,540 4,219 Total Assets 43,995 46,265 19,291 14,057 Liabilities & Shareholders' Equity Accounts Payable 2,357 2,581 2.842 383 Short Term Deb 145 209 237 980 Current Portion of Long Term Debt 4,905 5,535 6,111 533 Other Current Liabilities 2,303 2,267 2,461 150 Accrued Payroll 2.054 2.314 2,381 637 Total Current Liabilities 11,764 12,906 14,032 2,683 Long-Term Debt 23, 107 20,30 23,765 11,033 45 Total Liabilities 34,871 33,207 37,797 13,716 46 F 47 48 Total Shareholders' Equity 9,124 13,058 11,494 341 49 50 Liabilities & Shareholders' Equity 43,995 46,265 49,291 14,057 51 D A Instructions Ratio List Hospital Financial Statements Q1 ID Financial Stmts Needed Q2 Ratio Calculations Q3 Summary and Analysis Q4 Common Size Fin Statement + Ready iJ Accessibility: Investigate + 100%AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (6) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial v 12 AA = ap Wrap Text Percentage LIX V V Paste BIUV DV Av E Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data D11 X V fx A B D E F G H K M N 0 P Q R UI AWN Financial Statements Analysis: What stands out; what should be investigated further Income Statements Pima Medical Center Tucson Regional Common Size 2019 2020 2021 2021 Basis: Percent of Revenue Revenues 100.0% 100.0% 100.0% 100.0% Salaries and Benefits 45.9% 10 Supplies 16.5% 11 Other Operating Expenses 12 Depreciation/Amortization 13 Interest Expense 14 Total Operating Expenses 15 Operating Income 16 Income Taxes 17 Net Income 18 19 20 Balance Sheets 21 Common Size 2019 2020 2021 2021 22 Basis: Percent of Total Assets Assets 23 Cash 1.4% 3.9% 2.9% 11.9% 24 Short-Term Investments 25 Accounts Receivables, Net 26 Inventories 27 Other Current Assets 28 Total Current Assets 29 30 |Net Property, Plant & Equipment 31 Long-Term Note Receivable 32 Intangible Assets 33 Total Assets 100.0% 100.0% 100.0 100.0% 34 A Instructions Ratio List Hospital Financial Statements Q1 ID Financial Stmts Needed Q2 Ratio Calculations Q3 Summary and Analysis Q4 Common Size Fin Statement + Ready iJ Accessibility: Investigate + 100%AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (6) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial v 12 AA =1 ae Wrap Text Percentage LIX V V v Z E $ ~ % " 08 20 Conditional Format Cell Insert Delete Format Sort & Analyze Paste BIU BY Merge & Center v Find & Formatting as Table Styles Filter Select Data D11 X V fx A C D E F G H J K L M N 0 P Q R 20 Balance Sheets 21 Common Size 2019 2020 2021 2021 22 Basis: Percent of Total Assets Assets 23 Cash 1.4% 3.9% 2.9% 11.9% 24 Short-Term Investments 25 Accounts Receivables, Net 26 Inventories 27 Other Current Assets 28 Total Current Assets 29 30 |Net Property, Plant & Equipment 31 Long-Term Note Receivable 32 Intangible Assets 33 Total Assets 100.0% 100.0% 100.0% 100.0% 34 35 Liabilities & Shareholders' Equity 36 Accounts Payable 37 Short Term Debt 38 Current Portion of Long Term Deb 39 Other Current Liabilities 40 Accrued Payroll Total Current Liabilities 42 Long-Term Debt 43 Total Liabilities 45 46 Total Shareholders' Equity 47 48 Liabilities & Shareholders' Equity 100.0% 100.0% 100.0% 100.0% A Instructions Ratio List Hospital Financial Statements Q1 ID Financial Stmts Needed Q2 Ratio Calculations Q3 Summary and Analysis Q4 Common Size Fin Statement + Ready iJ Accessibility: Investigate + 100%