Financial Analysis Excel Templates 企业估值模板-lboval

44

MERGER LBO VALUATION WORKSHEET

44

INPUT SHEET: MERGER & LBO VALUATIONSTEP 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%

INPUT SHEET: MERGER & LBO VALUATION

Price per share=

# shares outstanding (mil)=Total cost of the deal=

Debt outstanding currently (mil)=Other costs (Investment banker etc.)=

STEP 2: Define how the deal will be financed

Repayment schedule ( as % of principal)

Source

EquityPfd. STOCKDebt: Type 1Debt: Type 2Debt: Type 3Debt: Type 4

Suggestion: For the first run, enter an arbitrary schedule; Check cashflows to equity; If -'ve modify schedule;

STEP 3: Define rates of future growth

Revenues

Depreciation (See below)Cap. Spending (See below)Working Cap : % of Revenue

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)

COGS: % of Revenues

Default values= If you do not enter rates, the growth rates in depreciation, capital spending = growth rate in revenues. values for COGS and Working Capital come from Step 6 below.

Current T.Bill rate=Ordinary tax rate=

Market risk premium=Current beta of firm=

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)

Assets sold: Mkt. ValueAssets sold: RevenuesAssets sold: COGS (%)Assets sold: Depreciation

[ Enter the market value of the asset sold; Enter the EBIT, Depreciation and Capital Spending of the asset; Only in the year sold]

Revenues =Current EBIT =Current Int. Exp=Current Deprec'n=

COGS as % of Revenue=Current Capital Spending=Working Cap. as % of Revenues=Interest rate on Debt Currently=

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 $2

00 $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

RevenuesCOGSDepreciationEBIT -Int: Type 1 -Int: Type 2 -Int: Type 3 -Int: Type 4Taxable Income - TaxesNet Income + Deprec'nCF from Oper. - Capital Sp. - WC Chg - Prin. Rep:1 - Prin. Rep:2 - Prin. Rep:3 - Prin. Rep:4 - Pref. Div + Asset SalesCF to Equity

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=

MERGER LBO VALUATION WORKSHEET + Pref. Div

+ Int (1-t) + Princ. RepCF to firm

47

47

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.37526672 1.87152932 2.49747144 3.25485541 4.11787784 5.07936716 6.15065894 7.34894542 8.6722131 10.1422861 1.14781721 1.31753294 1.51246328 1.7364457 1.99367033 2.28924788 2.62904569 3.01983671 3.46914089 3.98596599

Cum Cost of Eq.Cum WACC

1.375266721.871529322.497471443.254855414.117877845.079367166.150658947.348945428.672213110.14228611.147817211.317532941.512463281.73644571.993670332.289247882.629045693.019836713.469140893.98596599

49

MERGER LBO VALUATION WORKSHEET

49

RESULTS OF THE MERGER/LBO ANALYSIS

P

V 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

RESULTS OF THE MERGER/LBO ANALYSIS

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%

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

$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%

本文来源：https://www.bwwdw.com/article/leei.html