### Financial Modelling Courses

Excellence in financial modelling is the core from which all other areas of the Trivium team’s expertise is based on. We’ve developed innovative solutions in almost every industry in finance, and we have over 30 years of experience in training teams. We offer numerous courses, and can easily offer customized courses for anything not listed.

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

**Overview: **Advanced Project Finance Analysis provides participants with the ability to create and understand sophisticated project finance models. Through building models in a hands-on environment, you will be better able to quantify risks of different types of projects and to use models to design the best debt, equity and contractual structure to build models.

**Course Length**: 3 days

**What You’ll Learn**

- Understand the objectives and the structure of project finance models in terms of structuring and risk analysis.
- Learn financial modeling best practices to allow for high levels of flexibility and complete transparency in calculations.
- Master model accuracy with effective error checks to verify model results.
- Create master scenario pages with time series and fixed variables to allow for advanced risk analysis.
- Learn to understand and program structured models that incorporate complex cash flow waterfalls, alternative funding cascades and sculpted repayment techniques.
- Create flexible models with effective summary statistics to evaluate alternative timing, operating assumptions, financial structures, re-financing and contract pricing.
- Work through the difficult problems in project finance modelling including
- Complex cash flow waterfalls with balloon payments and mini-perm structures
- Sizing of debt with capitalized interest and alternative drawdown schedules
- Flexible debt sculpting with income taxes
- Sizing of debt service reserves and use of DSRA in waterfall
- Re-financing of debt and mini-perm debt
- Debt service reserves that look ahead to future years

- Understand the benefits of creating user defined functions rather than copy and paste macros to resolve any circular reference problems in project finance models including funding problems and debt sculpting
- Incorporate structural enhancements into models such as maintenance reserve accounts and gain insight into the costs and benefits of the alternative features such as cash flow sweeps, covenants and debt service reserve accounts
- Learn Excel techniques with some VBA to make better presentations from models and to make models more transparent and efficient

**Agenda**

**Module I: Introduction and Model Structure **

The Project Modelling with Excel program begins with introductory comments about the skills and general objectives in project finance modelling with an emphasis on the difficulty in measuring and valuing risk. After the introductory discussion, participants begin work on construction of a flexible, structured, accurate and transparent project finance model.

- Flexible Timing in Project Finance Models
- Review of actual models and date inputs and timing
- Modelling project phases with switches and timing switches
- Modelling delay risk and flexible construction periods and S-curves
- Conversion of periodic data into annual and semi-annual presentations

- Operating analysis and pre-tax IRR
- Setting-up operating assumptions for revenues, operating expenses and capital expenditures
- Alternative methods for presenting time series assumptions
- Modelling inflation rates and growth rates with different time periods
- Calculation of pre-tax IRR and construction of summary page
- Computation of liquidated damages from construction delay
- Calculation of contract price from required pre-tax IRR

- Depreciation, capital allowance and fixed asset module
- Notion of structured models with separate page for depreciation analysis
- Use of timing switches for depreciation and/or capital allowance
- Introduction to verification and auditing for testing balances
- Benefits of separating deprecation on interest during construction and fees from other depreciation and amortization
- Calculation of after-tax project IRR
- Calculation of project IRR assuming alternative sale dates earlier than the retirement date
- Scenario analysis with data tables and VBA

**Module II: Financial Structuring In Model During Construction **

The second module of the program begins the addressing details of project finance models including funding structure during construction, interest during construction, bond financing and various other exercises relevant to financing during construction in project finance models.

- Review of Financing Calculations in Sample Completed Models
- Alternative possible financing assumptions
- Notion of funding versus debt commitment and presentation in models
- Presentation of sources and uses of funds

- Circularity Macros and Functions for Capitalized Interest and Fees
- Alternative methods to resolve circularity
- Illustration of four methods for resolving circularity with fee example
- Advantage of functions relative to macros for transparency and flexibility
- Use of algebra and functions instead of macros

- Funding Cascade
- Pro-rata construction versus equity up-front versus equity bridge loan
- Funding Needs versus Debt Commitment and Funding Ratio
- Use of MAX and MIN in developing funding cascade
- Modelling of bond financing with flexible timing and cash fund

