### Corporate Finance Courses

Firms, schools and academies offer introductory corporate finance training on every corner of the internet. We applaud them, and pick up where they leave off.

By blending traditional theory with Trivium’s financial modelling expertise, and we’ve created a number of offerings geared towards practitioners rather than undergraduates. From complex valuation issues to advanced credit analysis and special forms of debt, we’re happy to offer a large spectrum of corporate finance courses with immediate on-the-job applicability.

We’ve taught courses on almost every topic in financial modeling, project finance, energy finance and advanced topics in corporate finance. In the following sections, you’ll see outlines and samples of courses we’ve taught before.

Every course listing you see here has been taught, and we can easily adapt courses to fit region-specific needs. Let us know what sort of training you need, and our team will put it together.

**Course Information**

**Background: **This course is for levels intermediate through advanced. There are many training services that teach beginner-level M&A modelling. Trivium’s Advanced Corporate Finance Modelling course begins where those courses leave off. You’ll take a deep dive into structuring highly robust corporate finance models using a combination of sophisticated modeling techniques and VBA programs.

**Course Length: **2 days

**Overview: **The Corporate Modelling in Excel training course will provide participants with the ability to create corporate models with sophisticated analytical techniques that measure value and that evaluate the costs and benefits of mergers and acquisitions.

Key objectives of the course include understanding the process of building a well-structured model that incorporates continually updated historic information; using models to compute valuation with adjustments are 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; and effectively presenting model results to evaluate credit risk and ranges in equity value.

**You will learn how to:**

- Create a structured corporate model that uses and automatically updates historic information in a flexible manner which allows for 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; normalise 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.

**Agenda**

**Day 1**

*Part I **– **Creating an Efficient and Well Structured Corporate Model*

- A review of model objectives, model structure and flexible 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.

**Day 2**

*Part II – Computation of Valuation and Evaluation of Credit Risks Using Corporate Model*

- 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 normalised working capital changes, normalised depreciation expense, normalised capital expenditures and normalised 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.

**Course Information**

**Course Length:** 2 days

**Overview: **Excel is the premier tool for financial modeling, but anyone who has spent enough time building models will realize that it has its limits. Learning VBA opens up an whole new door of functionality in Excel. In our Advanced Modelling with VBA course, we’ll teach you how to apply VBA solutions to a variety of complex issues in corporate valuation and risk analysis.

**What You’ll Learn:**

- Demonstration of areas where VBA is required to solve deficiencies in excel including scenario analysis built on data tables, scenario analysis combined with optimization, resolution of circular references, Monte Carlo Simulation, presentation of results improve the goal seek function and automate reading of data from the internet.
- Structuring of models to eliminate problems with transparency that can result from use of VBA.
- Develop of a set of user defined excel functions to compute volatility, XMIRR, discounted payback, Black Scholes premium, vintage depreciation schedules and other statistics.
- Use of VBA and functions to address difficult problems in valuation including calculation of implied EV/EBITDA ratios from transition growth rates, changing ROIC and varying risk premier.
- Application of macros in modelling valuation from equity cash flow where terminal value multiples are function of model results and where capital structures remain constant on a market or a book basis over the forecast horizon.
- Use of VBA to compute the value of real and financial options from Monte Carlo simulation and the Black Sholes formula in the context of LBO model incorporating mean reversion and correlation among variables.
- Development of VBA functions and macros to resolve difficult problems in project finance and real estate models related to structuring financing with capitalized interest and fees, debt service reserve accounts, debt sculpting and modelling portfolios of projects.

**Agenda**

**Day 1: Use of VBA in Financial Models **

- General Philosophy of Financial Models
- Flexible, Accurate, Structured and Transparent Models
- Problems with Macros and VBA in Models
- Benefits of using VBA

- Fixing Goal Seek Problems – The Most Common Financial Macro
- Creation of a Macro
- Fixing the Problem of Inputting the Value
- Essential Use of Range Names to Make Macro Robust

- Using VBA to Improve Data Tables
- Problems with Excel Data Tables
- Cannot use recursive data tables
- Cannot use inputs from different sheets
- Limited presentation of data tables
- Re-calculation of data tables

- Fixing Data Tables
- Re-calculate on save
- Simple VBA program to limit re-calculation

- Creation of Data Tables with VBA
- Use of FOR NEXT loops
- Creation of Robust Tables
- Problems with use of macros for data tables

- Problems with Excel Data Tables

- Solving Circular References with VBA
- Circular references and interest expense
- Problem with use of iteration button
- Weak solution from re-pasting formulas

- Solution from copy and paste macro with iterations
- Presentation of iterations
- Problems with copy and paste method

- Circular references and interest expense

- Reading data from the Internet with VBA
- Practical uses of functions that transfer data
- Finding links for downloading files
- Downloading excel files with VBA and inserting data in separate sheets
- Downloading text with VBA

- Creating Monte Carlo Simulation with VBA
- Creating Monte Carlo simulation without VBA
- Computing Monte Carlo simulation with FOR/NEXT loop
- Calculation of value at risk and probability of default from Monte Carlo Simulation

- Use of VBA for effective presentation of financial model results

- Auto-optimization with solver
- Problems requiring solver in finance
- Financial institution capital

- Problems requiring solver in finance

*Use of Functions in Financial Models *

- Introduction to Functions
- Use of functions versus macros in financial models
- Finding of functions in VBA editor
- Building a simple function to document formulas

- Programming Functions for Payback Period
- Simple calculation of payback function with MATCH
- Use of FOR/NEXT Loop in functions
- Inclusion of Discount Rates

- Creation of a Function to compute volatility
- Computing volatility without function
- Use of function with array function

- Use of Functions to compute lengthy algebraic equations to resolve circular references
- Problems with long algebraic formulas
- Programming functions with formula segments and tests

