Advanced Project Finance Modelling

4 days 11-14 Sep 2017, New York United States $4,845.00 Download brochure Add to basket
4 days 16-19 Oct 2017, London UK £4,295.00 + VAT* Download brochure Add to basket

* Claim back your VAT
Find out more

Request a different date or location for this course (we regularly add courses following requests).

{{alternativeRequestSuccess}}
{{alternativeRequestError}}

* Claim back your VAT
Find out more

Overview

Advanced Project Finance Modelling provides participants with the ability to understand project finance theory through creating and understanding project finance models. Advanced elements of the course focus on sculpting with debt fees, taxes, balloon repayments and DSRA’s as well as resolving circularity using functions rather than copy and paste macros. The general idea is that through building 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 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 different kinds of risk analysis and presentation of summary statistics.  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.

Key benefits:

  • 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 structure
     - Sizing of debt with capitalized interest and alternative drawdown schedule
     - 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 reverses 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

Instructors

We work with a series of expert instructors, please select the course location of interest to review the credentials of who will be delivering the programme.

New York
Ed Bodmer
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.S. 

His consulting clients include investment banks, commercial banks, research institutions and government agencies on a wide variety of complex valuation and advisory matters. He has constructed a unique framework for electricity price forecasting and valuation using production cost modelling techniques combined with option price theory and Monte Carlo simulation.

He is also an adjunct professor at leading University where he teaches courses in microeconomics. Along with his practical experience that covers a multitude of major advisory projects, he has taught specialised courses in financial modelling, electricity pricing, option valuation, mergers and acquisitions and contracting to investment banks, commercial banks, industrial corporations and electric utility companies.

He was formerly Vice President at the First National Bank of Chicago where he directed analysis of energy loans and also created financial modelling techniques used in advisory projects. He has used the models in providing expert testimony on subjects ranging from capital structure to investments in multi-billion dollar nuclear plants to complex valuation of new investments.

He received an MBA degree specialising in econometrics (with honours) from the University of Chicago and a BS degree in finance from the University of Illinois (with highest university honours). He has written many articles and is in the process of completing a textbook on valuation of electricity assets.
London
Ed Bodmer
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.S. 

His consulting clients include investment banks, commercial banks, research institutions and government agencies on a wide variety of complex valuation and advisory matters. He has constructed a unique framework for electricity price forecasting and valuation using production cost modelling techniques combined with option price theory and Monte Carlo simulation.

He is also an adjunct professor at leading University where he teaches courses in microeconomics. Along with his practical experience that covers a multitude of major advisory projects, he has taught specialised courses in financial modelling, electricity pricing, option valuation, mergers and acquisitions and contracting to investment banks, commercial banks, industrial corporations and electric utility companies.

He was formerly Vice President at the First National Bank of Chicago where he directed analysis of energy loans and also created financial modelling techniques used in advisory projects. He has used the models in providing expert testimony on subjects ranging from capital structure to investments in multi-billion dollar nuclear plants to complex valuation of new investments.

He received an MBA degree specialising in econometrics (with honours) from the University of Chicago and a BS degree in finance from the University of Illinois (with highest university honours). He has written many articles and is in the process of completing a textbook on valuation of electricity assets.

Venue

New York

New York Hotel

This program takes place on a non-residential basis at a New York hotel. Non-residential course fees include training facilities, documentation, lunches and refreshments for the duration of the programme. Delegates are responsible for arranging their own accommodation, however, a list of convenient hotels (many at specially negotiated rates) is available upon registration.

As with all programmes on-site administrators are with you throughout the programme to ensure smooth administration and group interaction.

London

Central London Hotel Venue

All courses are held at four or five star venues in Central London, Zone 1. We strive to provide you with a training environment of the highest quality, to ensure that the whole learning experience exceeds your expectations.

Your training venue will be confirmed by one of our course administrators approximately 3-4 weeks before the course start date.

Related Courses

Inhouse


 

Do you have five or more people interested in attending this course? Do you want to tailor it to meet your company's exact requirements? If you'd like to do either of these, we can bring this course to your company's office. You could even save up to 50% on the cost of sending delegates to a public course.

To find out more about running this course in-house:





Our Tailored Learning Offering

If you want to run this course at a location convenient to you or if you want a completely customised learning solution, we can help.

We produce learning solutions that are completely unique to your business. We'll guide you through the whole process, from the initial consultancy to evaluating the success of the full learning experience. Our learning specialists ensure you get the maximum return on your training investment.

inhouse-learn-more

We can offer any of our public courses delivered at your office or we can devise completely tailored solutions:


