Advanced Project Finance Analysis in Excel


The Project Finance Modelling in Excel training course provides participants with the ability to create and understand 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. The course is designed so that participants can build and interpret flexible models with effective summary statistics; so that models will be accurate with effective error checks that verify the model; so the attendees understand and program structured models that incorporate complex cash flow waterfalls, alternative funding cascades and sculpted repayment techniques; and so that models will be transparent and clear to understand by users. The course includes effective development of scenario analysis through use of data tables or VBA where participants learn how to create a master scenario page with time series and fixed variables. Additionally, attendees learn how to use advanced techniques to resolve circular references associated with funding of a project and debt sculpting that use VBA functions rather than macros.


The course is recommended for finance professionals, bankers, credit managers, project finance managers, financial analysts, project managers, corporate banking officers, business development managers, engineers, legal advisors, financial consultants, business analysts, investment managers and portfolio managers from both the private and public sectors. All professionals interested in creating models or simply understanding how to interpret models created by others can benefit in addition to understanding the evaluation of projects.

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 IndustriesM
  • 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 Corpo rate 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