- Debt Schedule During Construction
- Interest during construction and interest capitalized using periodic interest rates and credit spreads
- Set-up of summary sources and uses of funds to resolve circularity
- Up-front fees and commitment fees
- Repayment of construction debt at project completion date to avoid DSRA circularity

- Model Verification and Accuracy Audits
- Developing multiple tests for funding needs and funding uses using logical variables
- Aggregation of multiple tests
- Effective presentation of model verification on each page of model

**Module III: Debt Structure, Sculpting and Debt Sizing**

As project finance is a type of debt, the third module addresses various theoretical and practical issues related to debt financing in general and project debt in particular. Subjects included in module three include setting up a debt schedule, debt sculpting, flexible debt terms, debt capacity, debt structure and credit measures.

- Debt Schedule and Debt Capacity
- Set-up of debt schedule from construction debt
- Debt capacity from debt service coverage
- Debt repayment with equal instalments or mortgage repayments
- Presentation of cash flow and debt service on summary page

- Debt Structuring
- Alternative debt tenor
- Varying credit spreads
- Verification tests for debt balance and debt repayment
- Inclusion of bond financing and mini-perm

- Debt Sculpting
- Computing repayments assuming debt sculpting without taxes and DSRA
- Circularity problems arising from sculpting and taxes
- Resolution of circularity problems using function

** **

** Module IV: DSRA, MRA and Cash Flow Waterfall **

The fourth module moves from debt structuring to risk analysis. The principal issue addressed is how to model a cash flow waterfall where different instruments such as cash flow sweeps reserve accounts and covenants are used to protect senior debt service.

- Cash Flow Sweep and Cash Trap Covenants
- Incorporation of cash sweep in debt schedule
- Limits on cash sweep using MIN function
- Programming cash trap covenants
- Set-up of cash lock-up account

- Efficient Cash Flow Waterfall Modelling
- Setting up debt and reserve schedule combined with cash flow analysis
- Setting up cash flow with positive and negative conditions
- Use of sub-totals in modelling cash flow
- Application of MAX and MIN functions to limit cash flows
- Modelling sinking fund, sweep and bullet repayment

- Debt Service Reserve Accounts
- Modelling required debt service balances
- Withdrawals from debt service reserve
- Topping-up of debt service reserves
- Debt reserve during end of construction period
- Resolving circularity associated with debt service reserve

**Module V: Taxes and Financial Ratios **

The fifth module completes the project finance model through inclusion of a profit and loss statement and computation of income taxes. Given the income, a balance sheet is computed to verify calculations in the model. Finally, the LLCR, PLCR and the average debt life are computed.

- Profit and loss statement and income taxes
- Structuring profit and loss
- Inclusion of depreciation on interest during construction and amortization of fees
- Programming basic net operating loss account
- Accounting for expiration of net loss carry forward

- Calculation of Alternative Financial Ratios
- DSCR with and without tax effect of interest during construction and fees
- Calculation and interpretation of PLCR with varying interest rates
- Adjustments to compute LLCR
- Alternative calculations of loan life

- Balance Sheet Calculation
- Limited need for balance sheet in analysis
- Concept of computing equity balance and using closing balances from other sections of the model
- Use of balance sheet as auditing tool

**Module VI: Re-financing and Risk Analysis **

In the final part of the program participants use the model they have created to analyze a series of decisions and evaluate various risks. Participants first address traditional measurement of risk through break-even analysis, scenario analysis and sensitivity analysis. Next, the program moves to mathematical approaches that directly attach a numeric value to risk. Exercises in this part of the program include creation of time series models, Monte Carlo simulation, and credit issues associated with long-term supply contracts.

- Major Risks of Projects (analysis and mitigation)
- Commodity price risk versus volume risk
- Technology risk (breakdown and obsolescence)
- Input supply and availability risk
- Foreign currency and political risk
- Construction cost
- Contract breakage risk

- Re-financing
- Switches for the re-financing period
- Sources and uses for re-financing
- Sizing of re-financed debt

- Risk Analysis Exercises
- Set-up of master scenario page
- Break-even analysis
- Sensitivity analysis and graphs
- Scenario analysis
- Tornado analysis
- Monte Carlo Simulation

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

