杠桿收購分析EXCEL模板

1、Investment BankingValuation, Leveraged Buyouts,and Mergers & AcquisitionsLBO Analysis - TemplateJOSHUA ROSENBAUMJOSHUA PEARL gvValueCo CorporationLeveraged Buyout AnalysisFinancing Structure:Structure 1($ in millions, fiscal year ending December 31)Operating Scenario: BaseTransaction SummarySour

2、ces of FundsUses of FundsPurchase PriceReturn Analysis% of TotalMultiple of EBITDA% of TotalOffer Price per Share-Exit Year2013AmountSources9/30/2008CumulativePricingAmountUsesFully Diluted Shares-Entry Multiple- xRevolving Credit Facility- %- x- xNAPurchase ValueCo Equity- % Equity Purchase Price-E

3、xit Multiple- xTerm Loan A- %- x- xNARepay Existing Debt- %Plus: Existing Net Debt- IRRNATerm Loan B- %- x- xNATender / Call Premiums- % Enterprise Value- Cash ReturnNMTerm Loan C- %- x- xNAFinancing Fees - %2nd Lien- %- x- xNAOther Fees and Expenses- %Transaction MultiplesOptionsSenior Notes- %- x-

4、 xNAEnterprise Value / SalesFinancing Structure1Senior Subordinated Notes- %- x- xNALTM 9/30/2008- xOperating Scenario1Equity Contribution- %- x- x2008E- xCash Flow Sweep1Rollover Equity- %- x- xEnterprise Value / EBITDACash Balance1Cash on Hand- %- x- x LTM 9/30/2008- xAverage Interest 1 Total Sour

5、ces- %- x- x Total Uses- %2008E- xFinancing Fees1Summary Financial DataHistorical PeriodProjection PeriodLTMPro formaYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 102005200620079/30/200820082009201020112012201320142015201620172018Sales- % growthNA-NA-Gross Profit- % margin-EBITDA- % mar

6、gin-Capital Expenditures- % sales-Cash Interest Expense-Total Interest Expense-Free Cash FlowEBITDA-Less: Cash Interest Expense-Plus: Interest Income-Less: Income Taxes-Less: Capital Expenditures-Less: Increase in Net Working Capital- Free Cash Flow- Cumulative Free Cash Flow-Capitalization Cash-Rev

7、olving Credit Facility-Term Loan A-Term Loan B-Term Loan C-Existing Term Loan-2nd Lien-Other Debt- Total Senior Secured Debt-Senior Notes- Total Senior Debt-Senior Subordinated Notes- Total Debt-Shareholders Equity- Total Capitalization- % of Bank Debt Repaid-Credit Statistics% Debt / Total Capitali

8、zation-EBITDA / Cash Interest Expense- x- x- x- x- x- x- x- x- x- x- x(EBITDA - Capex) / Cash Interest Expense- x- x- x- x- x- x- x- x- x- x- xEBITDA / Total Interest Expense - x- x- x- x- x- x- x- x- x- x- x(EBITDA - Capex) / Total Interest Expense- x- x- x- x- x- x- x- x- x- x- xSenior Secured Deb

9、t / EBITDA- x- x- x- x- x- x- x- x- x- x- xSenior Debt / EBITDA- x- x- x- x- x- x- x- x- x- x- xTotal Debt / EBITDA- x- x- x- x- x- x- x- x- x- x- xNet Debt / EBITDANMNMNMNMNMNMNMNMNMNMNM($ in millions, fiscal year ending December 31)Income StatementHistorical PeriodProjection PeriodLTMPro formaYear

10、 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 102005200620079/30/200820082009201020112012201320142015201620172018Sales- % growthNA-NA-Cost of Goods Sold -Gross Profit- % margin-Selling, General & Administrative - % sales-Other Expense / (Income)-EBITDA- % margin-Depreciation & Amort

11、ization-EBIT- % margin-Interest ExpenseRevolving Credit Facility-Term Loan A-Term Loan B-Term Loan C-Existing Term Loan-2nd Lien -Senior Notes-Senior Subordinated Notes-Commitment Fee on Unused Revolver-Administrative Agent Fee- Cash Interest Expense-Amortization of Deferred Financing Fees- Total In

12、terest Expense-Interest Income- Net Interest Expense-Earnings Before Taxes-Income Tax Expense- Net Income- % margin-Income Statement AssumptionsSales (% YoY growth)NA- %- %NA- %- %- %- %- %- %- %- %- %- %- %Cost of Goods Sold (% margin)- %- %- %- %- %- %- %- %- %- %- %- %- %- %- %SG&A (% sales)-

13、 %- %- %- %- %- %- %- %- %- %- %- %- %- %- %Other Expense / (Income) (% of sales)- %- %- %- %- %- %- %- %- %- %- %- %- %- %- %Depreciation & Amortization (% of sales)- %- %- %- %- %- %- %- %- %- %- %- %- %- %- %Interest Income- %- %- %- %- %- %- %- %- %- %- %Tax Rate- %- %- %- %- %- %- %- %- %-

