并購(gòu)模型(Excel表格以及公式)

1、,BuyerCo Acquisition of ValueCo,Merger Consequences Analysis,Financing Structure:,Structure 1,($ in millions, fiscal year ending December 31),Operating Scenario:, Base,Transaction Summary,Sources of Funds,Uses of Funds,Premium Paid & Exchange Ratio,Acquisition Structure & Synergies,% of Total,Multip

2、le of Pro Forma EBITDA,% of Total,ValueCo Current Share Price,$43.50 ,Stock Consideration for Equity,50%,Amount,Sources,2012,Cumulative,Pricing,Amount,Uses,Offer Price per Share,$58.73 ,Transaction Debt Raised, $3,700.0 ,Revolving Credit Facility, - , - % , - x , - x ,L+350 bps,Purchase ValueCo Equi

3、ty, $4,700.0 , 74.0%, Premium Paid, 35%,% of ValueCo Enterprise Value,62%,Term Loan A, - , - % , - x , - x ,NA,Repay Existing Debt, 1,500.0 , 23.6%,BuyerCo Current Share Price,$70.00 ,Acquisition Type,Stock Sale,Term Loan B, 2,200.0 , 34.6%, 1.0 x, 1.0 x,L+375 bps,Tender / Call Premiums, 20.0 , 0.3%

4、, Exchange Ratio, 0.8x,Term Loan C, - , - % , - x , 1.0 x,NA,Transaction Fees, 40.0 , 0.6%,Year 1 Synergies,$100 ,2nd Lien, - , - % , - x , 1.0 x,NA,Debt Financing Fees , 90.0 , 1.4%,Purchase Price,Options,Senior Notes, 1,500.0 , 23.6%, 0.6x, 1.6x,7.500%,Offer Price per Share, $58.73 ,Financing Stru

5、cture,1,Senior Subordinated Notes, - , - % , - x , 1.6x,NA,Fully Diluted Shares, 80.0 ,Operating Scenario,1,Issuance of Common Stock, 2,350.0 , 37.0%, 1.0 x, 2.6x, Equity Purchase Price, $4,700.0 ,Cash Flow Sweep,1,Cash on Hand, 300.0 , 4.7%, 0.1x, 2.7x,Cash Balance,1,Other, - , - % , - x , 2.6x, ,

6、,Plus: Existing Net Debt, 1,250.0 ,Average Interest,1, Total Sources, $6,350.0 , 100.0%, 2.7x, 2.7x, Total Uses, $6,350.0 , 100.0%, Enterprise Value, $5,950.0 ,Financing Fees,1,Pro Forma Combined Financial Summary,Valuation Summary,Pro Forma,1,2,3,4,5,Target,Acquirer,2012,2013,2014,2015,2016,2017,Co

7、mpany Name,ValueCo,BuyerCo,Sales,$10,205.8 ,$10,937.5 ,$11,593.7 ,$12,173.4 ,$12,660.3 ,$13,040.1 ,Ticker,VLCO,BUY, % growth, 8.2%, 7.2%, 6.0%, 5.0%, 4.0%, 3.0%,Current Share Price (12/20/2012),$43.50 ,$70.00 ,Gross Profit,$3,947.2 ,$4,230.4 ,$4,484.2 ,$4,708.4 ,$4,896.8 ,$5,043.7 ,Premium to Curren

8、t Share Price,35%, % margin, 38.7%, 38.7%, 38.7%, 38.7%, 38.7%, 38.7%,Offer Price per Share,$58.73 ,EBITDA,$2,311.3 ,$2,469.7 ,$2,611.9 ,$2,737.5 ,$2,843.0 ,$2,925.3 ,Fully Diluted Shares,80.0 ,140.0 , % margin, 22.6%, 22.6%, 22.5%, 22.5%, 22.5%, 22.4%, Equity Value,$4,700.0 ,$9,800.0 ,Interest Expe

9、nse,374.7 ,348.5 ,296.2 ,270.2 ,270.2 ,270.2 ,Plus: Total Debt, 1,500.0 , 2,200.0 ,Net Income,$945.8 ,$1,043.3 ,$1,148.8 ,$1,231.4 ,$1,289.6 ,$1,336.0 ,Plus: Preferred Equity, - , - , % margin, 9.3%, 9.5%, 9.9%, 10.1%, 10.2%, 10.2%,Plus: Noncontrolling Interest, - , - ,Fully Diluted Shares,173.6 ,17

10、3.6 ,173.6 ,173.6 ,173.6 ,173.6 ,Less: Cash and Equivalents,(250.0),(400.0),Diluted EPS,$5.45 ,$6.01 ,$6.62 ,$7.09 ,$7.43 ,$7.70 , Enterprise Value,$5,950.0 ,$11,600.0 ,Cash Flow from Operating Activities,1,387.9 ,1,528.0 ,1,641.7 ,1,729.6 ,1,803.5 ,Transaction Multiples,Less: Capital Expenditures,(

11、383.8),(406.8),(427.1),(444.2),(457.5),Target,Acquirer,Free Cash Flow,$1,004.1 ,$1,121.2 ,$1,214.6 ,$1,285.4 ,$1,346.0 ,Metric,Multiple,Metric,Multiple,Enterprise Value / LTM EBITDA, $700.0 ,8.5x, $1,443.1 ,8.0 x,Senior Secured Debt,2,200.0 ,1,095.9 ,0.0 ,0.0 ,0.0 ,0.0 ,Enterprise Value / 2012E EBIT