**Course Name:** Renewable Energy Masterclass

**Overview:** This class is our largest, most comprehensive course offering for renewable energy. We’ll walk you step-by-step through every aspect of analyzing, structuring, and financing every major renewable energy project. You’ll work through resource assessment for technologies from Hydro to Solar, you’ll analyze risks belonging to each respective technology, and you’ll learn to structure the project’s financial profile under every credit constraint known.

**Course Length: **7 days

**Agenda**

*DAY 1*

* *

**PART 1: DEFINITION OF TERMS, RENEWABLE COSTS, LEVELIZED COST OF ELECTRICITY**

*Overview of Terms in Project Finance Terms in the Context of Renewable Energy through Review of Financial Models*

- Renewable Energy Terms
- Economic Issues Associated with Renewable Energy
- Alternative Contract Structures Relative to Conventional Plants
- Grid Parity
- Carrying Charge Factor
- Feed-in Tariff Policy
- Renewable Energy Pricing versus Multi-part Tariffs in Conventional Energy

*Drivers of Value in Renewable Energy Projects*

- Resource Assessment and Capacity Factor
- Development Cost and Timing
- Electricity Pricing
- Capital Costs
- Operating and Maintenance Costs
- Capital Recovery Factor, Taxes, Financing
- Operating Costs, Maintenance Costs and Availability

*Capital Intensity and Levelized Cost of Alternative Technologies*

- Definition of Capital Intensity and Cost of Capital
- Four Factors that Drive LOCE
- Capital Cost of Project
- Capacity Factor of Project
- Operation and Maintenance Expenses
- The Carrying Charge Rate

*Exercise on Computing Grid Parity*

- Databases for Electricity Costs
- General Discussion of Cost Trends in Wind, Solar, Natural Gas and Other Technologies
- Review of IEA Cost Analysis for Various Types of Renewable and Non-Renewable Plants Including On-Shore and Off-Shore Wind, Solar PV, Solar Thermal, Geothermal, Wave Energy, Hydro and Biomass
- Natural Gas Prices by Region and Over Time
- Grid Parity with Different Capital Cost, Carrying Charge Rates and Natural Gas Prices

*DAY 2*

**PART 2: OVERVIEW OF PROJECT FINANCE, COST OF CAPITAL AND PRICING CONTRACTS FOR RENEWABLE ENERGY**

*Economic and Financial Theory of Project Finance*

- General Discussion of Risks for Renewable Projects
- Capacity Factor Risk (Solar, On-Shore Wind, Off-Shore Wind, Hydro)
- Construction Risk (Off-Shore Wind and Hydro versus Solar)
- Operation and Maintenance Risk
- Contract Risk
- Measurement of Risk using Traditional Finance Theory and WACC
- Review of Solar Manufacturers
- Theory of Cost of Capital, Beta and Equity Risk Premium Applied to Renewable Energy
- Contrast of Solar and Off-shore Wind from Traditional Cost of Capital and Free Cash Flow Perspective
- Measurement of Value and Risk using Project Finance
- Debt Capacity and Credit Analysis of Different Transactions
- Use of Equity IRR versus Project IRR
- Relying on External Cash Infusions Rather than Internal Analysis
- Financing for Different Types of Projects
- Debt Capacity and Project Finance Terms
- Effects of Debt Service Coverage Constraint versus Debt to Capital Constraint
- Debt Service Coverage Ratio Definition and Targets
- Debt Tenor, Alternative Repayment Structure, Average Life
- Credit Spreads and Target Credit Ratings in Project Finance
- Debt Service Reserve and Maintenance Reserve
- Covenants, Cash Flow Sweeps and Subordinated Debt

*Project Finance Valuation for Renewable Energy*

- Project IRR to Screen Projects
- Equity IRR to Structure Projects and Minimum Required Equity IRR for Different Renewable Projects
- Equity IRR Complexities from Re-Financing and Development Fees

**PART 3: REVIEW OF COSTS, CAPACITY FACTORS, AND PRICING STRUCTURES FOR ALTERNATIVE RENEWABLE TECHNOLOGIES AND BUILD-UP OF PROJECT FINANCE MODEL**

