| 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 |
jwilly18
Case30StBenedictsTeachingHospital-Student-6thedition.xlsx
