This article describes various merger and acquisition modelling concepts and includes examples of M&A analysis. A merger and or acquisition (M&A) analysis is an extension of corporate models. In general, M&A models use principles from both corporate models and project finance models. Corporate models provide the base operating data and historic analysis for M&A models. The base data in corporate models includes all of the operating and depreciation data (the depreciation data be changed depending on the tax treatment of the acquisition). The base data is changed In this picture a sources and uses of funds analysis is displayed as well as a display of the equity IRR earned on the transaction and the accretion or dilution in earnings per share. These are statistics that in one way or another are shown on all of the models on this page. This page includes a description of an acquisition model with a pro-forma balance sheet, alternative debt funding and risk analysis. A video reviews how to use the file in financial analysis. Videos that describe various aspects of building the model are described. Videos also describe how to construct a detailed cash flow waterfall in a leveraged buyout model.
Synergies and Premiums in M&A
The first video and excel file below describe some general M&A finance and economic concepts that are different from standard M&A modelling. Different ways to evaluate the costs and benefits of an acquisition or a merger are described including (1) an economic approach where the after-tax NPV of synergies is compared to the premium paid in the merger; (2) accretion and dilution analysis where the acquirer earnings per share (or some other statistic) is presented before and after the merger; (3) measurement of the IRR on transaction where the equity invested at the transaction date is compared to the equity cash flows realised from the merger; and (4) DCF valuation of components of a transaction where the standalone valuation of the target company is adjusted for transaction costs, synergies and tax effects. The video and file explain how to present the different valuation techniques and discuss different theoretical finance issues associated with valuation in the context of a merger. The file and video below shows how you can use a little bit of data from a real merger to evaluate the economics of synergies and premiums.
Consolidation of Two Companies in Merger Model
The file and the video below use a simple stylized example of combining two companies to create a merger model. Inputs to the merger analysis include two different standalone models, transaction assumptions related to consideration paid for the merger and financing in of the acquisition with debt. The model is simple from in many respects including the simple structure of the standalone models and the manner in which the transaction assumptions feed to the sources and uses of funds and then to the pro-forma balance sheet. The simple model can be used to illustrate how accretion and dilution is affected by the amount of premium paid as well as the financing of the transaction. For example, you can increase the amount of consideration paid for the merger which reduces accretion in earnings per share or increases dilution of earnings per share. You can then offset this dilution by increasing the debt financing for the merger. However when the debt financing is increased, the financial ratios become worse suggesting a lower bond rating.
.
LBO Model with Alternative Capital Structure Analysis
The file below and the video (using a different larger model) is a standard LBO model that illustrates how results can be summarised along in the context of different capital structure assumptions and different selling price scenarios. The equity IRR on the transaction is demonstrated for for different holding periods through using TRUE/FALSE switches and making a flexible selling date assumption to create a J-curve. You can change the capital structure and then evaluate risk analysis with break-even and other analysis. Data tables are used to illustrate break-even points before which defaults occur for different capital structure items. The break-even analysis is presented in terms of the IRR earned on different debt and equity instruments used to finance the transaction.
Acquisition Model (LBO) with History
You can find a lot of template files or simple LBO files when you go to Youtube and search for LBO modelling. The templates and files are generally pretty simple with a basic capital structure and computed on an annual basis. The video below and the associated file works through a lot of details of completing an LBO analysis. It is too long (sorry about this, it is more than an hour). The LBO analysis begins with a corporate model that is created on a quarterly basis. Transaction assumptions are added in a separate page and include the consideration paid for the company which can be expressed as a premium. The model then works through a cash flow waterfall. The model demonstrates that the main work of converting a standalone corporate model to an LBO model is maintaining the operating data (with addition of synergies) and then adding a new finance structure.
Excel File Illustrating Leverage Buyout Model with Quarterly Flows and Alternative Acquisition Dates
How to Combine Two Standalone Models to Create a Merger Model
In M&A models and in LBO models, the process in general involves using a standalone model that you have created and then putting an new financial structure and/or combining the standalone model with another model. In combining models, I think it is a good idea to add transaction assumption page to begin the analysis. The transaction page should include consideration paid for the merger (i.e. amount paid to current equity holders), the manner in which the transaction is financed with debt and equity (including share exchanges), the amount of debt assumed, the amount and terms of new debt issued, what happens to cash on the balance sheet and other related items. The transaction page can also include assumptions for synergies and present the premium on the transaction as well as the EV/EBITDA for the acquisition. The video and associated file below describe how to create a transaction assumptions page using an actual merger from a long time ago (that led to bankruptcy because of the level of debt combined with reduced air freight prices and maintenance capital expenditures).
.
Financial Model of Failed Tribune LBO
The Tribune LBO that was completed just before the 2008 financial crisis demonstrates real (and completely absurd) assumptions and transaction structuring. Despite what should have been a clear danger of losing advertising revenues from newspaper sales, advisors to the transaction assumed that EBITDA would continue to increase Details of how the transaction was presented and how the EBITDA forecasts were developed are included in the case study chapter of the google drive. The financial model file displays the actual EBITDA compared to the forecasts made in the transaction. The transaction can also be used to evaluate pricing of different debt issues and break-even analysis.
Tribune LBO Model.xlsm
Merger analysis of an Acquisition of Retail Shops
The files below have similar structures and analogous outputs. Each of the models begin with a standalone corporate model. After the corporate model is complete, a transaction page is created that presents the consideration in the merger, financing of the merger, fees and other uses of funds in the merger. The acquisition model is then created using using the standalone model key outputs (Revenues, cash operating expenses, depreciation expense, capital expenditures and working capital). These items are brought into the acquisition model using the MATCH and INDEX function or the LOOKUP function. The first two files use examples of retail stores.
Excel File with Acquisition Model Using Example of Solar Producer Suntech (Now Bankrupt)
Excel File with Acquisition Model Using Example of Solar Producer Suntech (Now Bankrupt)
This file shows illustrates different valuation and merger techniques
Integrated Merger Model of Banks
The file that you can download below illustrates the process of evaluating a bank merger. Two standalone models of banks are created from items such as loan to deposits, loan growth, fee income, loan yields etc. Then the two banks are combined with the same sort of techniques that are in other models. These techniques include creating a pro-forma sources and uses of funds analysis, a goodwill calculation and a pro-forma balance sheet.
Unique Aspects of M&A Models on Website
As with project finance models and corporate models, the M&A models include innovative features that are not included in typical models. These features include transforming automatically transforming the corporate models into LBO models or integrated consolidation models; including accounting adjustments and different tax treatments in the analysis; allowing different transaction structure designs, financing and holding periods; and, including detailed cash flow waterfalls with different default points.
The alternative merger and acquisition model models accept information about merger transactions including the purchase price, the debt financing and the retirement of existing securities. The models use alternative techniques to evaluate the benefits of a transaction.