I need help with questions 1 and 2. See attached documents for case study and spreadsheet model. Thank you :)

For this assignment, read Case Study 31, Riverview Community Hospital (B) in theCases in Healthcare Financetextbook (pp 217-219). Type the answers to the following questions in a Word document and upload for grading:

1. To begin, review the spreadsheet model (linked above) paying particular attention to (1) assumptions 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. Now, disregard the forecasting results supplied with the model.
   • 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.
   • Use the current industry comparative data given in Case 1 to help assess the hospital's projected financial condition. Summarize your results in a table that shows key financial ratios, retained earnings, and external financial requirements (if required) for each year, along with five-year totals for earnings and financing.

Cases in Healthcare Finance Copyright 2014 Health Administration Press CASE 31 QUESTIONS RIVER COMMUNITY HOSPITAL (B) Financial Forecasting 1. To begin, review the spreadsheet model paying particular attention to (1) the assumptions 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. Now, disregard the forecasting results supplied with the model. a. 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. b. Use the current industry comparative data given in Case 1 to help assess the hospital's 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 impact do a firm's profitability, capital intensity, and growth rate have on its external financing requirement? 4. Discuss what changes would have to be made to the forecast if the hospital were investorowned? 5. Which of the footnotes to the financial statements given in Case 1 could have a material affect on your forecast? Explain. 6. In your opinion, what are three key learning points from this case? 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 Err:522 Err:522 Err:522 Err:522 Err:522 2018 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Statements of Operations: (Millions of Dollars) Growth Rate Forecast 10.0% 10.0% NA 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 REVENUES Net patient service revenue Other revenue Total revenues 2011 2012 2013 $28.796 $30.576 $34.584 1.237 1.853 1.834 $30.033 $32.429 $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 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 Err:522 3.056 0.854 0.240 13.033 Err:522 $16.933 3.107 Err:522 3.361 0.939 0.264 14.336 Err:522 $16.933 3.107 Err:522 3.361 0.939 0.264 14.336 Err:522 $18.626 3.418 Err:522 3.698 1.033 0.290 15.770 Err:522 $18.626 3.418 Err:522 3.698 1.033 0.290 15.770 Err:522 $20.489 3.760 Err:522 4.067 1.136 0.319 17.347 Err:522 $20.489 3.760 Err:522 4.067 1.136 0.319 17.347 Err:522 $22.537 4.136 Err:522 4.474 1.250 0.351 19.081 Err:522 $22.537 4.136 Err:522 4.474 1.250 0.351 19.081 Err:522 $2.629 $2.102 $2.458 NA $2.884 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 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 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 Err:522 8.154 0.661 1.015 Err:522 $65.507 20.065 $45.442 Err:522 8.970 0.727 1.117 Err:522 $72.058 23.426 $48.632 Err:522 8.970 0.727 1.117 Err:522 $72.058 23.426 $48.632 Err:522 9.867 0.800 1.229 Err:522 $79.264 27.124 $52.140 Err:522 9.867 0.800 1.229 Err:522 $79.264 27.124 $52.140 Err:522 10.853 0.880 1.351 Err:522 $87.190 31.191 $55.999 Err:522 10.853 0.880 1.351 Err:522 $87.190 31.191 $55.999 Err:522 11.939 0.968 1.487 Err:522 $95.909 35.665 $60.244 Err:522 11.939 0.968 1.487 Err:522 $95.909 35.665 $60.244 $45.738 $52.964 $54.275 NA $58.348 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 $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 Err:522 Err:522 $5.325 Err:522 Err:522 $5.325 Err:522 Err:522 $5.858 Err:522 Err:522 $5.858 Err:522 Err:522 $6.444 Err:522 Err:522 $6.444 Err:522 Err:522 $7.088 Err:522 Err:522 $7.088 Err:522 Err:522 $45.738 $52.964 $54.275 NA $55.987 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 $2.360 $2.360 $0.118 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 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 Err:522 3.056 (0.741) (0.060) (0.092) 0.176 0.118 Err:522 Err:522 3.361 (0.815) (0.066) (0.102) 0.194 0.129 Err:522 Err:522 3.698 (0.897) (0.073) (0.112) 0.213 0.142 Err:522 Err:522 4.067 (0.987) (0.080) (0.123) 0.234 0.157 Err:522 Err:522 4.474 (1.085) (0.088) (0.135) 0.258 0.172 Err:522 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) Err:522 0.147 Err:522 Err:522 0.161 Err:522 Err:522 0.177 Err:522 Err:522 0.195 Err:522 Err:522 0.214 Err:522 NET INCREASE (DECREASE) IN CASH BEGINNING CASH/INVESTMENTS $0.396 4.673 ($2.274) 5.069 Err:522 2.795 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 ENDING CASH/INVESTMENTS $5.069 $2.795 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 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 ROE = ROA x Equity multiplier 9.54% 7.09% 7.66% 6.01% Check ROE (Excess/Net equity) 9.54% 7.09% 7.66% 2014 Err:522 Err:522 Err:522 Err:522 2015 Err:522 Err:522 Err:522 Err:522 2016 Err:522 Err:522 Err:522 Err:522 2017 Err:522 Err:522 Err:522 Err:522 2018 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 2014 2015 2016 2017 2018 Financial Statement Analysis: Ratios 2011 2012 2013 Industry Median 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% Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Liquidity Ratios: Current ratio Days cash on hand 3.91 68.08 2.92 66.87 2.67 32.72 1.99 15.89 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 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 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 Err:522 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 Err:522 0.84 Err:522 78.24 57.54 Err:522 0.86 Err:522 78.24 57.54 Err:522 0.88 Err:522 78.24 57.54 Err:522 0.90 Err:522 78.24 57.54 Err:522 0.92 Err:522 78.24 Average payment period Other Ratios: Average age of plant 36.39 54.05 51.52 56.52 Err:522 Err:522 Err:522 Err:522 Err:522 5.03 5.39 6.12 7.39 6.57 6.97 7.34 7.67 7.97 END