如何建立LBO模型

1、maexhibitsmodelslbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10Page 1 of 10LBO Short Form ModelHow To GuideGett ing StartedKey Notes About Model LayoutThe worksheets in the model are: Descripti onexpla ins key assumpti ons made by the model and contains the“lastmodified date, ” so you know if you have

2、 the latest vers ion of the model (whe n start ing a new project, always dow nl oad a fresh vers ion from the web); also contains a log ofrevisi ons to the model LBO LBO model with in puts for key drivers and assumpti ons, capital structure, project ions, etc.; the model is split into 7 pages:1. Key

3、 Drivers & Assumpti ons, Warnin gs, Modeli ng with Circular Formulasthe“ Key Drivers & Assumpti ons” box is where you in put some key data such as thepurchase price, opti ons, and bala nee sheet in formati on and make some key choices for assumpti ons about the exit (ow nership and multiples

4、), goodwill, fees, and the use of recap accounting; the“ Warnings ” box will highlight any particular issues that arisebased on the in puts and assumptions you have entered, for example, to check with experts regard ing the tax deductibility of PIK debt with a high coup on; and the“ Modeling with Ci

5、rcular Formulas” box notes some key issues in using circularformulas and contains a fix in case the circular formulas get stuck2. Summary Page contains the sources and uses of fun ds, summary credit stats,and equity returns (everything you need to see whether your LBO“ worked ” ); this iswhere you i

6、n put your new capital structure and in terest rate assumpti ons and where you enter your “ Rules of Thumb ” constraints on credit stats and returns3. In come Stateme nt contains the historical and projected in come stateme nt andoperati ng assumpti on in formati on; this where you in put your 5 yea

7、r project ions and the assumpti ons to build the projecti ons out from years 6 to 10; work ing capitalin puts are in the assumptions at the bottom of the page4. Cash Flow Stateme nt contains the projected cash flows in cludi ng cash taxben efits from tax-deductible goodwill and loss carryforwards; t

8、his is where you in put capital expe nditure projectio ns5. Capital Structure and Credit Statistics contains the projected capital structureand result ing credit statistics6. Equity Returns details the calculati on for equity retur ns on a traili ng EBITDA andforward P/E multiple basis7. Detailed Ow

9、n ership Calculati on details the calculati on for spon sor, sub. debt,man ageme nt and other equity own ership at exitmaexhibitsmodelslbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10Page 4 of 10LBO Short Form ModelHow To GuideRunning the ModelThis short-form LBO analysis is designed for valuation purp

10、oses i.e., to determine (ina quick and dirty fashion) what a financial sponsor would be willing to pay for the target. The object of the analysis is to maximize purchase price while staying within the constraints of an appropriate capital structure and maintaining sufficient equity returns for the s

11、ponsor. As a first cut, these constraints will be laid out on the“ Rules of Thumb ” page available on theLeveraged Finance home page. Once you have the model up and running, you will most likely want to consult with Leveraged Financeto determine if you have an appropriate capitalstructure for your p

12、articular target.Begin by entering the Key Drivers and Assumptions in the box on the top of the LBO model worksheet. Next, move to page 3 and enter the historical and projected financials for the target. The model is set up to accept 5 years of projections. However, because the amount of bank debt t

13、hat can be used will be limited by the ability to pay that bank debt off by maturity (typically, 7 or 8 years), the model is set to build out projections for years 6 through 10. To build these projections, enter the assumptions i n the “ Operating Statistics/Assumptions” section of theincome stateme

14、nt. Don ' t forget to input assumptions for working capital (in the Operating Statistics/Assumptions section) and for capital expenditures (on the Cash Flow Statement page).Return to the Summary Page of the model to layer in the new capital structure using the “ Rules of Thumb ” page. Begin by l

15、ayering in the maximum amounts of bank debt and subordinated debt as a multiple of LTM EBITDA (see note below on LTM EBITDA). Enter the appropriate costs of debt. Don' t forget to enter a cost of debt for the Revolverin the casethat the Revolver will need to be drawn down. Finish the initial dra

16、ft of the model by entering in the leverage, coverage, and returns constraints from the“ Rules of Thumb ” in the SummaryCredit Statistics and Equity Returns sections of the Summary Page.Now it ' s time to begin running iterations on the model to maximize purchase price. Check your leverage and c

17、overage statistics. If any fail to meet target guidelines, reduce the amount of debt in your capital structure until the statistics reach target levels. Next, check your equity returns. If your equity returns are below target levels, reduce the purchase price until the equity returns are in line. (S

18、ince equity is a plug in the capital structure to make Sources of Funds equal to Uses of Funds, reducing the purchase price without changing the debt will reduce the equity automatically.) If the equity returns are above target levels, raise the purchase price.You will most likely want to check with

19、 Leveraged Finance to see if you have set up an appropriate capital structure for your particular target. You might also want to think about other ways to structure the transaction (asset deal or 338(h)(10) for tax-deductible goodwill, recap accounting for no book goodwill) to boost returns and enab

