| CASE 30 ST. BENEDICT'S TEACHING HOSPITAL: Merger Analysis | 12/1/17 |
| Copyright 2018 Foundation of the American College of Healthcare Executives. Not for sale. |
| Model without Questions, Student Version |
| This case consists of a valuation analysis on a 400-bed acute care hospital. The model uses both |
| discounted cash flow (DCF) and market multiple methodologies for the valuation. The DCF method |
| focuses on cash flows to equityholders. The INPUT DATA section of the model contains key |
| assumptions needed to generate the pro forma (forecasted) cash flow statements. In addition, |
| historical data used in the forecasting process is contained in its own section. |
| The model consists of a complete base case analysis--no changes need to be made to the existing |
| MODEL-GENERATED DATA section. However, values in the INPUT DATA section of the student |
| spreadsheet have been replaced by zeros. Students must select appropriate input values and enter |
| them into the cells with values colored red. After this is done, any error cells will be corrected and |
| the base case solution will appear. The KEY OUTPUT section includes the most important output |
| from the MODEL-GENERATED DATA section. |
| INPUT DATA: | KEY OUTPUT: |
| (millions of $) | (millions of $) |
| Cash flow data: | Pro forma (forecasted) net cash flows: |
| Growth in revenues (2018-2022): | 2018 | $100.236 |
| Inpatient | 0% | 2019 | $100.236 |
| Outpatient | 0% | 2020 | $100.236 |
| Nonoperating | 0% | 2021 | $100.236 |
| Patient services exp as a | 2022 | ERROR:#DIV/0! |
| % of net patient rev | 0% |
| Percent of net CF | 2018 EBITDA | $100.236 |
| retained for growth | 0% | Average 5-yr EBITDA | ERROR:#DIV/0! |
| Annual inputs: |
| *Interest on | **Interest on | ***Cost Saving | Acquisition value: |
| Year | Current Debt | Required Loans | at Acquirer |
| 2018 | $0.000 | $0.000 | $0.000 | DCF method | ERROR:#DIV/0! |
| 2019 | $0.000 | $0.000 | $0.000 | Market multiple method: |
| 2020 | $0.000 | $0.000 | $0.000 | Applied to 2014 EBITDA | $0.000 |
| 2021 | $0.000 | $0.000 | $0.000 | Applied to avg EBITDA | ERROR:#DIV/0! |
| 2022 | $0.000 | $0.000 | $0.000 | Applied to discharges | $0.000 |
| Long-term (2019 and beyond) | *Interest on Lafayette General's current debt. |
| constant growth rate | 0% |
| **Interest on any borrowings required to expand |
| Market data: | the asset base or to fund the acquisition. |
| Discount rate (cost of equity) | 0% |
| EBITDA multiple | 0 | ***Cost savings at St. Benedict's due to any |
| Market value to discharges ratio | $0 | synergistic benefits that will accrue at |
| the acquirer as opposed to at the target. |
| Operating data: |
| Expected number of discharges | 0 |
| HISTORICAL DATA: |
| Lafayette General Hospital |
| Historical Income Statements: |
| (Millions of Dollars) |
| 2013 | 2014 | 2015 | 2016 | 2017 |
| Inpatient revenue | $42.472 | $46.014 | $53.410 | $58.650 | $59.513 |
| Outpatient revenue | 28.314 | 30.676 | 35.606 | 39.100 | 39.675 |
| Net patient service revenue | $70.786 | $76.690 | $89.016 | $97.750 | $99.188 |
| Nonoperating revenue | 1.922 | 1.515 | 1.367 | 1.725 | 1.048 |
| Total revenues | $72.708 | $78.205 | $90.383 | $99.475 | $100.236 |
| Patient services expenses | $60.245 | $73.858 | $81.525 | $90.645 | $89.505 |
| Interest expense | 3.045 | 3.147 | 3.093 | 3.002 | 2.980 |
| Depreciation | 3.466 | 3.689 | 4.395 | 4.258 | 6.031 |
| Total expenses | $66.756 | $80.694 | $89.013 | $97.905 | $98.516 |
| Net income | $5.952 | ($2.489) | $1.370 | $1.570 | $1.720 |
| MODEL-GENERATED DATA: |
| Lafayette General Hospital |
| Pro Forma (Forecasted) Cash Flow Statements: |
| (Millions of Dollars) |
| 2018 | 2019 | 2020 | 2021 | 2022 |
| Inpatient revenue | $59.513 | $59.513 | $59.513 | $59.513 | $59.513 |
| Outpatient revenue | 39.675 | 39.675 | 39.675 | 39.675 | 39.675 |
| Net patient service revenue | $99.188 | $99.188 | $99.188 | $99.188 | $99.188 |
| Nonoperating revenue | 1.048 | 1.048 | 1.048 | 1.048 | 1.048 |
| Total revenues | $100.236 | $100.236 | $100.236 | $100.236 | $100.236 |
| Patient services expenses | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 |
| Interest expense | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| Total expenses | $0.000 | $0.000 | $0.000 | $0.000 | $0.000 |
| Net operating cash flow | $100.236 | $100.236 | $100.236 | $100.236 | $100.236 |
| Cost savings at teaching (other) hospital | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| Growth retentions | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |
| Terminal value | ERROR:#DIV/0! |
| Net cash flow to equityholders | $100.236 | $100.236 | $100.236 | $100.236 | ERROR:#DIV/0! |
| EBITDA | $100.236 | $100.236 | $100.236 | $100.236 | ERROR:#DIV/0! |
| Average 5-year EBITDA | ERROR:#DIV/0! |
| Valuation Results: |
| (Millions of Dollars) |
| Value according to DCF method | ERROR:#DIV/0! |
| Value according to market multiple method: |
| Applied to 2014 EBITDA | $0.000 |
| Applied to average 2014-2018 EBITDA | ERROR:#DIV/0! |
| Applied to number of discharges | $0.000 |
| Sensitivity of DCF Value to Terminal Growth Rate and Discount Rate Estimates: |
| Discount Rate |
| ERROR:#DIV/0! | 12% | 13% | 14% | 15% | 16% | 17% | 18% |
| 1% | $883.559 | $810.454 | $748.580 | $695.531 | $649.541 | $609.289 | $573.762 |
| TV | 2% | $941.470 | $857.027 | $786.624 | $727.020 | $675.903 | $631.577 | $592.770 |
| Growth | 3% | $1,012.249 | $912.915 | $831.584 | $763.757 | $706.320 | $657.049 | $614.312 |
| Rate | 4% | $1,100.724 | $981.223 | $885.536 | $807.174 | $741.807 | $686.440 | $638.931 |
| 5% | $1,214.477 | $1,066.607 | $951.478 | $859.274 | $783.746 | $720.729 | $667.338 |
| 6% | $1,366.148 | $1,176.386 | $1,033.906 | $922.952 | $834.073 | $761.252 | $700.479 |
| 7% | $1,578.488 | $1,322.759 | $1,139.884 | $1,002.550 | $895.583 | $809.880 | $739.647 |
| END |
marissa1190
CopyofCase30StBenedictsTeachingHospital-Student-6thedition.xlsx