- Development of function to compute vintage depreciation
- Calculation of depreciation and retirements from vintage table
- Use of SUMPRODUCT method to compute depreciation
- Function to with multiple loops to compute depreciation

- Creation of Add-ins for functions
- Copy and pasting macros from Dividend Discount Model
- Use of P/E Ratio
- Use of M/B Ratio
- Project Finance, LBO and Surveys

*Analysis of Price to Earnings Ratio, EV/EBITDA Ratio and Market to Book Ratio using VBA and Functions*

- Case Study of Using Multiples in Valuation
- Process of Deriving Valuation from Comparative Multiples
- Alternative Multiples and which Multiple to Use
- Problem and Biases in Comparative Valuation
- Reconciliation of P/E ratio and EV/EBITDA Ratio

- Case Study of Using P/E ratio in Valuation
- Importance of P/E Ratio in Valuation
- Process of Deriving Future Stock Price
- Relation Between Equity Cash Flow, P/E Ratio and DCF Process
- Derivation of Future P/E Ratio

- Derivation of P/E Ratios from Value Drivers
- Model of Valuation from Equity Cash Flow
- Derivation of Formula for Dividend Pay-out Ratio and Growth Rate
- Computation of P/E Ratio and Market to Book Ratio
- Evaluation of the Cost of Equity Capital from the P/E Ratio and Use of Goal Seek together with MACRO in Excel

- Importance of EV/EBITDA ratio in valuation
- Use of EV/EBITDA in M&A and valuation analysis
- Use of EV/EBITDA in acquisition exit proceeds
- Complexity of computing EV/EBITDA
- Problems with alternative methods such as simple growth rate and value driver formula
- Sensitivity Analysis with Alternative Value Drivers

- Establishment of Stable Period in DCF Calculation
- Problems without Stable Period – example of Working Capital
- Problems of working capital and circular reference
- Mechanics of computing stable period with life cycle
- Computation of stable deferred taxes with straight line depreciation
- Use of VBA function for tax depreciation
- Adjustments in Stable Period

- Sensitivity, Scenario Analysis and Tornado Diagram
- Construction of scenario analysis without VBA
- Construction of scenario analysis with VBA
- Creation of Tornado diagram and sensitivity analysis
- Creation of VBA functions to facilitate tornado diagram
- Programming VBA function for break-even analysis

- Regression Analysis of Multiples
- VBA program to read multiples from website
- Adjustment of links to download data for multiple companies
- Arranging data in different sheets
- Factors that should Drive the Multiples
- Adjustment of Sample Companies
- Construction of Regression Analysis
- Interpretation of Regression Analysis

**Day 2: Use of VBA and Functions in Corporate Modelling for Valuation **

*Setting-up Efficient Corporate Models *

- Importance of Time Line and Using TRUE/FALSE commands along with AND function
- Use of dates and EDATE function to enter historic period, transaction period, explicit period and stable period and use of DATA VALIDATION to limit time period increments in the model
- Historic period and flexibility to add new historic financial statements

- Development of Assumptions Module
- Use of historic financial statements and operating reports to establish assumptions
- Structure of assumptions – operating assumptions, tax and depreciation assumptions, financing assumptions, valuation parameters
- Alternative set-up of time series assumptions with INDEX function
- Use of VBA to automatically colour inputs and develop table of contents

- Setting-up Model Integrity Page
- Idea of effective integrity page – find and report errors without having to look around model
- Tests in corporate model – historic income statement, historic balance sheet, debt balance, prospective balance sheet
- Putting together the integrity label with IF tests and conditional formatting

- Depreciation and Deferred Tax Analysis
- Potential distortion created by not accounting for retirements
- Existing depreciation on net plant and use of stable ratios using OFFSET function for computing retirements
- Calculation of depreciation using SUMPRODUCT function
- Use of function to compute tax depreciation with accelerated schedule

- Debt Schedule and Plug Figure
- Debt schedule in standard corporate model and problem of circularity
- Copy and paste solution to circularity with iteration option
- VBA solution and problems with macros
- Algebraic solution with function instead of macro

- Case Study of Analysis Actual DCF Model
- Calculation and Theoretical Basis of Free Cash Flow
- Mechanics and Presentation of DCF
- Alternative Computations of WACC and Cost of Equity
- Different Calculations of Terminal Value

- Development of DCF Model Through Construction of Simple Model
- Use of Market Weights and Incremental Cost in Computing WACC
- Use of Switches for Flexible Timing
- Computation of Free Cash Flow
- Computation of constant market capital structure with SOLVER
- Use of VBA together with Solver
- Calculation of constant market capital structure using algebraic formulas

- Use of EPS and P/E ratio in valuation
- Importance of equity cash flow and earnings for financial companies
- Calculation of EPS with alternative capital structure
- Inclusion of dividends and equity financing in cash flow
- Use of M/B ratio in terminal value with regression analysis
- Computing value per share from equity cash flow

*Use of VBA and Functions in Project Finance and Real Estate Models*

- Difficult Problems in Project Finance and Real Estate Models
- Circular References from Capitalized Interest and Debt Commitment
- Circular References from Sculpting, Taxes and DSRA
- Limited Function of Data Tables in Real Estate Models

- Debt Sculpting in Project Finance Models
- Debt Sculpting with Solver, VBA and Algebra
- Circular Reference from Taxes
- Use of Algebra to Resolve Circular Reference
- Creation of Function with VBA to Resolve Circularity
- Analysis of Model with Function and Macro to Evaluate Contract Pricing