Read more about our offering or complete a call back request to speak to a learning specialist.

 

Agenda

Agendas are localised, please select your preferred location.

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 of key structuring aspects of project finance models participants begin work on construction of a flexible, structured, accurate and transparent project finance model.  

Flexible Timing in Project Finance Models

  • Timing in the context of Project Finance Theory
  • 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

Incorporating Operating Assumptions in Project Finance Models

  • 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 amortisation
  • Calculation of after-tax project IRR
  • Calculation of project IRR assuming alternative sale dates earlier than the retirement date

Module II Risk Analysis Introduction and Basic Debt Structure


The second module addresses the theory and practice of risk analysis in project finance models. Different risks that are affected by historic record, mean reversion, volatility, resource risk and political risk are discussed. This is followed by addressing appropriate downside cases for credit analysis. Most of the time for this session is spend demonstrating how to construct scenario analysis, sensitivity analysis and Monte Carlo simulation.  

Major Risks of Projects in Financial Models (analysis and mitigation)

  • Commodity price risk versus traffic volume risk
  • Technology risk (breakdown and obsolescence)
  • Evaluation of political risk with models
  • Setting-up models for measuring foreign currency risk
  • Construction cost and delay risk

Assumptions and Mechanics for Basic Debt Schedule -  Five parts of debt – size, funding, repayment, interest, enhancements

  • Set-up of basic assumptions with input debt level
  • Debt schedule mechanics
  • Cash flow waterfall with debt

Set-up of master scenario page 

  • Development of reasonable downside case assumptions
  • Presentation of scenario and custom sensitivity with user-forms
  • Use of macros and data tables

Break-even and sensitivity analysis

  • Importance of break-even analysis in project finance
  • Use of DSCR, LLCR and PLCR for measuring alternative break-even points
  • Alternative sensitivity mechanics
  • Use of macros and data tables

Sensitivity Presentation with Tornado Diagram and Waterfall Charts

  • Adding sensitivity analysis to scenario table
  • Creating cumulative and incremental tables
  • Using scenario reporter

Optional Monte Carlo Session

  • Time series variables in Project Finance
  • Measuring volatility of value drivers
  • Creating distribution analysis with data table or macro
  • Measuring probability of default with Monte Carlo Simulation

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.

Theory of Debt Capacity

  • Philosophy of debt to capital and “skin in the game”
  • Philosophy of debt service coverage and “negotiated forecast”
  • Implications of different debt sizing metrics
  • Interaction of debt sizing, debt repayment, DSRA and other factors

 
Debt Schedule and Debt Capacity Mechanics

  • Set-up of sources and uses to evaluate debt size from target debt to capital ratio
  • Debt capacity from debt service coverage with formulas
  • Debt repayment with equal instalments or mortgage repayments
  • Presentation of cash flow and debt service on summary page

 
Debt Repayment Analysis

  • Alternative debt tenor
  • Mini-perm and re-financing
  • 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 Financial Structuring During Construction and Development Phase

The fourth 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 from summary sources and uses
  • Presentation of sources and uses of funds

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

 
Circularity Macros and Functions for Capitalised 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

Debt Schedule During Construction

  • Interest during construction and interest capitalised 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 V DSRA, MRA and Cash Flow Waterfall


The fifth 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 VI Taxes and Financial Ratios


The sixth 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 VII Re-financing and Valuation Analysis

 
MThe seventh module completes the project finance analysis through inclusion of valuation section that reflects the changing risk of a project over time and the potential upside from re-financing of a project.

Theory of Project Valuation and Re-financing

  • Changes in risk for project finance versus corporate finance
  • Problems with equity IRR as a measure of value
  • Value of project in development phase
  • Project life, debt tenor and terminal value

Re-financing

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

Project Valuation

  • Changes in cost of capital from risk changes
  • Measurement of IRR with alternative holding periods
  • Sizing of re-financed debt

Optional Excel Session


The objective of this session is to assure that all participants, including people who do not routinely work with Excel, become familiar with the tools in Excel and work comfortably on the class exercises.  The optional Excel session will cover short-cut keys, effective presentations, use of forms, one-way and two-way data tables, and look-up functions for scenario analysis.

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 of key structuring aspects of project finance models participants begin work on construction of a flexible, structured, accurate and transparent project finance model.  

Flexible Timing in Project Finance Models

  • Timing in the context of Project Finance Theory
  • 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

Incorporating Operating Assumptions in Project Finance Models

  • 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 amortisation
  • Calculation of after-tax project IRR
  • Calculation of project IRR assuming alternative sale dates earlier than the retirement date

Module II Risk Analysis Introduction and Basic Debt Structure


