RIVER COMMUNITY HOSPITAL (B): Financial Forecasting
CASE 25 RIVER COMMUNITY HOSPITAL (B): Financial Forecasting
Model without Questions, Student Version
Case 24 illustrates an extensive financial and operating analysis of River Community Hospital.
In Case 25, students prepare a financial plan, consisting primarily of pro forma (forecasted)
financial statements for the same hospital.
This case requires many assumptions about future events at the hospital. The sole bases for making
these judgments are the historical data (and analyses conducted in Case 24, if applicable) and
knowledge of general trends in the hospital industry.
This model was constructed using the percentage of sales (constant growth rate) method of
forecasting with a totally arbitrary 10 percent growth for all balance sheet accounts and
income statement items that might be tied to volume. The 10 percent growth rate multipliers are
contained in Column I. To complete the case, students must make their own judgments about the
best forecasting technique to apply to each line and the input assumptions needed for the
technique selected. In essence, each line must be analyzed separately and modified as needed
to create the best possible forecast. Note that the model extends out to Column S. Also, the
student version of the model is the same as the instructor version.
The model is circular and hence may require multiple recalculations before the forecasted
values stabilize. To account for this, the model is set to automatically recalculate 100 times.
Note that the model is set up very simply in that any external funds needed are automatically
obtained by long-term debt financing. Furthermore, any surplus funds generated are automatically
added to the cash and investments account. Any change to these assumptions requires a
revision of the model.
INPUT DATA: KEY OUTPUT:
2018 2022
Debt interest rate 5.0% Total Margin 6.9% 7.4%
ROA 4.7% 5.5%
Debt ratio 40.3% 37.4%
ROE 7.9% 8.7%
Current ratio 2.67 2.67
Cumulative external funds = $14.516
(in millions)
Statements of Operations:
(Millions of Dollars)
Growth Rate Initial Final Initial Final Initial Final Initial Final Initial Final
REVENUES 2016 2017 Forecast 2018 2018 2019 2019 2020 2020 2021 2021 2022 2022
Net patient service revenue $30.576 $34.584 10.0% $38.042 $38.042 $41.847 $41.847 $46.031 $46.031 $50.634 $50.634 $55.698 $55.698
Other revenue 1.853 1.834 10.0% $2.017 $2.017 $2.219 $2.219 $2.441 $2.441 $2.685 $2.685 $2.954 $2.954
Total revenues $32.429 $36.418 NA $40.060 $40.060 $44.066 $44.066 $48.472 $48.472 $53.320 $53.320 $58.652 $58.652
EXPENSES
Salaries and wages $12.468 $13.994 10.0% $15.393 $15.393 $16.933 $16.933 $18.626 $18.626 $20.489 $20.489 $22.537 $22.537
Fringe benefits 2.408 2.568 10.0% 2.825 2.825 3.107 3.107 3.418 3.418 3.760 3.760 4.136 4.136
Interest expense 1.598 1.776 NA 1.776 1.900 1.900 2.034 2.034 2.178 2.178 2.334 2.334 2.502
Depreciation 2.658 2.778 10.0% 3.056 3.056 3.361 3.361 3.698 3.698 4.067 4.067 4.474 4.474
Medical supplies and drugs 0.655 0.776 10.0% 0.854 0.854 0.939 0.939 1.033 1.033 1.136 1.136 1.250 1.250
Professional liability 0.201 0.218 10.0% 0.240 0.240 0.264 0.264 0.290 0.290 0.319 0.319 0.351 0.351
Other 10.339 11.848 10.0% 13.033 13.033 14.336 14.336 15.770 15.770 17.347 17.347 19.081 19.081
Total expenses $30.327 $33.958 NA $37.176 $37.300 $40.840 $40.974 $44.868 $45.013 $49.296 $49.452 $54.163 $54.331
Excess of revenues over expenses $2.102 $2.458 NA $2.884 $2.759 $3.225 $3.091 $3.604 $3.460 $4.024 $3.868 $4.488 $4.320
Balance Sheets:
Growth Rate Initial Final Initial Final Initial Final Initial Final Initial Final
2016 2017 Forecast 2018 2018 2019 2019 2020 2020 2021 2021 2022 2022
ASSETS
Cash and investments $5.069 $2.795 NA $3.075 $3.075 $3.382 $3.382 $3.720 $3.720 $4.092 $4.092 $4.501 $4.501
Accounts receivable (net) 5.674 7.413 10.0% 8.154 8.154 8.970 8.970 9.867 9.867 10.853 10.853 11.939 11.939
Inventories 0.523 0.601 10.0% 0.661 0.661 0.727 0.727 0.800 0.800 0.880 0.880 0.968 0.968
Other current assets 0.703 0.923 10.0% 1.015 1.015 1.117 1.117 1.229 1.229 1.351 1.351 1.487 1.487
Total current assets $11.969 $11.732 NA $12.905 $12.905 $14.196 $14.196 $15.615 $15.615 $17.177 $17.177 $18.895 $18.895
Gross plant and equipment $55.333 $59.552 10.0% $65.507 $65.507 $72.058 $72.058 $79.264 $79.264 $87.190 $87.190 $95.909 $95.909
Accumulated depreciation 14.338 17.009 NA 20.065 20.065 23.426 23.426 27.124 27.124 31.191 31.191 35.665 35.665
Net plant and equipment $40.995 $42.543 NA $45.442 $45.442 $48.632 $48.632 $52.140 $52.140 $55.999 $55.999 $60.244 $60.244
Total assets $52.964 $54.275 NA $58.348 $58.348 $62.827 $62.827 $67.755 $67.755 $73.176 $73.176 $79.139 $79.139
LIABILITIES AND FUND BALANCE
Accounts payable $1.253 $1.760 10.0% $1.936 $1.936 $2.130 $2.130 $2.343 $2.343 $2.577 $2.577 $2.834 $2.834
Accrued expenses 1.503 1.176 10.0% 1.294 1.294 1.423 1.423 1.565 1.565 1.722 1.722 1.894 1.894
Current portion of long-term debt 1.341 1.465 10.0% 1.612 1.612 1.773 1.773 1.950 1.950 2.145 2.145 2.359 2.359
Total current liabilities $4.097 $4.401 NA $4.841 $4.841 $5.325 $5.325 $5.858 $5.858 $6.444 $6.444 $7.088 $7.088
Long-term debt $19.222 $17.795 NA $16.184 $18.668 $16.895 $19.572 $17.622 $20.508 $18.363 $21.475 $19.115 $22.473
Net assets 29.645 32.079 NA 34.963 34.838 38.064 37.930 41.534 41.390 45.413 45.258 49.746 49.578
Total liabilities and net assets $52.964 $54.275 NA $55.987 $58.348 $60.284 $62.827 $65.014 $67.755 $70.220 $73.176 $75.949 $79.139
External funds needed $2.360 $2.485 $2.543 $2.677 $2.741 $2.885 $2.956 $3.112 $3.190 $3.357
Additional LT debt $2.360 $2.485 $2.543 $2.677 $2.741 $2.885 $2.956 $3.112 $3.190 $3.357
Added interest exp $0.118 $0.124 $0.127 $0.134 $0.137 $0.144 $0.148 $0.156 $0.159 $0.168
Cumulative external funds = $14.516
Statements of Cash Flows:
(Millions of Dollars)
2016 2017 2018 2019 2020 2021 2022
CASH FLOWS FROM OPERATING ACTIVITIES
Net income $2.102 $2.458 $2.759 $3.091 $3.460 $3.868 $4.320
Depreciation and noncash expenses 2.633 2.756 3.056 3.361 3.698 4.067 4.474
Change in accounts receivable (1.315) (1.739) (0.741) (0.815) (0.897) (0.987) (1.085)
Change in inventories (0.091) (0.078) (0.060) (0.066) (0.073) (0.080) (0.088)
Change in other current assets (0.395) (0.220) (0.092) (0.102) (0.112) (0.123) (0.135)
Change in accounts payable 0.325 0.507 0.176 0.194 0.213 0.234 0.258
Change in accrued expenses 0.043 (0.327) 0.118 0.129 0.142 0.157 0.172
Net cash flow from operations $3.302 $3.357 $5.215 $5.793 $6.431 $7.137 $7.916
CASH FLOWS FROM INVESTING ACTIVITIES
Investment in plant and equipment ($7.686) ($4.328) ($5.955) ($6.551) ($7.206) ($7.926) ($8.719)
CASH FLOWS FROM FINANCING ACTIVITIES
Change in long-term debt 3.549 (1.427) $0.873 $0.904 $0.936 $0.967 $0.998
Change in current portion of long-term debt 1.231 0.124 0.147 0.161 0.177 0.195 0.214
Net cash flow from financing $4.780 ($1.303) $1.020 $1.065 $1.113 $1.162 $1.213
NET INCREASE (DECREASE) IN CASH $0.396 ($2.274) $0.280 $0.307 $0.338 $0.372 $0.409
BEGINNING CASH/INVESTMENTS 4.673 5.069 2.795 3.075 3.382 3.720 4.092
ENDING CASH/INVESTMENTS $5.069 $2.795 $3.075 $3.382 $3.720 $4.092 $4.501
Cash account check $3.075 $3.382 $3.720 $4.092 $4.501
Note: “Depreciation and noncash expenses” and “Investment in plant and equipment” 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
Industry
2016 2017 Median 2018 2019 2020 2021 2022
Total margin 6.48% 6.75% 3.48% 6.89% 7.02% 7.14% 7.25% 7.37%
Total asset turnover 0.61 0.67 0.89 0.69 0.70 0.72 0.73 0.74
ROA = TM x TATO 3.97% 4.53% 3.10% 4.73% 4.92% 5.11% 5.29% 5.46%
Equity multiplier 1.79 1.69 1.94 1.67 1.66 1.64 1.62 1.60
ROE = ROA x Equity multiplier 7.09% 7.66% 6.01% 7.92% 8.15% 8.36% 8.55% 8.71%
Check ROE (Excess/Net equity) 7.09% 7.66% 7.92% 8.15% 8.36% 8.55% 8.71%
Financial Statement Analysis: Ratios
Industry
2016 2017 Median 2018 2019 2020 2021 2022
Profitability Ratios:
Total margin 6.48% 6.75% 3.48% 6.89% 7.02% 7.14% 7.25% 7.37%
Return on assets 3.97% 4.53% 3.10% 4.73% 4.92% 5.11% 5.29% 5.46%
Return equity 7.09% 7.66% 6.01% 7.92% 8.15% 8.36% 8.55% 8.71%
Liquidity Ratios:
Current ratio 2.92 2.67 1.99 2.67 2.67 2.67 2.67 2.67
Days cash on hand 66.87 32.72 15.89 32.77 32.82 32.87 32.91 32.95
Debt Management Ratios:
Debt ratio 44.03% 40.90% 48.40% 40.29% 39.63% 38.91% 38.15% 37.35%
LT debt to equity 64.84% 55.47% 64.70% 53.58% 51.60% 49.55% 47.45% 45.33%
Times interest earned 2.32 2.38 2.23 2.45 2.52 2.59 2.66 2.73
Fixed charge coverage 1.26 1.31 1.35 1.33 1.35 1.37 1.38 1.40
Cash flow coverage 3.53 3.52 3.22 3.61 3.69 3.78 3.87 3.96
Asset Management Ratios:
Inventory turnover 58.46 57.54 63.95 57.54 57.54 57.54 57.54 57.54
Current asset turnover 2.55 2.95 3.38 2.95 2.95 2.95 2.95 2.95
Fixed asset turnover 0.75 0.81 1.76 0.84 0.86 0.88 0.90 0.92
Total asset turnover 0.58 0.64 0.89 0.65 0.67 0.68 0.69 0.70
Avg collection period 67.73 78.24 75.67 78.24 78.24 78.24 78.24 78.24
Average payment period 54.05 51.52 56.52 51.60 51.68 51.75 51.82 51.89
Other Ratios:
Average age of plant 5.39 6.12 7.39 6.57 6.97 7.34 7.67 7.97
Based on this file,
Given the standard 10% growth rate (baseline), what are your initial takeaways on the financial forecast for River Community Hospital?
What is the threshold (lowest) growth rate for revenues to maintain projected 10% growth in expenses?
If you wanted to expand your organization and required a 25% growth for gross plant and equipment, what adjustments would you make to meet this growth requirement and to sustain this long-term?
Your HR Director stated that they wanted to increase the annual growth of benefits (pay and fringe) to 15% to stay competitive in the area. What adjustments would you make to meet this growth requirement and to sustain this long-term? Could you sustain the capital projects from Question #3 if you did this benefits growth?
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.