Financial Modelling in Excel: How to Make Adjustments Upon Merger or Acquisition

The question: "How do I make adjustments in an excel financial model for a merger or acquisition?"

This post arises as a response to a question received by a financial modelling course delegate. Here we consider the main adjustments to a financial model when we are using Excel to analyse the acquisition of a business. These notes are high level and consider the relatively simplified case where a buyer is acquiring 100% of a target company.

Overview

Main changes that arise in the financial statements upon acquisition are:

• Profit & loss statement: adding the main operating line items of the target to the acquirer (e.g. revenue, EBITDA). Adding any synergies such as revenue increases, cost savings, cost increases e.g. restructuring costs. Adding increased financing costs arising for example through an increase in debt taken on fund the acquisition;
  
• Balance sheet: adding the main line items of the target to the acquirer. Adjusting for finance taken on to fund the acquisition e.g. an increase in debt. Adding “goodwill” that arises on acquisition. Goodwill is a type of intangible asset. It is a non cash accounting entry on the balance sheet. It represents the surplus of the price paid by the acquirer over the target’s net assets. Net assets equals the difference between total assets and liabilities on the balance sheet. It is also known as shareholders’ funds or shareholders’ equity.
  
• Cash flow: adding the cash impact of the adjustments above.

Modelling framework

Acquisition modelling involves building a financial statement for the acquiring business, building a model for the target business, and then combining the two models:

• Step 1: build a financial model for the acquiring business that separates inputs/ assumptions and financial statement calculations. For example, assumptions on one tab (tab 1a) and calculations for the p&l, balance sheet and cash flow on another tab (tab 1b);
  
• Step 2: next to the model for the acquiring business, build a similar model for the target business that includes adjustments upon acquisition in its assumptions (tab 2a) and runs these through a financial statement model for the acquisition target (tab 2b);
  
• Step 3: add the assumptions for the target and the acquirer (tab 3a; which equals tab 1a plus tab 2a). Run those combined tab 3a assumptions through a new financial statement model (tab 3b) for the merged business. Tab 3b yields the key results for the exercise: a full set of forecast financial statements for the merged business.

Building the model this way:

• Allows you independently to flex the assumptions for the acquirer and its target;
  
• Ensures that the changes in financing for the acquisition as well as changes in cash and debt balances, net interest expense, along with their interactions with the tax charge for the merged business, all flow through to the financial statements for the merged business at tab 3b.
  

Detail of modelling balance sheet adjustments

As outlined above (see the second bullet point under “Overview”) major adjustments to the balance sheet for the merged business involve:

• Adjusting for finance used for the acquisition (e.g. an increase in debt); and
  
• Adjusting for goodwill.
  

In this section we consider the detail of how you could accommodate these particular adjustments within a model:

• Step 4: build a “sources and uses” table within your model assumptions e.g. at tab 2a. Sources and uses sets out where all the funding for the transaction is coming from and what it is being used for. See the table below as an example. There is no completely standard way of presenting the detail of these tables and some judgement is required regarding the exact line items and how they are presented;
  
• Step 5: adjust the balance sheet assumptions for the target for the key items in the sources and uses table. Remember, key adjustments from the transaction are going to be around finance and goodwill. See the table below as an example. It walks through the exact steps (A-E) in careful detail.
  

Once you have separated the adjustments in this fashion, the assumptions at tab 2a will clearly show sources and uses of funds for the transaction. The adjustments will be built into opening balance sheet assumptions for tab 2a, feeding through into opening balance sheet assumptions at tab 3a and the financial statement forecast at tab 3b. Building in a balance sheet check (that total assets equals total liabilities including shareholders funds) will help you confirm you have made the adjustments correctly.