- Funding Calculations in Project Finance Models
- Alternative Funding Methods – Pro-rata, Equity First, Equity Bridge Loan, Bonds
- Funding without Capitalized Interest and Fees
- Circular Reference Created by Capitalized Interest and Fees
- Resolution of Circular Reference with macro and VBA
- Use of Algebra to Resolve Problem in Two Period Case
- Development Function to Resolve Circular Reference
- Contrast Between using Macro and Function

- Portfolios of Projects in Real Estate and Other Industries
- Resolution of Different Start Dates, Construction Periods and Holding Periods
- Consolidation of Projects Using Data Tables
- Consolidation of Projects Using VBA
- Problems of Risk Analysis with Consolidation using Multiple Data Tables
- Resolution of Scenario Analysis and Sensitivity Analysis

- Presentation of Results using VBA
- Creation of Menus in VBA with User Forms
- Use of Offset Function with VBA to Create Flexible Graphs
- Development of Macro to Re-size Graphs
- Creation of Table of Contents and Working with Sheet Names in VBA

**Course Information**

**Course: Credit Risk Masterclass**

**Course Length: **2 days

**Overview: **This course covers the credit risk, market risk and liquidity risk using judgmental risk assessments, cash flow projections and mathematical techniques. The course begins with an overview of alternative credit models, credit analysis concepts and credit scoring. The course then moves to cash flow modeling and incorporation of covenants, cash flow traps and other credit enhancements in the models. The third part of the course covers use of option price models and Monte Carlo simulation in analysis of market risk analysis.

**Agenda**

**PART 1: CREDIT ANALYSIS OVERVIEW **

*Review of the Theory and Practice of Credit Analysis*

- Credit Analysis Terms
- Traditional versus Mathematical Credit Analysis
- The Five C’s in Credit Analysis
- Definition of Probability of Default and Loss Given Default
- Overview of Key Credit Ratios
- Development and Use of Credit Matrix with Mitigation and Weightings
- Credit Ratings and Classification
- General Overview of Objectives of Basel II and Basel II

*Four General Categories of Financial Ratios to Measure Credit Risk*

- Debt to EBITDA and Time to Repay Debt
- Debt to Capital and Value of Firm versus Value of Debt
- Interest Coverage/Debt Service Coverage and Cash Flow Buffer
- Quick Ratios and Other Measures of Liquidity Risk
- Why Different Ratios should be used in Different Industries
- Case Study of Credit Ratios for LBO

*Credit Scoring and Credit Ratings*

- Credit Ratings as a Measure of Probability of Default
- Credit Matrix and Credit Migration
- General Classification of Credit According to Ability to Meet Downturns
- Use of Financial Ratios and Business Risk Classifications to Score Credits
- Statistical Approach to Credit Scoring and Problems
- Attempts to Directly Measure Probability of Default
- Case Study of Statistical Analysis for Housing Loans

*Overview of Mathematical Models for Credit Analysis*

- Debt Defined as Sold Put Option
- Merton Model and KMV Model Discussion
- Case Exercise on Building the Merton Model
- Use of Option Pricing Models for Credit Scoring
- Structure of Subordinated Debt in Option Pricing Models
- Practical Use of Option Pricing Models in Measuring Subordinated Debt
- Valuation of Senior and Subordinated Debt Using Option Pricing Models
- Problems with Measuring Parameters and Limits of Mathematical Models

**PART 2: FINANCIAL MODELLING AND CREDIT ANALYSIS**

*Credit Analysis and Corporate Cash Flow Models*

- Types of Credits where Cash Flow Modelling is Useful
- Objectives of Financial Models in Measuring Credit Quality
- Measuring Re-financing Risk with Corporate Models
- Measuring Default Risk with LBO and Project Finance Models
- Incorporation of Monte Carlo Simulation in Models
- Cash Flow Analysis, Liquidity Analysis in Models

*A-Z Model Exercise*

- Discussion of Model Structure
- Workings Exercise
- Debt Structure Exercise
- Financial Statement Exercise

*Case Study – Corporate Financial Model, LBO Model and Project Finance Model for Credit Risk Analysis *

- Analysis of Historical Financial Statements
- Establishment of Value Drivers
- Break Even Analysis for Credit
- Scenario Analysis for Credit
- Pro-forma Balance Sheet and Sources and Uses in LBO
- Modelling the Structure of Alternative Debt Issues
- Valuation of Senior and Subordinated Debt

*Analysis of Covenants and Cash Flow Sweeps*

- Advantages and Disadvantages of Covenants
- Cash Trap Covenants and Cash Flow Sweeps
- Good Time Covenants and Cash Flow Sweeps
- Valuation of Covenants

**PART 3: ANALYSIS OF MARKET RISK**

*General Discussion of Market Risk *

- General Definition of Market Risk and Implications for Financial Institutions
- Market Risk in Basel II and Basel III
- Market Risk and Interest Rates
- Market Risk and Exchange Rates
- Market Risk and Equities

*Time series Analysis of Prices and Economic Data in Models to Measure Credit Risk*

- Economic theory behind alternative time series models
- Definition and application of volatility
- Brownian motion time series in Interest Rates, Exchange Rates and Equity Prices
- Mean Reversion in Time Series for Equities, Yield Curves and Exchange Rates

*Measurement of Market Risk and VAR*

- Direct Measurement of Market Risk and Calculation of VAR
- Case Exercise on Computing VAR using Excel
- Incorporation of Correlations in Measurement of VAR
- Simulation Exercise with Excel to Measure Market Risk from Exchange Rates
- Incorporation of Correlation in Market Risk Analysis
- Simulation of VAR from Different Interest Rates with Excel
- Problems with VAR and use of Statistical Analysis in Measuring Risk

*Measurement of Risk from Swaps Using Market Risk*