*Discussion of Wind and Solar Technologies*

- On-Shore Wind
- Historic and Current Trends in Capital Cost
- Review of Financial Data for Suppliers
- Fixed and Variable O&M Cost
- Capacity Factors and General Discussion of One-year versus Long-term P90, P75 etc.
- General Contract Terms
- Feed in Tariffs and PPA Agreements for On-Shore Wind
- Off-Shore Wind
- Capital Cost Data Base, Distance, Depth and Height
- Balance of System Costs for Off-Shore versus On-Shore
- Operating Costs and Availability Issues
- Capacity Factors
- Feed-in Tariffs
- Case Study of Princess Amelia Financing
- Solar PV
- General Discussion of Technology
- Demand and Supply and Changes in German Feed-in Tariffs
- Review of Solar Companies and Financial Issues
- Financial Performance of Manufacturers
- Balance of System Costs and Inverter Costs
- Performance Guarantees and Other Contract Terms
- First Solar Case Study
- Solar Thermal
- Costs and Capacity Factor
- Operating Cost
- Potential Trends

*DAY 3*

**PART 4: RESOURCE ASSESSMENT**

*Overview of Resource Assessment in Renewable Projects*

- Resource Assessment of Wind – working with hourly wind speeds, Weibull Distributions and Statistical Analysis (P90, P50 etc.)
- Resource Assessment of Solar – Direct and Diffuse Radiation, Sunlight Angles and RetScreen.)
- Resource Assessment of Geothermal and Hydro
- Probability Distributions of Resources
- Case Study on Resource Analysis in Loans

*Modelling Resource Distributions of Solar Power*

- Data sources for Solar Irradiation
- Computation of Production from Efficiency with Adjustments for Performance
- Statistical and Seasonal Distribution of Production Data in Different Locations
- Use of RetScreen for Simple Analysis
- Computation of P95, P90, P75 and P50 Statistics

*Case Study on Interpretation of P75, P90, P95 and P99*

- Review of Credit Analysis for Wind Projects
- Analysis of Resource Risk in Different Reports
- P90/P50 for One Year and for Ten Years
- Case Study on Detailed Calculation of Resource Distribution

*Modelling Resources for Hydro Power*

- Fundamental Hydro Power Equation
- Sizing of Hydro Plants and Exceedance Curves
- Variance in Rainfall and River Flow
- Computation of P95, P90, P75 and P50 Statistics for Hydro Capacity Factor

*Modelling Resource Distributions of Wind*

- Overview of Wind Data
- Weibull Distribution and Wind Data
- Power Curve for Wind Turbines
- Distribution of Wind Resources and Wind Studies
- Simulation of Power Distribution
- Computation of P95, P90, P75 and P50 Statistics

*Interpretation of Resource Assessment for Risk Analysis*

- Mean Reversion of Resources
- Aspects of Probability Distribution other than Resources
- Use of Probabilities in Financial Analysis
- Contrast Among Different Risks

*DAY 4*

**PART 5: VALUATION AND PROJECT FINANCE MODELLING FOR RENEWABLE PROJECTS**

*Financial Statement Analysis in Renewable Project Finance*

- Risks and Stages of Project
- Source and Use of Funds Statement before Commercial Operation
- Focus on Cash Flow and EBITDA
- Interpretation of Cash Flow Statement
- Reserve Accounts for Debt Service and Other Factors
- Computation of Equity Cash Flow and Free Cash Flow and IRR versus Return on Investment

*General Discussion of Project Finance Models*

- General Objectives of Financial Models and Financial Forecasts
- Objectives of Project Finance Models
- Flexibility, Structure, Accuracy and Transparency of Project Finance Models
- Examples of the Structure of Actual Project Finance Models
- Creation of Project Finance Model Structure

*Mechanical Issues in Creating and Interpreting Project Finance Models*

- Phases in Project Financing for Renewable Projects
- Sources and Uses Analysis in Models
- Importance of Debt Sizing, Debt Funding and Debt Repayment
- Fixed Asset Analysis
- Profit and Loss Statement and Tax Analysis
- Cash Flow Waterfall
- Resolving Circular References without Copy and Paste Macros arising from Funding and Sculpting

