Corporate and Acquisition Modeling
Measure value and evaluate the costs and benefits of mergers and acquisitions
This program is eligible for 18 CE credit hours as granted by CFA Society VBA Netherlands
The Corporate and Acquisition Modeling program provides participants with the ability to create corporate models with sophisticated analytical techniques that measure value and evaluate the costs and benefits of mergers and acquisitions.
Key objectives of the program include:
- Understanding the process of building a well structured model that incorporates continually updated historic information
- Using models to compute valuation with adjustments made for stable capital expenditures, working capital and depreciation
- Evaluating different terminal valuation techniques using sophisticated implied multiples
- Computing equity value from enterprise value
- Converting corporate models into acquisition models
- Effectively presenting model results to evaluate credit risk and ranges in equity value
How you will benefit
- Understand the theoretical issues with structuring of corporate models, development of assumptions, computation of rate of return on invested capital, risk analysis, valuation formulas, capital structure and other issues
- Create a structured corporate model that uses and updates historic information in a flexible manner and allows efficient statistical analysis of assumptions
- Use corporate models to evaluate credit issues through measuring re-financing potential and through evaluating cash flow relative to debt service obligations in the context of an acquisition
- Add valuation sections to corporate models that include provisions for changing terminal growth, WACC, multiples and valuation dates; normalize working capital, capital expenditures, depreciation and deferred taxes; and evaluate items that comprise the difference between equity value and enterprise value
- Resolve tricky issues in terminal value from derived EV/EBITDA ratios that correct for flaws in the value driver (1-g/ROIC)/(WACC-g) formula and consider alternative growth rates; changes in cost of capital and variations in the spread between cost of capital and return on invested capital
- Compute equity value from enterprise value through creating proofs of how different items such as deferred taxes, warranty provisions, derivatives, long-term receivables, unfunded pensions and stock options affect the difference between equity value and enterprise value
- Derive acquisition models from the corporate model to evaluate the effect of different purchase prices, financing structures and accounting assumptions on alternative measures of financial performance from the perspective of lenders and equity investors
- Use corporate models to quantify risks to risks to debt and equity investors using structured scenario analysis, break-even analysis, sensitivity analysis and Monte Carlo simulation
- Learn Excel techniques including selected user-defined VBA functions to make better presentations from models, to resolve circular references and to make models more transparent and efficient
A laptop computer, equipped with Microsoft Excel, is required for this program. It is necessary that participants bring their own laptop, or if requested, a laptop can be provided at an additional charge.
Optional Excel Session (evening before the program starts)
Corporate and Acquisition Modeling is a hands-on program that will be conducted using numerous exercises in Excel. For some participants who do not regularly use Excel in their day to day work, AIF is offering an optional Excel session. The objective of the session is to assure that all participants, including people who do not routinely work with Excel, become familiar with the tools in Excel and work comfortably on the class exercises. The optional Excel session will cover short-cut keys, effective presentations, use of forms, one-way and two-way data tables, and look-up functions for scenario analysis.
Creating an Efficient and Well Structured Corporate Model
- Theoretical background on corporate models and economic reasons for changes in operating cash flow and returns
- A review of model objectives and model structure using examples of completed models that will be used as references throughout the training
- Development of historic/projected timing switches that allow you to add new historic financial statements to a model without re-programming equations each time a new set of historic data becomes available
- Setting up assumptions for variables that vary over time and scalar variables that remain constant and that compare historic levels with projected values and facilitate statistical analysis of the assumptions
- Computation of revenues, operating expense, capital expenditures, pre-tax cash flow, free cash flow and from operating assumptions and computation of return on invested capital using the financing and direct approaches
- Development of enterprise valuation analysis that allows for flexible start dates; flexible terminal dates and holding periods; and different terminal valuation approaches
- Calculation of financial statements through adding financial routines with a cash flow waterfall to the model in debt and cash balance schedules and using the model to establish a target capital structure
- Illustration of complexities in corporate models related to asset retirements, income taxes, minority interest and capital expenditures
Computation of Valuation and Evaluation of Credit Risks Using Corporate Model
- Demonstrate financial theory associated with multiples, terminal value and credit analysis using financial models
- Incorporation of a master scenario analysis and sensitivity diagram to evaluate credit ratios and to demonstrate variability in enterprise value and use of the return on invested capital to evaluate the reasonableness of the EBITDA assumptions
- Development of normalized working capital changes, normalized depreciation expense, normalized capital expenditures and normalized deferred taxes that vary as a function of different terminal growth rates and incorporate derived historic growth rates
- Computation of P/E and EV/EBITDA multiples from growth rates, cost of capital, returns, tax rates and asset lives as well as transition periods of each value driver and demonstration of problems with the (1-g/ROIC)/(WACC-g) formula
- Evaluation of which balance sheet items should be included in the bridge between equity value and enterprise value through creating long-term models that prove whether items should be included in free cash flow or as an adjustment to enterprise value
- Calculation of value from equity cash flow rather than free cash flow and derivation of equity multiples (P/E or market to book) to evaluation how multiples are affected by return and growth forecasts in the model
Conversion of Corporate Models to Acquisition Models
- Review of merger and acquisition theory including leveraged buyout models, integrated merger models and break-even synergy models
- Transfer of corporate model into merger or acquisition model where acquisition can occur at different time periods
- Setting up transaction structure assumptions with alternative purchase price premiums, debt funding levels and accounting adjustments
- Use of projected balance sheet from corporate model, acquisition assumptions and synergy projections to develop sources and uses analysis, goodwill calculation and pro-forma balance sheet in acquisition analysis
- Model alternative debt provisions of acquisition financing including subordinated debt, cash flow sweeps, covenants and working capital facilities with amortizing, bullet and capitalizing debt
- Analyze risks to alternative providers of capital (senior, subordinated, equity and management) in terms of IRR to equity and alternative debt providers using break-even analysis and Monte Carlo simulation
- Compute the value of management earn-outs and flip structures to provide alternative incentives
Who should attend
Corporate and Acquisition Modeling is recommended for financial professionals who are interested in building corporate models for valuation and/or credit analysis. It is designed for a range of objectives ranging from building comprehensive models to understanding how to interpret models created by others.
Corporate and Acquisition Modeling is a hands-on program that will be conducted using numerous exercises in Excel. All participants are required to have a solid, basic knowledge of Excel prior to attendance.
Optional Excel Session
An optional extra Excel session is available for participants who do not regularly use Excel in their day-to-day work. The objective of this session is to assure that all participants become familiar with the Excel tools needed to be able to work comfortably on the class exercises. The optional Excel session will cover short-cut keys, effective presentations, use of forms, one-way and two-way data tables, and look-up functions for scenario analysis.
This optional session will take place at AIF on the evening prior to the first day of the program, from 17.00 - 20.30.
Edward Bodmer teaches a number of modeling courses and is a consultant who specializes in financial analysis and modeling. He is a former banker and has taught courses for major corporations and financial institutions around the world for many years. Visit his website to see some samples of models: www.edbodmer.com. He received an MBA specializing in econometrics (with honors) from the University of Chicago and a BSc in Finance from the University of Illinois (with highest university honors).
Dates & fees
4 - 6 September 2018
Program fee includes all study materials, books and software that are required for the program as well as daily luncheons.>
Program fee is exempt from VAT for clients located in the Netherlands. For other EU and Non-EU clients, VAT may be due by client and will not be charged by AIF. Fees may be subject to change.
100% of the attendees in the September 2017 session would recommend the Corporate and Acquisition Modeling program to their colleagues.
“It's a quick way to improve your Excel skills.”
Advisor Financial Regulation
“Very practical and useful.”
Analyst / Non-Executive Board Member
Intrinsic Value Investors
“Really happy with the instructor, he made the course interactive and accessible to everyone.”
Business Development Associate
GE Artesia Bank
“Lots of information and useful pointers.”
Corporate Business Development Analyst