如何建立LBO模型

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 1 of 10

LBO Short Form Model How To Guide

Getting Started Key Notes About Model Layout The worksheets in the model are:

— Description — explains key assumptions made by the model and contains the “last

modified date,” so you know if you have the latest version of the model (when starting a new project, always download a fresh version from the web); also contains a log of revisions to the model — LBO — LBO model with inputs for key drivers and assumptions, capital structure,

projections, etc.; the model is split into 7 pages:

1. Key Drivers & Assumptions, Warnings, Modeling with Circular Formulas — the

“Key Drivers & Assumptions” box is where you input some key data such as the

purchase price, options, and balance sheet information and make some key choices for assumptions about the exit (ownership and multiples), goodwill, fees, and the use of recap accounting; the “Warnings” box will highlight any particular issues that arise based on the inputs and assumptions you have entered, for example, to check with experts regarding the tax deductibility of PIK debt with a high coupon; and the “Modeling with Circular Formulas” box notes some key issues in using circular formulas and contains a fix in case the circular formulas get stuck 2. Summary Page — contains the sources and uses of funds, summary credit stats,

and equity returns (everything you need to see whether your LBO “worked”); this is where you input your new capital structure and interest rate assumptions and where you enter your “Rules of Thumb” constraints on credit stats and returns 3. Income Statement — contains the historical and projected income statement and

operating assumption information; this where you input your 5 year projections and the assumptions to build the projections out from years 6 to 10; working capital inputs are in the assumptions at the bottom of the page 4. Cash Flow Statement — contains the projected cash flows including cash tax

benefits from tax-deductible goodwill and loss carryforwards; this is where you input capital expenditure projections 5. Capital Structure and Credit Statistics — contains the projected capital structure

and resulting credit statistics 6. Equity Returns — details the calculation for equity returns on a trailing EBITDA and

forward P/E multiple basis 7. Detailed Ownership Calculation — details the calculation for sponsor, sub. debt,

management and other equity ownership at exit

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 2 of 10

LBO Short Form Model How To Guide

Running the Model

