44 MERGER LBO VALUATION WORKSHEET 44 INPUT SHEET: MERGER & LBO VALUATION STEP 1: Estimate the total cost of the deal Price per share= 201 Debt outstanding currently (mil)= 1800 # shares outstanding (mil)= 21.9 Other costs (Investment banker etc.)= 0 Total cost of the deal= 6201.9 STEP 2: Define how the deal will be financed Repayment schedule ( as % of principal) Source Amount Int. rate 1 2 3 4 5 6 7 8 9 Equity 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% 0.00% 0.00% Debt: Type 2 3500 18.00% 0.00% 0.00% 0.00% 10.00% 10.00% 10.00% 10.00% 20.00% 20.00% Debt: Type 3 0 0.00% Debt: Type 4 0 0.00% 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 9 10 Revenues 14.00% 14.00% 14.00% 14.00% 14.00% 8.00% 8.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% 8.00% 8.00% Cap. Spending (See below) 14.00% 14.00% 14.00% 14.00% 14.00% 8.00% 8.00% 8.00% 8.00% 8.00% Working Cap : % of Revenue 16.00% 16.00% 16.00% 16.00% 16.00% 16.00% 16.00% 16.00% 16.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= 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 the firm from those assets) 1 2 3 4 5 6 7 8 9 10 Assets sold: Mkt. Value $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Assets sold: Revenues $0 $0 $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% 0.00% 0.00% Assets sold: Depreciation $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Assets sold: Capital Spending $0 $0 $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 sold] STEP 6: ENTER INFORMATION ON CURRENT INCOME STATEMENT Revenues = $10,000 COGS as % of Revenue= 85.68% (Default = 1 -(EBIT+Depreciation)/Revenues) Current EBIT = $892.00 Current Capital Spending= $438.00 Current Int. Exp= $200.00 Working Cap. as % of Revenues= 16.00% Current Deprec'n= $540.00 Interest rate on Debt Currently= 12.00%46 MERGER LBO VALUATION WORKSHEET 46 CASHFLOWS FROM LBO PRE-LBO AFTER LBO CURRENT 1 2 3 4 5 6 7 8 9 10 Revenues $10,000 $11,400 $12,996 $14,815 $16,890 $19,254 $20,794 $22,458 $24,255 $26,195 $28,291 COGS $8,568 $9,768 $11,135 $12,694 $14,471 $16,497 $17,817 $19,242 $20,781 $22,444 $24,239 Depreciation $540 $616 $702 $800 $912 $1,040 $1,123 $1,213 $1,310 $1,415 $1,528 EBIT $892 $1,017 $1,159 $1,322 $1,507 $1,717 $1,855 $2,003 $2,164 $2,337 $2,524 -Int: Type 1 $200 $272 $272 $272 $272 $272 $272 $272 $272 $272 $272 -Int: Type 2 $0 $630 $630 $630 $630 $567 $504 $441 $378 $252 $126 -Int: Type 3 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 -Int: Type 4 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Taxable Income $692 $115 $257 $420 $605 $878 $1,079 $1,290 $1,514 $1,813 $2,126 -Taxes $277 $46 $103 $168 $242 $351 $432 $516 $605 $725 $850 Net Income $415 $69 $154 $252 $363 $527 $647 $774 $908 $1,088 $1,275 + Deprec'n $540 $616 $702 $800 $912 $1,040 $1,123 $1,213 $1,310 $1,415 $1,528 CF from Oper. $955 $685 $856 $1,052 $1,275 $1,567 $1,770 $1,987 $2,218 $2,502 $2,803 -Capital Sp. $438 $499 $569 $649 $740 $843 $911 $984 $1,062 $1,147 $1,239 -WC Chg $196 $224 $255 $291 $332 $378 $246 $266 $287 $310 $335 -Prin. Rep:1 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 -Prin. Rep:2 $0 $0 $0 $0 $350 $350 $350 $350 $700 $700 $700 -Prin. Rep:3 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 -Prin. Rep:4 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 -Pref. Div $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 + Asset Sales $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 CF to Equity $321 ($39) $32 $112 ($147) ($5) $263 $387 $168 $344 $52947 MERGER LBO VALUATION WORKSHEET 47 + Pref. Div $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 + Int (1-t) $120 $541 $541 $541 $541 $503 $466 $428 $390 $314 $239 + Princ. Rep $0 $0 $0 $0 $350 $350 $350 $350 $700 $700 $700 CF to firm $441 $502 $573 $653 $744 $849 $1,079 $1,165 $1,258 $1,359 $1,467 Term Value of Equity= $17,828 Term Value of Firm= $19,52848 MERGER LBO VALUATION WORKSHEET 48 CAPITAL STRUCTURE AND COSTS OF EQUITY/CAPITAL PRE-LBO AFTER-LBO CURRENT 1 2 3 4 5 6 7 8 9 10 Debt: Type 1 $1,800 $1,700 $1,700 $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 $1,400 $700 Debt: Type 3 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Debt: Type 4 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 Pfd. Div $0 $0 $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 $4,694 $5,782 D/E 40.89% 520.00% 486.47% 425.09% 352.54% 263.91% 190.29% 137.78% 100.36% 66.04% 41.51% D/(D+E+Pfd) 29.02% 83.87% 82.95% 80.96% 77.90% 72.52% 65.55% 57.94% 50.09% 39.77% 29.33% Pfd/(D+E+Pfd) 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% Beta 1.05 3.47 3.30 2.99 2.63 2.18 1.81 1.54 1.35 1.18 1.05 Cost of Equity 16.93% 37.53% 36.08% 33.45% 30.33% 26.51% 23.35% 21.09% 19.48% 18.01% 16.95% Interest rate 12.00% 17.35% 17.35% 17.35% 17.35% 17.30% 17.24% 17.18% 17.11% 16.90% 16.58% WACC 14.10% 14.78% 14.79% 14.80% 14.81% 14.81% 14.83% 14.84% 14.86% 14.88% 14.90% Cum Cost of Eq. 1.37526672 1.87152932 2.49747144 3.25485541 4.11787784 5.07936716 6.15065894 7.34894542 8.6722131 10.1422861 Cum WACC 1.14781721 1.31753294 1.51246328 1.7364457 1.99367033 2.28924788 2.62904569 3.01983671 3.46914089 3.9859659949 MERGER LBO VALUATION WORKSHEET 49 RESULTS OF THE MERGER/LBO ANALYSIS PV of CF Investment Decision Equity Investors $1,974 $1,000 ACCEPT THE DEAL All Investors $9,148 $6,200 ACCEPT THE DEAL Cashflow to Equity Investors Average FCFE = $164 Maximum FCFE = $529 Minimum FCFE= ($147) Std. Deviation of FCFE= $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 Beta in year 10 = 0.9650 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,46253 MERGER LBO VALUATION WORKSHEET 53 $0 $122 $0 $1,58554 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%