20、le the sponsor to pay more. Note: Warning BoxMake sure to check the warning box before completing the model. The warning box will let you know if you need to modify the model for any specific assumptions you have made. Warnings are given for three particular issues:LBO Short Form ModelHow To Guide1.

21、 Warrants on subordinated debt. If warrants are issued in conjunction with subordinated debt (i.e., you entered a percent of the equity at exit to go to sub. debt holders), there is a risk that some of the interest on the sub. debt may not be tax-deductible. You will need to consult an expert.2. Hig

22、h coupon PIK debt. If you enter a coupon on PIK debt that is more than 5 points above the 10-year US Treasury rate, there is a risk that some of the interest on the PIK debt may not be tax-deductible. You will need to consult an expert.3. Retained equity for recap accounting. If you elect to use rec

23、ap accounting, you need to assume some equity is retained (typically, 5-10%). A warning will also be shown if equity is retained and recap accounting is not selected. Note: Modeling with Circular FormulasThe model uses circular formulas to calculate interest on average debt balances (rather than yea

24、r-ending balances which fail to give credit for debt pay-down or draw-down during the year). When running a model with circular formulas, you should set calculations to manual and have iterations turned on. Remember to use F9 to calculate the model.Models with circular formulas can sometimes get stu

25、ck with #REF, #VALUE, or #DIV/0 in the formulas. If this happens, the“ Modeling with Circular Formulas” box contains a fix for thisproblem. Setting the toggle to“ F” will replace the circular formulas with hard code zeroes;hitting F9 will then clear all the formulas.Make sure the toggle is set back

26、to“ N” beforerunning the model to replace the hard code zeroes with the real formulas. Note: 5-year vs. 10-year ProjectionsFairness. The model is designed to be run with 5-year projections that are then built out to 10-year projections. This can be an issue when using the model in conjunction with a

27、 fairness opinion. For fairness purposes, only projections provided by Management should be used. If you have only 5 years of projections from Management, then limit your analysis for 5 years. In particular, check to make sure you have not set the summary credit statistics to look for bank debt rema

28、ining at the end of a year beyond the last year of projections. Also make sure you have not set the forward P/E multiple based returns to look at year 5, which will need Year 6 net income to calculate the return.Printing. The model has the outline mode (see the top of the worksheet) to print in eith

29、er 5year or 10-year mode. Even if you are running 10-year projections, you probably will want to print the Summary Page on 5-year mode for aesthetic purposes. Note: Transaction Close Not at a Year-EndThe model is basically designed around the assumption of a transaction that closes concurrent with t

30、he fiscal year-end of the target. If your deal is not likely to close at a year- end you may want to focus on two areas in the model.1. LTM EBITDA and CapEx.Typically, Pro Forma Credit Statistics are calculated on anLTM basis. If you have LTM EBITDA and/or CapEx figures that take you closer to theLB

31、O Short Form ModelHow To Guidequarter the transaction will close than the year-end figures, you will probably want to use them. These figures are entered in the“ Pro Forma ” column on the incomestatement and cash flow.2. Balance Sheet. If you have a quarterly balance sheet that takes you closer to t

32、he quarter the transaction will close than the year-end balance sheet,it may be moreaccurate to look at the cash and debt outstanding from the latest quarter'balance sheet prior to the close from the standpoint of determining the uses of funds (i.e., amount of debt that is assumed or needs to be

33、 refinanced). However, because the model makes changes to the balance sheet (e.g., draws down or pays down debt) based on year-end projections, using an interim balance sheet throws off this assumption in the model. In weighing the benefits of having the uses of funds be more accurate vs. the downsi

34、de of having the first- year ' s cash flow be less accurate, people are typically more inclined to ensure the accuracy in the uses of funds. Moreover,themodel can be modified to adjust for an interim balance sheet.For example, if thetransaction is expected to close after the first quarter and yo

35、u use a first quarter balance sheet, you will need to adjust the projections as follows: (i) Interest Expense: 1/4 of the year will be on the pre-transaction capital structure, 3/4 will be on the new capital structure; (ii) Transaction Goodwill Amortization: only 3/4 of a year of amortization (if go

36、odwill is tax-deductible, make the corresponding adjustment on the cash flow statement to goodwill amortization for tax purposes); (iii) Debt Pay Down: only 3/4 of the cash flow from the first year will be available to pay down debt (or cause a draw down on the revolver); and (iv) Book Equity: only

37、3/4 of the net income from the first year will be available to increase book equity. ( Note: Don' t use the latest quarter balance sheet for Working Capital . Continue to use year-end so that the change in working capital in the cash flow statement is a full year change. An adjustment can be mad

38、e to take only a portion of the ending cash flow as described above.) Note: TaxesBook Taxes. Book taxes in the model will show a book tax benefit if there is a book pre-tax loss. Under GAAP , you are allowed to show a book tax benefit if you are confident that you can realize the tax benefit of the

39、loss. Since a viable LBO candidate will most likely need to show a profit pretty quickly, this seemed a reasonable default assumption for the model. The book tax benefit will be reversed on the cash flow statement because it is only a book benefit.If there is notCash Taxes. The model tracks the cash

