Copy of STUCASE22.xls Download Attachment

CASE22
CASE 22
4/3/07

Student Model

Copyright 2006
By FACHE

MOUNT OLIVE TEACHING HOSPITAL
Merger Analysis
This case consists of an acquisition analysis on a 400-bed acute-care hospital. The...CASE22
CASE 22
4/3/07

Student Model

Copyright 2006
By FACHE

MOUNT OLIVE TEACHING HOSPITAL
Merger Analysis
This case consists of an acquisition analysis on a 400-bed acute-care hospital. The model uses
both discounted cash flow (DCF) and market multiple methodologies for the valuation. Note that
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 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, all values in the student
version INPUT DATA section have been replaced with zeros. Thus, students must determine
the appropriate input values and enter them into the model. These cells are colored red.
When this is done, any error cells will be corrected and the base case solution will appear.
Note that the model does not contain any risk analyses, so students will have to create
their own if required by the case . Furthermore, students must create their own graphics
(charts) as needed to present their results.

INPUT DATA:
(millions of $)

KEY OUTPUT:
(millions of $)

Cash flow data:
Growth in gross revenues (2006-2010):
Inpatient
0.0%
Outpatient
0.0%
Allowances and discount %
0.0%
Other op rev growth rate
0.0%
Patient services exp as a
% of net patient rev
0.0%
Percent of net op CF
retained for growth
0.0%
Annual inputs:
*Interest on
**Interest on ***Cost Saving
Year
Current Debt Required Loans at MT. Olive
2006
$0.000
$0.000
$0.000
2007
$0.000
$0.000
$0.000
2008
$0.000
$0.000
$0.000
2009
$0.000
$0.000
$0.000
2010
$0.000
$0.000
$0.000

Pro forma net cash flows:
2006
2007
2008
2009
2010

Long-term (2011 and beyond)
constant growth rate

2006 EBITDA
Average 5-yr EBITDA

$162.242
$162.242
$162.242
$162.242
#DIV/0!
$162.242
#DIV/0!

Acquisition value:
DCF method
Market multiple method:
Applied to 2003 EBITDA
Applied to avg EBITDA
Applied to discharges

#DIV/0!
$0.000
#DIV/0!
$0.000

*Interest on Avalon General's current debt.
0.0%
**Interest on any borrowings required to expand
the asset base or to fund the acquisition.

Market data:
Discount rate (cost of equity)
EBITDA multiple
Market value to discharges ratio

0.0%
0.0
$0

Operating data:
Expected number of discharges

0

***Cost savings at Mount Olive due to any
synergistic benefits that will accrue at
the acquirer as opposed to at the target.

HISTORICAL DATA:
Avalon General Hospital
Historical Income Statements:
(Millions of Dollars)
2001
2002
2003
2004
2005
$81.624
$88.249
$99.010 $105.332 $110.384
22.861
27.067
34.628
43.616
50.810
$104.485 $115.316 $133.638 $148.948 $161.194
33.699
38.626
44.622
51.198
62.006
$70.786
$76.690
$89.016
$97.750
$99.188
1.922
1.515
1.367
1.725
1.048
$72.708
$78.205
$90.383
$99.475 $100.236
$60.245
$73.858
$81.525
$90.645
$89.505
3.045
3.147
3.093
3.002
2.980
3.466
3.689
4.395
4.258
6.031
$66.756
$80.694
$89.013
$97.905
$98.516

Inpatient revenue
Outpatient revenue
Gross patient revenue
Allowances and discounts
Net patient revenue
Other operating revenue
Total operating revenue
Patient services expenses
Interest expense
Depreciation
Total operating expense
Net income

$5.952

($2.489)

$1.370

$1.570

$1.720

MODEL-GENERATED DATA:
Avalon General Hospital
Pro Forma (Forecasted) Income Statements:
(Millions of Dollars)

Inpatient revenue
Outpatient revenue
Gross patient revenue
Allowances and discounts
Net patient revenue
Other operating revenue
Total operating revenue
Patient services expenses
Interest expense
Total operating expense
Net operating cash flow
Cost savings at teaching (other) hospital
Growth retentions
Terminal value

2006
2007
2008
2009
$110.384 $110.384 $110.384 $110.384
50.810
50.810
50.810
50.810
$161.194 $161.194 $161.194 $161.194
0.000
0.000
0.000
0.000
$161.194 $161.194 $161.194 $161.194
1.048
1.048
1.048
1.048
$162.242 $162.242 $162.242 $162.242
$0.000
$0.000
$0.000
$0.000
0.000
0.000
0.000
0.000
$0.000
$0.000
$0.000
$0.000
$162.242 $162.242 $162.242 $162.242
0.000
0.000
0.000
0.000
0.000
0.000
0.000
0.000

2010
$110.384
50.810
$161.194
0.000
$161.194
1.048
$162.242
$0.000
0.000
$0.000
$162.242
0.000
0.000
#DIV/0!

Net cash flow to equityholders

$162.242

$162.242

$162.242

$162.242

#DIV/0!

EBITDA

$162.242

$162.242

$162.242

$162.242

#DIV/0!

Average 5-year EBITDA

#DIV/0!

Valuation Results:
(Millions of Dollars)
Value according to DCF method
Value according to market multiple method:
Applied to 2006 EBITDA
Applied to average 2006-2010 EBITDA
Applied to number of discharges

#DIV/0!
$0.000
#DIV/0!
$0.000

Sensitivity of DCF Value to Terminal Growth Rate and Discount Rate Estimates:

TV
Growth
Rate

#DIV/0!
1%
2%
3%
4%
5%
6%
7%

12%
$1,430.129
$1,523.863
$1,638.427
$1,781.632
$1,965.753
$2,211.248
$2,554.940

13%
$1,311.801
$1,387.185
$1,477.645
$1,588.207
$1,726.410
$1,904.099
$2,141.018

Discount Rate
14%
15%
16%
###
###
###
###
###
###
###
###
###
###
###
###
###
###
###
### $1,493.891
###
###
### $1,449.591

17%
18%
$986.196 $928.692
### $959.457
### $994.325
###
###
###
###
###
###
###
###

END

Page 1

 View Full Attachment Show more