BD AutoSave C Home Insert Draw Page Layout Formulas Data Review View Automate Case Study Developer...

Transcribed Image Text:

BD AutoSave C Home Insert Draw Page Layout Formulas Data Review View Automate Case Study Developer Excel QM Tell me Comments Share Calibri (Body) 11 A A Wrap Text General Paste B I U v Merge & Center $ % 9 .00 0 Conditional Format Formatting as Table Cell Styles Insert Delete Format M Sort & Filter Find & Select Add-ins Analyze Data Q34 fx A B C D E F G H | J K L M N P Q R S T U V W Table 1 123456 Panther Canyon Community Hospital Balance Sheet as of 12/31/22 Current Assets Cash 7 Temporary Investments Accounts Receiveable less allowances $280,000 30,000 8,860,000 8 Inventory 140,000 9 Prepaid expense 41,000 10 Total Current Assests 9,350,000 11 12 Selected Industry Financial and Productivity Ratios for 100-199 Bed Hospitals in Texas Table 6 Financial Ratios PCHH Benchmark Liquidity Ratios Current Ratio 2.08 Collection Ratio 57.30 PCCH Operating Expenses, 2023 Employee Compensatic 142,274,566 Employee benefits Benefits 113,288,128 28,986,438 Supplies, drugs, service 16,991,384 Days Cash on Hand, short-term resources 11.60 Estimated bad debt 9,173,042 Days cash on hand, all sources 15.20 Depreciation Expense 3,467,769 Payment Ratio 50.40 Interest expense 1,155,923 Table 2 Current Liabilities 22 Accounts Payable 23 Notes Payable 24 Accrued Expenses payable 25 Deferred Revenues 26 Esimated third-party adjustments 27 Current portion of long-term debt Noncurrent Assets 13 Land, plant, equipment 14 Accumulated depreciation 15 Land, plant, and equipment (net) 16 long-term investments 17 Other non-current investments 18 19 20 21 Total noncurrent investments Total Assests 60,580,000 60,000,000 580,000 400,000 1,030,000 $10,380,000 Profitability Ratio Operating Margin 4.92 Table 3D Excess Margin 5.54 Hospital (Gross) Revenue per Patient Day FY 2024 50,000 Return on Net Assets 9.31 Service PCCH PGH UDH Effiency Ratios Inpatient Services Total assest turnover 1.21 Medical $2,114 $2,220 $2,008 Age of plant 9.10 Surgical 2,404 2,524 2,884 28 29 30 475,500 427,500 787,500 0 292,500 650,000 Total Current Liabilties 2,533,000 Fixed asset turnover 2.70 ICU/CCU 3,134 3,291 2,977 Current asset turnover 3.51 Psychiatric 2,517 2,643 2,391 Inventory turnover 48.52 Obtetric (mother only) 1,548 1,625 1,471 Pediatric 2,215 2,326 2,104 Capital Ratios Outpatient Services Net asset financing 55.89 Emergency Charges 855 812 Long-term debt to capital 5.60 Debt service coverage 3.06 Noncurrent liabilities Cash Flow to debt 15.82 31 Long-term debt, net of current portion 5,547,000 32 33 34 Total noncurrent liabilities 5,547,000 Productivity Ratios Total Liabilities 8,180,000 Cost per patient day $2,883 37 35 Unrestricted net assets 36 Restricted net assets Total Net Assets 1,200,000 Nursing Service 1,000,000 Nursing hours per patient day 6.25 2,200,000 RN's as a percent of total nursing 32.20 38 39 Total Liabilities & Net Assets $10,380,000 40 41 42 43 44 Table 1 Table 2 Table 6 Table 3D Ready + A + 100% BD AutoSave C Home Insert Draw Page Layout Formulas Data Review View Automate Case Study Developer Excel QM Tell me Comments Share Calibri (Body) 11 A A Wrap Text General Paste B I U v Merge & Center $ % 9 .00 0 Conditional Format Formatting as Table Cell Styles Insert Delete Format M Sort & Filter Find & Select Add-ins Analyze Data Q34 fx A B C D E F G H | J K L M N P Q R S T U V W Table 1 123456 Panther Canyon Community Hospital Balance Sheet as of 12/31/22 Current Assets Cash 7 Temporary Investments Accounts Receiveable less allowances $280,000 30,000 8,860,000 8 Inventory 140,000 9 Prepaid expense 41,000 10 Total Current Assests 9,350,000 11 12 Selected Industry Financial and Productivity Ratios for 100-199 Bed Hospitals in Texas Table 6 Financial Ratios PCHH Benchmark Liquidity Ratios Current Ratio 2.08 Collection Ratio 57.30 PCCH Operating Expenses, 2023 Employee Compensatic 142,274,566 Employee benefits Benefits 113,288,128 28,986,438 Supplies, drugs, service 16,991,384 Days Cash on Hand, short-term resources 11.60 Estimated bad debt 9,173,042 Days cash on hand, all sources 15.20 Depreciation Expense 3,467,769 Payment Ratio 50.40 Interest expense 1,155,923 Table 2 Current Liabilities 22 Accounts Payable 23 Notes Payable 24 Accrued Expenses payable 25 Deferred Revenues 26 Esimated third-party adjustments 27 Current portion of long-term debt Noncurrent Assets 13 Land, plant, equipment 14 Accumulated depreciation 15 Land, plant, and equipment (net) 16 long-term investments 17 Other non-current investments 18 19 20 21 Total noncurrent investments Total Assests 60,580,000 60,000,000 580,000 400,000 1,030,000 $10,380,000 Profitability Ratio Operating Margin 4.92 Table 3D Excess Margin 5.54 Hospital (Gross) Revenue per Patient Day FY 2024 50,000 Return on Net Assets 9.31 Service PCCH PGH UDH Effiency Ratios Inpatient Services Total assest turnover 1.21 Medical $2,114 $2,220 $2,008 Age of plant 9.10 Surgical 2,404 2,524 2,884 28 29 30 475,500 427,500 787,500 0 292,500 650,000 Total Current Liabilties 2,533,000 Fixed asset turnover 2.70 ICU/CCU 3,134 3,291 2,977 Current asset turnover 3.51 Psychiatric 2,517 2,643 2,391 Inventory turnover 48.52 Obtetric (mother only) 1,548 1,625 1,471 Pediatric 2,215 2,326 2,104 Capital Ratios Outpatient Services Net asset financing 55.89 Emergency Charges 855 812 Long-term debt to capital 5.60 Debt service coverage 3.06 Noncurrent liabilities Cash Flow to debt 15.82 31 Long-term debt, net of current portion 5,547,000 32 33 34 Total noncurrent liabilities 5,547,000 Productivity Ratios Total Liabilities 8,180,000 Cost per patient day $2,883 37 35 Unrestricted net assets 36 Restricted net assets Total Net Assets 1,200,000 Nursing Service 1,000,000 Nursing hours per patient day 6.25 2,200,000 RN's as a percent of total nursing 32.20 38 39 Total Liabilities & Net Assets $10,380,000 40 41 42 43 44 Table 1 Table 2 Table 6 Table 3D Ready + A + 100%