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


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






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

https://www.youtube.com/watch?v=iyJCVB3gBp8

Chapter 4

21

Overview of Integrated Merger Model Structure

Exercise 2: Basic Integrated Model

https://www.youtube.com/watch?v=b3qjiUE2Dyg

Chapter 4

25

Structuring Acquistion Inputs

Exercise 3: Acquisition Model Inputs

https://www.youtube.com/watch?v=RfN3WXniz0w

Chapter 6

60

Timing Issues in Converting Corporate Model to Acquisition Model

Exercise 4: Timing in Acquisition Model

https://www.youtube.com/watch?v=iAE82CO9lyE

Chapter 7

70

Computing IRR and Accretion/Dilution

Exercise 5: Outputs and Analysis in Acquisition

https://www.youtube.com/watch?v=IPaj8SUgidc

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











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.






==