CASE31 CASE 31 8/13/13 Student Version RIVER COMMUNITY HOSPITAL (B) Financial Forecasting Case 1 illustrated an extensive financial and operating analysis of River Community Hospital. In this case (Case 31), 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 basis for making these judgments are the historical data (and analyses conducted in Case 1 if applicable) plus knowledge of general trends in hospital industry. The 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 as well as 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, note that 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 simplistically, 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 revision of the model. INPUT DATA: Debt interest rate KEY OUTPUT: 5.0% 2013 Op margin 6.7% ROA 4.5% Debt ratio 40.9% ROE 7.7% Current ratio 2.67 Cumulative external funds = (in millions) 2014 6.9% 4.7% 40.3% 7.9% 2.67 2018 7.4% 5.5% 37.4% 8.7% 2.67 $14.516 2011 2012 2013 Statements of Operations: (Millions of Dollars) REVENUES Page 1 CASE31 Net patient service revenue Other revenue Total revenues $28.796 1.237 $30.033 $30.576 1.853 $32.429 $34.584 1.834 $36.418 EXPENSES Salaries and wages Fringe benefits Interest expense Depreciation Medical supplies and drugs Professional liability Other Total expenses $12.245 1.830 1.181 2.350 0.622 0.140 9.036 $27.404 $12.468 2.408 1.598 2.658 0.655 0.201 10.339 $30.327 $13.994 2.568 1.776 2.778 0.776 0.218 11.848 $33.958 $2.629 $2.102 $2.458 2011 2012 2013 $4.673 4.359 0.432 0.308 $9.772 $47.786 11.820 $35.966 $5.069 5.674 0.523 0.703 $11.969 $55.333 14.338 $40.995 $2.795 7.413 0.601 0.923 $11.732 $59.552 17.009 $42.543 $45.738 $52.964 $54.275 $0.928 1.460 0.110 $2.498 $15.673 27.567 $1.253 1.503 1.341 $4.097 $19.222 29.645 $1.760 1.176 1.465 $4.401 $17.795 32.079 $45.738 $52.964 $54.275 Excess of revenues over expenses Balance Sheets: ASSETS Cash and investments Accounts receivable (net) Inventories Other current assets Total current assets Gross plant and equipment Accumulated depreciation Net plant and equipment Total assets LIABILITIES AND FUND BALANCE Accounts payable Accrued expenses Current portion of long-term debt Total current liabilities Long-term debt Net assets Total liabilities and net assets External funds needed Page 2 CASE31 Additional LT debt Added interest exp Cumulative external funds = Statements of Cash Flows: (Millions of Dollars) 2012 2013 CASH FLOWS FROM OPERATING ACTIVITIES Net income Depreciation and noncash expenses Change in accounts receivable Change in inventories Change in other current assets Change in accounts payable Change in accrued expenses Net cash flow from operations $2.102 2.633 (1.315) (0.091) (0.395) 0.325 0.043 $3.302 $2.458 2.756 (1.739) (0.078) (0.220) 0.507 (0.327) $3.357 CASH FLOWS FROM INVESTING ACTIVITIES Investment in plant and equipment ($7.686) ($4.328) CASH FLOWS FROM FINANCING ACTIVITIES Change in long-term debt Change in current portion of long-term debt Net cash flow from financing 3.549 1.231 $4.780 (1.427) 0.124 ($1.303) NET INCREASE (DECREASE) IN CASH BEGINNING CASH/INVESTMENTS $0.396 4.673 ($2.274) 5.069 ENDING CASH/INVESTMENTS $5.069 $2.795 Cash account check Note: "Depreciation and noncash expenses" and "Investment in plant and equipment" data in the statements somewhat different than they would be if calculated directly from the other financial statements becau Financial Statement Analysis: Du Pont Analysis Total margin Total asset turnover ROA = TM x TATO Equity multiplier 2011 8.75% 0.66 5.75% 1.66 2012 6.48% 0.61 3.97% 1.79 2013 6.75% 0.67 4.53% 1.69 ROE = ROA x Equity multiplier 9.54% 7.09% 7.66% Page 3 CASE31 Check ROE (Excess/Net equity) 9.54% 7.09% 7.66% 2011 2012 2013 Profitability Ratios: Total margin Return on assets Return equity 8.75% 5.75% 9.54% 6.48% 3.97% 7.09% 6.75% 4.53% 7.66% Liquidity Ratios: Current ratio Days cash on hand 3.91 68.08 2.92 66.87 2.67 32.72 Debt Management Ratios: Debt ratio LT debt to equity Times interest earned Fixed charge coverage Cash flow coverage 39.73% 56.85% 3.23 2.95 4.66 44.03% 64.84% 2.32 1.26 3.53 40.90% 55.47% 2.38 1.31 3.52 Asset Management Ratios: Inventory turnover Current asset turnover Fixed asset turnover Total asset turnover Avg collection period Average payment period 66.66 2.95 0.80 0.63 55.25 36.39 58.46 2.55 0.75 0.58 67.73 54.05 57.54 2.95 0.81 0.64 78.24 51.52 5.03 5.39 6.12 Financial Statement Analysis: Ratios Other Ratios: Average age of plant Page 4 CASE31 Copyright 2014 Health Administration Press Hospital. basis for making able) plus ethod of ultipliers are nts about the eded for the d as needed Also, note casted 0 times. e automatically automatically equires Growth Rate Forecast Initial 2014 Final 2014 Initial 2015 Page 5 Final 2015 Initial 2016 Final 2016 CASE31 10.0% 10.0% NA $38.042 $2.017 $40.060 $38.042 $2.017 $40.060 $41.847 $2.219 $44.066 $41.847 $2.219 $44.066 $46.031 $2.441 $48.472 $46.031 $2.441 $48.472 10.0% 10.0% NA 10.0% 10.0% 10.0% 10.0% NA $15.393 2.825 1.776 3.056 0.854 0.240 13.033 $37.176 $15.393 2.825 1.900 3.056 0.854 0.240 13.033 $37.300 $16.933 3.107 1.900 3.361 0.939 0.264 14.336 $40.840 $16.933 3.107 2.034 3.361 0.939 0.264 14.336 $40.974 $18.626 3.418 2.034 3.698 1.033 0.290 15.770 $44.868 $18.626 3.418 2.178 3.698 1.033 0.290 15.770 $45.013 NA $2.884 $2.759 $3.225 $3.091 $3.604 $3.460 Growth Rate Forecast Initial 2014 Final 2014 Initial 2015 Final 2015 Initial 2016 Final 2016 NA 10.0% 10.0% 10.0% NA 10.0% NA NA $3.075 8.154 0.661 1.015 $12.905 $65.507 20.065 $45.442 $3.075 8.154 0.661 1.015 $12.905 $65.507 20.065 $45.442 $3.382 8.970 0.727 1.117 $14.196 $72.058 23.426 $48.632 $3.382 8.970 0.727 1.117 $14.196 $72.058 23.426 $48.632 $3.720 9.867 0.800 1.229 $15.615 $79.264 27.124 $52.140 $3.720 9.867 0.800 1.229 $15.615 $79.264 27.124 $52.140 NA $58.348 $58.348 $62.827 $62.827 $67.755 $67.755 10.0% 10.0% 10.0% NA NA NA $1.936 1.294 1.612 $4.841 $16.184 34.963 $1.936 1.294 1.612 $4.841 $18.668 34.838 $2.130 1.423 1.773 $5.325 $16.895 38.064 $2.130 1.423 1.773 $5.325 $19.572 37.930 $2.343 1.565 1.950 $5.858 $17.622 41.534 $2.343 1.565 1.950 $5.858 $20.508 41.390 NA $55.987 $58.348 $60.284 $62.827 $65.014 $67.755 $2.360 $2.485 $2.543 $2.677 $2.741 $2.885 ernal funds needed Page 6 CASE31 ditional LT debt ded interest exp mulative external funds = $2.360 $0.118 $2.485 $0.124 $14.516 $2.543 $0.127 $2.677 $0.134 $2.741 $0.137 2014 2015 2016 2017 2018 $2.759 3.056 (0.741) (0.060) (0.092) 0.176 0.118 $5.215 $3.091 3.361 (0.815) (0.066) (0.102) 0.194 0.129 $5.793 $3.460 3.698 (0.897) (0.073) (0.112) 0.213 0.142 $6.431 $3.868 4.067 (0.987) (0.080) (0.123) 0.234 0.157 $7.137 $4.320 4.474 (1.085) (0.088) (0.135) 0.258 0.172 $7.916 ($5.955) ($6.551) ($7.206) ($7.926) ($8.719) $0.873 0.147 $1.020 $0.904 0.161 $1.065 $0.936 0.177 $1.113 $0.967 0.195 $1.162 $0.998 0.214 $1.213 $0.279 2.795 $0.307 3.075 $0.338 3.382 $0.372 3.720 $0.409 4.092 $3.075 $3.382 $3.720 $4.092 $4.501 $3.075 $3.382 $3.720 $4.092 $4.501 nt" data in the statements of cash flows are nancial statements because of asset revaluations. Industry Median 3.48% 0.89 3.10% 1.94 2014 6.89% 0.69 4.73% 1.67 2015 7.02% 0.70 4.92% 1.66 2016 7.14% 0.72 5.11% 1.64 2017 7.25% 0.73 5.29% 1.62 2018 7.37% 0.74 5.46% 1.60 6.01% 7.92% 8.15% 8.36% 8.55% 8.71% Page 7 $2.885 $0.144 CASE31 7.92% 8.15% 8.36% 8.55% 8.71% Industry Median 2014 2015 2016 2017 2018 3.48% 3.10% 6.01% 6.89% 4.73% 7.92% 7.02% 4.92% 8.15% 7.14% 5.11% 8.36% 7.25% 5.29% 8.55% 7.37% 5.46% 8.71% 1.99 15.89 2.67 32.77 2.67 32.82 2.67 32.87 2.67 32.91 2.67 32.95 48.40% 64.70% 2.23 1.35 3.22 40.29% 53.58% 2.45 1.33 3.61 39.63% 51.60% 2.52 1.35 3.69 38.91% 49.55% 2.59 1.37 3.78 38.15% 47.45% 2.66 1.38 3.87 37.35% 45.33% 2.73 1.40 3.96 63.95 3.38 1.76 0.89 75.67 56.52 57.54 2.95 0.84 0.65 78.24 51.60 57.54 2.95 0.86 0.67 78.24 51.68 57.54 2.95 0.88 0.68 78.24 51.75 57.54 2.95 0.90 0.69 78.24 51.82 57.54 2.95 0.92 0.70 78.24 51.89 7.39 6.57 6.97 7.34 7.67 7.97 Page 8 CASE31 Initial 2017 Final 2017 Initial 2018 Final 2018 Page 9 CASE31 $50.634 $2.685 $53.320 $50.634 $2.685 $53.320 $55.698 $2.954 $58.652 $55.698 $2.954 $58.652 $20.489 3.760 2.178 4.067 1.136 0.319 17.347 $49.296 $20.489 3.760 2.334 4.067 1.136 0.319 17.347 $49.452 $22.537 4.136 2.334 4.474 1.250 0.351 19.081 $54.163 $22.537 4.136 2.502 4.474 1.250 0.351 19.081 $54.331 $4.024 $3.868 $4.488 $4.320 Initial 2017 Final 2017 Initial 2018 Final 2018 $4.092 10.853 0.880 1.351 $17.177 $87.190 31.191 $55.999 $4.092 10.853 0.880 1.351 $17.177 $87.190 31.191 $55.999 $4.501 11.939 0.968 1.487 $18.895 $95.909 35.665 $60.244 $4.501 11.939 0.968 1.487 $18.895 $95.909 35.665 $60.244 $73.176 $73.176 $79.139 $79.139 $2.577 1.722 2.145 $6.444 $18.363 45.413 $2.577 1.722 2.145 $6.444 $21.475 45.258 $2.834 1.894 2.359 $7.088 $19.115 49.746 $2.834 1.894 2.359 $7.088 $22.473 49.578 $70.220 $73.176 $75.949 $79.139 $2.956 $3.112 $3.190 $3.357 Page 10 CASE31 $2.956 $0.148 $3.112 $0.156 $3.190 $0.159 $3.357 $0.168 Page 11 CASE31 END Page 12 River community hospital is a 210 bed not for profit acute care hospital with a longstanding reputation for quality service to a growing service area. River competes with three other hospitals in it metropolitan statistical area (MSA): two not for profit and one for profit. It is the smaller of the four but has traditionally been ranked highest in patient satisfaction polls. For a complete description of the hospital, along with its 2011-2013 financial statements, see case 1: river community hospital (A). As the newly hired special assistant to the CEO, you have completed the financial and operating analyses (see case 1) assigned by your boss, Melissa Randoloph. In fact, your presentation to the board of trustees went so well that Melissa asked you to present the hospitals preliminary five year - financial plan at the next board meeting. To aid in the planning process, she provided the following information: 1. Given your knowledge of the historical situation for River, current trends in the healthcare industry, and the competitive situation facing hospitals today, use your own best judgment to create the hospitals financial plan, make any assumptions you believe to be necessary to create the plan, including assumptions about inpatient and outpatient volume growth, capacity constraints, reimbursement patterns, hospital staffing patterns, input cost inflation, and so on. Be sure to completely document your assumption in the report. You have very limited specific information about the hospital, so use your general knowledge about trends in the hospital industry to make the forecasts. The quality of your financial plan will be judged as much (or more) on the validity of your assumptions as on the mechanics of the forecasting process. 2. The emphasis should be on the forecast for the first year (2014) but you should also create rough income statements, balance sheets, and statements of cash flows for the following four years (five years in total), including key financial ratios 3. The five primary methods for forecasting income statement items and balance sheets accounts are (a) percentage of sales (in which a constant growth rate is applied), (b) simple linear regression, (c) curvilinear regression, (D) multiple regressions and (e) specific item forecasting. You may need to use several of these methods in your forecast (hint: do no forget that spreadsheets have a regression capability) 4. Use the financial analysis from case 1 to help with the forecast if that case was assigned. Those areas were hospital performance has been poor should be improved, and your forecasts should reflect anticipated operational improvements where applicable 5. Do not get so involved in the mechanics of the forecasting process that you forget to apply common sense to your forecasts. Think about what has happened in the past and what is likely to happen in the future in regard to utilization, prices, costs, and asset requirements. If the forecast does not make sense, modify it until it does. For example a blind application of statistical forecasting techniques might lead to a forecast containing five years of net operating losses. Regardless of statistical fit, such a forecast makes no sense because any hospital, if it expects survive, will have to take actions to change utilization, pricing, or cost trends to ensure positive operating results. Thus, the blindly forecasted values do not represent what is likely to happen in the future, even though they might be a perfect reflection of historical trends, similarly, a forecast that is widely optimistic probably needs to be modified because payers and competitors likely would react to dampen profitability if it rose dramatically. In closing, Melissa gave you her view of a good financial plan: \"first and foremost, the plan should consist of pro forma financial statements along with a table that summarizes the amount of financing generated internally and any external financing requirements. Second, key financial ratios should be calculated, and the hospitals expected future financial condition should be assessed, with special emphasis on changes from the hospitals current condition, third make sure that your forecasted financial statements are consistent with one another. The last special assistant could not figure out that two balance sheet accounts - equity (fund) capital and accumulated depreciation - are tied to income statement items, so he did not last very long. Finally, make all your assumptions clear and be prepared to answer questions form the board concerning the impact of changes in your assumptions on the financial plan\