M&A+Model+Collection

=Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||=

= This website is now obsolete because of being extinguished by horrible wikispaces. Please go to http://edbodmer.com for a much improved website. =

A merger and or acquisition (M&A) modeling 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.

= Files Associated with M&A Videos (brief descriptions of the files are below the file names): =

=General M&A Concepts and Valuation=

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.

media type="youtube" key="QlUN4ZKfmvI" width="560" height="315"



=Consolidation of Two Companies in Merger Model=

The file and the video below use a simple stylised 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.

media type="youtube" key="b3qjiUE2Dyg" width="560" height="315"



=LBO Model with 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.

media type="youtube" key="qwttwhuFC-0" width="560" height="315"



=Acquisition Model 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.

media type="youtube" key="h72H2AHSlpk" width="560" height="315"



=This file shows how to combined 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).

media type="youtube" key="iZSa5ijpaXE" width="560" height="315"



=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.



=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.



=This file illustrates how to use history in making an acquisition analysis=



=This file shows illustrates different valuation and merger techniques=



=Demonstrates set-up of Transaction Assumptions and Consolidation Analysis=



=Works through integration analysis using bank financial model=

= Video Overviews of Merger and Acquisition Models: =


 * || Subject ||  || File ||   || Video ||   || Chapter Reference ||
 * || Overview of Featured M&A Models ||  ||   ||   || https://www.youtube.com/watch?v=g0cJpR1vtUo ||   || Chapter 4 ||
 * || Presentation, Structuring, Valuation ||  || Featured LBO Model ||   ||   ||   || Chapter 4 ||
 * || Transition from Corporate Model ||  || Suntech ||   ||   ||   || Chapter 4 ||
 * || Valuation and Acquisition ||  || Refinery Acquisition Model ||   ||   ||   || Chapter 4 ||
 * || Integrated Consolidation Assumptions ||  || M&A Retail Stores ||   ||   ||   || Chapter 4 ||
 * || Transition Assumptions ||  || Kraft and Cadbury ||   ||   ||   || Chapter 4 ||
 * || Different Modelling Techniques ||  || M&A Concepts ||   ||   ||   || Chapter 4 ||
 * || Acquisition Model with Detailed Expenses ||  || Metro Express LBO ||   ||   ||   || Chapter 4 ||
 * || LBO with Multiple Debt and Waterfall ||  || Tribune Analysis ||   ||   ||   || Chapter 4 ||
 * || Creating Merger with Sources and Uses ||  || Kitty Hawk and Kalitta Merger ||   || https://www.youtube.com/watch?v=iZSa5ijpaXE ||   || Chapter 4 ||
 * || Updating Merger Model with New Analysis ||  || Kitty Hawk and Kalitta Merger ||   || https://www.youtube.com/watch?v=7v3SaSysbO0 ||   || Chapter 4 ||
 * || Credit Analysis with Merger Model ||  || Kitty Hawk and Kalitta Merger ||   ||   ||   || Chapter 4 ||
 * || Complex Working Capital in LBO Model ||  || Quarterly LBO Model ||   || https://www.youtube.com/watch?v=4IMmR3fbo6w ||   || Chapter 4 ||
 * || Revolver Analysis and Tax and Financing ||  || Quarterly LBO Model ||   || https://www.youtube.com/watch?v=UF4VmDh_uxY ||   || Chapter 4 ||
 * || Completion of LBO Model ||  || Quarterly LBO Model ||   || https://www.youtube.com/watch?v=h72H2AHSlpk ||   || Chapter 4 ||
 * || IRR's and Annual Summary in LBO Model ||  || Quarterly LBO Model ||   || https://www.youtube.com/watch?v=1lSIXSau-KE ||   || Chapter 4 ||
 * || Final Analysis and Quarterly/Annual ||  || Quarterly LBO Model ||   || https://www.youtube.com/watch?v=qwttwhuFC-0 ||   || Chapter 4 ||
 * || Final Analysis and Quarterly/Annual ||  || Quarterly LBO Model ||   || https://www.youtube.com/watch?v=qwttwhuFC-0 ||   || Chapter 4 ||

Merger and Acquisition Model Examples





 * 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 retirment of existing securities. The models use alternative techniques to evaluate the benefits of a transaction.





The files below are a couple of models for mergers and acquisiton models. The first model is a template for a Leveraged Buyout Transation





=**General Comments About Corporate Models:**=

The tricky part about M&A models involves how to incorporate corporate analysis and acquisition assumptions in a smooth way and how to effectively model cash flow waterfalls with cash flow sweeps, defaults and revolvers. The models below illustrate how to: 1. Create the acquisition assumptions 2. Develop pro-forma balance sheet and accretion and dilution analyis 3. Evaluate the equity IRR using different assumptions with respect to holding period and exit multiples 4. Transfer data from a corporate model

=**Related Pages on the Site:**=


 * Other Featured Models **
 * Other M&A Model Examples **
 * Corporate Model Exercises **
 * Template Models **
 * Scenario and Senstivity Analysis **
 * Generic Macros **
 * Auditing Files **