并购模型(Excel表格以及公式)

InvestmentBanking,2EValuation,LeveragedBuyouts,andMergers&AcquisitionsM&AModel-CompletedJOSHUAROSENBAUMJOSHUAPEARLBuyerCoAcquisitionofValueCoMergerConsequencesAnalysisFinancingStructure:Structure1($inmillions,fiscalyearendingDecember31)OperatingScenario:BaseTransactionSummarySourcesofFundsUsesofFundsPremiumPaid&ExchangeRatioAcquisitionStructure&Synergies%ofTotalMultipleofProFormaEBITDA%ofTotalValueCoCurrentSharePrice$43.50StockConsiderationforEquity50%AmountSources2012CumulativePricingAmountUsesOfferPriceperShare$58.73TransactionDebtRaised$3,700.0RevolvingCreditFacility--%-x-xL+350bpsPurchaseValueCoEquity$4,700.074.0%PremiumPaid35%%ofValueCoEnterpriseValue62%TermLoanA--%-x-xNARepayExistingDebt1,500.023.6%BuyerCoCurrentSharePrice$70.00AcquisitionTypeStockSaleTermLoanB2,200.034.6%1.0x1.0xL+375bpsTender/CallPremiums20.00.3%ExchangeRatio0.8xTermLoanC--%-x1.0xNATransactionFees40.00.6%Year1Synergies$1002ndLien--%-x1.0xNADebtFinancingFees90.01.4%PurchasePriceOptionsSeniorNotes1,500.023.6%0.6x1.6x7.500%OfferPriceperShare$58.73FinancingStructure1SeniorSubordinatedNotes--%-x1.6xNAFullyDilutedShares80.0OperatingScenario1IssuanceofCommonStock2,350.037.0%1.0x2.6xEquityPurchasePrice$4,700.0CashFlowSweep1CashonHand300.04.7%0.1x2.7xCashBalance1Other--%-x2.6xPlus:ExistingNetDebt1,250.0AverageInterest1TotalSources$6,350.0100.0%2.7x2.7xTotalUses$6,350.0100.0%EnterpriseValue$5,950.0FinancingFees1ProFormaCombinedFinancialSummaryValuationSummaryProForma12345TargetAcquirer201220132014201520162017CompanyNameValueCoBuyerCoSales$10,205.8$10,937.5$11,593.7$12,173.4$12,660.3$13,040.1TickerVLCOBUY%growth8.2%7.2%6.0%5.0%4.0%3.0%CurrentSharePrice(12/20/2012)$43.50$70.00GrossProfit$3,947.2$4,230.4$4,484.2$4,708.4$4,896.8$5,043.7PremiumtoCurrentSharePrice35%%margin38.7%38.7%38.7%38.7%38.7%38.7%OfferPriceperShare$58.73EBITDA$2,311.3$2,469.7$2,611.9$2,737.5$2,843.0$2,925.3FullyDilutedShares80.0140.0%margin22.6%22.6%22.5%22.5%22.5%22.4%EquityValue$4,700.0$9,800.0InterestExpense374.7348.5296.2270.2270.2270.2Plus:TotalDebt1,500.02,200.0NetIncome$945.8$1,043.3$1,148.8$1,231.4$1,289.6$1,336.0Plus:PreferredEquity--%margin9.3%9.5%9.9%10.1%10.2%10.2%Plus:NoncontrollingInterest--FullyDilutedShares173.6173.6173.6173.6173.6173.6Less:CashandEquivalents(250.0)(400.0)DilutedEPS$5.45$6.01$6.62$7.09$7.43$7.70EnterpriseValue$5,950.0$11,600.0CashFlowfromOperatingActivities1,387.91,528.01,641.71,729.61,803.5TransactionMultiplesLess:CapitalExpenditures(383.8)(406.8)(427.1)(444.2)(457.5)TargetAcquirerFreeCashFlow$1,004.1$1,121.2$1,214.6$1,285.4$1,346.0MetricMultipleMetricMultipleEnterpriseValue/LTMEBITDA$700.08.5x$1,443.18.0xSeniorSecuredDebt2,200.01,095.90.00.00.00.0EnterpriseValue/2012EEBITDA725.08.2x1,486.37.8xSeniorDebt5,900.04,795.93,700.03,700.03,700.03,700.0EnterpriseValue/2013EEBITDA779.47.6x1,590.37.3xTotalDebt5,900.04,795.93,700.03,700.03,700.03,700.0Cash&Equivalents350.0250.0275.31,489.92,775.34,121.3EquityValue/2012ENetIncome$268.817.5x$728.513.5xEquityValue/2013ENetIncome297.015.8x786.812.5xCreditStatisticsProFormaOwnershipBuyerCoProForma12345SharesOwnership2012201220132014201520162017ExistingBuyerCoShareholders140.080.7%EBITDA/InterestExpense10.3x6.2x7.1x8.8x10.1x10.5x10.8xFormerValueCoShareholders33.619.3%(EBITDA-Capex)/InterestExpense8.9x5.2x6.0x7.4x8.6x8.9x9.1xProFormaFullyDilutedShares173.6100.0%SeniorSecuredDebt/EBITDA-x1.0x0.4x-x-x-x-xAnnualEPSAccretion/(Dilution)SensitivityAnalysis-PremiumPaidSeniorDebt/EBITDA1.5x2.6x1.9x1.4x1.4x1.3x1.3xYearTotalDebt/EBITDA1.5x2.6x1.9x1.4x1.4x1.3x1.3xOfferPricePremium201220132014201520164.7%7.0%10.1%11.6%11.6%NetDebt/EBITDA1.2x2.4x1.8x1.3x0.8x0.3x(0.1x)$54.3825%7.3%9.5%12.4%13.6%13.7%Debt/TotalCapitalization47.0%55.3%45.2%34.7%31.1%28.1%25.5%$56.5530%6.0%8.2%11.3%12.6%12.7%$58.7335%4.7%7.0%10.1%11.6%11.6%$60.9040%3.5%5.7%8.9%10.5%10.6%Accretion/(Dilution)Analysis$63.0845%2.2%4.5%7.7%9.4%9.7%BuyerCoStandaloneDilutedEPS$5.20$5.62$6.01$6.36$6.66$6.892013EEPSAccretion/(Dilution)SensitivityAnalysis-PremiumPaid&ConsiderationMixValueCoStandaloneDilutedEPS$3.36$3.71$4.08$4.44$4.70$4.86%StockConsiderationMixOfferPricePremium0%25%50%75%100%7.0%0.0%25.0%50.0%75.0%100.0%ProFormaCombinedDilutedEPS$5.45$6.01$6.62$7.09$7.43$7.70$54.3825%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.5530%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.7335%23.3%14.3%7.0%0.8%(5.6%)Accretive/DilutiveAccretiveAccretiveAccretiveAccretiveAccretiveAccretive$60.9040%22.4%13.1%5.7%(0.4%)(6.9%)BreakevenPre-TaxSynergies/(Cushion)($68.8)($109.5)($170.2)($206.0)($216.9)($225.6)$63.0845%21.4%12.0%4.5%(1.6%)(8.1%)($inmillions,exceptpersharedata,fiscalyearendingDecember31)ProFormaIncomeStatementProjectionPeriodLTMProFormaYear1Year2Year3Year4Year52009201020119/30/2012201220132014201520162017BuyerCoSales$4,771.7$5,484.7$6,232.6$6,559.6$6,755.8$7,228.7$7,662.4$8,045.5$8,367.4$8,618.4ValueCoSales2,600.02,900.03,200.03,385.03,450.03,708.83,931.34,127.84,293.04,421.7TotalSales$7,371.7$8,384.7$9,432