Comprehensive course analysis
About the course
Focus on the financing of projects, risk analysis and learn much more
Project Finance Modelling is a digital class that will provide you with the ability to understand nuances of project finance theory through creating and understanding project finance models. Through working through the construction of models in a hands-on environment, you will be better able to quantify risks of different types of projects and you will be able to use models to assist in the negotiation of contracts. The course is designed so that you understand the importance of: (1) structuring models with transparent formulas and a sheet structure that makes sense; (2) debt sizing using alternative methods including sculpting; (3) developing the funding parts of the model before commercial operation and problems with circular references that arise from interest during construction fees and the debt service reserve account; (4) building flexible models with effective summary statistics. The course will show you how to construct that models are accurate with effective error checks that verify the model; so that your models can incorporate complex cash flow waterfalls, alternative funding cascades and sculpted repayment techniques; and so that your models will be transparent and clear to understand by users. 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.
- Understand project finance models in the context of finance theory related to subtle issues associated with contract structuring, debt sizing, debt funding, debt repayment, debt servicing costs and credit enhancements.
- Interpret models developed by other people and add master scenario pages to any model using case studies of actual models.
- 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
- 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
- Learn Excel techniques with some VBA to make better presentations from models and to make models more transparent and efficient
Module 1: Model Structure and Alternative Excel Techniques
The class begins with introductory comments about the importance of sensible structuring of the model and individual sheets in your model. Different model and sheet structures are demonstrated for alternative models demonstrating poor and good practices. In this initial session, inputs will be provided for a model and participants will work through selected equations to derive pre-tax operating income and capital expenditures. You will also establish a master timeline and structure equations using the timeline.
Review of Model Structure
Alternative Model Structures in Actual Models * Fundamental difference between project finance and corporate finance * Alternative model structures – time or cash flow * Different structures for calculation sheets * Types of Assumptions set-ups * Incorporation of scenarios * Summary presentations * Modelling as incorporating EPC, O&M, Loan Agreement and other contracts in the context of project operations
Structure of Model, Assumptions and Sheets in Exercise File * Illustration of style * Use of partner macros for formatting * Use of partner macros for copying to the right * Rational for assumptions set-up * Model structure set-up with sculpting * Sheet set-up for transparency * Table of contents
Flexible Timing in Project Finance Models
Review of Timing Flags in Different Models * No master timeline * Separation of calculations pre-COD and post-COD * Pages with timing flags * References to timing flags * Formulas for timing flags
Construction of master timeline * Importance of master timeline * Construction of timeline * Formatting of timeline * Potential for operations with different timing from financing
Modelling Operations, Capital Expenditures, Revenues and Expenses
Alternative Operating Calculations * Computation of capacity and energy * Modelling monthly variation in semi-annual or quarterly model * Examples of presentation of battery use * Challenges with scheduled outages * Summary for revenues and expenses
Capital Expenditures (EPC Contract) * Benchmarking and presentation of total cost * Development costs and construction costs * Separate spreadsheet page for different capital expenditure components * Modelling and presentation of S-curve * Incorporation of capital expenditures in exercise * Introduction to short-cuts and tools
Operating Cost (O&M Contract) * Inflation and macro-economic assumptions discussion * Incorporation of macro-economic assumptions in model * 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
Revenue, Cash Flow and Project IRR (Revenue Contract, Commodity Prices) * Alternative inflation rates and macro-economic scenarios * Computing bid prices using NPV and IRR * Using flags for expiration of contracts * Incorporating alternative scenarios efficiently * Computing pre-tax IRR and bid price
Module 2: Debt Sculpting and Sizing from Cash Flow and Introduction to Circular References
The second module of the class addresses debt sizing and sculpting using cash flow. Reasons for addressing the debt sizing before discussing model funding, taxes and other issues is to introduce the fundamental formulas for sculpting and provide a basis for more complex cases that will be addressed in subsequent sections. In the sculpting section you will be introduced to the five aspects of debt that need to be modelled including debt sizing, debt funding, debt repayment, interest and fees and debt protections.
Review of Debt Inputs and Term Sheet
Five Key Provisions of Loan Contracts * Debt Size from maximum debt to capital or minimum DSCR * Debt Funding with pro-rata, up-front contributions and EBL * Debt Repayment: Tenor and Type * Interest Rates and Fees * DSRA, Covenants and Sweeps
Assumptions Set-up and Term Sheet * Debt sizing and two constraints * Debt funding and use of up-front equity percent * Debt tenure, sculpting and mini-perm * Swap rates, step-up credit spreads and fees * DSRA with letter of credit or cash funded * Cash sweeps and covenants
Debt Size Equations from Sculpting
Debt Size from Cash Flow * Inputs for maximum debt to capital and minimum DSCR * Formula for size of debt from NPV of cash flow * Setting-up debt repayment flag * CFADS without taxes over repayment period * Using SUMPRODUCT for varying interest rates
Debt balance after COD for verification * Setting-up balances * Flag for period before COD * Using Flag for funding at COD * Repayment from sculpting formula * Verification of debt closing balance
Introduction to Funding and Circular References
Computing Debt Size * Sources and Uses Example * Problems with Use of Iteration Button * Demonstration of data table and goal seek * Copy and Paste Method and Problems * Creating a UDF and Parallel Model with a Program
Module 3: Funding Analysis Before COD and Debt Accounts
The third module of the digital class addresses debt and equity funding during the pre-COD period and the resulting circular references. A summary sources and uses of funds is introduced as a technique to compute the debt size, evaluate circular references and evaluate the pro-rata versus up front funding. The period by period funding is then developed with flexible approaches.
Summary Sources and Uses
Summary Sources and Uses Statement * Reasons for Summary Sources and Uses * Incorporation of Development Fees and Other Adjustments * Computing Debt with Different Debt Sizing Methods * Calculation of Pro-Rata Percentages * Interest Rates and Fees * DSRA, Covenants and Sweeps
Computing Pro-Rata Ratios * Computing the Amount of Up-Front Equity from Total Equity * Remaining Equity for Pro-Rata Calculation * Addition of Debt to Pro-Rata Calculation * Equity Pro-Rata and Debt Pro-Rata Percentages
Funding Needs and Funding Sources
Computing Periodic Funding Needs and Sources * Setting-up Funding Needs without Circular Reference Problems * Computing Up-Front Equity from Commitment in Summary Sheet * Evaluating the Remaining Equity to Fund * Computing Equity Funding with MIN Function * Using Pro-Rata Percentages and Debt
Setting-up and Equity Balances * Equity Balance with Up-Front Equity * Debt Balance with Interest and Fees * Allocation of Interest to Interest During Construction * Up-Front Fees and Commitment Fees * Debt Repayments with Alternative Sculpting Assumptions
Resolution of Circular Reference with Copy and Paste and UDF
Structuring Circular Reference from Funding * Causes of Circular Reference * Presenting Total and Difference in the Summary Page * Writing a Macro for Setting Fixed Value to Equal Computed Value * Effects of Copy and Paste on Model * Using the Template UDF to resolve circular references
Module 4: Cash Flow Waterfall Post COD, Income Taxes and Credit Protections
The fourth module of the class addresses cash flows after the COD period, income taxes and the equity cash flow. In the module you will work through alternative debt repayment methods including using fixed sculpting repayments for risk analysis. Challenges arising from the DSRA account and income taxes are also introduced. In the sculpting section you will be introduced to the five aspects of debt that need to be modelled including debt sizing, debt funding, debt repayment, interest and fees and debt protections.
Modelling the Cash Flow Waterfall, Taxes and Alternative Debt Repayments
Cash Flow Waterfall and Taxes * Fundamental Cash Flow Waterfall Layout * Depreciation on Base Assets for Income Taxes * Depreciation on Financing Items * Computation of CFADS * Debt Service in Cash Flow Waterfall
Modelling Cash Flow Waterfall with MIN and Max * Application of MIN and MAX to Compute * Debt funding and use of up-front equity percent * EBL with switch * Debt tenure, sculpting and mini-perm * Swap rates, step-up credit spreads and fees * DSRA with letter of credit or cash funded * Cash sweeps and covenants
DSRA and Balance Sheet
Modelling the DSRA * Setting-up DSRA Requirements after Debt Balance * Splitting-up the DSRA flow pre-COD and Post-COD * Pre-COD flows in the Cash Flow and Post-COD flows in the Cash Waterfall * Option for DSRA with a Letter of Credit * Circular Reference Problems from the DSRA
Balance Sheet Modelling * Setting-up the Balance Sheet * Use of Balance Sheet in Project Finance Modelling * Filling in the Balance Sheet with Closing Balance Accounts * Setting up Equity Account from Dividends and Income * Balance Sheet Tests in Model
Alternative Debt Repayment Modelling and Risk Analysis versus Structuring
Modelling Different Options for Debt Repayment * Setting up Debt Repayment Options * Fixed Repayment and Fixed Debt Service * Fixing the Sculpting from Base Case Structuring * Making the Fixed Payments Structured to Compute Delays * Risk Analysis with Fixed Payments
Modelling Defaults and Repayments * Reasons for Modelling Defaults and Repayments * Setting-up Default Balance * Including Defaults in Waterfall * Including Repayments in Waterfall * Interest on Defaulted Debt in Cash Flow
Module 5: Financial Ratios, Scenario Analysis, Model Presentation and Advanced Issues
The fifth model addresses alternative methods to present financial ratios and risk analysis using scenario analysis. The sensitivity and scenario analysis is taught using unique methods whereby you can create a project finance structure diagram and illustrate different risks. The advanced project finance modelling issues include sculpting with multiple debt issues, using an equity bridge loan, sculpting with curved DSCR’s and re-financing.
Computing and Presenting Financial Ratios
Different IRR Statistics * After-tax Project IRR and Equity IRR * Debt IRR pre-tax and after-tax * DSCR, LLCR and PLCR * Understanding and Interpreting LLCR and PLCR * Average Debt Life
Presentation of Outputs and Scenario Analysis * Graph of CFADS and Debt Service * Sensitivity on Variables with Re-set Button * Setting-up Scenario Analysis with Scalar Variables * Setting-up Scenario Analysis with Time Series Variables * Combining Scenario Analysis with Sensitivity Analysis * Presentation of Results in a Diagram
Advanced Project Finance Modelling Issues Complex Sculpting Issues * Sculpting with Multiple Debt Issues * Sculpting with Balloon Payment * Sculpting and Mini-Perm * Sculpting with Maximum Debt to Capital Constraint and Minimum DSCR
Other Complex Project Issues * Equity Bridge Loan * Project Cost Adjustments for Capitalised Interest on Selected Issues * DSRA cash flow Adjustments to DSCR * Complex Working Capital Issues * LLCR Calculations with Multiple Debt Issues * Flexible Re-Financing and Income Taxes
Ed has created innovative forward pricing, productivity measurement and investment valuation software for consulting clients throughout the United States. He has taught energy economics and finance throughout the world, and formulated significant government policy and corporate strategy in the U....
Videos and materials
Because of COVID-19, many providers are cancelling or postponing in-person programs or providing online participation options.
We are happy to help you find a suitable online alternative.