Excel training tips for merger or LBO modelling

www.financialtrainingassociates.com considers the answer to the question asked by a delegate on a recent financial modelling course: “How can I model a more complicated transaction such as merger, acquisition, refinancing, or LBO?”

 

1. The starting point – structuring your model: the starting point is to insert a new tab in your financial model that includes the new deal structure – and contains both sources and uses of funds.

 

The new deal structure will result in some significant changes for the business model (e.g. debt will increase).  This means that the balance sheet in the financial model needs to be ‘re-wired’ so it picks up key adjustments arising from the deal structure.

 

If, having made the adjustments, your balance sheet still balances then you are likely to be on the right track with your adjustments!

 

Note: the detail of how to make the adjustments above is covered on our financial modelling courses.  For each transaction that you model, the impact on deal structure, balance sheet and other financial statements will differ.  Summary guidance is provided below, starting with how you might model a leveraged buy out (LBO).

 

2. Modelling a buy out

- Sources of funds in your deal structure tab = new debt and equity

- Uses of funds in the deal structure tab = refinance of old debt, purchase of 100% of the shares of the target, plus any other needs (e.g. extra working capital, extra capex, extra restructuring costs that can't be met through short-term cash flow) and fees.

- Balance sheet effect - debt goes up post deal, goodwill goes up, net assets going forward match the new equity contribution made for the buy out. We sandwich that new deal structure together with the balance sheet of the company we are buying

- P&L effect - extra debt means forecast interest costs are higher. If the accounts are IFRS accounts, fees are usually expensed in the first year of the deal.

 

3. Modelling a refinancing: this is the simplest transaction to model.

- Sources of funds in the deal structure tab = new debt.

- Uses of funds = refinance of old debt and fees.

- Balance sheet effect - debt goes up post deal.  Items 1,2&5 in the diagram above disappear (you’re not usually raising equity or buying anything in a refinancing).  All you’re doing is raising some extra debt and perhaps using that to pay off old debt.  To the extent that total debt increases post deal, cash on the balance sheet will also increase by the extra total debt raised (until, for example, the extra cash raised is paid out as a dividend).

- P&L effect – as per the buy out.

 

4. 100% Merger/ acquisition: this one is a bit more complicated.

- Sources and uses of funds = just the same as the buy out.

- Balance sheet effect = the same as the buy out, except this time we are sandwiching together the deal structure, the balance sheet of the buyer and the balance sheet of the target.  So we have three things to add together: deal structure + balance sheets of two operating companies.

- P&L effect - the post-deal P&L is an amalgamation of the 2 operating companies together with the flow through costs of the new deal structure (fees plus increased debt means higher interest cost).

 

About the author: training company Financial Training Associates Ltd

 

FTA Ltd is a provider of finance programs, including financial modelling course training.

Add comment
Log in or register to post comments
This blog

FTA Ltd is a provider of commercially focussed courses for accountants, bankers, lawyers and financial services executives.

Our training courses are rigorous, commercially relevant and up to the minute. On our courses delegate interaction, discussion and case-related problem solving are all key components of learning.

We only use experienced trainers who have a professional track record aside from their training work.  Our people are happy to tell a few salient “war stories” and are instantly credible with their audiences.

For further information regarding our current course programme, including our financial modelling course training, please see www.financialtrainingassociates.com.