This page is designed to provide background in valuation of the potential costs and benefits of inorganic growth through acquisitions. The files below allow you to work through different types of M&A models including LBO models and Integrated consolidation models. As with the other exercises, the M&A Model exercise files are designed so that you can work through essential points of acquisition models. The M&A exercises include the structure of the models where the various titles are given and you attempt to enter the excel formulas. Video explanations of are included that describe the theory and practical programming issues. The first set of models address M&A issues and the second set involve integrated acquisition models. Next M&A models with some more complex issues including developing dates, taxes, debt structuring, integration of different models are addressed. Instead of working through all of the tedious M&A model exercises in the model, you can look at the formulas and the model structure as well as the description of the issues in order to get a general understanding of the different issues.
The general objective of describing M&A analysis is to work through theoretical and practical valuation analysis that is the basis of M&A analysis. M&A valuation techniques are founded on general financial methods and typically use several alternative approaches. Valuation methods applied in M&A include transaction and public comparables, discounted cash flow (DCF), simulated LBO value, accretion and dilution assessment, and premiums paid analysis. Understanding the subtleties, inherent problems and mechanics of the different methods that can be presented in a football field diagram (American Football) demonstrates valuation ranges of each approach.
In evaluating alternative valuation approaches, the general idea of the course is to quickly review basics and move to the nuances and distortions of each valuation technique. For example, in analysing multiples, the effect of changes in prospective earned return and problems with the valuation driver formula: Earnings Multiple = (1-Return/Growth)/(Cost of Capital – Growth) is demonstrated. When discussing the DCF method valuation, issues with computing stable capital expenditures, EBITDA and working capital changes in the terminal period, assessment of projected returns and reconciling the difference between equity value and enterprise value are covered. In the case of LBO simulation, tricky issues with measuring IRR and Return on investment will be addressed. Whilst evaluating all of the analysis, the implications of high recent valuation multiples with respect to target returns will be assessed in included in the analysis. The general idea of the course is to use hands-on financial modelling techniques to prove how the various M&A valuation techniques work in practise. Financial modelling principles of efficient, transparent and structured will be used to demonstrate how the valuation analyses can produce incorrect or useless results. The idea of sophisticated financial analysis is not to use fancy (and generally useless excel techniques), but rather to see how selected functions when used in creative ways can be most effective.
M&A Models and Analysis
I have created a set of videos that work through the theory and modelling practice in the context of M&A. This is divided into ___ parts with different videos for each section. I have attached the video and exercise file for each section whereby you can complete the each exercise and then move to the next. For each section, the video describing the theory is listed first. After that, technical videos that explain the excel formulas and other technical issues are described in separate videos.
Part 1: Introduction and Football Field
This section introduces M&A analysis with a football field diagram (American Football), economics of synergies and accretion and dilution. There are three separate videos for the introductory section. The first video is an outline of M&A theory without excel formulas and the next two videos explain excel details of computing the economics of a merger with synergies and premiums while the final video works through computing accretion and dilution for a hypothetical merger between Apple and Google. The excel analysis reviews LOOKUP and INTERPOLATE functions and creating flexible graphs. The excel and power point files that go along with the videos are included below the files.
Part 2: Multiple Comparables (P/E and EV/EBITDA): Overview and Differences over Time and Within an Industry
The second section discusses how to use multiples in the context of an M&A transaction. Some suggest that multiples are stable across time and similar for different companies in an industry. Review of actual multiples demonstrates that neither of these ideas are true. Multiples are evaluated for selected companies to demonstrate the things that drive multiples. High multiples at the end of 2017 and the beginning of 2018 are evaluated in the context of reduced corporate taxes. As the databases on multiples are used extensively, the added technical videos explain details of how to implement the data extraction for particular companies.
Part 3: Multiple Comparables Explained: Value Drivers from return, growth and risk to explain variation in multiples
Section three continues the discussion of multiples and demonstrates in quantitative terms the effects of return, growth and cost of capital. As with other sections, I first have a discussion of theory which uses results of technical analysis. The theory demonstrates how it is the interaction between growth and earning returns higher than the cost of capital that can drive multiples to very high levels. If there is no growth the P/E is equal to 1/k. It the return equals the cost of capital, the P/E is also equal to 1/k. You need both growth and returns above the cost of capital to achieve a really high return. Achieving a high growth over the long-term is very difficult. Achieving a return above the cost of capital without monopoly position is also one of the most difficult things a company can achieve. The discussion of value drivers and multiples also addresses the EV/EBITDA ratio and how is affected by asset lives and capital expenditures; tax rates; and working capital as well as returns, growth and cost of capital.
Part 4: Basic Cash Flow Modelling for Financial versus Industrial Companies with Simple Examples
Part 6: Historic Analysis: Acquiring Data; Presentation of Assumptions and Measuring Return on Investment
Part 7: Valuation from Financial Forecasts: DCF and Complex Terminal Value
Part 8: LBO Valuation and Backing into value from required IRR after a sale of the company.
Part 4: Cost of Capital: Difficulties with CAPM and use of the Relationship between ROE and P/B
The second section section introduces M&A analysis with a football field diagram (American Football), economics of synergies and accretion and dilution. There are two videos. The first is an outline of the theory and the second works through the excel analysis. The excel analysis reviews LOOKUP and INTERPOLATE
Video Explanations of M&A Model Exercises
| Subject | Excel Exercise File | Video | Chapter Reference | Page Reference | |||||
| Overview of Acquisition Model Structure | Exercise 1: Simple LBO Model | Chapter 4 | 21 | ||||||
| Overview of Integrated Merger Model Structure | Exercise 2: Basic Integrated Model | Chapter 4 | 25 | ||||||
| Structuring Acquistion Inputs | Exercise 3: Acquisition Model Inputs | Chapter 6 | 60 | ||||||
| Timing Issues in Converting Corporate Model to Acquisition Model | Exercise 4: Timing in Acquisition Model | Chapter 7 | 70 | ||||||
| Computing IRR and Accretion/Dilution | Exercise 5: Outputs and Analysis in Acquisition | Chapter 4 | 33 | ||||||
| Computing Pro-forma Balance Sheet in Acquisition | Exercise 6: Balance Sheet and Pro Forma | https://www.youtube.com/watch?v=3Q21CrDpZpc | Chapter 12 | 144 | |||||
| Cash Flow Sweep with Crash and Default Analysis | Exercise 7: Cash Flow Sweep and Default | https://www.youtube.com/watch?v=ZggsTCUARKs | Chapter 46 | 572 | |||||
| LBO Model with Pro-Forma Analysis | Exercise 8: LBO with Pro-Forma | https://www.youtube.com/watch?v=-lAr2ksqu8Q | Chapter 7 | 70 | |||||
| Tax Treatment and Acquisition Valuation | Exercise 9: Acquisition Tax Treatment | Chapter 12 | 139 | ||||||
| Economic Value from Synergies versus Premium | Exercise 10: Synergies and Premium Exercise | https://www.youtube.com/watch?v=QlUN4ZKfmvI | Chapter 13 | 149 | |||||
| Accretion and Dilution from EPS Estimates | Exercise 11: Accretion and Dilution Exercise | https://www.youtube.com/watch?v=oYq-On7vBv4 | Chapter 13 | 149 | |||||
| Capacity Synergy Optimisation Exercise | Exercise 12: Capacity Synergy | Chapter 13 | 149 | ||||||
| Earn Out Option in Acquisition | Exercise 13: Earn Out Exercise | Chapter 11 | 132 | ||||||
| Management Rachet in Acquisiton | Exercise 14: Management Rachet | Chapter 11 | 132 | ||||||
| ABC Debt in Acquisition | Exercise 15: ABC Debt in Acquisition | Chapter 10 | 124 | ||||||
Basic Acquisition Model
The first two exercises are designed so that you become comfortable with the general structure of an acquisition model where you can input the acquisition price and the debt used in the acquisition and then assess the equity IRR. The general structure of an acquisition model is described in Chapter 2 of the text. The second model is a little more complex in that you account for goodwill, asset write-ups and other factors in developing a pro-forma balance sheet that becomes the launching point for the model.
Tax Aspects of Acquisition Model
The tax aspects of an acquisition model can become somewhat complex, particularly in the rather typical situation where purchase accounting is used for book purposes, but for tax purposes the transaction is treated as a tax free exchange. When this case, the tax depreciation will be different from the book depreciation and deferred taxes will arise. Furthermore, the deferred taxes will expire as the existing assets retire unlike deferred taxes related to on-going capital expenditures and normal growth. A number of the complex issues associated with modelling tax aspects of an acquisition are addressed in Excecise 6. Other issues associated with taxes such as NOL and depreciation are covered in the corrporate fnance model exercises.