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