This short-form LBO analysis is designed for valuation purposes — i.e., to determine (in a 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 sponsor. As a first cut, these constraints will be laid out on the “Rules of Thumb” page available on the

Leveraged Finance home page. Once you have the model up and running, you will most likely want to consult with Leveraged Finance to determine if you have an appropriate capital structure for your particular 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 that 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 in the “Operating Statistics/Assumptions” section of the income statement. 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 layering 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 Revolver in the case that the Revolver will need to be drawn down. Finish the initial draft of the model by entering in the leverage, coverage, and returns constraints from the “Rules of Thumb” in the Summary Credit 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 coverage 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. (Since 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 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 enable the sponsor to pay more. — Note: Warning Box

Make 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:

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 3 of 10

LBO Short Form Model How To Guide

1. 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. High 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 recap 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 Formulas

The model uses circular formulas to calculate interest on average debt balances (rather than year-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 stuck with #REF, #VALUE, or #DIV/0 in the formulas. If this happens, the “Modeling with Circular Formulas” box contains a fix for this problem. 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 to “N” before running the model to replace the hard code zeroes with the real formulas. — Note: 5-year vs. 10-year Projections

Fairness. 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

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 remaining 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 either 5-year 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-End

The model is basically designed around the assumption of a transaction that closes

concurrent with the 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 an

LTM basis. If you have LTM EBITDA and/or CapEx figures that take you closer to the

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 4 of 10

LBO Short Form Model How To Guide

quarter 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 income statement and cash flow.

2. Balance Sheet. If you have a quarterly balance sheet that takes you closer to the

quarter the transaction will close than the year-end balance sheet, it may be more accurate to look at the cash and debt outstanding from the latest quarter’s

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 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 downside 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, the model can be modified to adjust for an interim balance sheet. For example, if the transaction is expected to close after the first quarter and you 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 goodwill 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 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 made to take only a portion of the ending cash flow as described above.) — Note: Taxes

Book 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 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.

Cash Taxes. The model tracks the cash 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 amortization 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).” If there is not enough pre-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.

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 5 of 10

LBO Short Form Model How To Guide

Pre-Existing Losses. While the model provides an input for pre-existing losses, there are rules that substantially limit the ability to take advantage of those losses. (The rationale is that the government wants to discourage people from buying businesses just to take

advantage of the tax losses.) Before assuming you can take advantage of any pre-existing losses, you should check with tax experts. Additional Notes About Model Design

— Help, Notes, and Comments — throughout the model there are helpful tips in red and in

comment boxes that show up as red flags in the corner of a cell; these tips will help you to navigate 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 needs; 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 calculations into a lot of detail to

make it easier to modify; however, most of the time it won’t be necessary to show all that detail; the model has preset groupings of rows to be hidden by pressing the “+/-“ buttons in the outline on the left of the spreadsheet — Think! — the model is not a “black box” or a substitute for thinking; to get accurate and

meaningful output you still have to think about the inputs and assumptions you are using — Don’t forget to check your work! — Automated Footnotes — the footnotes in the model are set to toggle with the different

drivers/assumptions; check the footnotes carefully to make sure you are comfortable with the assumptions

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 6 of 10

LBO Short Form Model How To Guide

Key Drivers & Assumptions Page Item

Source / Issues to Consider

All Items Sources: Public vs. Management Issue:

— Every input can be gathered from either public sources or management. The

following descriptions will list only the public sources unless specifying management as a source helps to clarify a point.

Purchase Price Driver

Issue: 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 “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 the “Total Purchase Price” line item in the summary will be greater than enterprise value because it only adds in the debt assumed, it does not back out cash.

LIBOR Rate Source: Web — IBD Home Page: Money Market Rates

Issue: 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 structure has you just input a rate for the bank debt.

10-year US Treasury rate Source: Web — IBD Home Page: Money Market Rates

Issue: 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 is just used in a footnote. It is also used in the “Warning” section — if the rate you have set on the PIK debt is greater than the 10-year plus 5.00%, a warning will flag that some of the interest may not be tax deductible.

Basic shares outstanding

Source: Latest 10-Q or 10-K

Options Outstanding and Weighted Avg. Strike Price

Source: Latest 10-K

Issues: 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 a material error. 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 “in the money” as the deal price changes.

Options Treatment: General

Defaults: — Liquidation

— Tax-deductibility: The conservative default assumption is that 0% of the options

are tax-deductible. In fact, options are most often “Non-Qualified” or “NQSO’s” and therefore tax-deductible to the employer. They are called “Non-Qualified” because they are taxable to the employee. The other type of option is an “Incentive Stock Option” or “ISO.” ISO’s are not taxable to employees as ordinary income and are therefore not tax-deductible to employers as

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 7 of 10

LBO Short Form Model How To Guide

compensation 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’s

options 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.

Options Treatment: Roll-over

— Roll-over: The target’s options remain outstanding (or are translated into options of

the new entity post-transaction with no loss of intrinsic value).

— Sources and Uses: The model will show the value attributable to the options as

a use of funds. However, since no cash will actually be paid to the option holders, the value attributable to the options will also show up as a source of

funds so that there is no net use of cash. The model uses “Intrinsic Value” as the value of the options, i.e., the difference between the purchase price per share and the weighted average strike price multiplied by the number of options. — Tax-deductibility: If you have some % of the options be tax-deductible, the

model will net tax savings against the consideration of the options. Although the rollover itself does not generate any immediate cash tax savings, these savings are imbedded in the options when they are exercised, so from a theoretical standpoint they are imbedded in the consideration paid for the options

Options Treatment: Liquidation

— Liquidation: The target’s options are liquidated for cash. The model assumes the

options are liquidated for their Intrinsic Value.

— Tax-deductibility: This default is solely to be conservative. Under U.S. tax

rules, when options are liquidated for cash, they will be tax deductible because the rules allowing them to quality as ISO’s are broken. To account for tax

deductibility when 100% of the options are being liquidated for cash, the % tax-deductible should be set to 100%.

— Cash tax savings: The model nets the cash tax savings against the value

of the options. The implicit assumption is that the cash tax savings are used to reduce the debt needed to finance the transaction.

Latest Balance Sheet Items

Sources: Latest 10-Q or 10-K Issues:

— See “Note: Transaction Close not at Year End” in the “Running the Model” section

above. — Complex capital structures: If the company has convertible debt, preferred stock,

or other hybrid debt or equity instruments, the model may need to be modified. Check with you team leader for the appropriate adjustments to deal with more complex capital structures.

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 8 of 10

LBO Short Form Model How To Guide

Tax Rate Source: 10-K Issues:

— Marginal: Do not use the effective tax rate (calculated by dividing book taxes by

book pre-tax income). If, for example, pre-tax income includes charges for non-tax-deductible goodwill amortization, the effective tax rate will be too high. The marginal tax rate — the taxes paid on each additional dollar of income (or the tax savings from each additional dollar of deductions from income) — should be used. The marginal rate will be a compilation of the federal corporate tax rate (35%) and the state/local tax rate (probably 2-3%). The marginal rates are typically laid out in a footnote to the 10-K. Alternatively, an estimate of 37 to 38% is usually reasonable. — Other Tax Issues: While the model is set up to track net operating losses and cash

taxes fairly carefully, there can be a myriad of other tax issues (e.g., foreign tax

credits) that may exist in a particular case. When confronting these tax issues talk to your team leader and tax experts about the appropriate adjustments to make to the model.

Equity to sub. debt at exit Equity to mgmt. at exit

Issue:

— Sub. debt: High yield or mezzanine debt investors may require warrants, in addition

to a coupon, to enhance the return on their investment. This input provides you the opportunity to have a percentage of the equity value at exit go to the sub. debt

holders rather than the sponsor. The model does not calculate IRR’s for the sub. debt holders. If you need to calculate the IRR’s to the sub. debt holders consult the

Leveraged Finance department for guidance on building an IRR table into the model. — Mgmt.: Financial sponsors will often give management additional options to align

their incentives with the sponsor. This input provides you the opportunity to have an additional percentage of the equity value at exit go to the management team. This is in addition to any options they rolled over or other equity contribution that was made. The model will track the equity owned by management from options roll-over or a retained equity stake separately on the “Detailed Ownership Calculations” page.

Exit Multiples

Issue: The model calculates returns to the sponsor on both an EBITDA and P/E exit

multiple basis. EBITDA multiples are typically thought of as representing a private/merger market exit and P/E multiples are typically thought of as representing a public/IPO market exit. As a good starting point, your exit multiples should be close to your entry multiples.

PIK Securities Cash Pay After Year:

— Default: 10 years

— This input allows you turn PIK securities from PIK to cash pay after a certain number

of years. The default has the PIK securities remain PIK for all 10 years. Switching to cash pay after year 5 is common. Consult Leveraged Finance to determine the most appropriate choice of your case.

Purchased R&D Default: 0% Issues:

— Only relevant for companies in industries like high technology and pharmaceuticals

which have large investments in R&D. — Accounting rules allow the acquirer to allocate some of the purchase price to

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 9 of 10

LBO Short Form Model How To Guide

purchased R&D. Any amount allocated to purchased R&D will reduce goodwill. This amount will also be expensed immediately.

— The model allows you to input a % of the goodwill to be “written-off” to purchased

R&D. This not only reduces the annual goodwill amortization charge, but the

purchased R&D “write-off” reduces the new company’s book equity. The “write-off” will be tax deductible if goodwill is tax deductible.

Goodwill Tax Deductible? Default: N Issue:

— Whether or not goodwill is tax deductible depends on the structure of the transaction.

If the transaction is structured as a purchase of stock, then goodwill is not tax

deductible. If the transaction is structured as a purchase of assets, then goodwill is tax deductible. The one other possibility is that the acquisition is structured as a purchase of stock, but a 338(h)(10) election is made. Under a 338(h)(10) election, the transaction will be treated as an asset purchase (i.e., goodwill will be tax deductible) even if it is actually a stock purchase. — The default is set for goodwill not to be tax deductible since most acquisitions of

public companies are structured as stock purchases without 338(h)(10) elections. — Tax and book goodwill are tracked separately by the model because it is possible to

have goodwill for tax purposes and not for book purposes. This would happen where there is a 338(h)(10) election and recap accounting.

Goodwill Amortization Period (Book)

Source: 10-K Default: 40 Years Issues:

— This can make a material difference in P/E based returns. The difference

between a 40 year amortization period and a 20 year amortization period in a deal with a lot of goodwill can make the difference between an extremely attractive and extremely unattractive P/E returns. Check with your team leader about the

amortization period you have chosen. You may want to run the model at two different amortization periods to see the difference. — The right range is probably between 20 and 40 years. The default is set at 40 years,

currently the maximum amortization period allowed by US GAAP. However, recent indications from regulators suggest that the maximum may be shrinking to 20 years. — To start, check the company’s 10-K for its accounting policy on goodwill amortization.

If the company’s financials do not provide that information, check 10-Ks for other companies in the industry and try to get a sense of the industry standard. — If the company has historically used 40 years, check with your team leader to

determine if, in light of the current regulatory environment, a shorter amortization period should be used. — Consider using a weighted average amortization period to account for the fact that

some of the purchase price may be allocated to asset write-ups rather than goodwill.

maexhibits\\models\\lbo_short_htg.doc kekiehl 4 Apr 2013 5:43 /10

Page 10 of 10

LBO Short Form Model How To Guide

Goodwill Amortization Period (Tax)

Source: Tax Code Default: 15 Years Issues:

— Under the U.S. tax code, goodwill is amortized over 15 years for tax purposes. The

difference in timing between book and tax goodwill amortization will show up as a cash benefit on the cash flow statement from deferred taxes.

% of Existing Goodwill Tax Deductible

Default: 0% tax deductible Issues:

— Recap: Existing goodwill will remain on the books. Some of those deals may have

generated tax deductible goodwill. As a default, the % has conservatively been set to zero. However, if you are able to determine (from the 10K or management) that the deals were asset deals or 338(h)(10) elections, then you should put the appropriate percentage of the goodwill that is tax deductible here. — Purchase Accounting: Under purchase accounting, all existing goodwill is

theoretically irrelevant because goodwill for the new company will be calculated as the purchase price less the fair market value of net assets. For the model, however, we simplify the goodwill calculation as purchase price less existing book equity and leave existing goodwill outstanding. Given that this “existing” goodwill should really be part of new goodwill, it is probably most consistent to set the existing goodwill be tax deductible if new goodwill is tax deductible and not if new goodwill is not. — Cash Flow Impact: Note that the model does not calculate the cash flow impact

from any existing goodwill that is tax deductible. To incorporate the tax benefits you should utilize the “Other Deferred Taxes” line item in the cash flow statement.

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