*Model Complexities for Renewable Finance Projects*

- Mechanics and Rational for Alternative Incentive Schemes
- Benefits of Investment Schemes Relative to Feed-In Tariffs
- Effects of Incentives on the Overall Cost and Require Feed-in Tariffs of Different Renewable Projects
- Cash Flips and Allocations of Cash Flow to Different Equity Investors – Interpretation of Risk and Return with Different Cash Flow Allocations
- Computation of Project 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.
- Incorporate Refinancing Assumptions in Financial Models through Adding Sources and Uses of Funds Analysis in Alternative Re-financing Periods and Evaluating Different Possible Features of Re-financing.

*DAY 5*

**PART 6: RISK ANALYSIS OF RENEWABLE ENERGY**

*General Discussion of Risk in Renewable Energy Projects*

- Discussion of Differences in the Nature of Risks for:
- On-Shore Wind (Wind Resource)
- Off-Shore Wind (Maintenance and Life Expectation)
- Solar (Small Risks become Big with High Leverage)
- Hydro (Capacity Factor and Merchant Price Risk)
- Wave (Refurbishment Timing)
- Geothermal (Development Probability)

- Risk Matrix, Risk Classification and Risk Mitigation
- Risk Evaluation Using Break-Even and Sensitivity Analysis
- Risk Evaluation Using Scenario Analysis with Focus on the Manner in which Bankers Apply Downside Analysis
- Measurement of Risk Using Structured Master Scenario Page in Excel Model with Options for Adding Sensitivity Analysis to Defined Scenarios
- Risk Analysis Using Spider and Tornado Diagrams

*Credit Analysis in Renewable Project Finance*

- Background on Probability of Default and Loss Given Default
- Definition and Calculation of DSCR
- Use of DSCR in Base (P50 Cases) and Downside (P90, P95 Cases) in Determining Debt Capacity
- Application of LLCR and PLCR

*Contract Structuring in Renewable Project Finance*

- Importance of EPC Contract in Different Projects (Off-Shore Wind and Hydro)
- Performance Contracts in Solar Projects
- Power Curve and Availability Guarantees in Solar and Wind Projects
- O&M Contracts and Warranties
- Insurance
- Counterparty Risk in Different Projects

**PART 7: RE-FINANCING, ELECTRICITY PRICING, BIOMASS AND GEOTHERMAL ISSUES**

*Re-Financing for Renewable Projects*

- Types of projects where re-financing is important – off-shore wind, wave energy, merchant hydro projects and geothermal
- Effects of re-financing on equity IRR and difficulty of defining the equity IRR with short-debt duration
- Structuring project finance models and analysis to measure the effect of re-financing on equity returns

*Electricity Pricing Analysis in the Context of Renewable Energy*

- Overview of Electricity Prices and Electricity Pricing Designs Around the World
- Relevance of Electricity Pricing to Renewable Analysis
- Short-term and Long-term Marginal Cost for Pricing Analysis

*Pricing Analysis for Biomass Projects*

- Evaluation of Project Finance Model for Biomass
- Capital Cost, Heat Rates and Operating Costs of Biomass Projects
- Examples of Biomass Projects
- Tipping Fees and Construction of Price Curves from Local Supply and Demand Data

*Development Costs in Renewable Projects*

- Development Time Frame and Costs in Wind and Solar Projects
- Exploration Costs and Time Frame in Geothermal Projects
- Fees and Compensation for Development and Treatment of Development Fees when Computing Equity IRR
- Probabilities of Proceeding Beyond Development
- Value and Costs of Development and Research Stage
- Value of Development Expenditures versus Construction Expenditure
- Payment of Development Fees
- Development Costs and Real Option
- Valuation of Development as Real Option
- Compensation for Development Costs

*Modelling Exploration and Development Options – Geothermal Case*

- General Cost and Resource Parameters for Geothermal Projects
- Review of Project Finance Model for Geothermal Project
- Exploration and Development Cost, Probability Time Frame
- Segregating Development Phases
- Discount Rates for Different Stages
- Computation of Expected Value

*Interpretation of Development Value*

- Relative Effect of Development Cash Costs and Construction Costs
- Break-even Development Probability
- Length of Development Period

