D37 A B 1 Financial Ratios Assignment 2 3 4 Instructions Answer all questions below. The statements start at row 18 and the questions start at row 80. A Detroit Free Press Story that inspired the questions is in row 16. 6. For each question, there are three required parts: 7 (1) calculate the ratio for both years, 2012 and 2011 8 (2) comment on whether performance has gotten better or worse based on the ratio. 9 (3) provide a direct answer to the question. 10 Grading will be related to the "quality" of the answer. A simple "yes" or "no" will not yield a high grade. 11 Eventhough the questions deal with the same topic - treat each question as being separate. Don't use the data or information for one question on the others. 13 Insert rows between the questions to permit space for your answers. 14 Please insert rows rather than creating additional worksheets. 15 16 17 Henry Ford Health System 2012 System Report 18 Note: Components of the financial statements attributable to Health Alliance Plan, the wholely owned HMO of the Henry Ford 19 Health System are labeled (HAP) 20 21 CONSOLIDATED BALANCE SHEETS 22 December 31, 2012 and 2011 (In Thousands) 2012 2011 23 ASSETS 24 Current Assets: 25 Cash and cash equivalents 266,960 226,068 26 Cash and cash equivalents (HAP) 209,028 27 Short-term investments 118,010 16,211 2. Net Patient rare rareivables 14,261 Sheet1 Sheet2 174 524 Sheet3 195 592 0 0 B lige 7. 14 IO Financial+Ratios+Assignment (2) - Protected View Search AutoSave Of Insert File Home Draw Page Layout Formulas Review Data View Help Enable E PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. D37 fx A 18 Note: Components of the financial statements attributable to Health Alliance Plan, the wholely owned HMO of the Henry Ford 9 Health System are labeled (HAP) 2012 2011 266,960 209,028 16,211 174,534 43,020 208,312 918,065 226,068 118,010 14,261 195,592 50,479 175,351 779,761 21 CONSOLIDATED BALANCE SHEETS 22 December 31, 2012 and 2011 (In Thousands) 23 ASSETS 24 Current Assets: 25 Cash and cash equivalents 26 Cash and cash equivalents (HAP) 27 Short-term investments 28 Net Patient care receivables 29 Health care premium receivables (HAP) 30 Other current assets 31 Total Current Assets 32 33 LONG-TERM INVESTMENTS 34 ASSETS LIMITED AS TO USE 35 ASSETS LIMITED AS TO USE (HAP) 36 JOINT VENTURES AND OTHER ASSETS 37 PROPERTY, PLANT AND EQUIPMENT 38 (Accumulated Depreciation) 39 NET PROPERTY, PLANT AND EQUIPMENT 40 TOTAL ASSETS 41 42 LIABILITIES AND NET ASSETS 43 Current Liabilities 44 Current Liabilities (HAP) 45 Long-Term Debt 46 Other Long-Term Liabilities 47 TOTAL LIABILITIES 48 NET ASSETS 49 NET ASSETS (HAP) 50 TOTAL LIABILITIES AND NET ASSETS Sheet1 Sheet2 345412 608,390 249,842 116,726 2,815,104 (1,596,587) 1,218,517 3,454,652 460,767 470,480 329,399 100,628 2,628,995 (1,467,043) 1,161,952 3,302,987 430,943 270,435 817,971 497,828 2,017,177 1,206,020 374,483 259,328 827,923 481,157 1,942,891 1,121,536 238,560 3,302,987 231,455 Sheet3 3,454,652 o E g lip AutoSave Off HD Financial+Ratios +Assignment (2) - Protected View Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable D37 fx A B 2012 2011 51 52 CONSOLIDATED STATEMENT OF OPERATIONS 53 December 31, 2012 and 2011 (In Thousands) 54 UNRESTRICTED REVENUE 55 Patient service revenue 56 (Provision for uncompensated services) 57 Net patient service revenue 58 Health care premiums (HAP) 59 Investment income 60 Other income 61 Total Unrestricted Revenue 2,224,038 (158,729) 2,065,309 1,875,087 66,150 456,943 4,463,489 2,115,846 (147,992) 1,967,854 1,791,335 26,186 189,051 3,974,426 62 63 EXPENSES 64 Salaries, wages, and employee benefits 65 Salaries, wages, and employee benefits (HAP) 66 Healthcare provider expense (HAP) 67 Supplies and other expenses 68 Supplies and other expenses (HAP) 69 Depreciation and amortization 70 Total Expenses 1,356,884 145,758 1,380,862 1,036860 326,634 168,568 4,415,566 1,299,574 140,379 1,398,888 693,106 228,289 145,278 3,905,514 72 EXCESS OF REVENUE OVER EXPENSES 73 EXCESS OF REVENUE OVER EXPENSES (HAP) 74 UNUSUAL ITEMS 25 EXCESS OF REVENUE OVER EXPENSES 76 26,090 21,833 7,425 55,348 45,133 23,779 (4,263) 64,649 8 1. Calculating the Total Margin, separately for (a) Health Alliance Plan and (b) the Henry Ford Health System excluding Health Alliance -9 Plan, does it appear that Henry Ford Health System has "the wherewithal to go it alone"? 2012 2011 0 1 Sheet1 Sheet2 Sheet3 X S lyje AutoSave ON A 2 dB Financial+Ratios+Assignment (2) - Protected View Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing D37 A B 77 78 2012 2011 1. Calculating the Total Margin, separately for (a) Health Alliance Plan and (b) the Henry Ford Health System excluding Health Alliance 79 Plon, does it appear that Henry Ford Health System has "the wherewithal to go it alone"? 80 81 82 84 85 86 87 88 39 20 1 23 14 1 2. Calculated Days Cash on Hand separately (a) using current asset accounts only and (b) including long-term investments and assets limited as to use, do you think that Henry Ford Health System has the liquidity to pay their bills? 5 7 Sheet1 Sheet2 Sheet3 2 0 E W S logo Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. D37 Enable Editing 06 D 3. Based upon the Debt Ratio, do you think that Henry Ford would have the ability to borrow money if they need to make additional investments associated with the Affordable Care Act? 07 08 09 10 11 12 13 14 15 16 17 4. Based upon the Average Age of Plant, do you think that they are keeping pace with technology? 18 20 21 1 22 3 24 os 26 27 5. Based upon the Current Ratio, do you think that they are able to meet their short term obligations and do you foresee any liquidity issues? 28 29 30 31 32 -- 33 34 35 36 30 37 6. Calculate the Return on Assets, separately for (a) Health Alliance Plan and (b) the Henry Ford Health System excluding Health Alliance Plan, does it appear that Henry Ford Health System has "the wherewithal to go it alone"? 38 139 240 Sheet1 Sheet2 Sheet3 D37 A B 1 Financial Ratios Assignment 2 3 4 Instructions Answer all questions below. The statements start at row 18 and the questions start at row 80. A Detroit Free Press Story that inspired the questions is in row 16. 6. For each question, there are three required parts: 7 (1) calculate the ratio for both years, 2012 and 2011 8 (2) comment on whether performance has gotten better or worse based on the ratio. 9 (3) provide a direct answer to the question. 10 Grading will be related to the "quality" of the answer. A simple "yes" or "no" will not yield a high grade. 11 Eventhough the questions deal with the same topic - treat each question as being separate. Don't use the data or information for one question on the others. 13 Insert rows between the questions to permit space for your answers. 14 Please insert rows rather than creating additional worksheets. 15 16 17 Henry Ford Health System 2012 System Report 18 Note: Components of the financial statements attributable to Health Alliance Plan, the wholely owned HMO of the Henry Ford 19 Health System are labeled (HAP) 20 21 CONSOLIDATED BALANCE SHEETS 22 December 31, 2012 and 2011 (In Thousands) 2012 2011 23 ASSETS 24 Current Assets: 25 Cash and cash equivalents 266,960 226,068 26 Cash and cash equivalents (HAP) 209,028 27 Short-term investments 118,010 16,211 2. Net Patient rare rareivables 14,261 Sheet1 Sheet2 174 524 Sheet3 195 592 0 0 B lige 7. 14 IO Financial+Ratios+Assignment (2) - Protected View Search AutoSave Of Insert File Home Draw Page Layout Formulas Review Data View Help Enable E PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. D37 fx A 18 Note: Components of the financial statements attributable to Health Alliance Plan, the wholely owned HMO of the Henry Ford 9 Health System are labeled (HAP) 2012 2011 266,960 209,028 16,211 174,534 43,020 208,312 918,065 226,068 118,010 14,261 195,592 50,479 175,351 779,761 21 CONSOLIDATED BALANCE SHEETS 22 December 31, 2012 and 2011 (In Thousands) 23 ASSETS 24 Current Assets: 25 Cash and cash equivalents 26 Cash and cash equivalents (HAP) 27 Short-term investments 28 Net Patient care receivables 29 Health care premium receivables (HAP) 30 Other current assets 31 Total Current Assets 32 33 LONG-TERM INVESTMENTS 34 ASSETS LIMITED AS TO USE 35 ASSETS LIMITED AS TO USE (HAP) 36 JOINT VENTURES AND OTHER ASSETS 37 PROPERTY, PLANT AND EQUIPMENT 38 (Accumulated Depreciation) 39 NET PROPERTY, PLANT AND EQUIPMENT 40 TOTAL ASSETS 41 42 LIABILITIES AND NET ASSETS 43 Current Liabilities 44 Current Liabilities (HAP) 45 Long-Term Debt 46 Other Long-Term Liabilities 47 TOTAL LIABILITIES 48 NET ASSETS 49 NET ASSETS (HAP) 50 TOTAL LIABILITIES AND NET ASSETS Sheet1 Sheet2 345412 608,390 249,842 116,726 2,815,104 (1,596,587) 1,218,517 3,454,652 460,767 470,480 329,399 100,628 2,628,995 (1,467,043) 1,161,952 3,302,987 430,943 270,435 817,971 497,828 2,017,177 1,206,020 374,483 259,328 827,923 481,157 1,942,891 1,121,536 238,560 3,302,987 231,455 Sheet3 3,454,652 o E g lip AutoSave Off HD Financial+Ratios +Assignment (2) - Protected View Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable D37 fx A B 2012 2011 51 52 CONSOLIDATED STATEMENT OF OPERATIONS 53 December 31, 2012 and 2011 (In Thousands) 54 UNRESTRICTED REVENUE 55 Patient service revenue 56 (Provision for uncompensated services) 57 Net patient service revenue 58 Health care premiums (HAP) 59 Investment income 60 Other income 61 Total Unrestricted Revenue 2,224,038 (158,729) 2,065,309 1,875,087 66,150 456,943 4,463,489 2,115,846 (147,992) 1,967,854 1,791,335 26,186 189,051 3,974,426 62 63 EXPENSES 64 Salaries, wages, and employee benefits 65 Salaries, wages, and employee benefits (HAP) 66 Healthcare provider expense (HAP) 67 Supplies and other expenses 68 Supplies and other expenses (HAP) 69 Depreciation and amortization 70 Total Expenses 1,356,884 145,758 1,380,862 1,036860 326,634 168,568 4,415,566 1,299,574 140,379 1,398,888 693,106 228,289 145,278 3,905,514 72 EXCESS OF REVENUE OVER EXPENSES 73 EXCESS OF REVENUE OVER EXPENSES (HAP) 74 UNUSUAL ITEMS 25 EXCESS OF REVENUE OVER EXPENSES 76 26,090 21,833 7,425 55,348 45,133 23,779 (4,263) 64,649 8 1. Calculating the Total Margin, separately for (a) Health Alliance Plan and (b) the Henry Ford Health System excluding Health Alliance -9 Plan, does it appear that Henry Ford Health System has "the wherewithal to go it alone"? 2012 2011 0 1 Sheet1 Sheet2 Sheet3 X S lyje AutoSave ON A 2 dB Financial+Ratios+Assignment (2) - Protected View Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing D37 A B 77 78 2012 2011 1. Calculating the Total Margin, separately for (a) Health Alliance Plan and (b) the Henry Ford Health System excluding Health Alliance 79 Plon, does it appear that Henry Ford Health System has "the wherewithal to go it alone"? 80 81 82 84 85 86 87 88 39 20 1 23 14 1 2. Calculated Days Cash on Hand separately (a) using current asset accounts only and (b) including long-term investments and assets limited as to use, do you think that Henry Ford Health System has the liquidity to pay their bills? 5 7 Sheet1 Sheet2 Sheet3 2 0 E W S logo Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. D37 Enable Editing 06 D 3. Based upon the Debt Ratio, do you think that Henry Ford would have the ability to borrow money if they need to make additional investments associated with the Affordable Care Act? 07 08 09 10 11 12 13 14 15 16 17 4. Based upon the Average Age of Plant, do you think that they are keeping pace with technology? 18 20 21 1 22 3 24 os 26 27 5. Based upon the Current Ratio, do you think that they are able to meet their short term obligations and do you foresee any liquidity issues? 28 29 30 31 32 -- 33 34 35 36 30 37 6. Calculate the Return on Assets, separately for (a) Health Alliance Plan and (b) the Henry Ford Health System excluding Health Alliance Plan, does it appear that Henry Ford Health System has "the wherewithal to go it alone"? 38 139 240 Sheet1 Sheet2 Sheet3