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