**Resources Received by Participants**

- Financial Model Library
- Electricity/Commodity Pricing Software (C++)
- Databases on Actual Projects, Commodity Price History and Case Studies
- Manuscript of Trivium Lead Trainer Ed Bodmer’s upcoming book,
*The Valuation Mirage*– addressing many modeling and valuation issues covered in the course

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

**Course Information**

**Overview: **This course is designed for renewable energy finance beginners and professionals alike. With a focus on Solar PV and thermal, we will break down every aspect of a solar project financing while keeping a broader view of how the technology compares to the rest of the renewable energy sources at large. We won’t just teach you about the financing, we’ll teach you how to think about and quantitatively analyze the value drivers of solar projects.

**Course Length: **2**–**3 Days

**Key Objectives:**

- Understand the differences in financing structures for solar PV and solar thermal project relative to other sources of renewable energy (biomass, wind, geothermal, etc.)
- Learn to develop and analyze solar project finance models
- Measure and evaluate changes in project risk over the different stages of development, and how these changes impact equity returns and asset valuation for potential sale

**Solar Specific Operations Topics:**

- Resource availability risk and economics
- Measuring annual and seasonal production from financial analysis perspective
- Degradation
- Alternative financing structures
- Feed-in-Tariffs
- Purchase Power Agreements (PPAs)

- Learn to evaluate trends in renewable energy cost of capital, operating costs, and resource efficiency

**Project Finance Modeling Features:**

- Understanding the implications, effects, and applications of various project finance tools, topics and conventions
- Debt modeling topics: debt sculpting, debt sizing, DSCR, DSRA, tenor sizing, re-financing
- Create flexible scenario and sensitivity analysis to evaluate resource risk
- Resolve all circularity issues while maintain model flexibility with Trivium Circularity Management
- Measure the effect of probabilistic risk assessment on the debt capacity of alternative renewable projects.

**Agenda**

*Day 1: The Grammar*

*Key Topic: Definition of Terms, Renewable Costs, Levelized Cost of Energy*

*Overview of Terminology in Renewable Energy Project Finance through Review of Financial Models*

- Renewable Energy Terms
- Project Finance Terms and Sponsor Objectives
- Economic Issues Associated with Renewable Energy
- General Contract Structure versus Conventional Energy
- Grid Parity
- Carrying Charges
- Feed-in Tariff Policy

*Drivers of Value in Renewable Energy Projects through Review of Financial Model*

- Resource Assessment
- Development Cost and Timing
- Electricity Pricing
- Capital Costs
- Taxes and Financing
- Operating Costs, Maintenance Costs and Availability

*Exercise on Computing Grid Parity and Levelized Cost of Electricity *

- Databases for Electricity Costs
- General Discussion of Cost Trends in Wind, Solar, Natural Gas and Other Technologies
- Review of IEA Cost Analysis for Various Types of Renewable and Non-Renewable Plants Including On-Shore and Off-Shore Wind, Solar PV, Solar Thermal, Geo-thermal, Wave Energy, Hydro and Biomass
- Natural Gas Prices by Region and Over Time
- Grid Parity with Different Capital Cost, Carrying Charge Rates and Natural Gas Prices

*Key Topic: Overview of Project Finance, Cost of Capital, and Pricing Contracts for Renewable Energy*

*Economic and Financial Theory of Project Finance*

- General Discussion of Risks for Renewable Projects
- Capacity Factor Risk (Solar, On-Shore Wind, Off-Shore Wind, Hydro)
- Construction Risk (Hydro and Off-Shore Wind versus Solar)
- Operation and Maintenance Risk
- Contract Risk
- Measurement of Risk using Traditional Finance Theory and WACC
- Review of Solar Manufacturers
- Theory of Cost of Capital, Beta and Equity Risk Premium Applied to Renewable Energy
- Contrast of Solar and Off-shore Wind from Traditional Cost of Capital and Free Cash Flow Perspective
- Debt Capacity and Project Finance Terms
- Debt Service Coverage Ratio Definition and Targets
- Effects of Debt Service Coverage Constraint versus Debt to Capital Constraint
- Debt Tenor, Alternative Repayment Structure, Average Life
- Credit Spreads and Target Credit Ratings in Project Finance
- Debt Service Reserve and Maintenance Reserve
- Covenants, Cash Flow Sweeps and Subordinated Debt