The second module addresses the theory and practice of risk analysis in project finance models. Different risks that are affected by historic record, mean reversion, volatility, resource risk and political risk are discussed. This is followed by addressing appropriate downside cases for credit analysis. Most of the time for this session is spend demonstrating how to construct scenario analysis, sensitivity analysis and Monte Carlo simulation.  

Major Risks of Projects in Financial Models (analysis and mitigation)

  • Commodity price risk versus traffic volume risk
  • Technology risk (breakdown and obsolescence)
  • Evaluation of political risk with models
  • Setting-up models for measuring foreign currency risk
  • Construction cost and delay risk

Assumptions and Mechanics for Basic Debt Schedule -  Five parts of debt – size, funding, repayment, interest, enhancements

  • Set-up of basic assumptions with input debt level
  • Debt schedule mechanics
  • Cash flow waterfall with debt

Set-up of master scenario page 

  • Development of reasonable downside case assumptions
  • Presentation of scenario and custom sensitivity with user-forms
  • Use of macros and data tables

Break-even and sensitivity analysis

  • Importance of break-even analysis in project finance
  • Use of DSCR, LLCR and PLCR for measuring alternative break-even points
  • Alternative sensitivity mechanics
  • Use of macros and data tables

Sensitivity Presentation with Tornado Diagram and Waterfall Charts

  • Adding sensitivity analysis to scenario table
  • Creating cumulative and incremental tables
  • Using scenario reporter

Optional Monte Carlo Session

  • Time series variables in Project Finance
  • Measuring volatility of value drivers
  • Creating distribution analysis with data table or macro
  • Measuring probability of default with Monte Carlo Simulation

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.

Theory of Debt Capacity

  • Philosophy of debt to capital and “skin in the game”
  • Philosophy of debt service coverage and “negotiated forecast”
  • Implications of different debt sizing metrics
  • Interaction of debt sizing, debt repayment, DSRA and other factors

 
Debt Schedule and Debt Capacity Mechanics

  • Set-up of sources and uses to evaluate debt size from target debt to capital ratio
  • Debt capacity from debt service coverage with formulas
  • Debt repayment with equal instalments or mortgage repayments
  • Presentation of cash flow and debt service on summary page

 
Debt Repayment Analysis

  • Alternative debt tenor
  • Mini-perm and re-financing
  • 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 Financial Structuring During Construction and Development Phase

The fourth 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 from summary sources and uses
  • Presentation of sources and uses of funds

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

 
Circularity Macros and Functions for Capitalised 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

Debt Schedule During Construction

  • Interest during construction and interest capitalised 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 V DSRA, MRA and Cash Flow Waterfall


The fifth 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 VI Taxes and Financial Ratios


The sixth 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 VII Re-financing and Valuation Analysis

 
MThe seventh module completes the project finance analysis through inclusion of valuation section that reflects the changing risk of a project over time and the potential upside from re-financing of a project.

Theory of Project Valuation and Re-financing

  • Changes in risk for project finance versus corporate finance
  • Problems with equity IRR as a measure of value
  • Value of project in development phase
  • Project life, debt tenor and terminal value

Re-financing

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

Project Valuation

  • Changes in cost of capital from risk changes
  • Measurement of IRR with alternative holding periods
  • Sizing of re-financed debt

Optional Excel Session


The objective of this session is to assure that all participants, including people who do not routinely work with Excel, become familiar with the tools in Excel and work comfortably on the class exercises.  The optional Excel session will cover short-cut keys, effective presentations, use of forms, one-way and two-way data tables, and look-up functions for scenario analysis.

Why us


We have a combined experience of over 60 years providing learning solutions to the world’s major organisations and are privileged to have contributed to their success. We view our clients as partners and focus on understanding the needs of each organisation we work with to tailor learning solutions to specific requirements.

We are proud of our record of customer satisfaction. Here is why you should choose us to help you achieve your goals and accelerate your career:

  • Quality – our clients consistently rate our performance ‘excellent’ or ‘outstanding’. Our average overall score awarded to us by our clients is nine out of ten.
  • Track record – we have delivered training solutions for 95% of worlds’ top 100 banks and have trained over 250,000 professionals.
  • Knowledge – our 150 strong team of industry specialist trainers are world leading financial leaders and commentators, ensuring our knowledge base is second to none.
  • Reliability – if we promise it, we deliver it. We have delivered over 20,000 events both in person and online, using simultaneous translation to delegates from over 180 countries.
  • Recognition – we are accredited by the British Accreditation Council and the CPD Certification Service. In an independent review by Feefo we scored 96% on service and 95% on product