This page has exercises and other files (utility files and database files) that I use for merger and acquisition courses. The exercises in file below include both a completed case and a case with selected blank equations. In delivering the renewable course I have created practical exercises where you can get your hands dirty and see how some of the key concepts work. In creating the exercises, I have included the titles for in excel models so you do not have to waste your time typing data. I have left blanks on selected equations that I think will be helpful in understanding the concepts. For example, in the first exercise, I think it is important to understand that you should compute the value of synergies on an after tax basis and that you cannot use the same discount rate for synergies as for other items. To see how this works, you fill in equations for a very simple financial model with and without inflation. The exercises below cover both economic issues like the synergies and a host of different valuation issues. The valuation issues cover issues that include creation of a simple financial model to demonstrate the proof of levelised equations; equations that demonstrate the importance of debt sizing and debt structuring; exercises that show how risks as measured with P90, P99 etc. are computed and can affect the debt structuring.I have created a few basic acquisition and merger model exercises if you are just starting out. As with other subjects, I have included videos along with the exercise files.
Power point slides that cover different sessions of the course are available for download by clicking on the button below. The sections of this webpage follow the same structure as the power point slides.
.
Power Point Slides for Merger and Acquistion Course with Covering Valuation Concepts and Appliations
.
I have included two files that are used to make the excel exercises work more smoothly. The first is a file called Read PDF which allows you to grab data from PDF files and then convert the data to excel files. To run this file you copy stuff from the pdf file and then operate the macro with SHIFT, CNTL, A. When you copy data from a PDF to excel, make sure that you copy and paste special as UNICODE text. There are different formats that you can use to resolve the PDF. If you are reading from the Lazard LCOE stuff you can use the first green box. If you are reading from the PVGIS you can use the second green box.
The second file is a file that has a whole lot of macros to prevent you from wasting time on formatting and copying formulas to the right. This file is called GENERIC MACROS. I have revised the generic macro file in the link below so that you are not prevented to open it because of something called the auto open — a macro that operates when you open the file and excel considers dangerous. When working through the exercises, it would help a lot if you have this file open and enable the macros. The big things that this GENERIC MACRO allows are to press SHIFT, CNTL, R to copy to the right and also CNTL, ALT, C to open the window that has a whole lot of formatting options. There are a lot more utility macros in the GENERIC MACROS file. You can go to https://edbodmer.com/excel-utilities-and-backpack/generic-macros-file/ and see some of the other stuff including a few user defined functions.
.
.
.
.
Part 1: Overview of M&A Analysis and Football Field Diagram
The file and video below provides a general overview of terms for merger and acquisition analysis. Some of the valuation issues are addressed in the context of a football field diagram. There are two exercises below that are associated with the overview section. The button is attached to a file for creating a football field diagram yourself. The football field diagram is illustrated below.
.
.
The second exercise is a few questions to assure your understanding of some of the fundamental concepts. It is structured as a quiz. You can download the questions by clicking on the button below.
.
.
Part 2: Synergies and Premium in Merger and Acquisition Analysis
.
The second set of exercises works through issues associated with measuring the costs and benefits of mergers and acquisitions. This case uses a case study that includes estimates of synergies and alternative measures of the premium in a merger. The case study is the Norfolk and Southern case where Canadian Pacific attempted to take over Norfolk Southern.
.
.
The file attached to the button below includes data from the case (I am not allowed to put the pdf file on the website. The data includes the stock prices before and after the merger as well as financials. I use the case for a lot of stuff. One thing is making a valuation of the company. Another is to evaluate whether the premium and synergy is reasonable.
.
An example of items in this exercise is illustrated in the screenshot below. The exercise illustrates how to work through issues associated with mergers using exchange of shares.
An example of how this case is used is shown in the screenshot below.
.
.
.
Part 3: M&A Screening and P/E Ratio Interpretation
.
The third set of exercises works through issues associated with measuring the costs and benefits of mergers and acquisitions. This exercise uses an illustration of computing the value driver formula. In the case you fill in the value driver formula to see how it works and how it does not work in cases where the return changes over time.
.
.
.
The second file for this section of the course illustrates how to gather data in practice. It is a file that you can use to download financial data and stock prices. You enter ticker symbols from marketwatch and for finance.yahoo for the companies. After that, you can review a lot of financial data with various presentations. An illustration of the output from this database is illustrated below the button.
.
.
Part 4: Financial Modelling for M&A
The fourth part of the M&A analysis addresses financial modelling of corporations. For this case I use the Burton Sensors case which is commonly used in business schools for evaluating valuation and M&A issues. This case has an example of a horrible financial model that should must be fixed. It does not have anything about return on invested capital and it uses arbitrary multiples.
.
.
I have included two files for the Burton Sensors case. The first file is the one associated with the case that was provided. This file has the horrible model with no ROIC and a very poor structure. The button which is attached to the second file has the revised model with some blanks you can fill in.
.
.
.
.
.
Part 5: EV/EBITDA Multiples in M&A
.
The fifth part of the M&A analysis addresses how to evaluate the EV/EBITDA that is fundamental to so many issues in M&A. I demonstrate that the EV/EBITDA is affected by the economic lifetime of assets — how long it takes to before the assets must be replaced. This is in addition to the return on invested capital, the growth rate and other factors including the income tax rate.
The file below is an exercise where you work through the calculation of Enterprise value with different growth rates, different asset lives, different tax rates and different working capital adjustments.
.
.
.
Part 6: Terminal Value Alternatives
.
The sixth part of the M&A analysis addresses the important question of different ways to compute the terminal value. The method which is optimal is to compute the Return on Invested Capital with an interpolate function were the current return gradually declines to a long-term stable value. This requires more work than a really simple formula. The exercise attached to the button below illustrates alternative terminal value methods. This works by establishing a proof of the true cash flow over a very long period. This process is illustrated in the screenshot below.
.
.
.
The exercise demonstrates that the method that yields the correct value is a method where a fade period is established. This can then be used to use the interpolate function. With the interpolate function, the change in the ROIC can be projected. This yields the NOPAT. In addition to NOPAT, the invested capital is grown at the transition or final growth rate. The final value is computed using the formula:
Value = ROIC x Invested Capital x (1-g/ROIC)/(WACC-g)
.
.
.
Part 7: Leveraged Buyouts and Private Equity in M&A
.
The sixth part of the M&A analysis addresses acquisition of project financed investments. The focus of this analysis is to compute the equity IRR on a project with different levels of gearing or leverage. This part of the course uses the case study of Hertz (which I think subsequently went bankrupt). An excerpt of this case is shown below.
.
.
.
.
.
Part 8: Project Finance in M&A
.
The fifth part of the M&A analysis addresses acquisition of project financed investments.
This part of the course covers more nuanced issues for project finance analysis in the context of M&A. The general idea of this exercise is to demonstrate that the IRR in the initial analysis is just a first step. Because the risk of projects often declines, it is possible to realize upsides when projects are sold. At least in the context of the IRR without the sale. These upsides include use of development fees, use of re-financing and use of the sale of a plant to investors who are interested in paying for safe investments. This third exercise also includes the true definition of IRR, the theory of development risk and development fee; upside option from re-finance; the upside option from selling a project; computing performance measures and equity bridge loans. As with the first exercise, the first file is completed and the second exercise has equations that you should fill in.
.
.
As with the first exercise, the button attached to the first file is the file with equations for you to fill in. The second button is attached to the file with the completed exercise.
.
.
.
.
.
Part 9: Accretion/Dilution and Consolidation
.
The ninth of the M&A analysis addresses accretion and dilution in earnings per share from a merger. I start with a simple case of two public companies, a share exchange and no synergies. This demonstrates the problems with accretion and dilution analysis as well as more understanding of P/E multiples. In this section you also practice real world downloading of financial data. This first part is attached to the button below.
.
.
.
Once you look at stock prices, you can get the financial data for the comparison. In this file you put in the marketwatch and finance yahoo ticker symbols and download the data. I hope you try yourself to download the data with alternative companies including companies that are not US companies.
.
.
.
.
.
Merger and Acquisition Exercise for Accretion and Dilution with Public and Private Company Example
.
.
Part 10: Pro-Forma Balance Sheet and Accounting in Acquisition
.
The tenth part of the M&A course addresses pro-forma balance sheets in establishing the financial model after the acquisition. The pro-forma comes from a goodwill analysis and a sources and uses of funds calculation. This can be evaluated with two exercises below.
.
.
.
.
.
Timing Issues in Acquisition Models
The second set of exercises demonstrates how to create a model in which there are different holding periods before which the acquisition is sold. The first model is computed on an annual basis and assumes the sale also occurs at the end of the period. The second model includes more flexible dates and the third exercise (exercise number 5) includes a set of fairly complex functions that allow you to incorporate flexible dates for the transaction date and the sale date as well as growth rates in EBITDA and Capital Expenditures that account for the flexible dates.
I am in the process of uploading these files. If you would like to get access before they are uploaded, send me an e-mail to edwardbodmer@gmail.com.
Exercise 3 – Acquisition from Corporate Model with History.xlsm
Exercise 4 – Acquisition Timing and Cash Flow Construction.xlsm
Exercise 3 – Holding Period Exercise – Basic.xls
Exercise 4 – Holding Period Exercise – Including Dates.xls
Exercise 5 – Holding Period Exercise Complex.xls
Structuring Transaction Inputs in Acquisition Models
Presentation of Outputs in Acquisition Model
Exercise 5 – Acquisition Model Analysis and Outputs.xlsm
.
The second file and video provide an overview of an integrated merger model where you put two companies together and you set-up a transaction assumption page.
The file below is in introductory set of slides that reviews general issues associated with mergers and acquisitions including the economic theory of synergies and premiums.
Power Point Slides Reviewing the Theory and Modelling of Mergers and Analysis (Summary Slides)