- Credit Risk in Interest Rate Swaps and Exchange Rate Swaps
- Credit Exposure when In the Money and Out of the Money
- Creation of Excel Model to Measure Counterparty Risk of Interest Rate Swap
- Model to Measure the PG and LGD of Exchange Rate Swap
- Theoretical Pricing of Counterparty Risk in Swaps

**PART 4: ANALYSIS OF LIQUIITY RISK**

*General Discussion of Liquidity Risk *

- General Definition of Liquidity Risk and Implications for Financial Institutions
- Liquidity Risk Discussion in Basel II and Basel III
- Reason of Introducing Liquidity Risk
- Implications of Liquidity Risk
- Alternative Ways to Measure Liquidity Risk

*Mechanics of Measuring Liquidity Risk*

- Metrics Used to Measure Liquidity Risk
- Effects of Liquidity Risk in Product Pricing
- Changes in Liquidity Risk Pricing since the Financial Crisis

*Case Study of Liquidity Risk *

- Introduction to Case Study
- Measuring Liquidity Risk Before Problems Arose
- Cost of Liquidity Risk
- Mitigation Measures for Liquidity Risk

**Course Information**

**Course Name: **Levered Buyout Analysis

**Days: **1-2

**Agenda**

**Part I: Overview of Levered Buyouts**

*Review of Leveraged Buyout Theory *

- General Definition of LBO
- Theory of behind leveraged buyouts; why should companies add a lot of debt to the balance sheet
- Studies of efficiency from leveraged buyouts
- History of LBO Waves in 1980’s and 2000’s
- Expected Returns from LBO Providers of Capital
- Free Cash Flow DCF Valuation versus Earnings Analysis
- Valuation from Premium and Synergy versus Earnings Dilution
- Risk Analysis and Debt Capacity in Valuation
- LBO and Call and Put Options
- Use of LBO and Debt Capacity to Determine Implied Cost of Capital

*Accounting in Leveraged Buyouts*

- Purchase Accounting
- Minority Interest
- Goodwill, Impairment, and Equity Balance
- Asset Write-ups and Write-downs
- Tax Aspects of LBO

*Review of LBO Financial Models*

- Standalone Target Valuation Model
- Presentation of Analysis in LBO Model
- Modelling of A, B, C debt (Amortizing, Bullet and Capitalizing Debt)
- Work with M&A Template Model
- Exit Analysis in LBO’s
- Re-financing from Asset Sales

**Part II: Standalone Valuation in LBO Analysis **

*Use of Discounted Cash Flow and Equity IRR *

- Use of IRR versus MIRR in measuring returns
- Problems with traditional WACC Calculation in highly levered transaction
- Computation of IRR with uneven periods including transaction period
- Introduction to Euro LBO Case Study

*Financial Ratios and Standalone Valuation in LBO Case Study *

- Stock Price and Financial Returns
- Price to Earnings Ratios and EV/EBITDA
- Return on Equity and Return on Capital Employed
- Value/Book and Value to Replacement Cost
- Debt/Capital, EBIT/Interest, Debt/EBITDAX
- Case Exercise on PE Ratio and Growth

*Case Study on Historic Financial Analysis as Background for Valuation*

- Review of term sheet for Tribune company transaction
- Evaluation of Growth Rates over Alternative Periods
- ROE versus ROIC in assessing historic performance
- Interpretation of Credit Ratios
- Construction of Basic Forecast Variables

*Valuation Discussion in LBO Case Study*

- Alternatives-Multiples, DCF, Venture Capital, Risk Neutral, Option Pricing
- Discounting, Growth and Terminal Value
- Fixed Rate Debt Valuation in M&A
- Theory and Economic Value Analysis (EVA)
- Foreign Currency, Inflation and Free Cash Flow
- Intermediate and Long-Term Growth
- DCF Application

**Part III: Case Study and Risk Assessment of Leveraged Buy-outs **

*Debt Sizing and Structure in Leveraged Buyouts *

- Sizing using Debt/EBITDA and Debt Service Coverage Ratios
- Senior and Subordinated Debt
- Covenants
- Use of Earn-outs in LBO’s
- Management Ratchets in LBO’s
- Equity Kickers in Loan Obligations
- Rating Agencies and Debt Capacity

*Modelling Alternative Debt Structures *

- Cash flow Waterfall with Junior and Senior Debt
- Modelling the Impact of Covenants such as Tiered EBITDA/Debt
- Payment in Kind Subordinated Debt
- Cash Flow Sweeps on Senior and Junior Debt
- Mezzanine Debt with Equity Kicker
- Risk analysis from Credit Perspective

*Assessing Risk and Return in Leveraged Buyouts *

- Difficulty in assessing risk and return of different debt instruments
- Alternative methods of measuring risk and central issue of mathematical analysis
- Measuring risk and return with scenario analysis and judgmental probabilities
- Measuring risk by evaluating change of sudden change in variables and sudden loss of margin
- Use of Monte Carlo Simulation to directly measure the required return and risk of different financing instruments

*Risk Measurement of Alternative Financial Instruments in LBO Model *

- Sensitivity Analysis
- Break-even Analysis
- Scenario Analysis
- Monte Carlo Simulation
- Stress Cases

**Course Information**

**Course: **Modeling M&A and LBO Transactions

**Course Length:** 4 days

**Overview: **Financial modelling for mergers and acquisitions is an intensive hands-on course that offers practical instruction on how to model economic, financial and strategic issues associated with mergers and acquisitions. The course covers alternative methods for evaluating the financial and economic effects of acquisitions including discounted cash flow models, earnings accretion models, leveraged cash flow models and economic models. After describing general issues in mergers and acquisitions, programming and model structuring subjects are covered where attendees follow the lead of the instructor in building their own valuation model. As the course progresses, case studies are used to illustrate the practical issues associated with M&A modelling including debt structuring, minority interest, goodwill and deferred taxes that arise from alternative tax treatment of acquisitions.