*Financial Statement Analysis in Renewable Energy Project Finance *

- Risks and Stages of Project
- Source and Use of Funds Statement before Commercial Operation
- Focus on Cash Flow and EBITDA
- Interpretation of Cash Flow Statement
- Reserve Accounts for Debt Service and Other Factors
- Computation of Equity Cash Flow and Free Cash Flow and IRR versus Return on Investment

*Project Finance Model and Set-up for Valuation of Renewable Energy*

- Setting-up Project Finance Model for Valuation
- Flexible Dates and Modelling Project Phases
- Assuring the Model is Accurate
- Structuring the Model with Operation and Financing Items
- Transparency in Model
- Project IRR to Screen Projects
- Equity IRR to Structure Projects and Minimum Required Equity IRR for Different Renewable Projects
- Equity IRR Complexities from Re-Financing, Alternative Financing, Tax Structuring, IRR measurement and Development Fees
- Resolving Circular References without Copy and Paste Macros arising from Funding and Sculpting

*Mechanical Issues in Creating and Interpreting Project Finance Models *

- Phases in Project Financing for Renewable Projects
- Sources and Uses Analysis in Models
- Importance of Debt Sizing, Debt Funding and Debt Repayment
- Fixed Asset Analysis
- Profit and Loss Statement and Tax Analysis
- Cash Flow Waterfall & Partnership Flip Structures

*Model Complexities for Renewable Finance Projects – Valuation, Re-financing, Renewable Energy Pricing, Cash Flow Flips*

- Mechanics and Rational for Alternative Incentive Schemes
- Benefits of Investment Schemes Relative to Feed-In Tariffs
- Effects of Incentives on the Overall Cost and Require Feed-in Tariffs of Different Renewable Projects
- Flips and Allocations of Cash Flow to Different Equity Investors
- Interpretation of Risk and Return with Different Cash Flow Allocations

- Project Valuation 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.
- Incorporate Refinancing Assumptions in Financial Models through Adding Sources and Uses of Funds Analysis in Alternative Re-financing Periods and Evaluating Different Possible Features of Re-financing.

**DAY 2: Application**

*Review of Costs, Capacity Factors, and Pricing Structures for Alternative Renewable Technologies and Construction of the Project Finance Model*

- General Discussion of Solar PV and Solar Thermal
- General Discussion of Technology
- Demand and Supply and Changes in German Feed-in Tariffs
- Review of Solar Companies and Financial Issues
- Financial Performance of Manufacturers
- Balance of System Costs and Inverter Costs
- Performance Guarantees and Other Contract Terms
- Financial Analysis of Supplier: First Solar Case Study

*Overview of Resource Assessment in Renewable Projects *

- Resource Assessment of Wind – working with hourly wind speeds, Weibull Distributions and Statistical Analysis (P90, P50 etc.)
- Resource Assessment of Solar – Direct and Diffuse Radiation, Sunlight Angles and RetScreen.)
- Resource Assessment of Geothermal and Hydro
- Probability Distributions of Resources

*Modelling Resource Distributions of Solar Power *

- Data sources for Solar Irradiation by Hour over Multiple Years
- Computation of Production from Efficiency with Adjustments for Performance
- Statistical and Seasonal Distribution of Production Data in Different Locations
- Use of RetScreen for Simple Analysis
- Computation of P95, P90, P75 and P50 Statistics

*Interpretation of Resource Assessment for Risk Analysis *

- Mean Reversion of Resources
- Aspects of Probability Distribution other than Resources
- Use of Probabilities in Financial Analysis
- Contrast Among Different Risks

**Risk Analysis of Renewable Energy**

*General Discussion of Risk in Renewable Energy Projects *

