Leveraged buyout (LBO) model

Purpose
The purpose of the model is to enable the user to plan the acquistion of a company with the option of using debt to fund the acquisition. This is often referred to as a leveraged buyout or LBO. This is a perfect model for private equity (PE) and venture capital (VC) investment planning
 
Outputs
Integrated Financial Statements including Income Statement, Balance Sheet and Cash Flow for the target acquisition company (Ops), the "New company" making the acquisition (Bidco) and the newly consolidated & combined entity (Consol). There is an additional output (Elims) containing all of the elimanation required to consolidate the new entitites e.g. Goodwill.
Dashboard including key summary financials, results, sales split, sources & uses & exit, and valuation and IRR
 
Timeline
The model has a monthly timeline that runs for 8 years
There are annual summaries
A flexible model start date and flexible forecast start date
The ability to include both historical actual data and produce forecast data
The ability to enter historical Profit and Loss balance and an Opening Balance sheet
 
General
The ability to update the model units
The model provides check and commercial alert messages to assist the user
Commercial alerts include reminders that there may not be enough cash to pay deferred consideration or to pay dividends
 
Structure
The user enters operations (described below) for the target company (Ops) along with the transaction assumptions (Bidco). The Consol sheet combines the Ops with the Bidco and makes the appropriate eliminations (Elims)
 
Sales
Enter and name up to 3 different revenue streams
Sales entered annually and spread over a 12 month period (based on a user defined seasonality trend)
 
Cost of sales
Split by those related to direct employee costs and non-employee related direct costs
Employee based direct costs are based on direct headcount staff, their average salary, social security and pension cost assumptions
Non-employee related direct costs are calculated on a gross margin assumption
 
Administration costs
Split by rent and rates costs, indirect staff costs, office costs, maintenance and repair costs, professional fees and other adjustments
Rent costs are entered annually and the user has the option of triggering prepayments or paid in the month
Business rates/taxes are entered annually, spread evenly over the year and paid monthly
Indirect staff costs are based on average annual headcount multiplied average salary, pension and bonuses
All other administration costs are entered annually and spread over the month
 
Fixed assets
Capital expenditure is entered annually and spread on a monthly basis
Depreciation is calculated on a straight-line basis
 
Working capital
Includes stock, debtors, creditors and VAT (sales tax)
Stock is calculated on a inventory days assumption based on direct non-employee related costs
Trade debtors is calculated on a debtor days assumption and based on total sales
Trade creditors is calculated on a creditor days assumption and includes direct non-employee related cost of sales, non-employee and non-prepayment related administration expenses, and capital expenditure
VAT/sales taxes are based on a percentage of non-employee related costs and are paid based on flexible payment flags
 
Income/corporation tax
Enter an effective tax rate and manually enter the tax payments
The user will also have the option to enter transaction tax benefits (refer to the transaction section within the model)
 
Interest
Interest rates are entered annually for both cash balance and overdraft balances
 
Dividend
Manually entered dividend distribution
Commercial alerts will be triggered where there isn't enough cash to pay dividends
 
Transaction details
Enter planned transaction date
User defined what happens to existing cash in the business and outstanding taxes/liabilities
User enters price of the acquisition (based on user defined adjusted EBITDA and the multiple of EBITDA), the transaction costs and the preferred equity injection (note that the commercial alerts will warn the user if increased equity injection is required)
User enters target exit assumptions from the acquistion including exit date, the multiple and transaction costs
Acquisition financing assumptions include senior debt and deferred consideration
Senior debt includes the drawdown amount, arrangement fee, term of the loan, type of amortisation, interest margin, interest roll up option and the base rate
Deferred consideration include the amount, the minimum cash balance required to remain in the business in order to pay the consideration and the manually entered deferred consideration payment.
 
 
Valuation
Ability to enter a date on which the valuation is taken
User enters a terminal growth rate
The discount rate is built up from multiple assumptions and is calculated using a weighted average cost of capital calculation (WACC)
Valuation is calculated using the net present value methodology