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

I will deal with the Expiration of Wikispaces -- I can now make a better website.

M&A Models and Analysis

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 practise 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


Excel Exercise File


Chapter Reference

Page Reference

Overview of Acquisition Model Structure

Exercise 1: Simple LBO Model


Chapter 4


Overview of Integrated Merger Model Structure

Exercise 2: Basic Integrated Model


Chapter 4


Structuring Acquistion Inputs

Exercise 3: Acquisition Model Inputs


Chapter 6


Timing Issues in Converting Corporate Model to Acquisition Model

Exercise 4: Timing in Acquisition Model


Chapter 7


Computing IRR and Accretion/Dilution

Exercise 5: Outputs and Analysis in Acquisition


Chapter 4


Computing Pro-forma Balance Sheet in Acquisition

Exercise 6: Balance Sheet and Pro Forma


Chapter 12


Cash Flow Sweep with Crash and Default Analysis

Exercise 7: Cash Flow Sweep and Default


Chapter 46


LBO Model with Pro-Forma Analysis

Exercise 8: LBO with Pro-Forma


Chapter 7


Tax Treatment and Acquisition Valuation

Exercise 9: Acquisition Tax Treatment

Chapter 12


Economic Value from Synergies versus Premium

Exercise 10: Synergies and Premium Exercise


Chapter 13


Accretion and Dilution from EPS Estimates

Exercise 11: Accretion and Dilution Exercise


Chapter 13


Capacity Synergy Optimisation Exercise

Exercise 12: Capacity Synergy

Chapter 13


Earn Out Option in Acquisition

Exercise 13: Earn Out Exercise

Chapter 11


Management Rachet in Acquisiton

Exercise 14: Management Rachet

Chapter 11


ABC Debt in Acquisition

Exercise 15: ABC Debt in Acquisition

Chapter 10


On this page you can download exercises that help you create corporate models. Each exercise include an introduction that describes the objective and the key ideas of the exercise. In the exercises, you can inspect the results by pressing the complete button. In most of the exercieses, instructions for each cell are shown in the model. The corporate model exercises include (note the macros should be enabled for the all of the features to work):

General Comments About M&A Models:

The tricky part about corporate models involves how to incorporate moving from historic to projected periods in a smooth way and how to model terminal value.
The model below illustrates how to:
1. use macros to read from PDF files into excel2. put history and forecasts together in an effective way that allows you to add history in the future
3. compute stable ratios of working capital, depreciation, capital expenditures and deferred tax that depend on the terminal growth
4. create flexible valuation periods

Related Pages on the Site:

Exercises for adding DCF to Corporate Models
Other Featured Models
Featured Corporate Finance Models
Template Models
Scenario and Senstivity Analysis
Generic Macros
Auditing Files

Files Associated with Videos:

Basic Acquisition Model

The first two exercises are designed so that you become comfortable with the general structure of an acquistion 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 acquisiton 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.

Flexible Holding Periods 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.

Tax Aspects of Acquisition Model

The tax aspects of an acquisiton model can become somewhat complex, particularly in the rather typical situation where purchase accounting is used for book purpoes, but for tax purposes the transaction is treated as a tax free exchange. When modeling 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.