**What You’ll Learn**

**Key Benefits:**

- Participants will build a complete financial model from A to Z. The model will start with a blank spreadsheet, and transform into a tool to measure the costs/benefits of an M&A transaction.
- In addition to building their own model, the course will show participants how to construct that incorporate sophisticated M&A concepts.
- By the end of the course, participants will be able to construct an integrated model that includes sources and uses of funds, pro-forma financial statements, acquisition premiums, cash flow waterfalls, synergies and effective presentation of the merger analysis.
- Models built off of case studies for complete comprehension.

**Advanced Topics:**

- Management of topics such as Cash Flow Waterfalls, Terminal Value Calculations, Derivation and Analysis of Multiples and Complex Modeling Aspects
- Advanced DCF Models for M&A that Include Complex Terminal Valuation Techniques and Flexible Transaction Timing
- Modelling of Alternative Transaction Structures with Pro-forma Balance Sheet, Taxes and Calculation of Accretion and Dilution
- Evaluation of Transaction Multiples through Evaluating Growth, Return on Investment, and Transition Periods
- Converting Corporate Models into Acquisition Models with Different Timing and Synergy Assumptions
- Constructing and Analyzing LBO Models with Complex Debt Structuring and Cash Flow Waterfalls

*Discounted Cash Flow and Multiples *

- Problems with EV/EBITDA, Growth, and Value Driver Methods for Computing Terminal Value
- Accounting for ROIC, Transition Growth and Cost of Capital Changes in Terminal Value
- Stable Period Adjustments for Capital Expenditures, Working Capital and Deferred Tax
- Evaluation of Items to Include in the Bridge between Equity Capital and Enterprise Value
- Simulating P/E Ratio with Alternative Growth, Return, and Risk Premium and Transition – Simulating EV/EBITDA ratios with Alternative Value Drivers

*Cash Flow Waterfall in Acquisition Models *

- Separately Modelling Amortizing, Bullet and Capitalizing Debt Tranches
- Risk and Return of Tranches of Acquisition Capital Structure
- Analysis of Cash Traps and Liquidity Facilities
- Simulating Earn Out and Incentive Payments
- Structuring Alternative Equity Tranches with Target IRR and Flip Structures

*Flexible Transaction Structures and Analysis*

- J-Curve and Optimal Holding Period
- Timing of Transaction in Mid Period
- Modelling of Alternative Acquisition Tax Structures
- Computing New Shares in Transaction with Share Exchange and Cash Transactions
- Using Share Premium to Set-Up Transactions or Implied Multiples
- Risk and Return Characteristics of Senior Debt, Mezzanine Debt and Equity
- Pro-forma Balance Sheet and Accounting

**Agenda**

*Day 1: Introduction to M&A, Model Drivers and DCF Analysis *

*M&A Terminology and Course Themes *

- General Discussion of terms in merger analysis
- Overview of valuation in acquisition and mergers in the context of finance theory and lessons learned from the financial crisis
- Alternative objectives in valuing M&A including DCF Valuation, multiples, internal rate of return, synergies versus premium and constrained accretion and dilution analysis
- Exercise on valuation from premium and synergy versus earnings dilution – Risk analysis and importance of debt capacity in M&A valuation

** **

*Analysis with Existing M&A Models – Model Objectives and Model Structure*

- Review of buy side and sell side presentations in actual M&A transactions
- Standalone target valuation model using DCF and synergy estimates with value build-up
- Benefits and problems from using P/E, EV/EBITDA and price to book multiples to evaluate acquisitions and in combination with DCF analysis
- Use of integrated M&A model to evaluate earnings accretion and dilution with alternative capital structures and alternative accounting assumptions
- LBO model with analysis of IRR using alternative capital structure assumptions with senior amortizing debt, senior bullet debt and subordinated capitalizing debt
- Credit analysis in alternative merger models
- Combined model with Equity IRR, unlevered IRR, accretion and dilution and DCF analysis

** **

*Discounted Free Cash Flow and Value Drivers*

- Financial theory and basis for DCF model
- Development of assumptions through considering industry structure, growth potential, pricing strategy, capital expenditure costs and fixed versus variable operating costs
- Construction of revenue, expense and capital expenditure model section from operating assumptions
- Building flexible models that evaluate different start periods, explicit periods and terminal periods
- Calculation of depreciation expense from capital expenditures and problems with retirements and computation of depreciation on existing assets
- Evaluation of return on invested capital from invested capital perspective and assets perspective and use of return to evaluate reasonableness of forecasts.
- Discussion of the theoretical and philosophical problems with measuring growth and WACC
- Calculation of terminal value using constant growth assumption and problem with wide range in values from different growth and WACC assumptions.
- Determination of enterprise value using constant growth assumption and valuation multiples
- Sensitivity analysis and scenario analysis for different operating assumptions and valuation assumptions

*Day 2: Stand Alone Financial Model for Valuation and Transaction Module for Acquisition Analysis *

*Corporate Modelling and Standalone Valuation*

- Layout and structure of corporate and M&A models
- Balance Sheet as model starting point and ending point
- Debt module and reconciliation of cash flow with balance sheet
- Model of financial statements and use of balance sheet to audit cash flow
- Calculation of projected return on invested capital
- Modelling constant capital structure on a book and market basis

*Valuation and Adjustments in Terminal Period and Bridge from Enterprise Value to Equity Value*

- Four methods of valuation from corporate models – growth method, value driver method, multiple method and P/E ratio method
- Importance of stable period in evaluating cash flows
- Discounting with mid-year cash flows and terminal value at end of period
- Use of ROIC/WACC spread in computing terminal value
- Calculation of stable period working capital changes from terminal growth rate
- Modelling issues with ratio of stable level of capital expenditures to depreciation
- Items to include in bridge between equity value and enterprise value
- Effect of items in bridge on the WACC
- Presentation and risk analysis of alternative valuation methods

