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

Lesson Sets for M&A Models

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

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.