AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (7) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial v 12 AA = al Wrap Text v General LIX V V V Z Paste BIUV DVAv E Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data K25 fx A B C D E F H J K L M N 0 3 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 Other Operating Expenses 9,481 9.307 9,961 2.857 Depreciation/Amortization 2,596 2,721 2,853 458 Interest Expense 1,824 1,584 1,566 1,031 Total Operating Expenses 45,942 5,85 50,64 11,720 13 Operating Income 5,394 5,678 8,112 69 14 ncome Taxes 1,099 1,043 2,112 14 15 Net Income 4,295 4,635 6,000 55 16 17 Note: Amounts in 1,000s of dollars 18 19 Balance Sheets 20 2019 2020 2021 202 21 Assets 22 Cash 621 1,793 1,451 1,676 23 Short-Term Investments 147 116 103 24 Accounts Receivables, Net 7,333 7,159 8,09 1,927 25 Inventories 1,849 2,025 1,986 335 26 er Current Assets 1,190 1,340 1,896 519 27 Total Current Asset 11,140 12,433 13,531 4,457 28 29 Net Property, Plant & Equipment 24,549 25,223 26,176 5,322 30 Long-Term Note Receivable 37 31 44 59 Intangible Assets 3,269 8,578 9,540 4,219 Total Assets 43,995 5.265 19,291 14,057 Liabilities & Shareholders' Equity Accounts 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 5,111 533 Other Current Liabilities 2,303 2,267 2,461 150 39 Accrued Payrol 2.054 2,314 2,381 637 40 Total Current Liabilities 11,764 12,906 14,032 2,68 Long-Term Debt 23,107 20,301 23,765 11,033 42 Total Liabilities 34,871 33,207 37,797 13,716 43 44 Total Shareholders' Equity 9,124 13,058 11,494 341 45 46 Liabilities & Shareholders' Equity 43,995 46,265 49,291 14,057 D & Instructions Ratio List Hospital Financial Statements Q1 ID Financial Stmts Needed Q2 Ratio Calculations Q3 Summary and Analysis Q4 Common Size Fin Statement Sheet1 + Ready iT Accessibility: Investigate + 100%AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (7) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial v 12 AA ap Wrap Text v General LIX V V Paste BIU BY MVAv E Merge & Center v $ ~ % 9 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data D30 fx A D E F G H I K L M N P Q R 1 Question 1: Identify the financial statement(s) needed to find each number Financial Statement(s) Needed Ratio Name Select from drop-down list Return on Assets Both Needed Correct Debt Ratio Balance Sheet Only Correct Profit Margin Income Statement Only Correct Current Ratio Balance Sheet Only Correct Acid Test Ratio Balance Sheet Only Correct Inventory Turnover Both Needed Correct Days Payable Both Needed Correct G A W N J J O O V O U A W N - Days AR Both Needed Correct AR Turnover Neither - Combo of Other Ratios Correct Times Interest Earned me Statement Only Correct Debt to Equity Ratio Balance Sheet Only Correct Cash Conversion Cycle Neither - Combo of Other Ratios Correct Operating Margin Income Statement Only Correct Days in Inventory Neither - Combo of Other Ratios Correct Days Cash on Hand Both Needed Correct Score 15 out of 15 38 39 43 44 Ratio List Hospital Financial Statements Q1 ID Financial Stmts Needed Q2 Ratio Calculations Q3 Summary and Analysis Q4 Common Size Fin Statement Sheet1 + Instructions Ready IT Accessibility: Investigate + 100%AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (7) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial 12 AA ab Wrap Text v General ZY AP - O. v Z Paste B I UVV DVAv EE Merge & Center v $ ~ % " Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data A17 4 X V fx Calculate the given 15 Ratios for BOTH hospitals. A B C D E F 11 payables. 12 Pima Medical Center Tucson Regional 13 Procedure Step FY2019 FY2020 FY2021 FY2021 14 2. Use excel formulas to do the calculation 0.946957 0.963350 0.964296 1.661200 15 3. Format the answer 0.95 0.96 0.96 1.66 16 Calculate the given 15 Ratios for BOTH hospitals. 17 18 Ratio Name Procedure Step 1. Write the for Explain what the formula means 19 Acid Test Ratio 20 21 Pima Medical Center Tucson Regional 22 Procedure Step FY2019 FY2020 FY2021 FY2021 23 2. Use excel formulas to do the calculation 24 3. Format the answer 25 26 27 Ratio Name Procedure Step 1 rite the for Explain what the formula means 28 Profit Margin 29 30 Pima Medical Center Tucson Regional 31 Procedure Step FY2019 FY2020 FY2021 Y2021 32 2. Use excel formulas to do the calculation 33 3. Format the answer 34 35 36 Ratio Name Procedure Step 1: Write the formula Explain what the formula means 37 Operating Margin 38 20 Dima Medical Center Tucson Roninnal 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 Sheet1 + Ready & Accessibility: Investigate + 100%AutoSave OFF A ? CE ... HCA230_Project_Part_3 Revised 0822 (7) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial v 12 AA ab Wrap Text v General ZY AP - O. v Z Paste B I U V V DVAv EE Merge & Center v $ ~ % 9 Conditional Format Cell Insert Delete Format Sort & Find & Analyze Formatting as Table Styles Filter Select Data A17 4 X V fx Calculate the given 15 Ratios for BOTH hospitals. A B C D E F 38 39 Pima Medical Center Tucson Regional 40 Procedure Step FY2019 FY2020 FY202 FY2021 41 2. Use excel formulas to do the calculation 42 3. Format the answer 43 44 45 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Return on Assets 47 48 Pima Medical Center Tucson Regional 49 Procedure Step FY2019 FY2020 FY2021 FY2021 50 2. Use excel formulas to do the calculation 51 3. Format the answer 54 Ratio Name Procedure Step 1: Write the formula Explain what the formula means 65 Inventory Turnover 56 57 Pima Medical Center Tucson Regional 58 Procedure Step FY2019 FY2020 FY2021 FY2021 59 2. Use excel formulas to do the calculation 3. Format the answer 61 62 63 Ratio Name Procedure Step 1: Write the formula Explain what the formula means 64 Days in Inventory 65 66 Pima Medical Center Tucson Regional 67 Procedure Steps FY2019 FY2020 FY2021 FY202 68 2. Use excel formulas to do the calculation 3. Format the answer Instructions Ratio List Hospital Financial Statements Q1 ID Financial Stmts Needed Q2 Ratio Calculations Q3 Summary and Analysis Q4 Common Size Fin Statement Sheet1 + Ready iT Accessibility: Investigate + 100%