*Structuring of Acquisition Model and Accounting in M&A*

- Transaction assumptions – premium, shares issued, debt issued, transaction costs, cash used, minority interest, debt retirement
- Alternative transaction assumptions and calculation of implied transaction multiples
- Sources and uses of funds analysis with alternative residual funding
- Purchase accounting, elimination of existing equity and re-valuation of assets and liabilities
- Goodwill and impairment calculations in alternative situations
- Pro-from balance sheet for modelling and analysis using sources and uses and goodwill
- Using alternative balance sheet dates for pro-forma analysis
- Alternative tax accounting for mergers – share exchange versus asset purchase – Deferred tax changes from mergers and acquisitions – share exchange

*Day 3: Acquisition Model and Cash Flow Waterfall *

* *

*Financial Structure of Acquisitions and Financial Modelling *

- Debt capacity in transactions and use of Debt to EBITDA ratio
- Valuation in private equity transactions and use of entry and exit EV/EBITDA multiples.
- Amortizing and bullet debt structures in leveraged transactions
- Use of capitalizing subordinated debt in financing transactions
- Alternative structures for equity financing including earn out provisions and flip structures
- Deferred tax changes from mergers and acquisitions – share exchange

*Financial Structure of Acquisitions and Financial Modelling*

- Debt capacity in transactions and use of Debt to EBITDA ratio
- Valuation in private equity transactions and use of entry and exit EV/EBITDA multiples.
- Amortizing and bullet debt structures in leveraged transactions
- Use of capitalizing subordinated debt in financing transactions
- Alternative structures for equity financing including earn out provisions and flip structures
- Optimal holding period and J-curve

* *

*Construction of Acquisition Model *

- Transfer of data from standalone model and incorporation of flexible transaction dates
- Inclusion of terminal proceeds in operating analysis
- Construction of debt schedule for existing debt and amortizing debt with repayment at exit
- Modelling credit spreads and cash sweep as a function of Debt/EBITDA
- Development of bullet debt with provisions for cash flow sweep
- Structuring of liquidity facilities and provision for senior debt defaults
- Income statement and tax module with adjustments for transaction and net operating loss carry forward
- Cash flow statement with provisions for waterfall, cash sweep and dividends
- Alternative calculations for positive and negative cash flow
- Limits on cash sweep, use of liquidity facility and debt defaults
- Modelling of cash flow to alternative equity tranches and earn-out provisions

* *

*Analysis Using Leveraged Buyout Model*

- Presentation of cash flow waterfall
- Calculation of IRR on Equity, Sub-debt and Equity
- Determination of break-even points for equity, subordinated debt and senior debt
- Scenario analysis and use in evaluating debt capacity (can different debt levels be repaid in downside or worst case)
- Tornado analysis for due diligence and underwriting – discover which variables are the most important in equity IRR and debt analysis

*Day 4: Assessment of Entry and Exit Multiples in Acquisition Analysis, Integrated Acquisition Models and Synergies *

** **

*Calculation of Implied Multiples for Entry, Exit and DCF*

- Construction of P/E ratio from value drivers with alternative transition periods
- Modelling of invested capital using net depreciation rate and stable ratios of capital expenditures and deferred taxes
- Modelling of cost of capital from inflation, real risk free rate and risk premium
- Computation of free cash flow and growth rate from ROIC assumptions and growth rates
- Incorporation of implied multiples in DCF analysis and acquisition models
- Regression analysis of multiples and theory of multiples

** **

*Integrated Models and Accretion and Dilution Analysis*

- Importance of accretion and dilution for investment analysts and management
- Incorporation of stock price of acquiring company, exchange ratios and partial share swap transactions in merger models
- Debt issuances in integrated model and credit constraints
- Sources and uses and goodwill analysis in consolidated models
- Pro-forma balance sheet for consolidated company
- Consolidation of operating accounts, asset accounts and working capital
- Modelling of financing and taxes for consolidated company
- Calculation of consolidated and standalone EPS
- Computing alternative ratios to simulate credit ratings and evaluate credit quality – Sensitivity analysis of alternative acquisition premiums and debt financing

** **

*Synergy Analysis in Acquisition Analysis*

- Theory of synergies and identifying synergies
- Different types of synergies
- Valuation of synergies
- Break-even synergies

**Course Information**

**Course: **Mezzanine Finance Masterclass

**Course Length: **1 day

**Key Topics **

- How can senior and mezzanine debt be used to facilitate a management buyout or other forms of ownership transition?
- How can one assess a company’s borrowing capacity?
- What are the key credit, pricing and rating issues surrounding mezzanine financing?
- What is the right pricing and covenant structure for mezzanine debt in alternative types of transactions?
- How do mezzanine financing techniques such as warrant notes work, and when does it make sense to use them?
- How can one model the cash flows and debt pay-down in a transaction with mezzanine debt?

**Agenda **

*Why Mezzanine Finance?*

- Performance-driven leveraged finance
- Defensive leveraged finance
- Deal-driven leveraged finance
- Leverage in ownership transition
- Mezzanine debt as temporary financial instrument
- Use of Mezzanine debt in project finance
- Identifying corporate debt restructuring opportunities

*Alternative Structures of Mezzanine Debt *

- Review of Mezzanine Debt Term Sheet
- Capitalizing Interest in Mezzanine Debt and PIK
- Repayment Structures in Mezzanine Debt
- Equity Kickers in Mezzanine Debt
- Subordinated vendor financing

*Debt Capacity Analysis for Senior and Mezzanine Debt *