40、 flow impact from taxes separately from book taxes on two fronts: 1) the timing difference between tax-deductible goodwill (15 years) and book goodwill (typically 20-40 years) and 2) loss carryforwards. If goodwill is tax-deductible, the model will calculate the difference between the goodwill amort

41、ization for tax purposes and the goodwill amortization for book purposes. To the extent pre-tax income is available, this difference will shield that income and provide a cash tax benefit that will show in the cash flow statement as“ Deferred Taxes (from transaction goodwill amortization).”enough pr

42、e-tax income, the excess will add to loss carryforwards. Loss carryforwards will continue to build and roll forward until there is pre-tax income available to take advantage of the losses. When loss carryforwards are used the tax benefit will show up as an addition to cash flow.maexhibitsmodelslbo_s

43、hort_htg.doc kekiehl 4 Apr 2013 5:43 /10Page 11 of 10LBO Short Form ModelHow To GuidePre-Existi ng Losses. While the model provides an in put for pre-exist ing losses, there are rules that substa ntially limit the ability to take adva ntage of those losses. (The rati on ale is that the gover nment w

44、ants to discourage people from buying bus in esses just to take adva ntage of the tax losses.) Before assu ming you can take adva ntage of any pre-existi ng losses, you should check with tax experts.Additio nal Notes About Model Desig n Help, Notes, and Comme nts throughout the model there are helpf

45、ul tips in red and in comme nt boxes that show up as red flags in the corner of a cell; these tips will help you ton avigate through some of the trickier parts of the model Modify the Model the model was built to be modified easily and you should feel comfortable modifying it for your particular nee

46、ds; in particular, as you work with Leveraged Finance, Credit, and Debt Capital Markets, you may find that they focus on particular credit stats that you will want to show on your summary page or calculate on the detailed page Hideable Rows the Template worksheet breaks out the calculati ons into a

47、lot of detail tomake it easier to modify; however, most of the time it won' t be necessary to show all thatdetail; the model has preset g roupings of rows to be hidden by pressing the- “ buttons inthe outl ine on the left of the spreadsheet Think! the model is not a“ black box ” or a substitute

48、for thi nking; to get accurate andmeanin gful output you still have to thi nk about the in puts and assumpti ons you are usingDon' t forget to check your work! Automated Foot no tes the foot no tes in the model are set to toggle with the differe ntdrivers/assumpti ons; check the foot no tes care

49、fully to make sure you are comfortable with the assumpti onsLBO Short Form ModelHow To GuideKey Drivers & Assumpti ons PageItemSource / Issues to ConsiderAll ItemsSources: Public vs. ManagementIssue: Every input can be gathered from either public sources or management. The following descriptions

50、 will list only the public sources unless specifying management as a source helps to clarify a point.Purchase Price DriverIssue: The purchase price driver gives the model the flexibility to run the price off of a price per share or, for private targets with no share information, enterprise value. If

51、“ V'(enterprise value) is selected as a driver, the model will not utilize the options inputs. Total equity purchased will be calculated as enterprise value less net debt. Note that thePurchase Price " line item in the summary will be greater than enterprise value because it only adds in th

52、e debt assumed, it does not back out cash.LIBOR RateSource: Web IBD Home Page: Money Market RatesIssue: Typically the bank market will look at 90 day LIBOR. Bank debt is priced at a spread to LIBOR. For the purposes of this model, the LIBOR Rate input is just used in a footnote since the capital str

53、ucture has you just input a rate for the bank debt.10-year US Treasury rateSource: Web IBD Home Page: Money Market RatesIssue: Bond prices are usually set as a spread to the 10-year US Treasury rate. The model actually has you put in a rate for the bonds in the capital structure and the 10-year rate

54、 is just used in a footnote. It is also used in the“ Waifithg ratesectionhave set on the PIK debt is greater than the 10-year plus 5.00%, a warning will flag thatsome of the interest may not be tax deductible.Basic shares outstandingSource: Latest 10-Q or 10-KOptions Outstanding and Weighted Avg. St

55、rike PriceSource: Latest 10-KIssues: Using a single options outstanding number and a weighted average strike price (“WASP ) is a simplification. If the company has a significant amount of options and the contemplated deal prices are close to the WASP, this simplification could result in amaterial er

56、ror. Consider building a more detailed options schedule that lays out the various option issues and strike prices and can be used to determine which options are the money " as the deal price chang es.Options Treatment: GeneralDefaults:Liquidation Tax-deductibility:The conservative default assum

57、ption is that 0% of the optionsare tax- deductible. In fact, options are most often-QualifieNon" or “ NQSO s"and therefore tax-deductible to t he employer. They are called-QNalrified "because they are taxable to the employee. The other type of option is anf Incentive Stock Option"

58、; or f ISO. " ISO ' s are not taxable to employees asordinary income and are therefore not tax-deductible to employers asLBO Short Form ModelHow To Guidecompensation income. The model allows you the flexibility to assume some percentage of the consideration paid for the options will be tax-deductible.Issues: No rules: There are really no rules when it comes to the treatment of the target' soptions in a transaction. The default settings are chosen to match the most typical treatments and to make the calculations involving options the easiest to hand check.Op