please you answer this. I need it by August 8, 2015.

Book: Gapenski and Pink Cases in Healthcare Finance ISBN:978-1567936117

Complete Case 31: River Community Hospital (B):

• Review Case 31 (B), pages 217219, in your Cases in Healthcare Finance text. You will find a case spreadsheet model via the Student Spreadsheets link in the Resources area. (Select Case 31 to download the Excel spreadsheet.)
• Write a case review that addresses financial forecasting. Your review should be approximately three pages.

CASE 31 8/13/13 Student Version Copyright 2014 Health Administration Press 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 Statements of Operations: (Millions of Dollars) REVENUES Net patient service revenue Other revenue Total revenues 2011 2012 $28.796 $30.576 1.237 1.853 $30.033 $32.429 2013 $34.584 1.834 $36.418 Growth Rate Forecast 10.0% 10.0% NA 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 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 $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 $2.629 $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 Initial 2014 Final 2014 Initial 2015 Final 2015 Initial 2016 Final 2016 Initial 2017 Final 2017 Initial 2018 Final 2018 Excess of revenues over expenses Initial 2014 $38.042 $2.017 $40.060 Final 2014 $38.042 $2.017 $40.060 Initial 2015 $41.847 $2.219 $44.066 Final 2015 $41.847 $2.219 $44.066 Initial 2016 $46.031 $2.441 $48.472 Final 2016 $46.031 $2.441 $48.472 Initial 2017 $50.634 $2.685 $53.320 Final 2017 $50.634 $2.685 $53.320 Initial 2018 $55.698 $2.954 $58.652 Final 2018 $55.698 $2.954 $58.652 Balance Sheets: 2011 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 2012 2013 Growth Rate Forecast $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 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 $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 $45.738 $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 $0.928 1.460 0.110 $1.253 1.503 1.341 $1.760 1.176 1.465 10.0% 10.0% 10.0% $1.936 1.294 1.612 $1.936 1.294 1.612 $2.130 1.423 1.773 $2.130 1.423 1.773 $2.343 1.565 1.950 $2.343 1.565 1.950 $2.577 1.722 2.145 $2.577 1.722 2.145 $2.834 1.894 2.359 $2.834 1.894 2.359 Total current liabilities Long-term debt Net assets Total liabilities and net assets $2.498 $15.673 27.567 $4.097 $19.222 29.645 $4.401 $17.795 32.079 NA NA NA $4.841 $16.184 34.963 $4.841 $18.668 34.838 $5.325 $16.895 38.064 $5.325 $19.572 37.930 $5.858 $17.622 41.534 $5.858 $20.508 41.390 $6.444 $18.363 45.413 $6.444 $21.475 45.258 $7.088 $19.115 49.746 $7.088 $22.473 49.578 $45.738 $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 $2.360 $2.360 $0.118 $2.485 $2.485 $0.124 $14.516 $2.543 $2.543 $0.127 $2.677 $2.677 $0.134 $2.741 $2.741 $0.137 $2.885 $2.885 $0.144 $2.956 $2.956 $0.148 $3.112 $3.112 $0.156 $3.190 $3.190 $0.159 $3.357 $3.357 $0.168 External funds needed Additional LT debt Added interest exp Cumulative external funds = Statements of Cash Flows: (Millions of Dollars) 2012 2013 2014 2015 2016 2017 2018 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 $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 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 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) $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 NET INCREASE (DECREASE) IN CASH BEGINNING CASH/INVESTMENTS $0.396 4.673 ($2.274) 5.069 $0.279 2.795 $0.307 3.075 $0.338 3.382 $0.372 3.720 $0.409 4.092 ENDING CASH/INVESTMENTS $5.069 $2.795 $3.075 $3.382 $3.720 $4.092 $4.501 $3.075 $3.382 $3.720 $4.092 $4.501 Cash account check 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 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 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 ROE = ROA x Equity multiplier 9.54% 7.09% 7.66% 6.01% 7.92% 8.15% 8.36% 8.55% 8.71% Check ROE (Excess/Net equity) 9.54% 7.09% 7.66% 7.92% 8.15% 8.36% 8.55% 8.71% 2011 2012 2013 Industry Median 2014 2015 2016 2017 2018 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% 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% Liquidity Ratios: Current ratio Days cash on hand 3.91 68.08 2.92 66.87 2.67 32.72 1.99 15.89 2.67 32.77 2.67 32.82 2.67 32.87 2.67 32.91 2.67 32.95 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 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 Asset Management Ratios: Inventory turnover Current asset turnover Fixed asset turnover Total asset turnover Avg collection period 66.66 2.95 0.80 0.63 55.25 58.46 2.55 0.75 0.58 67.73 57.54 2.95 0.81 0.64 78.24 63.95 3.38 1.76 0.89 75.67 57.54 2.95 0.84 0.65 78.24 57.54 2.95 0.86 0.67 78.24 57.54 2.95 0.88 0.68 78.24 57.54 2.95 0.90 0.69 78.24 57.54 2.95 0.92 0.70 78.24 Financial Statement Analysis: Ratios Average payment period Other Ratios: Average age of plant 36.39 54.05 51.52 56.52 51.60 51.68 51.75 51.82 51.89 5.03 5.39 6.12 7.39 6.57 6.97 7.34 7.67 7.97 END