- Implicit or explicit credit rating targets to determine debt size
- Use of mezzanine debt to manage senior debt capacity
- Computing debt size with prospective financial ratios
- Deriving the debt size through assessing downside scenarios

*Pricing of Mezzanine Debt*

- History of debt pricing for different instruments and credit ratings
- Theory of debt pricing from probability of default and loss given default
- Deriving the implicit probability of default using alternative pricing
- Case study of pricing of mezzanine financing – Iridium
- Use of financial ratios to determine the rating of senior and subordinated debt

*Four General Categories of Financial Ratios to Measure Credit Risk of Mezzanine Debt *

- Debt to EBITDA and Time to Repay Debt
- Debt to Capital and Value of Firm versus Value of Debt
- Interest Coverage/Debt Service Coverage and Cash Flow Buffer
- Quick Ratios and Other Measures of Liquidity Risk
- Why Different Ratios should be used in Different Industries
- Case Study of Credit Ratios for LBO

*Overview of Mathematical Models for Credit Analysis of Mezzanine Debt*

- Debt Defined as Sold Put Option
- Merton Model and KMV Model Discussion
- Case Exercise on Building the Merton Model
- Use of Option Pricing Models for Credit Scoring
- Structure of Mezzanine Debt in Option Pricing Models
- Practical Use of Option Pricing Models in Measuring Mezzanine Debt
- Valuation of Senior and Mezzanine Debt Using Option Pricing Models
- Problems with Measuring Parameters and Limits of Mathematical Models

*Analysis of Covenants and Cash Flow Sweeps for Mezzanine and Senior Debt *

- Advantages and disadvantages of covenants
- Cash Trap covenants and cash flow sweeps
- Good time covenants and cash flow sweeps
- Valuation of covenants

*Pricing of Warrants and Equity Kickers for Mezzanine Finance*

- Structure of alternative equity kickers
- Value of warrant as call options
- Alternative ways to value call options using option pricing models
- Effect of warrants on coupon rate

**Course Information**

**Course Length:** 2 days

**What You’ll Learn:**

- Learn an efficient structure for developing real estate models
- Develop models from A-Z covering fundamental concepts through advanced issues
- Create transparent and effective presentation of models that present alternative portfolios of projects with different start dates and holding periods
- Incorporate complex financial structures into models and create cash flow waterfall analysis
- Develop alternative risk analyses from models including break-even analysis, sensitivity analysis, scenario analysis and Monte Carlo simulation
- Understand finance theory underlying modelling issues such as capitalization rates, required returns and debt capacity.
- Create alternative approaches to measure the trade-off between risk and return for different projects and different financing instruments.

**Overview: **Real Estate Modelling is an intensive hands-on course that provides attendees with knowledge regarding both fundamental and challenging modelling issues in the real estate industry. Delegates will learn how to model mixed development projects, residential projects with multiple portfolios, cash flow waterfalls, and simulation of risk associated with different lease rolls. Sessions of the course will include effective presentation of model outputs and comprehensive scenario analysis. In addition, the program will enable delegates to develop their skills in a variety of modelling issues associated with setting-up inputs, working with flexible time periods and incorporating alternative financing structures.

**Agenda**

**Section 1: Introduction **

- Real estate model structure compared to corporate model, LBO model, project finance model
- Difficulties in real estate modelling
- Modelling timing of construction, phases and exit proceeds o Modelling portfolios of projects
- Modelling milestone payments
- Modelling of cash flow waterfalls and structured finance
- Lease portfolios and risk analysis

- Excel techniques for real-estate modelling and annual single project
- Short-cut keys for setting-up sheets
- Use of switches for project phases and exit period
- Presentation of cash flows and sensitivity analysis

**Section 2: Model of Single Project **

- Periodic modelling and flexible analysis of alternative periods
- Modelling delays in construction and alternative terminal periods
- Theory of capitalization rates
- Conversion of periodic model to annual model

- Developing flexible inputs for utilization rates, lease rates and operating costs
- Variables that change as a function of calendar years
- Variables that change depending on the age of a project
- Development of annual period counters

- Debt sizing and debt re-structuring
- Debt inputs including repayment pattern, interest rates, covenants, debt service reserves and debt sizing
- Modelling of debt drawdowns during construction period
- Computation of repayment during operation and at exit
- Adjustments for periodic interest expense

- Model verification
- Creative establishment of multiple tests
- Aggregation of verification checks
- Identification of places in which model is not working

- Scenario analysis with single project model
- Computation of Equity IRR, Unleveraged IRR and other ratios
- Creation of flexible master scenario pages
- Presentation of sensitivity analysis demonstrating the relative effect of different variables

**Section 3: Model of Mixed Development Project **

- Set-up of inputs for overall project and for individual sub-projects
- Land costs and development of infrastructure costs
- Timing assumptions for individual sub=projects
- Operating assumptions for commercial projects
- Operating assumptions for residential projects including s-curves and progress payment profiles
- Set-up of financial assumptions

- Development of model for single project
- Use of common date structure for all projects
- Computation of time period counters for different projects
- Construction of models that allow flexible construction, revenue and operating costs that evaluated different types of projects
- Pre-tax cash flow and IRR’s on sub-project basis

- Consolidation of operating inputs for multiple sub-projects
- Efficiently summing sub-project items without creating separate models
- Alternative presentations of project portfolio
- Items required for financial model

- Financial model of consolidated model
- Debt commitment and debt draws with multiple completion dates
- Allocation of interest during construction
- Repayment of mortgage debt

- Scenario analysis in mixed development model
- Problems with traditional excel tools for sensitivity and scenario analysis
- Creation of master scenario page
- Use of macros in creating scenarios

**Section 4: Model of Residential Portfolio with Milestone Dates **

