Leveraged Buyout (LBO) Model
Leveraged Buy Out (LBO) Model presents the business case of the purchase of a company by using a high level of debt financing. The model generates the three financial statements, the exit analysis, a summary, metrics and bank ratios as well as graphs. The financing options for the project include a standard long term loan, a subordinated loan, as well as an overdraft facility and of course equity funding from investors.
On the manual tab you are able to feed the general information for the model such as: project name & title, responsible, timeline of the model and date and currency conventions.
Additionally there is a description of the color coding of the model in the same tab. Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.
There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents and checks).
Moving on to the Inputs: the assumptions are split into three tabs: the sources and uses of funds, the pre & post closing financial statements, and the general inputs of the business. In the sources and uses the user needs to update the acquisition price, the exit Enterprise to Ebit Da, the amount of the term loan and sub debt as a multipe of the Ebit Da at closing, transaction and financing fees, as well as shares outstanding and share price of the acquired company. From this tab the sources and uses are calculated at the end of the sheet.
In the pre and post FS tab, the user needs to fill the pre closing income statement and balance sheet of the acquired company (yellow cells). Based on these inputs and the information on the previous tab, adjustments are calcuated and the post closing balance sheet is generated.
In the inputs tab, three scenarios have been created for the user to be able to assess the risk and reward of each option. The drivers that fall under scenario analysis are sales growth, cost of goods sold, SGA margin, receivables days, payable days, inventory days, incremental loan rates, minimum cash balance and income tax. The following assumptions are common to all scenarios, such as depreciation, amortization period in years (for both tangible, intangible). interest on cash and loans (term loan, sub debt and overdraft), cash flow sweep percentage, cost of equity and growth rate to perpetuity. The term loan is amortized of a set number of years as per the user input, while the sub debt capital repayment has a yearly percentage repayment which is defined by the user (currently the entire capital is repaid at the end of year 5).
Calculations: this is were all calculations are performed. The revenues are calculated based on the growth rates and deducting the variable and fixed costs the operating profit is resulting. Based on the assets financed and the gearing of the financing the interest and depreciation are occurring. By using the working capital assumptions the impact of the business cycle is presented. Finally depending on the level of the investment considered the relevant debt financing is calculated (Term loan, sub debt and overdraft).
In the Outputs tab: everything is aggregated here into the relevant statements: profit and loss, balance sheet and cash flow on a yearly basis.
In the Exit Analysis tab, the user needs to select the scenario to be used, as well as the method to calculate the exit value of the company (DCF or Enterprise to Ebit Da Multiple), and the percentage of the equity allocated to common share and subordinated debt holders. The internal rates of return are calculated as well as the cash on cash multiple for term loan, sub debt and common equity holders. Finally sensitivity tables are derived to take into account all three scenarios and both exit value methods. You may need to press F9 to update the data tables depending on the excel options you have.
In the summary tab you are able to see a high level report with the main financial & business ratios. It can be readily printed on one page for your convenience.
Moving to the Metrics tab, various metrics and bank ratios are calculated such as cash available for debt service, leverage ratios, debt service coverage ratio, and loan life coverage ratio.
In the Charts tab: Various graphs present the sources and uses of funds, Ebit Da and Ebit Da margin, bank ratios, various financials from the income statement, balance sheet and cash flow, exit valuation, investor returns, as well as discount rates.
