Homework Assignment

profilescooter
copy_of_ga6.3_creekside.xls
Home>Business & Finance homework help>Homework Assignment

CASE1

CASE 1 Instructor Version Copyright 2006
7/7/05 by FACHE
CREEKSIDE COMMUNITY HOSPITAL (A)
Assessing Hospital Performance
The INPUT DATA section of the model contains historical financial and operating data. The OUTPUT
DATA section contains all of the relevant analyses. Note that the model extends out to Column K.
Note that the industry data used in this case are for instructional use only, and do not
represent actual industry data for the time period of the case.
INPUT DATA:
Statements of Operations:
(Millions of Dollars)
REVENUES 2001 2002 2003 2004 2005
Net patient service revenue $ 25.661 $ 27.236 $ 28.796 $ 30.576 $ 34.582
Other revenue 1.305 1.261 1.237 1.853 1.834
Total revenues $ 26.966 $ 28.497 $ 30.033 $ 32.429 $ 36.416
EXPENSES
Salaries and wages $ 10.829 $ 11.135 $ 12.245 $ 12.468 $ 13.994
Fringe benefits 1.496 1.731 1.830 2.408 2.568
Interest expense 1.341 1.305 1.181 1.598 1.776
Depreciation 1.708 1.977 2.350 2.658 2.778
Provision for bad debts 0.546 0.589 0.622 0.655 0.776
Professional liability 0.102 0.157 0.140 0.201 0.218
Other 7.874 8.389 9.036 10.339 11.848
Total expenses $ 23.896 $ 25.283 $ 27.404 $ 30.327 $ 33.958
Excess of revenues over expenses $ 3.070 $ 3.214 $ 2.629 $ 2.102 $ 2.458
Balance Sheets:
(Millions of Dollars)
ASSETS 2001 2002 2003 2004 2005
Cash and investments $ 3.513 $ 5.799 $ 4.673 $ 5.069 $ 2.795
Accounts receivable (net) 5.915 4.832 4.359 5.674 7.413
Inventories 0.338 0.403 0.432 0.523 0.601
Other current assets 0.693 0.294 0.308 0.703 0.923
Total current assets $ 10.459 $ 11.328 $ 9.772 $ 11.969 $ 11.732
Gross plant and equipment $ 37.999 $ 42.005 $ 47.786 $ 55.333 $ 59.552
Accumulated depreciation 8.831 10.092 11.820 14.338 17.009
Net plant and equipment $ 29.168 $ 31.913 $ 35.966 $ 40.995 $ 42.543
Total assets $ 39.627 $ 43.241 $ 45.738 $ 52.964 $ 54.275
LIABILITIES AND NET ASSETS
Accounts payable $ 1.068 $ 1.273 $ 0.928 $ 1.253 $ 1.760
Accruals 0.692 0.942 1.460 1.503 1.176
Current portion of LT debt 0.136 0.290 0.110 1.341 1.465
Total current liabilities $ 1.896 $ 2.505 $ 2.498 $ 4.097 $ 4.401
Long-term debt $ 15.959 $ 15.775 $ 15.673 $ 19.222 $ 17.795
Net assets (Equity) $ 21.772 $ 24.961 $ 27.567 $ 29.645 $ 32.079
Total liabilities and net assets $ 39.627 $ 43.241 $ 45.738 $ 52.964 $ 54.275
Revenue and Cost Breakdown:
2001 2002 2003 2004 2005
Gross patient revenue
Inpatient $ 25.161 $ 25.275 $ 26.117 $ 29.148 $ 33.216
Outpatient 4.748 5.969 6.535 9.130 11.912
Total gross patient revenue $ 29.909 $ 31.244 $ 32.652 $ 38.278 $ 45.128
Revenue deductions
Contractual allowances $ 2.489 $ 2.053 $ 1.729 $ 5.196 $ 7.516
Charity care 1.759 1.955 2.127 2.506 3.030
Total deductions $ 4.248 $ 4.008 $ 3.856 $ 7.702 $ 10.546
Net patient service revenue $ 25.661 $ 27.236 $ 28.796 $ 30.576 $ 34.582
Inpatient operating costs $ 18.635 $ 19.221 $ 20.573 $ 22.229 $ 24.771
Outpatient operating costs 5.261 6.062 6.831 8.098 9.187
Total patient costs $ 23.896 $ 25.283 $ 27.404 $ 30.327 $ 33.958
Selected Operating Data:
(Millions of Dollars)
2001 2002 2003 2004 2005
Medicare discharges 3,008 2,960 2,721 2,860 2,741
Total discharges 9,680 9,311 8,784 8,318 8,576
Outpatient visits 30,754 31,960 32,285 32,878 36,796
Licensed beds 210 210 210 210 210
Staffed beds 192 196 193 197 178
Patient days 45,296 45,983 44,085 42,434 40,062
Case mix index 1.2531 1.2674 1.2869 1.2993 1.3161
Employee FTEs 604.5 618.1 610.8 625.8 619.3
OUTPUT DATA:
Statements of Cash Flows:
(Millions of Dollars)
CASH FLOWS FROM OPERATIONS 2002 2003 2004 2005
Income from operations $ 3.214 $ 2.629 $ 2.102 $ 2.458
Noncash expenses 1.952 2.326 2.633 2.756
Change in accounts receivable 1.083 0.473 (1.315) (1.739)
Change in inventories (0.065) (0.029) (0.091) (0.078)
Change in other current assets 0.399 (0.014) (0.395) (0.220)
Change in accounts payable 0.205 (0.345) 0.325 0.507
Change in accruals 0.250 0.518 0.043 (0.327)
Change in current portion of LT debt 0.154 (0.180) 1.231 0.124
Net cash flow from operations $ 7.192 $ 5.378 $ 4.533 $ 3.481
CASH FLOWS FROM INVESTING ACTIVITIES
Fixed asset acquisitions $ (4.722) $ (6.402) $ (7.686) $ (4.328)
CASH FLOWS FROM FINANCING ACTIVITIES
Long-term debt $ (0.184) $ (0.102) $ 3.549 $ (1.427)
Net increase (decrease) in cash $ 2.286 $ (1.126) $ 0.396 $ (2.274)
Beginning cash and investments 3.513 5.799 4.673 5.069
Ending cash and investments $ 5.799 $ 4.673 $ 5.069 $ 2.795
Note: The noncash expenses and fixed asset acquisitions data in the statements of cash flows are somewhat different than
they would be if calculated directly from the other financial statements because of asset revaluations.
Financial Statement Analysis: Du Pont Analysis
2005 Ind Data (200-299 Beds)
2001 2002 2003 2004 2005 +Quartile Median -Quartile
Total margin 11.38% 11.28% 8.75% 6.48% 6.75% 5.58% 3.48% 0.53%
Total asset turnover 0.68 0.66 0.66 0.61 0.67 1.04 0.89 0.75
ROA = TM x TATO 7.75% 7.43% 5.75% 3.97% 4.53% 5.80% 3.10% 0.40%
Equity multiplier 1.82 1.73 1.66 1.79 1.69 2.70 1.94 1.54
ROE = ROA x Equity multiplier 14.10% 12.88% 9.54% 7.09% 7.66% 15.66% 6.01% 0.62%
Check ROE (Excess/Funds) 14.10% 12.88% 9.54% 7.09% 7.66% Note: The quartile values are based on the upper and
lower numerical values regardless of whether that
value is good or bad. The interpretation is
left to the analyst.
Financial Statement Analysis: Ratios
2005 Ind Data (200-299 Beds)
2001 2002 2003 2004 2005 +Quartile Median -Quartile
Profitability Ratios:
Deductible ratio 0.14 0.13 0.12 0.20 0.23 0.34 0.26 0.18
Total margin 11.38% 11.28% 8.75% 6.48% 6.75% 5.58% 3.48% 0.53%
Return on assets 7.75% 7.43% 5.75% 3.97% 4.53% 5.80% 3.10% 0.40%
Return on equity 14.10% 12.88% 9.54% 7.09% 7.66% 15.66% 6.01% 0.62%
Liquidity Ratios:
Current ratio 5.52 4.52 3.91 2.92 2.67 2.53 1.99 1.48
Days cash on hand 57.79 90.82 68.08 66.87 32.72 32.35 15.89 6.24
Debt Management Ratios:
Debt ratio 45.06% 42.27% 39.73% 44.03% 40.90% 62.90% 48.40% 35.20%
LT debt to equity 73.30% 63.20% 56.85% 64.84% 55.47% 127.00% 64.70% 26.90%
Times interest earned 3.29 3.46 3.23 2.32 2.38 4.29 2.23 1.14
Fixed charge coverage 2.99 2.83 2.95 1.26 1.31 2.18 1.35 1.02
Cash flow coverage 4.56 4.98 5.22 3.98 3.95 5.32 3.22 1.76
Asset Management Ratios:
Inventory turnover 79.78 70.71 69.52 62.01 60.59 98.68 63.95 43.99
Current asset turnover 2.58 2.52 3.07 2.71 3.10 3.94 3.38 2.88
Fixed asset turnover 0.92 0.89 0.84 0.79 0.86 2.20 1.76 1.49
Total asset turnover 0.68 0.66 0.66 0.61 0.67 1.04 0.89 0.75
Avgerage collection period 84.13 64.76 55.25 67.73 78.24 87.53 75.67 63.33
Average payment period 31.19 39.23 36.39 54.05 51.52 71.24 56.52 45.84
Other Ratios:
Average age of plant (years) 5.17 5.10 5.03 5.39 6.12 8.86 7.39 6.14
Operating Indicator Analysis:
2005 Ind Data (200-299 Beds)
2001 2002 2003 2004 2005 +Quartile Median -Quartile
Profit Indicators:
Profit per inpatient discharge $305.00 $301.98 $280.03 $126.72 $79.61 $89.04 ($21.30) ($120.08)
Profit per outpatient visit ($38.61) ($26.87) ($33.07) ($24.49) ($1.60) $6.22 $0.66 ($7.01)
Net Price Indicators:
Net price per discharge $2,230 $2,366 $2,622 $2,799 $2,968 $4,091 $3,411 $2,815
Net price per visit $132 $163 $179 $222 $248 $201 $139 $98
Medicare payment % 31.07% 31.79% 30.98% 34.38% 31.96% 43.47% 36.60% 31.25%
Bad debt/charity % 5.88% 6.26% 6.51% 6.55% 6.71% 7.89% 4.76% 2.97%
Contractual allowance % 8.32% 6.57% 5.30% 13.57% 16.65% 25.27% 20.02% 12.12%
Outpatient revenue % 15.87% 19.10% 20.01% 23.85% 26.40% 25.26% 21.03% 17.44%
Volume Indicators:
Occupancy rate 64.63% 64.28% 62.58% 59.01% 61.66% 67.12% 58.10% 47.84%
Average daily census 124.10 125.98 120.78 116.26 109.76 173.23 144.73 114.39
Length of Stay Indicators:
Length of stay (days) 4.68 4.94 5.02 5.10 4.67 6.80 6.07 5.41
Adj length of stay 3.73 3.90 3.90 3.93 3.55 6.48 5.36 4.52
Intensity of Service Indicators:
Cost per discharge $1,925 $2,064 $2,342 $2,672 $2,888 $3,937 $3,392 $2,972
Adjusted cost per discharge $1,536 $1,629 $1,820 $2,057 $2,195 $3,417 $2,924 $2,572
Cost per visit $171 $190 $212 $246 $250 $202.23 $141.97 $111.53
Case mix index 1.2531 1.2674 1.2869 1.2993 1.3161 1.2795 1.1756 1.0259
Efficiency Indicators:
FTEs per occupied bed 4.10 3.97 4.04 4.10 4.15 4.59 4.15 3.77
Outpat manhours/visit 6.49 7.69 7.88 9.44 9.24 8.66 5.84 4.68
Unit Cost Indicators:
Salary per FTE $17,914 $18,015 $20,047 $19,923 $22,596 $24,447 $22,517 $20,347
Employee benefits % 13.81% 15.55% 14.94% 19.31% 18.35% 19.58% 17.04% 15.18%
Liability costs/disc $10.54 $16.86 $15.94 $24.16 $25.42 $80.94 $42.05 $18.31
EVA Analysis:
Cost of capital input 10.0%
2001 2002 2003 2004 2005
Economic value added (EVA) $ 0.4 $ 0.2 $ (0.8) $ (1.6) $ (1.2)
END
Incase01
Page &P