keep learning, keep moving

Basic knowledge of finance and accounting will be very helpful in building an LBO model.

A leveraged buyout model is a valuation model that private equity and investment banks use to determine what a company is worth in a leveraged transaction. An LBO model helps an investor determine how much debt financing a company can support given its assets and cash flow potential. The key elements of an LBO model are the three major financial statements (income statement, cash flow statement and balance sheet) as well as assumptions regarding debt levels, repayment periods and interest rates. Basic knowledge of finance and accounting will be very helpful in building an LBO model.

 Instructions

• o    1

Enter an outline of the company’s capital structure at the top of the page in Microsoft Excel. The capital structure should include the following lines: Senior debt, Mezzanine debt and Equity. Enter the dollar amounts for each tranche of debt and use equity as a plug, or a bridge, to get from total debt financing to the purchase price of the company. For example, if you are planning to pay $100 million for a company and use $50 million of senior debt and $25 million of mezzanine debt, the equity component will be $25 million.

• o    2

Build an outline of the company’s income statement up to Earnings Before Interest, Taxes and Depreciation. The major line items of the income statement are revenue, cost of goods sold and operating expenses. You should enter at least three years of historical data for the income statement and then use this data to create five years of projections. For example, if the company’s revenue has grown at a 10 percent annual rate for the past three years, you should probably assume 10 percent annual growth during the next five years.

• o    3

Enter three years of historical data for the company’s balance sheet. The balance sheet should be under the income statement in Excel. The balance sheet should include all relevant components of the company’s assets, liabilities and owners’ equity.

• o    4

Calculate inventory turnover, A/P days outstanding and A/R turnover for the historical balance sheet data, and use these ratios to calculate five years of projected balance sheet information.

• o    5

Build the cash flow statement underneath the balance sheet in Excel. The cash flow statement should start with Earnings Before Interest, Taxes and Depreciation from the income statement and should subtract increases in assets and decreases in liabilities from the company’s income statement. The final line in the cash flow statement should be free cash flow; that is, the amount of cash flow available after subtracting changes in assets and liabilities from EBITDA.

• o    6

Create a debt pay-down schedule underneath the income statement. The debt pay-down schedule should start with the beginning balances of debt entered in Step 1 and subtract the free cash flow calculated in Step 5 to arrive at an ending balance. Multiply the beginning balance of debt by the interest rate on the debt to determine interest expense. Link the interest expense figure back to the cash flow statement. The interest expense should reduce free cash flow available to pay down debt.

• o    7

Calculate your anticipated exit value for the business based on a multiple of Year 5 EBITDA. If you purchase the company for seven times EBITDA, you should probably assume an exit multiple of Year 5 EBITDA. Subtract any remaining debt that has not been paid down from the enterprise value to determine the equity value of the company at exit.

• o    8

Use the XIRR formula in Excel to calculate your annual return on investment based on the assumptions you have made. Enter the date of purchase and the equity amount invested in one column and the date of exit and the equity value at exit in a second column. Type =XIRR( and select the row with the dates followed by the row with the equity values, close the parenthesis and press "Enter."

Tips & Warnings

-              Be sure to compare the return on investment you calculate in the final step to your desired rate of return based on the riskiness of the investment. Do not invest in the company if the LBO model gives you an unsatisfactory rate of return.

-              Make sure that you have enabled circular references in Excel. Linking the interest expense back to the cash flow statement will create a circular reference, which Excel will not be able to handle unless it is set to manual calculation. Click the "Microsoft Office" start button and select "Excel Options." Then select "Formulas" and check the "Manual" box under "Calculation Options."

Read more: How to Build an LBO Model | eHow.com