44
MERGER LBO VALUATION WORKSHEET
44
INPUT SHEET: MERGER & LBO VALUATION STEP 1: Estimate the total cost of the deal Price per share=
201
# shares outstanding (mil)=
21.9
Total cost of the deal=
Debt outstanding currently (mil)=
1800
ther costs (Investment banker etc.)=
0
6 2 0 1 .9
STEP 2: Define how the deal will be financed Repayment schedule ( as % of principal) Source
Amount
In Int. rate
1
2
3
4
5
6
7
1000
NA
Pfd. STOCK
0
0.00%
Debt: Type 1
1700
16.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
Debt: Type 2
3500
18.00%
0.00%
0.00%
0.00%
10.00%
10.00%
10.00%
10.00%
Debt: Type 3
0
0.00%
Debt: Type 4
0
0.00%
Equity
Interest rate on debt remaining in terminal year=
12.00%
Suggestion: For the first run, enter an arbitrary schedule; Check cashflows to equity; If -'ve modify schedule;
STEP 3: Define rates of future growth 1
2
3
4
5
6
7
8
Revenues
14.00%
14.00%
14.00%
14.00%
14.00%
8.00%
8.00%
8.00%
Depreciation (See below)
14.00%
14.00%
14.00%
14.00%
14.00%
8.00%
8.00%
8.00%
Cap. Spending (See below)
14.00%
14.00%
14.00%
14.00%
14.00%
8.00%
8.00%
8.00%
45
MERGER LBO VALUATION WORKSHEET
45
Working Cap : % of Revenu
16.00%
16.00%
16.00%
16.00%
16.00%
16.00%
16.00%
16.00%
COGS: % of Revenues
85.68%
85.68%
85.68%
85.68%
85.68%
85.68%
85.68%
85.68%
Default values= If you do not enter rates, the growth rates in depreciation, capital spending = growth rate in revenues. es for COGS and Working Capital come from Step 6 below.
STEP 4: GENERAL INFORMATION Current T.Bill rate=
8.00%
Market risk premium=
8.50%
Ordinary tax rate=
40.00%
Current beta of firm=
1.05
STEP 5: SPECIFY ASSETS TO BE SOLD (Enter the year in which assets will be sold and market value and CF to th 1
2
3
4
5
6
7
8
Assets sold: Mkt. Value
$0
$0
$0
$0
$0
$0
$0
$0
Assets sold: Revenues
$0
$0
$0
$0
$0
$0
$0
$0
Assets sold: COGS (%)
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
Assets sold: Depreciation
$0
$0
$0
$0
$0
$0
$0
$0
Assets sold: Capital Spendin
$0
$0
$0
$0
$0
$0
$0
$0
[ Enter the market value of the asset sold; Enter the EBIT, Depreciation and Capital Spending of the asset; Only in the year sol
STEP 6: ENTER INFORMATION ON CURRENT INCOME STATEMENT Revenues =
$10,000
COGS as % of Revenue=
Current EBIT =
$892.00
Current Capital Spending=
Current Int. Exp=
$200.00
Working Cap. as % of Revenues=
16.00%
Current Deprec'n=
$540.00
Interest rate on Debt Currently=
12.00%
85.68% $438.00
(Default = 1 - (EBIT+De
46
MERGER LBO VALUATION WORKSHEET
46
CASHFLOWS FROM LBO PRE- LBO CURRENT
AFTER LBO 1
2
3
4
5
6
7
8
$10,000
$11,400
$12,996
$14,815
$16,890
$19,254
$20,794
$22,458
$24,255
$8,568
$9,768
$11,135
$12,694
$14,471
$16,497
$17,817
$19,242
$20,781
Depreciation
$540
$616
$702
$800
$912
$1,040
$1,123
$1,213
$1,310
EBIT
$892
$1,017
$1,159
$1,322
$1,507
$1,717
$1,855
$2,003
$2,164
-Int: Type 1
$200
$272
$272
$272
$272
$272
$272
$272
$272
-Int: Type 2
$0
$630
$630
$630
$630
$567
$504
$441
$378
-Int: Type 3
$0
$0
$0
$0
$0
$0
$0
$0
$0
-Int: Type 4
$0
$0
$0
$0
$0
$0
$0
$0
$0
Revenues COGS
Taxable Income - Taxes
$692
$115
$257
$420
$605
$878
$1,079
$1,290
$1,514
$277
$46
$103
$168
$242
$351
$432
$516
$605
Net Income
$415
$69
$154
$252
$363
$527
$647
$774
$908
+ Deprec'n
$540
$616
$702
$800
$912
$1,040
$1,123
$1,213
$1,310
CF from Oper.
$955
$685
$856
$1,052
$1,275
$1,567
$1,770
$1,987
$2,218
- Capital Sp.
$438
$499
$569
$649
$740
$843
$911
$984
$1,062
- WC Chg
$196
$224
$255
$291
$332
$378
$246
$266
$287
- Prin. Rep:1
$0
$0
$0
$0
$0
$0
$0
$0
$0
- Prin. Rep:2
$0
$0
$0
$0
$350
$350
$350
$350
$700
- Prin. Rep:3
$0
$0
$0
$0
$0
$0
$0
$0
$0
- Prin. Rep:4
$0
$0
$0
$0
$0
$0
$0
$0
$0
- Pref. Div
$0
$0
$0
$0
$0
$0
$0
$0
$0
+ Asset Sales
$0
$0
$0
$0
$0
$0
$0
$0
$0
47
MERGER LBO VALUATION WORKSHEET
47
CF to Equity
$321
($39)
$32
$112
($147)
($5)
$263
$387
$168
+ Pref. Div
$0
$0
$0
$0
$0
$0
$0
$0
$0
+ Int (1-t)
$120
$541
$541
$541
$541
$503
$466
$428
$390
$0
$0
$0
$0
$350
$350
$350
$350
$700
$441
$502
$573
$653
$744
$849
$1,079
$1,165
$1,258
+ Princ. Rep CF to firm
Term Value of Equity= Term Value of Firm=
48
MERGER LBO VALUATION WORKSHEET
48
CAPITAL STRUCTURE AND COSTS OF EQUITY/CAPITAL PRE-LBO CURRENT
AFTER-LBO 1
2
3
4
5
6
7
8
Debt: Type 1
$1,800
$1,700
$1,700
$1,700
$1,700
$1,700
$1,700
$1,700
$1,700
Debt: Type 2
$0
$3,500
$3,500
$3,500
$3,500
$3,150
$2,800
$2,450
$2,100
Debt: Type 3
$0
$0
$0
$0
$0
$0
$0
$0
$0
Debt: Type 4
$0
$0
$0
$0
$0
$0
$0
$0
$0
Pfd. Div
$0
$0
$0
$0
$0
$0
$0
$0
$0
Equity
$4, 402
$1,000
$1,069
$1,223
$1,475
$1, 838
$2,365
$3,012
$3,786
D/E
40.89%
520.00%
486.47%
425.09%
352.54%
263.91%
190.29%
137.78%
100.36%
D/(D+E+Pfd)
29.02%
83.87%
82.95%
80.96%
77.90%
72.52%
65.55%
57.94%
50.09%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
0.00%
1.05
3.47
3.30
2.99
2.63
2.18
1.81
1.54
1.35
Cost of Equity
16.93%
37.53%
36.08%
33.45%
30.33%
26.51%
23.35%
21.09%
19.48%
Interest rate
12.00%
17.35%
17.35%
17.35%
17.35%
17.30%
17.24%
17.18%
17.11%
WACC
14.10%
14.78%
14.79%
14.80%
14.81%
14.81%
14.83%
14.84%
14.86%
Cum Cost of Eq.
137.53%
1.87
2.5
3.25
4.12
5.08
6.15
7.35
Cum WACC
114.78%
1.32
1.51
1.74
1.99
2.29
2.63
3.02
Pfd/(D+E+Pfd)
Beta
49
MERGER LBO VALUATION WORKSHEET
49
RESULTS OF THE MERGER/LBO ANALYSIS
PV of CF nvestment Decision Equity Investors All Investors
$1,974
$1,000
ACCEPT THE DEAL
$9,148
$6,200
ACCEPT THE DEAL
Cashflow to Equity Investors Average FCFE = Maximum FCFE = Minimum FCFE= Std. Deviation of FCFE=
$164 $529 ($147) $214
Leverage D/E Ratio before LBO =
40.89%
D/E Ratio after LBO =
520.00%
D/E Ratio in year 5=
190.29%
D/E Ratio in year 10 =
24.09%
Beta Beta before LBO =
1.05
Beta after LBO =
3.47
50
MERGER LBO VALUATION WORKSHEET
Beta in year 10 =
0.96
50
51
MERGER LBO VALUATION WORKSHEET
8
9
10
0.00%
0.00%
0.00%
20.00%
20.00%
20.00%
9
10
After yr 10
8.00%
8.00%
8.00%
8.00%
8.00%
8.00%
8.00%
8.00%
8.00%
51
52
MERGER LBO VALUATION WORKSHEET
16.00%
16.00%
16.00%
85.68%
85.68%
85.68%
e firm from those assets) 9
10
$0
$0
$0
$0
0.00%
0.00%
$0
$0
$0
$0
ld]
reciation)/Revenues)
52
53
MERGER LBO VALUATION WORKSHEET
9
10
Term Year
$26,195
$28,291
$30,554
$22,444
$24,239
$26,179
$1,415
$1,528
$1,650
$2,337
$2,524
$2,725
$272
$272
$204
$252
$126
$0
$0
$0
$0
$0
$0
$0
$1,813
$2,126
$2,521
$725
$850
$1,009
$1,088
$1,275
$1,513
$1,415
$1,528
$1,650
$2,502
$2,803
$3,163
$1,147
$1,239
$1,338
$310
$335
$362
$0
$0
$0
$700
$700
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
53
54
MERGER LBO VALUATION WORKSHEET
$344
$529
$1,462
$0
$0
$0
$314
$239
$122
$700
$700
$0
$1,359
$1,467
$1,585
$17,828 $19,528
54
55
MERGER LBO VALUATION WORKSHEET
9
10
Term Year
$1,700
$1,700
$1,700
$1,400
$700
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$4,694
$5,782
$7,057
66.04%
41.51%
24.09%
39.77%
29.33%
19.41%
0.00%
0.00%
0.00%
1.18
1.05
0.96
18.01%
16.95%
16.20%
16.90%
16.58%
12.00%
14.88%
14.90%
14.45%
8.67
10.14
3.47
3.99
55
56
MERGER LBO VALUATION WORKSHEET
56
57
MERGER LBO VALUATION WORKSHEET
57