14、%($ in millions, fiscal year ending December 31)Balance SheetProjection PeriodOpeningAdjustmentsPro FormaYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 102008+-20082009201020112012201320142015201620172018Cash and Cash Equivalents-Accounts Receivable-Inventories-Prepaids and Other Current

15、 Assets- Total Current Assets-Property, Plant and Equipment, net-Goodwill and Intangible Assets-Other Assets-Deferred Financing Fees- Total Assets-Accounts Payable-Accrued Liabilities-Other Current Liabilities- Total Current Liabilities-Revolving Credit Facility-Term Loan A-Term Loan B-Term Loan C-E

16、xisting Term Loan-2nd Lien-Senior Notes-Senior Subordinated Notes-Other Debt-Other Long-Term Liabilities- Total Liabilities -Noncontrolling Interest-Shareholders Equity- Total Shareholders Equity- Total Liabilities and Equity-Balance Check0.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.0000.000N

17、et Working Capital-(Increase) / Decrease in Net Working Capital-Balance Sheet AssumptionsCurrent AssetsDays Sales Outstanding (DSO)-Days Inventory Held (DIH)-Prepaid and Other Current Assets (% of sales)- %- %- %- %- %- %- %- %- %- %- %- %Current LiabilitiesDays Payable Outstanding (DPO)-Accrued Lia

18、bilities (% of sales)- %- %- %- %- %- %- %- %- %- %- %- %Other Current Liabilities (% of sales)- %- %- %- %- %- %- %- %- %- %- %- %($ in millions, fiscal year ending December 31)Cash Flow StatementProjection PeriodYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 102009201020112012201320142

19、015201620172018Operating Activities Net Income- Plus: Depreciation & Amortization- Plus: Amortization of Financing Fees- Changes in Working Capital Items (Inc.) / Dec. in Accounts Receivable- (Inc.) / Dec. in Inventories- (Inc.) / Dec. in Prepaid and Other Current Assets- Inc. / (Dec.) in Accoun

20、ts Payable- Inc. / (Dec.) in Accrued Liabilities- Inc. / (Dec.) in Other Current Liabilities- (Inc.) / Dec. in Net Working Capital- Cash Flow from Operating Activities-Investing Activities Capital Expenditures- Other Investing Activities- Cash Flow from Investing Activities-Financing Activities Revo

21、lving Credit Facility- Term Loan A- Term Loan B - Term Loan C- Existing Term Loan- 2nd Lien- Senior Notes- Senior Subordinated Notes- Other Debt- Dividends- Equity Issuance / (Repurchase)- Cash Flow from Financing Activities- Excess Cash for the Period- Beginning Cash Balance- Ending Cash Balance-Ca

22、sh Flow Statement AssumptionsCapital Expenditures (% of sales)- %- %- %- %- %- %- %- %- %- %($ in millions, fiscal year ending December 31)Debt ScheduleProjection PeriodPro formaYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 1020082009201020112012201320142015201620172018Forward LIBOR Cur

23、ve- %- %- %- %- %- %- %- %- %- %- %Cash Flow from Operating Activities-Cash Flow from Investing Activities- Cash Available for Debt Repayment-Total Mandatory RepaymentsMinCash-Cash From Balance Sheet- Cash Available for Optional Debt Repayment-Revolving Credit FacilityRevolving Credit Facility Size-

24、Spread- %Term0 yearsCommitment Fee on Unused Portion- %Beginning Balance-Drawdown/(Repayment) - Ending Balance-Interest Rate-Average Interest Expense-Commitment Fee-Term Loan B FacilitySize-Spread- %Term0 yearsRepayment Schedule- %Per Annum, Bullet at MaturityBeginning Balance-Mandatory Repayments-O

25、ptional Repayments- Ending Balance-Interest Rate-Interest Expense-Senior Subordinated NotesSize-Coupon- %Term0 yearsBeginning Balance-Repayment- Ending Balance-Interest Expense-($ in millions, fiscal year ending December 31)Returns AnalysisProjection PeriodPro formaYear 1Year 2Year 3Year 4Year 5Year

26、 6Year 7Year 8Year 9Year 1020082009201020112012201320142015201620172018Entry EBITDA Multiple- xInitial Equity Investment- EBITDA-Exit EBITDA Multiple- x Enterprise Value at Exit-Less: Net DebtRevolving Credit Facility-Term Loan A-Term Loan B-Term Loan C-Existing Term Loan-2nd Lien-Senior Notes-Senio

27、r Subordinated Notes-Other Debt- Total Debt-Less: Cash and Cash Equivalents- Net Debt- Equity Value at Exit-Cash ReturnNMNMNMNMNMNMNMNMNMNMYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 102009201020112012201320142015201620172018Initial Equity Investment-Equity Proceeds-IRRNANANANANANANAN