- Discussion of Differences in the Nature of Risks for On-Shore Wind (Wind Resource), Off-Shore Wind (Maintenance and Life Expectation), Solar (Small Risks become Big with High Leverage), Hydro (Capacity Factor and Merchant Price Risk), Wave (Refurbishment Timing), Geothermal (Development Probability).
- Risk Matrix, Risk Classification and Risk Mitigation
- Risk Evaluation Using Break-Even and Sensitivity Analysis
- Risk Evaluation Using Scenario Analysis with Focus on the Manner in which Bankers Apply Downside Analysis
- Measurement of Risk Using Structured Master Scenario Page in Excel Model with Options for Adding Sensitivity Analysis to Defined Scenarios
- Risk Analysis Using Spider and Tornado Diagrams

*Credit Analysis in Renewable Energy Project Finance *

- Background on Probability of Default and Loss Given Default
- Definition and Calculation of DSCR
- Use of DSCR in Base (P50 Cases) and Downside (P90, P95 Cases) in Determining Debt Capacity
- Application of LLCR and PLCR

*Contract Structuring in Renewable Energy Project Finance *

- Importance of EPC Contract in Different Projects (Off-Shore Wind and Hydro)
- Importance of Performance Contracts in Solar Projects for Degradation
- O&M Contracts and Warranties
- Insurance
- Counter-party Risk in Different Projects

**Course Information**

**Course:** Wind Financial Analysis & Production Availability

**Course Length: **2 days

**Overview: **This course is all-encompassing for wind energy project. We’ll walk you through the financing, the risk analysis, the resource availability, and everything else you need to know to successfully finance a wind project in the most efficient manner.

*N.B. *This course is geared towards an international audience and does not include US tax equity financing concepts. If you’re looking for tax equity training, contact us.

**Agenda**

*Day 1: Structuring, Wind Project Finance and Simple Risk Analysis*

*Excel Background and Model Structure *

- Excel techniques, functions, short-cuts and tools in project finance modeling
- Alternative risk measurement and financial modeling
- Spreadsheet layout style and conventions

*Review of Structure of Actual Project Finance Models *

- Discussion of different actual models
- Working with real models to add sensitivity analysis
- Understanding the structure of actual models
- Running pre-built scenarios in actual models
- Interpretation of different ratios in models

*Structuring and Building a Project Finance Model *

- Develop a general model layout
- Construct transparent model titles
- Evaluate titles for model details such as amortization of debt fees, deferred taxes, and minority interest
- Build model equations in segments
- Compute key model outputs
- Computation and basis for LLCR, PLCR and average debt life
- Alternative specifications of the debt service coverage ratio

*Risk Analysis from Alternative Perspectives in Simple Models *

- Economic value drivers in projects
- Sensitivity analysis and flexible graphs
- Break-even analysis with data tables, match and index functions in basic project finance model
- Effective scenario analysis with index and data table
- Extending scenario analysis to compute spider diagrams
- Creating sensitivity analysis from scenario analysis
- Adding Monte Carlo to models

*Day 2: Advanced Risk Analysis, Debt Structuring and Cash Flow Waterfalls, Credit Enhancements*

*Risk Analysis with Existing Models*

- Using graphs to find variables that influence outcomes
- Finding problems in models with sensitivity analysis
- Adding scenario analysis to models without macros or circular reference problems
- Working with models that contain circular references
- Adding scenario analysis to models that include macros

*Effective Presentation of Risk Analysis *

- Use of Spinners, Drop Downs and Buttons without losing base case · Addition of custom case with flexible sensitivities from scenarios
- Creation of sensitivity cases derived from base case, downside case etc. · Presentation of sensitivity cases with graphs and charts

*Debt Structure and Cash Flow Waterfall Exercises*

- Debt sizing and capacity
- Debt structuring using alternative repayment techniques – level and amortizing
- Debt Sculpting using Solver and Macro
- Effects of debt tenor and debt structure on equity IRR and DSCR
- Cash flow sweeps and dividend lock-up
- Debt service reserves and re-financing
- Circularity macros

*Risk Assessment and Valuation of Credit Enhancements *

- Measurement of probability of default and loss given default from financial models
- Theory of risk benefits from sweeps, covenants and DSRA
- Measurement of benefits of covenants, cash flow sweeps and DSRA using break-even analysis
- Measurement of benefits of credit enhancements using scenario analysis
- Calculation of probability of default and loss given default with and without credit enhancements using Monte Carlo simulation
- Addition of Monte Carlo simulation in existing project finance models