Private Equity LBO Modelling Test

Most Private Equity firms will give you modelling tests to complete real-time at their offices from scratch. Without practice, this can be challenging even for seasoned investment bankers. Here are a few tips and an example of test you will likely get:

LBO Modelling Tips

Tip#1: Spend enough time to understand all the requirements properly

You need to read in details the information provided as well as what is asked. Often, candidates fail to answer the question asked by trying to do too much or waste time as they add complexities that are not required.

Tip#2: Always keep models simple

Do not try to "show off" by building complex models and advanced functions. Build a practical model that answers the question and only if you have enough time then add a bit more advanced functions or clean up the formatting but this is not necessary. 

Tip#3: Watch your time, and if you are running out of time, simplify

If you get stuck on a point, just simplify it and at minimum provide an IRR output. If you build only half of the model, then your ability to build a full LBO cannot be judged. But if you take a shortcut on some parts but still build the full LBO and IRR calculations, you might be able to get away with it.

Tip#4: Have a well-practiced template in mind

Make sure you have a very well rehearsed basic template in mind with the following items:

- Simple Source and Uses table (one or two tranches of debt). Input your entry/exit multiple assumptions here

- Basic income statement (Revenue, EBITDA, D&A, EBIT, Taxes, Interest, Net profit - that's it). Leave interest blank and link it later on from your debt schedule.

- Cash Flow Statement (EBITDA, Capex, Working Capital, Tax, Debt repayments and Interest Paid). You could model working capital and capex separately in a mini balance sheet for added details. Leave debt repayments and interest paid blank for now and link from debt schedule later.

- Debt Schedule: Here you need to detail the debt repayments and interest payments. You can then link those to the Cash Flow and P&L

- IRR Calculation. The cashflows should come from your cashflow statement and you only need to insert the IRR calc here. You should also insert some sensitivity tables for different exit years and different entry/exit multiples

LBO Model Test Example (2 hours)

For practice, try to solve this case. Your IRR in year 4 at a 8.0x exit multiple should be c.24%.

LBO Model Assumptions

1. A Private Equity Firm wants to acquires a German business for €280m + any Advisory Fees equivalent to 2% of the transaction value. Assume Transaction date on 30 June 2012 and no cash

- Senior debt of 3.0x EBITDA at transaction date has been obtained from a regional bank

- The seller has also agreed to provide an additional €35m in the for of a vendor loan

- The private equity firm will invest the balance in the form of a shareholder note

3. The Senior Bank Debt pays 7% per annum (cash pay) and 5% is repaid in year one, 15% in year two and 20% in year three, 30% in year four, 30% in year five

4. The Vendor Loan pays an  8% (non-cash) which accrues annually. This vendor loan is subordinated to the bank debt.

5. The Private Equity Firm shareholder loan pays a 15% non-cash pay coupon which accrues annually. This loan is subordinated to the senior bank debt and to the vendor loan

6. The Company needs to maintain a minimum of €1m operating cash at all times. Assume a full cash sweep for any amounts above €1m

7. The Private Equity firm wants to maintain control of the company and at the time of the acquisition will have a 85% shareholding in the company, while the management will retain 15%. 

8. Sales at closing were €100m, and assume this will grow by 5% in year 1, and 7% p.a. thereafter 

9. EBITDA margins will increase from 35% and increase by 2 percentage points per year until 2017

10. It is thought that capex over this period will be €15m per annum (equal to depreciation).

11. The Company has a 10 days (of sales) funding gap in working capital

12. Tax will be charged at 30%.

Questions

'A. What is the Private Equity firm IRR, and cash on cash returns at 7.0x, 8.0x and 9.0x EBITDA exit multiples in year 4 and 5?

'B. What are the returns if you assume senior debt of 2.5x and 3.5x EBITDA? What are the issues that we need to consider in deciding what the level of bank debt should be?

'C. What is your recommended level of bank debt?

'D. Which EV exit is realistic given the data provided and what return would you expect.

'E. What return should you be looking for with this kind of business

'F. What is the benefit of a vendor loan?

'G. What you be a sensible strategy you would adopt with regards to the vendor loan in 2 or 3 years?

H. How much exit proceeds will go to shareholders and how much will go to management?

For a fully worked out answer, please refer to the LBO model here