28、ANANAIRR - Assuming Exit in 2013EIRR - Assuming 0.0 x Entry MultipleExit MultipleExit YearNA-1.0 x-0.5x0.0 x0.5x1.0 x-2-1012-1.0 xNANANANANA6.5xNANANANANAEntry-0.5xNANANANANAExit 7.0 xNANANANANAMultiple0.0 xNANANANANAMultiple7.5xNANANANANA0.5xNANANANANA8.0 xNANANANANA1.0 xNANANANANA8.5xNANANANANAAss

29、umptions Page 1 - Income Statement and Cash Flow StatementProjection PeriodYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 102009201020112012201320142015201620172018Income Statement AssumptionsSales (% growth)- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3- %-

30、 %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-Cost of Goods Sold (% sales)- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3- %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-SG&A (% sales)- %- %- %- %- %- Base1- %- %- %- %- %-

31、 Sponsor2- %- %- %- %- %- Management 3- %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-Depreciation & Amortization (% sales)- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3- %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-I

32、nterest Income- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3- %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-Cash Flow Statement AssumptionsCapital Expenditures (% sales)- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3-

33、 %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-Assumptions Page 2 - Balance SheetProjection PeriodYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year 9Year 102009201020112012201320142015201620172018Current AssetsDays Sales Outstanding (DSO)- Base1- Sponsor2- Management 3- Do

34、wnside 14- Downside 25-Days Inventory Held (DIH)- Base1- Sponsor2- Management 3- Downside 14- Downside 25-Prepaids and Other Current Assets (% sales)- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3- %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-Cur

35、rent LiabilitiesDays Payable Outstanding (DPO)- Base1- Sponsor2- Management 3- Downside 14- Downside 25-Accrued Liabilities (% sales)- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3- %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-Other Current Liabi

36、lities (% sales)- %- %- %- %- %- Base1- %- %- %- %- %- Sponsor2- %- %- %- %- %- Management 3- %- %- %- %- %- Downside 14- %- %- %- %- %- Downside 25- %- %- %- %- %-($ in millions)Assumptions Page 3 - Financing Structures and FeesFinancing StructuresPurchase PriceStructurePublic / Private Target 2123

37、45 Sources of FundsStructure 1Structure 2Structure 3Structure 4Status QuoEntry EBITDA Multiple- xRevolving Credit Facility Size-LTM 9/30/2008 EBITDA-Revolving Credit Facility Draw-Enterprise Value-Term Loan A-Term Loan B-Less: Total Debt-Term Loan C-Less: Preferred Securities-2nd Lien-Less: Noncontr

38、olling Interest-Senior Notes-Plus: Cash and Cash Equivalents-Senior Subordinated Notes-Equity Purchase Price-Equity Contribution-Rollover Equity-Cash on Hand-Calculation of Fully Diluted Shares Outstanding-Offer Price per Share- Total Sources of Funds-Basic Shares Outstanding-Uses of FundsPlus: Shar

39、es from In-the-Money Options-Equity Purchase Price-Less: Shares Repurchased-Repay Existing Bank Debt- Net New Shares from Options-Tender / Call Premiums-Plus: Shares from Convertible Securities-Financing Fees - Fully Diluted Shares Outstanding-Other Fees and Expenses- - -Options/Warrants -Number ofE

40、xerciseIn-the-Money -TrancheSharesPriceSharesProceeds Total Uses of Funds-Tranche 1-Tranche 2-Financing FeesTranche 3-FeesTranche 4-Structure 1Size(%)($)Tranche 5-Revolving Credit Facility Size- Total-Term Loan A-Term Loan B-Convertible SecuritiesTerm Loan C-ConversionConversion New 2nd Lien-AmountP

41、riceRatioSharesSenior Notes-Issue 1-Senior Subordinated Notes-Issue 2-Senior Bridge Facility-Issue 3-Senior Subordinated Bridge Facility-Issue 4-Other Financing Fees & Expenses-Issue 5- Total Financing Fees- Total-Amortization of Financing FeesYear 1Year 2Year 3Year 4Year 5Year 6Year 7Year 8Year

42、 9Year 10Term2009201020112012201320142015201620172018Revolving Credit Facility Size-Term Loan A-Term Loan B-Term Loan C-2nd Lien-Senior Notes-Senior Subordinated Notes-Senior Bridge Facility-Senior Subordinated Bridge Facility-Other Financing Fees & Expenses- Annual Amortization- Administrative

43、Agent Fee-Copyright 2009 by Joshua Rosenbaum and Joshua Pearl. All rights reserved.Published by John Wiley & Sons, Inc., Hoboken, New Jersey.Published simultaneously in Canada.No part of this publication may be reproduced, stored in a retrieval system, or transmitted inany form or by any means,

44、electronic, mechanical, photocopying, recording, scanning, orotherwise, except as permitted under Section 107 or 108 of the 1976 United States CopyrightAct, without either the prior written permission of the Publisher, or authorization throughpayment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the webat . Requests to the Publisher for permission should be addressed