Excel modelling training company FTA Ltd considers the answer to the question: “How can I model more complicated transactions such as refinancing, merger, acquisition or leveraged buyout (LBO)?”
1. The merger modelling 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. See the diagram below for guidance.
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!
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).
4a. Special case: acquisition of a very small stake in another company (= investment: no control)
- Sources and uses of funds = as above (i.e. sources = any new debt or equity raised, uses = purchase of stake plus anything else plus fees)
- Balance sheet effect = different from the above. Because we are acquiring a small stake in another business, we don't consolidate the full balance sheet of the associate company. So the post deal balance sheet is going to = deal structure + a new line item "value of investments".
- P&L effect. There will be some flow through effect e.g. where we have raised extra debt to buy the investment, leading to higher interest costs. Then, on the P&L, we might see a new line item "other income - income from investments".
- See the diagram below ("Group accounting") for summary and guidance as to what is likely to count as an investment.
4b: Acquisition of a non-controlling stake e.g. 40% in another company (= associate) - Sources and uses of funds = as above (i.e. sources = any new debt or equity raised, uses = purchase of stake plus anything else plus fees). - Balance sheet effect = as per investment. See the example below.
- P&L effect. Here we "equity account" - on the P&L, what we would do is consolidate 40% of the associate's P&L into the acquirer's P&L. In addition there will be some flow through effect e.g. where we have raised extra debt to buy the stake, leading to higher interest costs.
4c: Acquisition of a majority stake (e.g 75%) in another company
- The same as 100% merger or acquisition (i.e. the post deal balance sheet = deal structure + 100% of the balance sheet of the two operating companies, post deal P&L = flow throughs from deal structure + 100% of the P&L of the two operating companies). But we need to somehow reflect the fact that 25% of the business belongs to another party.
- What you will see is a line item at the bottom of the balance sheet (used to be called "minority interest", now called "non-controlling interest") under liabilities - reflecting the fact that 25% of the value of the acquired business belongs to someone else, reducing the value of equity attributable to ordinary shareholders. See the example below.
- You will also see a line item at the bottom of the P&L ("minority interest" or "non-controlling interest") deducting or splitting net income and making it clear that 25% of the income from the acquired business belongs to outside shareholders, with the remainder net income attributable to ordinary shareholders.
5. Valuation impact of cases a-c above.
Where it is difficult to forecast future income/ cash flow from investments or associates, then the valuation approach is to exclude income from investments and associates from core income, valuing investments/ associates separately (e.g. based on their last balance sheet value) and 'topping up' our valuation of the underlying core business for the value of investments & associates.
6. De-merger/ sale of part of the business
Exactly the opposite of cases a-c above.
About the author: course provider Financial Training Associates Ltd