12、DA, 725.0 ,8.2x, 1,486.3 ,7.8x,Senior Debt,5,900.0 ,4,795.9 ,3,700.0 ,3,700.0 ,3,700.0 ,3,700.0 ,Enterprise Value / 2013E EBITDA, 779.4 ,7.6x, 1,590.3 ,7.3x,Total Debt,5,900.0 ,4,795.9 ,3,700.0 ,3,700.0 ,3,700.0 ,3,700.0 ,Cash & Equivalents,350.0 ,250.0 ,275.3 ,1,489.9 ,2,775.3 ,4,121.3 ,Equity Valu

13、e / 2012E Net Income, $268.8 ,17.5x, $728.5 ,13.5x,Equity Value / 2013E Net Income, 297.0 ,15.8x, 786.8 ,12.5x,Credit Statistics, ,Pro Forma Ownership,BuyerCo,Pro Forma,1,2,3,4,5,Shares,Ownership,2012,2012,2013,2014,2015,2016,2017,Existing BuyerCo Shareholders,140.0 , 80.7%,EBITDA / Interest Expense

14、 , 10.3x, 6.2x, 7.1x, 8.8x, 10.1x, 10.5x, 10.8x ,Former ValueCo Shareholders,33.6 , 19.3%,(EBITDA - Capex) / Interest Expense, 8.9x, 5.2x, 6.0 x, 7.4x, 8.6x, 8.9x, 9.1x, Pro Forma Fully Diluted Shares,173.6 ,100.0%,Senior Secured Debt / EBITDA, - x , 1.0 x, 0.4x, - x , - x , - x , - x ,Annual EPS Ac

15、cretion / (Dilution) Sensitivity Analysis - Premium Paid,Senior Debt / EBITDA, 1.5x, 2.6x, 1.9x, 1.4x, 1.4x, 1.3x, 1.3x,Year,Total Debt / EBITDA, 1.5x, 2.6x, 1.9x, 1.4x, 1.4x, 1.3x, 1.3x,Offer Price,Premium,2012,2013,2014,2015,2016,4.7% ,7.0% ,10.1% ,11.6% ,11.6% ,Net Debt / EBITDA, 1.2x, 2.4x, 1.8x

16、, 1.3x, 0.8x, 0.3x, (0.1x),$54.38 ,25%,7.3% ,9.5% ,12.4% ,13.6% ,13.7% ,Debt / Total Capitalization, 47.0% , 55.3% , 45.2% , 34.7% , 31.1% , 28.1% , 25.5% ,$56.55 ,30%,6.0% ,8.2% ,11.3% ,12.6% ,12.7% ,$58.73 ,35%,4.7% ,7.0% ,10.1% ,11.6% ,11.6% ,$60.90 ,40%,3.5% ,5.7% ,8.9% ,10.5% ,10.6% ,Accretion

17、/ (Dilution) Analysis,$63.08 ,45%,2.2% ,4.5% ,7.7% ,9.4% ,9.7% ,BuyerCo Standalone Diluted EPS,$5.20 ,$5.62 ,$6.01 ,$6.36 ,$6.66 ,$6.89 ,2013E EPS Accretion / (Dilution) Sensitivity Analysis - Premium Paid & Consideration Mix,ValueCo Standalone Diluted EPS,$3.36 ,$3.71 ,$4.08 ,$4.44 ,$4.70 ,$4.86 ,%

18、 Stock Consideration Mix,Offer Price,Premium,0%,25%,50%,75%,100%,7.0% ,0.0% ,25.0% ,50.0% ,75.0% ,100.0% ,Pro Forma Combined Diluted EPS,$5.45 ,$6.01 ,$6.62 ,$7.09 ,$7.43 ,$7.70 ,$54.38 ,25%,25.2% ,16.5% ,9.5% ,3.4% ,(2.9%),Accretion / (Dilution) - $,$0.25 ,$0.39 ,$0.61 ,$0.74 ,$0.77 ,$0.81 ,$56.55

19、,30%,24.2% ,15.4% ,8.2% ,2.1% ,(4.2%),Accretion / (Dilution) - %,4.7% ,7.0% ,10.1% ,11.6% ,11.6% ,11.7% ,$58.73 ,35%,23.3% ,14.3% ,7.0% ,0.8% ,(5.6%),Accretive / Dilutive,Accretive,Accretive, Accretive , Accretive , Accretive , Accretive ,$60.90 ,40%,22.4% ,13.1% ,5.7% ,(0.4%),(6.9%),Breakeven Pre-T

20、ax Synergies / (Cushion),($68.8),($109.5),($170.2),($206.0),($216.9),($225.6),$63.08 ,45%,21.4% ,12.0% ,4.5% ,(1.6%),(8.1%), ,Breakeven Synergies Sensitivity Analysis,2013E EPS Accretion / (Dilution) Sensitivity Analysis - Premium Paid & Synergies,Synergies Required to Breakeven,Estimated Synergies,

21、Offer Price,Premium,2012,2013,2014,2015,2016,Offer Price,Premium,$0 ,$50 ,$100 ,$150 ,$200 ,$31 ,($10),($70),($106),($117),7.0%,$0 ,$50 ,$100 ,$150 ,$200 ,$54.38 ,25%,($5),($47),($106),($139),($151),$54.38 ,25%,2.9% ,6.2% ,9.5% ,12.8% ,16.0% ,$56.55 ,30%,$13 ,($28),($88),($123),($134),$56.55 ,30%,1.7% ,5.0% ,8.2% ,11.5% ,14.7% ,$58.73 ,35%,$31 ,($10),($70),($106),