5 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Return on Assets Pima Medical Center Tucson Regional Procedure Step FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect Ratio Name Procedure Step 1: Write the formula Explain what the formula means Inventory Turnover Pima Medical Center Tucson Regional Procedure Step FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect Ratio Name Procedure Step 1: Write the formula Explain what the formula means Days in Inventory Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect IncorrectQuestion 2: Calculate the provided 15 ratios for BOTH hospitals . The first ratio calculation has been completed for you as an example. . Areas in which you are required to input data are shaded in blue. Ratio Calculation Procedure Steps Entering Excel Formula Help Video 1. Write the formula. https://youtu.be/4HdZx29SdoM 2. Use excel formulas to do the calculation * See help video - link in Cell D4 3. Format the answer 4. In the cell immediately below each set of calculations it will say "Correct" if your calculated value in part 2 is correct. If it is incorrect a red highlight will show "incorrect". Ratio Name Procedure Step 1. Write the formula Explain what the formula means (Example) Current Ratio Current Assets/Current Liabilities The current ratio is a liquidity ratio that measures a company's ability to pay short-term obligations or those due within one year. It tells investors and analysts how a company can maximize the current assets on its balance sheet to satisfy its current debt and other payables. Pima Medical Center Tucson Regional Procedure Step FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 0.946957 0.963350 0.964296 1.661200 3. Format the answer 0.95 0.96 0.96 1.66 Correct Correct Correct Correct Calculate the given 15 Ratios for BOTH hospitals.Pima Medical Center Tucson Regional Ratio FY2019 FY2020 FY2021 FY2021 Analysis Tucson is more liquid than Pima as it has 1.7X current assets to cover any cash needs in the next year compared to Current Ratio 0.9 1.0 1.0 1.7 Pima, which just has 1X. The Pima current ratio has only improvd modestly over the three years. Acid Test Ratio Profit Margin Operating Margin Return on Assets Inventory Turnover Days in Inventory Days in Accounts Receivabl AR Turnover Days in Accounts Payable Cash Conversion Cycle Debt Ratio Times Interest Earned Debt to Equity Ratio Days Cash on HandRatio Name Procedure Step 1: Write the formula Explain what the formula means Days in Accounts Receivable Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect Ratio Name Procedure Step 1: Write the formula Explain what the formula means AR Turnover Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect 10 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Days in Accounts Payable Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect IncorrectCalculate the given 15 Ratios for BOTH hospitals. 2 Ratio Name Procedure Step 1. Write the formula Explain what the formula means Acid Test Ratio Pima Medical Center Tucson Regional Procedure Step FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect Ratio Name Procedure Step 1. Write the formula Explain what the formula means Profit Margin Pima Medical Center Tucson Regional Procedure Step FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect Ratio Name Procedure Step 1: Write the formula Explain what the formula means Operating Margin Pima Medical Center Tucson Regional Procedure Step FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect IncorrectCash Short-Term Investments Accounts Receivables, Net Supplies Inventories Other Current Assets Total Current Assets Net Property, Plant & Equipment Long-Term Note Receivable Intangible Assets Total Assets 100.0% 100.0% 100.0% 100.0% Liabilities & Shareholders' Equity Accounts Payable Short Term Debt Current Portion of Long Term Debt Other Current Liabilities Accrued Payroll Total Current Liabilities Long-Term Debt Total Liabilities Total Shareholders' Equity Liabilities & Shareholders' Equity 100.0% 100.0% 100.0% 100.0%14 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Debt to Equity Ratio Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect 15 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Days Cash on Hand Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect IncorrectQuestion 1: Identify the financial statement(s) needed to find each number Financial Statement(s) Needed Ratio Name Select from drop-down list Income Statement Only Return on Assets Balance Sheet Only Debt Ratio Both Needed Profit Margin Neither - Combo of Other Ratios Current Ratio CO OO N O U A W N - Acid Test Ratio Inventory Turnover Days Payable Days AR AR Turnover 10 Times Interest Earned 11 Debt to Equity Ratio 12 Cash Conversion Cycle 13 Operating Margin 14 Days in Inventory 15 Days Cash on Hand Score out of 1511 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Cash Conversion Cycle Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect 12 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Debt Ratio Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer Incorrect Incorrect Incorrect Incorrect 13 Ratio Name Procedure Step 1: Write the formula Explain what the formula means Times Interest Earned Pima Medical Center Tucson Regional Procedure Steps FY2019 FY2020 FY2021 FY2021 2. Use excel formulas to do the calculation 3. Format the answer ncorrect ncorrect Incorrect ncorrectQuestion 4: Prepare a Common Size Analysis . Enter the relevant data for for each category. Categories shaded in yellow have been entered for you. . Complete the Analysis column (column J) 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. Students: In this section, the number gets highlighted in green Financial Statements if correct. If incorrect it returns to the original shade Analysis: What stands out; what should be investigated further Income Statements Pima Medical Center Tucson Regional (Be sure to comment on the trend and the comparison to Tucson.) Common Size 2019 2020 2021 2021 Basis: Percent of Revenue Revenues 100.0% 100.0% 100.0% 100.0% Salaries and Benefits Supplies Expense Other Operating Expenses Depreciation/Amortization Interest Expense Total Operating Expenses Operating Income Income Taxes Net Income Balance Sheets Common Size 2019 2020 2021 2021 Basis: Percent of Total Assets Assets Cash Short-Term Investments Accounts Receivables, Net Supplies Inventories Other Current Assets# Chapter Ratio Text Page Formula Liquidity Ratios LA Current Ratio 77 Total Current Assets / Total Current Liabilities Acid Test Ratio 92 (Cash and cash equivalent + Short-Term Investments + Accounts receivable, Net) / Total Current liabilities Profitability 3 3 Profit Margin 61 Net Income/Revenues 13 na Operating Margin Operating Income/Revenues 1 Return on Assets 26 Net Income/Total Assets Activity/Turnover 6 Inventory Turnover 112 Supplies Expense/Supplies Inventory na Days in Inventory 365 days / Inventory Turnover Ratio 8 Days in Accounts Receivable 148 Accounts Receivables, Net / (Revenues / 365 days) CO AR Turnover 164 365 days / Days in Accounts Receivable 7 Days in Accounts Payable 130 Accounts Payable/(Supplies Expense/365) 24 Cash Conversion Cycle 406 Days in Inventory + Days in Accounts Receivable - Days in Accounts Payables Solvency Ratios N 2 Debt Ratio 43 Total Liabilities / Total Assets 10 11 Times Interest Earned 197 EBIT / Interest Expense 11 14 Debt to Equity Ratio 244 Total Liabilities / Total Shareholders' Equity 15 na Days Cash on Hand Total Cash and unrestricted Investment / (Total Expenses - Depreciation - Amortization) Students: Please note that the terms here are a little different than in the text book to conform with the financial statements provided here. Also please not that for a couple of ratios you only have one dollar amount per year to use instead of an average. EBIT is earnings before deducting interest and income taxes.Hospital Financial Statements Financial Statements Income Statements Pima Medical Center Tucson Regiona 2019 2020 202 202 Revenues $51,336 $51,533 58,752 11,789 Salaries and Benefits 23,560 23, 874 26,779 5,411 Supplies Expense 8,481 8,369 9,481 Other Operating Expenses 1,963 9,481 9,307 9,961 Depreciation/Amortization 2,857 2,596 2,72 2,853 458 Interest Expense 1,824 1,584 1,566 Total Operating Expenses 1,031 45,942 45.855 50.640 11,720 Operating Income 5,394 5,678 8, 112 69 Income Taxes 1,099 1,043 2, 112 14 Net Income 4,295 4.635 6,000 55 Note: Amounts in 1,000s of dollars Balance Sheets 2019 2020 202 2021 Assets Cash 621 1,793 1,451 1,676 Short-Term Investments 147 116 103 Accounts Receivables, Net 7,333 7, 159 8,095 Supplies Inventory 1,927 1,849 2,025 1,986 Other Current Assets 335 1, 190 1,340 Total Current Assets 1,896 519 11, 140 12,433 3,531 4,457 Net Property, Plant & Equipment 24,549 25,223 26, 176 Long-Term Note Receivable 5,322 37 31 44 59 Intangible Assets 8,269 8,578 9,540 Total Assets 4,219 13,995 +6,265 19,291 14,057 Liabilities & Shareholders' Equity Accounts Payable 2,357 2,581 2,842 Short Term Debt 383 145 209 237 980 Current Portion of Long Term Deb 4,905 5,535 6, 111 Other Current Liabilities 533 2,303 2,267 2,461 Accrued Payroll 150 2,054 2,314 2,381 Total Current Liabilities 637 11,764 12,906 14,032 Long-Term Debt 2,683 23, 107 20,301 23,765 Total Liabilities 11,033 34,871 33,207 37,797 13,716 Total Shareholders' Equity 9,124 13,058 11,494 341 Liabilities & Shareholders' Equity 43,995 46,265 49,291 4,057