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