1.Review the spreadsheet model, paying particular attention to (1) what assumptions are currently embedded in the model, (2) how the financial forecasts are developed, and (3) how financing feedbacks are handled. Briefly describe the model and its assumptions.

2.Disregard the forecasting results supplied with the model.

2a. Use your own assumptions to develop the hospital's forecasted financial statements. Be sure to analyze your results from the perspectives of both accounting consistency and financial feasibility.

2b. Use the current industry comparative data given in case 1 to help assess the hospitals projected financial condition. Summarize your results in a table that shows key financial ratios, retained earnings, and external financing requirements (if required) for each year, along with five-year totals for earnings and financing.

3.In general, what effect do the profitability, capital intensity, and growth rate of a firm have on its external financing requirement?

INPUT DATA: Debt interest rate 5.0% KEY OUTPUT: 2017 Total Margin 6.7% ROA 4.5% Debt ratio 40.9% ROE 7.7% Current ratio 2.67 Cumulative external funds = (in millions) 2018 6.9% 4.7% 40.3% 7.9% 2.67 2022 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 2015 2016 $28.796 $30.576 1.237 1.853 $30.033 $32.429 Growth Rate Initial Final Initial Final Initial Final Initial Final Initial Final 2017 Forecast 2018 2018 2019 2019 2020 2020 2021 2021 2022 2022 $34.584 10.0% $38.042 $38.042 $41.847 $41.847 $46.031 $46.031 $50.634 $50.634 $55.698 $55.698 1.834 10.0% $2.017 $2.017 $2.219 $2.219 $2.441 $2.441 $2.685 $2.685 $2.954 $2.954 $36.418 NA $40.060 $40.060 $44.066 $44.066 $48.472 $48.472 $53.320 $53.320 $58.652 $58.652 F F F F EXPENSES Salaries and wages Fringe benefits Interest expense Depreciation Medical supplies and drugs Professional liability Other Total expenses $12.245 $12.468 $13.994 1.830 2.408 2.568 1.181 1.598 1.776 2.350 2.658 2.778 0.622 0.655 0.776 0.140 0.201 0.218 9.036 10.339 11.848 $27.404 $30.327 $33.958 10.0% $15.393 $15.393 "$16.933 $16.933 "$18.626 $18.626 "$20.489 $20.489 "$22.537 $22.537 10.0% 2.825 2.825 3.107 3.107 3.418 3.418 3.760 3.760 4.136 4.136 NA 1.776 1.900 1.900 2.034 2.034 2.178 2.178 2.334 2.334 2.502 10.0% 3.056 3.056 3.361 3.361 3.698 3.698 4.067 4.067 4.474 4.474 10.0% 0.854 0.854 0.939 0.939 1.033 1.033 1.136 1.136 1.250 1.250 10.0% 0.240 0.240 0.264 0.264 0.290 0.290 0.319 0.319 0.351 0.351 10.0% 13.033 13.033 14.336 14.336 15.770 15.770 17.347 17.347 19.081 19.081 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.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 Balance Sheets: Growth Rate 2017 Forecast Initial 2018 Final 2018 Initial 2019 Final 2019 Initial 2020 Final 2020 Initial 2021 Final 2021 Initial 2022 Final 2022 2015 2016 F F F F F F ASSETS Cash and investments Accounts receivable (net) Inventories Other current assets Total current assets Gross plant and equipment Accumulated depreciation Net plant and equipment $4.673 $5.069 4.359 5.674 0.432 0.523 0.308 0.703 $9.772 $11.969 $47.786 $55.333 11.820 14.338 $35.966 $40.995 $2.795 7.413 0.601 0.923 $11.732 $59.552 17.009 $42.543 NA $3.075 $3.075 $3.382 $3.382 $3.720 $3.720 $4.092 $4.092 $4.501 $4.501 10.0% 8.154 8.154 8.970 8.970 9.867 9.867 10.853 10.853 11.939 11.939 10.0% 0.661 0.661 0.727 0.727 0.800 0.800 0.880 0.880 0.968 0.968 10.0% 1.015 1.015 1.117 1.117 1.229 1.229 1.351 1.351 1.487 1.487 NA $12.905 $12.905 $14.196 $14.196 $15.615 $15.615 $17.177 $17.177 $18.895 $18.895 10.0% $65.507 $65.507 $72.058 $72.058 $79.264 $79.264 $87.190 $87.190 $95.909 $95.909 NA 20.065 20.065 23.426 23.426 27.124 27.124 31.191 31.191 35.665 35.665 NA $45.442 $45.442 $48.632 $48.632 $52.140 $52.140 $55.999 $55.999 $60.244 $60.244 Total assets $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 F F F LIABILITIES AND FUND BALANCE Accounts payable Accrued expenses Current portion of long-term debt Total current liabilities Long-term debt Net assets $0.928 $1.253 1.460 1.503 0.110 1.341 $2.498 $4.097 $15.673 $19.222 27.567 29.645 $1.760 1.176 1.465 $4.401 $17.795 32.079 10.0% $1.936 $1.936 $2.130 $2.130 $2.343 $2.343 $2.577 $2.577 $2.834 $2.834 10.0% 1.294 1.294 1.423 1.423 1.565 1.565 1.722 1.722 1.894 1.894 10.0% 1.612 1.612 1.773 1.773 1.950 1.950 2.145 2.145 2.359 2.359 NA $4.841 $4.841 $5.325 $5.325 $5.858 $5.858 $6.444 $6.444 $7.088 $7.088 NA $16.184 $18.668 $16.895 $19.572 $17.622 $20.508 $18.363 $21.475 $19.115 $22.473 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 $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 F External funds needed $2.360 Additional LT debt $2.360 Added interest exp $0.118 Cumulative external funds = $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 Statements of Cash Flows: (Millions of Dollars) 2016 2017 2018 2019 2020 2021 2022 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.091 $3.460 $3.868 3.056 3.361 3.698 4.067 (0.741) (0.815) (0.897) (0.987) (0.060) (0.066) (0.073) (0.080) (0.092) (0.102) (0.112) (0.123) 0.176 0.194 0.213 0.234 0.118 0.129 0.142 0.157 $5.215 $5.793 $6.431 $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.427) 1.231 0.124 $4.780 ($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 ($2.274) 4.673 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.074 $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 Total margin Total asset turnover ROA = TM X TATO Equity multiplier 2015 8.75% 0.66 5.75% 1.66 2016 6.48% 0.61 3.97% 1.79 2017 6.75% 0.67 4.53% 1.69 Industry Median 3.48% 0.89 3.10% 1.94 2018 6.89% 0.69 4.73% 1.67 2019 7.02% 0.70 4.92% 2020 7.14% 0.72 5.11% 1.64 2021 7.25% 0.73 5.29% 1.62 2022 7.37% 0.74 5.46% 1.60 1.66 ROE = ROAX 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% Financial Statement Analysis: Ratios Industry Median 2015 2016 2017 2018 2019 2020 2021 2022 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% 44.03% 56.85% 64.84% 3.23 2.32 2.95 1.26 4.66 3.53 40.90% 55.47% 2.38 1.31 3.52 48.40% 40.29% 39.63% 38.91% 38.15% 37.35% 64.70% 53.58% 51.60% 49.55% 47.45% 45.33% 2.23 2.45 2.52 2.59 2.66 2.73 1.35 1.33 1.35 1.37 1.38 1.40 3.61 3.78 3.87 3.96 3.22 3.69 Asset Management Ratios: Inventory turnover Current asset turnover Fixed asset turnover Total asset turnover Avg collection period Average payment period 57.54 2.95 0.86 57.54 2.95 0.92 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 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.88 0.68 78.24 51.75 57.54 2.95 0.90 0.69 78.24 51.82 0.67 0.70 78.24 51.68 78.24 51.89 Other Ratios: Average age of plant 5.03 5.39 6.12 7.39 6.57 6.97 7.34 7.67 7.97 INPUT DATA: Debt interest rate 5.0% KEY OUTPUT: 2017 Total Margin 6.7% ROA 4.5% Debt ratio 40.9% ROE 7.7% Current ratio 2.67 Cumulative external funds = (in millions) 2018 6.9% 4.7% 40.3% 7.9% 2.67 2022 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 2015 2016 $28.796 $30.576 1.237 1.853 $30.033 $32.429 Growth Rate Initial Final Initial Final Initial Final Initial Final Initial Final 2017 Forecast 2018 2018 2019 2019 2020 2020 2021 2021 2022 2022 $34.584 10.0% $38.042 $38.042 $41.847 $41.847 $46.031 $46.031 $50.634 $50.634 $55.698 $55.698 1.834 10.0% $2.017 $2.017 $2.219 $2.219 $2.441 $2.441 $2.685 $2.685 $2.954 $2.954 $36.418 NA $40.060 $40.060 $44.066 $44.066 $48.472 $48.472 $53.320 $53.320 $58.652 $58.652 F F F F EXPENSES Salaries and wages Fringe benefits Interest expense Depreciation Medical supplies and drugs Professional liability Other Total expenses $12.245 $12.468 $13.994 1.830 2.408 2.568 1.181 1.598 1.776 2.350 2.658 2.778 0.622 0.655 0.776 0.140 0.201 0.218 9.036 10.339 11.848 $27.404 $30.327 $33.958 10.0% $15.393 $15.393 "$16.933 $16.933 "$18.626 $18.626 "$20.489 $20.489 "$22.537 $22.537 10.0% 2.825 2.825 3.107 3.107 3.418 3.418 3.760 3.760 4.136 4.136 NA 1.776 1.900 1.900 2.034 2.034 2.178 2.178 2.334 2.334 2.502 10.0% 3.056 3.056 3.361 3.361 3.698 3.698 4.067 4.067 4.474 4.474 10.0% 0.854 0.854 0.939 0.939 1.033 1.033 1.136 1.136 1.250 1.250 10.0% 0.240 0.240 0.264 0.264 0.290 0.290 0.319 0.319 0.351 0.351 10.0% 13.033 13.033 14.336 14.336 15.770 15.770 17.347 17.347 19.081 19.081 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.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 Balance Sheets: Growth Rate 2017 Forecast Initial 2018 Final 2018 Initial 2019 Final 2019 Initial 2020 Final 2020 Initial 2021 Final 2021 Initial 2022 Final 2022 2015 2016 F F F F F F ASSETS Cash and investments Accounts receivable (net) Inventories Other current assets Total current assets Gross plant and equipment Accumulated depreciation Net plant and equipment $4.673 $5.069 4.359 5.674 0.432 0.523 0.308 0.703 $9.772 $11.969 $47.786 $55.333 11.820 14.338 $35.966 $40.995 $2.795 7.413 0.601 0.923 $11.732 $59.552 17.009 $42.543 NA $3.075 $3.075 $3.382 $3.382 $3.720 $3.720 $4.092 $4.092 $4.501 $4.501 10.0% 8.154 8.154 8.970 8.970 9.867 9.867 10.853 10.853 11.939 11.939 10.0% 0.661 0.661 0.727 0.727 0.800 0.800 0.880 0.880 0.968 0.968 10.0% 1.015 1.015 1.117 1.117 1.229 1.229 1.351 1.351 1.487 1.487 NA $12.905 $12.905 $14.196 $14.196 $15.615 $15.615 $17.177 $17.177 $18.895 $18.895 10.0% $65.507 $65.507 $72.058 $72.058 $79.264 $79.264 $87.190 $87.190 $95.909 $95.909 NA 20.065 20.065 23.426 23.426 27.124 27.124 31.191 31.191 35.665 35.665 NA $45.442 $45.442 $48.632 $48.632 $52.140 $52.140 $55.999 $55.999 $60.244 $60.244 Total assets $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 F F F LIABILITIES AND FUND BALANCE Accounts payable Accrued expenses Current portion of long-term debt Total current liabilities Long-term debt Net assets $0.928 $1.253 1.460 1.503 0.110 1.341 $2.498 $4.097 $15.673 $19.222 27.567 29.645 $1.760 1.176 1.465 $4.401 $17.795 32.079 10.0% $1.936 $1.936 $2.130 $2.130 $2.343 $2.343 $2.577 $2.577 $2.834 $2.834 10.0% 1.294 1.294 1.423 1.423 1.565 1.565 1.722 1.722 1.894 1.894 10.0% 1.612 1.612 1.773 1.773 1.950 1.950 2.145 2.145 2.359 2.359 NA $4.841 $4.841 $5.325 $5.325 $5.858 $5.858 $6.444 $6.444 $7.088 $7.088 NA $16.184 $18.668 $16.895 $19.572 $17.622 $20.508 $18.363 $21.475 $19.115 $22.473 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 $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 F External funds needed $2.360 Additional LT debt $2.360 Added interest exp $0.118 Cumulative external funds = $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 Statements of Cash Flows: (Millions of Dollars) 2016 2017 2018 2019 2020 2021 2022 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.091 $3.460 $3.868 3.056 3.361 3.698 4.067 (0.741) (0.815) (0.897) (0.987) (0.060) (0.066) (0.073) (0.080) (0.092) (0.102) (0.112) (0.123) 0.176 0.194 0.213 0.234 0.118 0.129 0.142 0.157 $5.215 $5.793 $6.431 $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.427) 1.231 0.124 $4.780 ($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 ($2.274) 4.673 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.074 $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 Total margin Total asset turnover ROA = TM X TATO Equity multiplier 2015 8.75% 0.66 5.75% 1.66 2016 6.48% 0.61 3.97% 1.79 2017 6.75% 0.67 4.53% 1.69 Industry Median 3.48% 0.89 3.10% 1.94 2018 6.89% 0.69 4.73% 1.67 2019 7.02% 0.70 4.92% 2020 7.14% 0.72 5.11% 1.64 2021 7.25% 0.73 5.29% 1.62 2022 7.37% 0.74 5.46% 1.60 1.66 ROE = ROAX 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% Financial Statement Analysis: Ratios Industry Median 2015 2016 2017 2018 2019 2020 2021 2022 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% 44.03% 56.85% 64.84% 3.23 2.32 2.95 1.26 4.66 3.53 40.90% 55.47% 2.38 1.31 3.52 48.40% 40.29% 39.63% 38.91% 38.15% 37.35% 64.70% 53.58% 51.60% 49.55% 47.45% 45.33% 2.23 2.45 2.52 2.59 2.66 2.73 1.35 1.33 1.35 1.37 1.38 1.40 3.61 3.78 3.87 3.96 3.22 3.69 Asset Management Ratios: Inventory turnover Current asset turnover Fixed asset turnover Total asset turnover Avg collection period Average payment period 57.54 2.95 0.86 57.54 2.95 0.92 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 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.88 0.68 78.24 51.75 57.54 2.95 0.90 0.69 78.24 51.82 0.67 0.70 78.24 51.68 78.24 51.89 Other Ratios: Average age of plant 5.03 5.39 6.12 7.39 6.57 6.97 7.34 7.67 7.97