- Model inputs with milestone dates
- Set-up of flexible milestones
- Use of dates or periods
- Construction expenditures for different milestones

- Individual projects with milestone dates
- Model timing and switches
- Calculation of construction time periods for different milestones
- Computation of construction expenditures and revenues
- Cash flows and IRR’s for individual projects

- Consolidation and financial model
- Effect of milestone dates on IRR
- Debt sizing and debt capacity with different residential margin and timing assumptions

**Section 5: Structured Finance in Real Estate Models **

- Alternative Financing Structures
- Mortgage debt
- Senior and subordinated debt
- Preferred stock and trigger returns

- Inclusion of alternative finance structure in mixed development model
- Inputs for alternative financing instruments
- Set-up of schedules for alternative financing instruments
- Modelling of cash flow waterfall
- Auditing of cash flow waterfall

- Evaluation of risk and return of different financing instruments
- Computation of IRR and NPV for each financial instrument
- Break-even points for different instruments
- Inclusion of NPV and IRR in scenario analysis

**Section 6: Lease Roll Analysis and Risk Simulation **

- Risk and return of projects with different lease expirations
- Volatility of lease rates
- Effect of lease rate on debt capacity and required return
- Valuation of projects with different lease rate structures

- Inputs for lease roll
- Lease rate, expiration dates, idle time and renewal rates
- Volatility of lease rates
- Downside and upside scenarios

- Modelling of future lease rates and idle time
- Vintage of lease rates
- Use of range names with formulas
- Monte Carlo simulation of the distribution of returns with different lease rolls

**Course Information**

**Overview:** The Risk Analysis Modelling in Excel training course will provide participants with the ability to add alternative types of risk analysis to different types of financial models in a flexible and efficient manner. Risk analysis techniques will cover traditional methods ranging from scenario analysis, sensitivity analysis and break even analysis to mathematical analysis with Monte Carlo simulation. Key objectives of the course include understanding the process to add scenario analysis to any financial model and to evaluate the benefits and problems of applying time series equations and simulation analysis with alternative parameters.

**Course Length: **1 day

**What You’ll Learn**

- Create a structured scenario and sensitivity analysis from existing models that effectively displays the effect of variables and compute break-even analysis in alternative scenarios.
- Evaluate alternative break-even points for structured finance transactions with multiple debt and equity tranches as well as different credit enhancement structures such as cash flow sweeps, traps and reserve accounts.
- Compute P75, P90 statistics for alternative variables and understand how to interpret effects of mean reversion, limits and alternative approaches to measuring variance.
- Discover how easy you can create a Monte Carlo simulation analysis without any add-in programs using a few lines of VBA code and how to create functions that can perform Monte Carlo simulation with a user-defined function.
- Compute volatility mean reversion and price boundary statistics for different time series and understand the difficulty in computing volatility in the presence of a series with high mean reversion.
- Use historic data for securities prices, commodity prices and demand to evaluate alternative whether the distributions follow a normal distribution or are better represented by alternative distributions.
- Simulate the possible movement of correlated variables and create test statistics to evaluate whether the input correlation is the same as the generated correlation.
- Add Monte Carlo simulation to corporate models, project finance models and acquisition models with different price boundaries, mean reversion parameters, correlations and volatility statistics.
- Learn Excel techniques included selected user defined functions with VBA to make better presentations from models and to make models more transparent and efficient.

** **

**Agenda**

**Part I – Adding Risk Analysis to Financial Models **

- Review of alternative traditional and mathematical risk analysis techniques applied to corporate models, project finance models and structured acquisition models.
- Incorporation of master scenario analysis and sensitivity diagram from corporate model 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.
- Inclusion of break-even analysis in structured finance transactions with multiple debt and equity tranches to measure the effects of different capitalization and different credit enhancements (such as cash flow sweeps) on the risk of a transaction.
- Measurement of Risk Using Structured Master Scenario Page in Excel Model with Options for Adding Sensitivity Analysis to Defined Scenarios
- Computation of value assuming different sale dates and risk adjusted discount rates from buyer perspective as risk of project changes from signing contracts, working through mechanical issues and demonstrating cash flows from historic record.

** **

**Part II – Evaluation of Time Series Data and Computation of Statistical Parameters **

- Review of time series theory to define the possible movement in prices, quantity sold, costs and other variables.
- Program spreadsheets to automatically upload data from alternative websites on an automated basis using simple VBA code.
- Compute volatility statistics for alternative time periods and evaluate whether different distributions such as stock prices, interest rates and commodity prices follow a normal distribution.
- Derive implied mean reversion from computing volatility with random walk compared to actual volatility and fit alternative distributions to data.
- Compute implied volatility from option pricing models and use the Merton Model to evaluate credit spreads on different types of debt instruments.

**Part III – Use of Monte Carlo Simulation and Options Pricing Models in Measuring Risk in Different types of Financial Models **

- Illustration of Monte Carlo simulation using four different excel methods to evaluate the credit spread on senior and subordinated debt with different levels of volatility.
- Reconciliation of Monte Carlo simulation models with option pricing models through constructing option pricing models with VBA functions.
- Incorporation of boundary conditions, mean reversion and correlation among in Monte Carlo simulation analysis and analysis of whether input parameters for mean reversion and correlation are consistent with constructed distributions.
- Construction of Monte Carlo simulation using non-normal distributions through creating distributions with fat tails, skewed probabilities and extreme jumps.
- Addition of Monte Carlo simulation to existing corporate models and project finance models and effective presentation of outputs to measure credit risk and equity value at risk.
- Case study of measuring the risk benefits of cash sweeps and other credit enhancements in a wind transaction through converting P90 and 75 statistics to volatility parameters and applying Monte Carlo simulation.