Project Finance Modelling

3 days 16-18 Sep 2015, Amsterdam 4,485.00 + VAT* Download brochure Add to basket
3 days 14-16 Oct 2015, New York $5,190.00 Download brochure Add to basket
4 days 19-22 Oct 2015, Johannesburg £3,345.00 Download brochure Add to basket
4 days 2-5 Nov 2015, London £4,295.00 + VAT* Download brochure Add to basket
2 days 22-24 Nov 2015, Dubai £3,545.00 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).

{{requestSuccess}}
{{requestError}}

* Claim back your VAT
Find out more

Overview

The course addresses a wide variety of programming, financial, statistical and economic issues. The programming issues include designing macros relevant for project finance models, auditing financial models, resolving circularity, modeling of debt service reserves, cash sweeps and other enhancements and organizing project finance models for effective presentation to investors. Financial issues include the theory of debt capacity from a project finance perspective, use of option pricing concepts in project finance, equity IRR requirements and cost of capital, probability of default and loss given default.

A 3-day course focused on providing delegates with the skills and techniques to develop a project finance model:

  • Understand the fundamental structure of a project finance model and learn excel techniques that create efficient, robust and stable project finance models
  • Incorporate simple applications involving debt capacity, contract pricing, debt structuring, break-even analysis and probability of default
  • Apply a project finance model in an energy or infrastructure case study to consider the appropriate debt service reserve levels, covenants, liquidated damage provisions, debt amortization schedules and contract prices
  • Understand how break-even analysis, scenario analysis, tornado diagrams, time series equations and Monte Carlo simulations can be used to analyze risk with project finance models
  • Address detailed and complex issues associated with debt, risk analysis, construction delays, reserve accounts, income taxes and other issues
  • Incorporate the debt aspects of project finance models including construction of a cash flow waterfall sculpting repayments, accounting for financing fees and evaluation of debt capacity
  • Measure the risk and return tradeoffs and the effectiveness of covenants, cash flow traps, senior and subordinated debt issues, finance models
  • Address how time series models can attach mathematical concepts to evaluation of risk
  • Apply Monte Carlo simulations to measure the probability of default and the loss given default in the model using alternative financial structures


Supported by:


Who should attend

 

  • Project Finance, Structured Finance & Corporate Finance Professionals
  • Analysts
  • Investment Officers
  • Risk Managers
  • Financial Consultants

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.

Dubai

The course instructor has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney Training since 2006.

He trained as a Chartered Accountant at KPMG in South Africa and New Zealand, before moving into industry with Ford Motor Company. He held various positions there in financial analysis, budgeting and forecasting, until he was appointed Sales Planning Manager, responsible for forecasting models, production planning and supply logistics. He joined a multinational private consultancy group in Australia, as their General Manager Finance; in this role, he guided the group through a period of major change and financial turnaround.

For the past 15 years, he has worked as a freelance financial modeller, trainer and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multi-billion pound franchise bids in the UK rail industry, forecasting models for private equity investment in the waste management sector, and a number of PFI transactions in the utilities, health and support services sectors.

With an extensive accounting background, the instructor brings accounting knowledge and analytical skills to transactions and financial modelling.

He has built up a great deal of experience in financial modelling in different sectors, including property development, insurance market, outsourcing and utilities in the transport, gas, electricity and water sectors, as well as building financial models in central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

Clients for whom the instructor has delivered training on behalf of Euromoney Training include Marfin Popular Bank (Cyprus), Finansbank and AK Bank (Turkey), Sace SPA (Italy), BayernLB Bank (Germany) and Access Bank (Nigeria). He also delivers training courses for Euromoney Training’s sister firm, DC Gardner Training, in many parts of the world.

Johannesburg

The course instructor has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney Training since 2006.

He trained as a Chartered Accountant at KPMG in South Africa and New Zealand, before moving into industry with Ford Motor Company. He held various positions there in financial analysis, budgeting and forecasting, until he was appointed Sales Planning Manager, responsible for forecasting models, production planning and supply logistics. He joined a multinational private consultancy group in Australia, as their General Manager Finance; in this role, he guided the group through a period of major change and financial turnaround.

For the past 15 years, he has worked as a freelance financial modeller, trainer and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multi-billion pound franchise bids in the UK rail industry, forecasting models for private equity investment in the waste management sector, and a number of PFI transactions in the utilities, health and support services sectors.

With an extensive accounting background, the instructor brings accounting knowledge and analytical skills to transactions and financial modelling.

He has built up a great deal of experience in financial modelling in different sectors, including property development, insurance market, outsourcing and utilities in the transport, gas, electricity and water sectors, as well as building financial models in central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

Clients for whom the instructor has delivered training on behalf of Euromoney Training include Marfin Popular Bank (Cyprus), Finansbank and AK Bank (Turkey), Sace SPA (Italy), BayernLB Bank (Germany) and Access Bank (Nigeria). He also delivers training courses for Euromoney Training’s sister firm, DC Gardner Training, in many parts of the world.

New York
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.
Amsterdam

The course instructor has over 20 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of Euromoney Training since 2006.

He trained as a Chartered Accountant at KPMG in South Africa and New Zealand, before moving into industry with Ford Motor Company. He held various positions there in financial analysis, budgeting and forecasting, until he was appointed Sales Planning Manager, responsible for forecasting models, production planning and supply logistics. He joined a multinational private consultancy group in Australia, as their General Manager Finance; in this role, he guided the group through a period of major change and financial turnaround.

For the past 15 years, he has worked as a freelance financial modeller, trainer and analyst for a range of blue-chip clients. Assignments have included structured financing for a large-scale property development, multi-billion pound franchise bids in the UK rail industry, forecasting models for private equity investment in the waste management sector, and a number of PFI transactions in the utilities, health and support services sectors.

With an extensive accounting background, the instructor brings accounting knowledge and analytical skills to transactions and financial modelling.

He has built up a great deal of experience in financial modelling in different sectors, including property development, insurance market, outsourcing and utilities in the transport, gas, electricity and water sectors, as well as building financial models in central government departments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

Clients for whom the instructor has delivered training on behalf of Euromoney Training include Marfin Popular Bank (Cyprus), Finansbank and AK Bank (Turkey), Sace SPA (Italy), BayernLB Bank (Germany) and Access Bank (Nigeria). He also delivers training courses for Euromoney Training’s sister firm, DC Gardner Training, in many parts of the world.

London
The course instructor is an expert in Project Finance Modelling.

He is an experienced financial modeller and highly software literate. Following an initial career in aerospace market forecasting he became a business analyst at Rolls-Royce Power Ventures (RRPV) responsible for producing financial models and investment analysis for project finance and equity funded power projects. 

He has developed financial models for: * Gas fired power stations throughout the world, * Several wind power projects in the UK, * A coal fired power station in Africa * A number of UK PFI health projects * Electricity transmission networks in Eastern Europe * A desalination project in the Middle East * Combined water and power projects in the Middle East. * Valuation of a portfolio of renewable generation in Greece.

He currently works for Project Financing Solutions, a boutique project finance advisory firm that has advised three of the ten European Greenfield IPPs that have closed in the last four years.

Venue

Amsterdam

Amsterdam Hotel

This programme takes place on a non-residential basis at a central Amsterdam 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.

 


 

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.

Johannesburg

Johannesburg Hotel

This programme takes place on a non-residential basis at a central Johannesburg 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.

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.

Dubai

Dubai Finance

This programme takes place on a non-residential basis at a central 4 to 5* Dubai 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.

Related Courses

Agenda

Agendas are localised, please select your preferred location.

Day 1

Introduction and course objectives

  • Brief overview of project finance
  • Review of models and their objectives
  • Introduction of a simple model and its components

Overall model structure

  • Best financial modelling practice
  • Overall structure of the model
  • Separation of inputs, calculations and outputs
  • Logic flow within the model
  • Use of switches to allow option selection
  • Use of flags to control timing factors
  • Set-up to ease flexibility
  • Accommodating multiple options at early stages of project
  • Checks and totals, and error reporting

Inputs and assumptions

  • Principles of model structure
  • Building assumptions off the term sheets
  • Using the assumptions sheets as a sign-off document
  • Building-in ability to change and work changes through the model
  • Restricting ranges of inputs and validation criteria
  • Version control
  • Tracking changes


Exercise: Creating an assumptions input sheet with built-in flexibility

Revenue and cost build ups

  • Build-up of construction or other capital costs
  • Correct matching of units
  • Treatment of fixed and variable costs
  • Use of Maintenance Reserve accounts
  • Pricing assumptions
  • Use of lookup functions to change expenditure timings
  • Building in sensitivities

Taxes

  • Tax treatment of costs
  • Allowing for deductibility and non-deductibility
  • Capital allowances
  • Cash versus accounting treatment

Interest and fee calculations

  • Circularity and consequences
  • Calculations of interest and fees
  • Timing of payments
  • Cash flow payment vs. amortization in the
  • P&L
  • Capitalised fees and interest
  • Exercise – from given term sheet of interest
  • rates and fees, model interest and fee cash flow and P&L effects


Day 2

Financing section

  • Leverage, risk and the debt/equity equation
  • Calculating the cost of different types of debt capital
  • Cost of equity capital
  • Use of Debt Service Reserve Accounts (DSRA)
  • Use of the cash flow waterfall
  • Modelling issues arising:
  • Timing of debt and equity funding
  • Fee costs, upfront and spread
  • Interest costs, capitalised interest
  • Interest rate ratchets
  • Debt repayment profiles
  • Rate switches or refinancings at various stages of deal
  • DSRA interest margin
  • Debt repayment profiles and built-in options
  • Dividend and other equity returns
  • Constraints on dividend payments
  • Overall risk profile

Exercise: Creation of simple model to reflect debt costs, DSRA, repayment profiles, and returns to equity under constraints.

Modelling mulitple drawdowns

  • Cash flow driven
  • Cash positive periods and interest earned
  • Debt service reserve accounts
  • Fees to be included in drawdown amounts
  • Multiple facilities


Exercise: Given a pattern of cash flows,
calculate facility drawdowns and related interest cost and earned

Multi-currency modelling

  • Modelling foreign exchange rates
  • Dealing with changes in exchange rates
  • Comparing actual with forecast

Exercise: For a given pattern of cash flows in various currencies, model the amounts to be drawn in each currency


Separate exercise: Compare modelled rates and actual and separate effect of exchange rate changes from other variances

Inflation / escalation factors

  • Use of indices
  • Controlling start time of inflationary pattern
  • Applying multiple rates to different cost & revenue items
  • Varying inflation rates over life of the project
  • Comparing the effect of actual inflation vs. modelled

Exercise: Model multiple, variable rates and analyse a separate set of actual rates


Day 3

Creation of balance sheet

  • Link between modelled cash flow and P&L
  • Key balance sheet items and their calculation
  • Non-cash items: depreciation, deferred tax
  • Assumptions required to be made
  • Use of existing figures or opening balance sheets
  • Creation of check totals

Exercise: From a given P&L and cash flow statement, calculate balance sheet

Derivation of ratios

  • Cash available for distribution and free cash flow
  • Debt service coverage reserve ratios
  • Interest cover ratios
  • Equity returns
  • IRR & NPV calculations
  • Components of the weighted average cost of capital ("WACC")

Exercise: From a given cash flow and balance sheet, calculate the above ratios

Comparing actuals to previously modelled results

  • Separate runs and variation of inputs
  • Ability to compare results
  • Reviewing future implications of variances


Exercise: From a modelled forecast and actual results, calculate variances and project future model changes resulting

Sensitivity analysis

  • Break-even calculations
  • Stress-testing of model
  • Varying inputs to assess effect on results
  • Use of goal seek
  • Use of statistical techniques – probabilities and Monte Carlo simulations
  • Version control to allow comparison of outputs
  • Comparison of actual results against forecast as a sensitivity analysis

Exercise: From a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even.

Risk reviews

  • Use of risk matrices
  • Relationship to model and sensitivity analysis
  • Probability analysis
  • Risk-adjusted returns – equity view
  • Risk-adjusted returns – lender’s view


Exercise: For a given model, calculate risk-adjusted returns from potential risks in the project


Exercise: Perform a risk assessment applicable to participants’ own organizations, and model probability-weighted outcomes.

Documenting the model

  • Setting up base case model
  • Recording changes to model structure
  • Recording changes to assumptions
  • User guides
  • Running scenarios: descriptions, comparisons to base, version control

Wrap up

  • Overall review
  • Key points to re-iterate
  • Brief introduction to further exercises
  • Final questions and issues to discuss

Course summary and close

DAY 1

Introduction and Model Structure

Risk Measurement and Financial Modeling

  • Enter two investments and compare project finance to corporate finance – evaluate two different renewable projects
  • Introduction to short-cut keys used by investment banks, effective use of data tables and use of forms in excel
  • Computation of DSCR and IRR on investment versus NPV rules for corporate finance
  • Use of switches (TRUE/FALSE) for model flexibility
  • Spreadsheet layout style and conventions including techniques to color input cells, limit the size of spreadsheets through grouping cells

Structuring and Building a Basic Project Finance Model

  • Develop a general model layout
  • Inputs for project phases, operational assumptions and financing assumptions
  • Fundamental structure of any project finance model – sources and uses, debt schedule, asset schedule, profit and loss for taxes, cash flow waterfall, balance sheet, summary page

Details of Model Structure

  • Period code for pre-construction and construction
  • Switches for construction and operational phases
  • Working analysis for computation of construction expenditures, revenues and expenses
  • Sources and uses details including multiple draw-downs of different types of debt facilities, interest during construction and equity funding
  • Debt schedule with alternative repayment options including level repayment, annuity repayment and sculpted repayment
  • Asset schedule with interest during construction and depreciation expense
  • Profit and loss schedule to compute income tax
  • Cash flow statement with basic waterfall
  • Equity cash flow and equity IRR
  • Cash flow available for debt service and DSCR
  • Balance sheet and model verification including set of tests for debt repayment, asset balance and balance sheet

Presentation of Model and Risk Analysis

  • Sensitivity analysis for delay, capital expenditure sensitivity and revenue sensitivity with alternative debt levels
  • Use of techniques to effectively make graphical presentations – presentation of cash flow and debt service to discuss notions of debt service buffer and tail
  • Use of MATCH and INDEX function to compute break-even points

Debt Structure and Cash Flow Waterfall Exercises

Debt Sizing and Capacity

  • Use of macro with VBA to effectively compute debt size given DSCR criteria

Debt Structuring using Alternative Repayment Techniques

  • Illustration of the importance of debt structure
  • Use of INDEX function to allow alternative debt repayment scenarios
  • Scenario analysis for allowing level or amortizing debt
  • Use of PPMT function and macro to compute amortizing debt

Debt Sculpting using Solver and Macro

  • Discussion of sculpting
  • Computation of sculpted debt repayment using SOLVER
  • Computation of sculpted debt repayment using formula and Macro
  • Sensitivity analysis with different debt repayment
  • Effects of debt tenor and debt structure on equity IRR and DSCR
  • Use of VBA versus data tables for scenario analysis

DAY 2

Model Structuring Issues – Detailed Model

Monthly Construction on a Monthly Basis with Delay and Liquidated Damage 

  • Set-up of detailed model with specific dates for financial close, commercial operation, and project retirement
  • Set-up of periods in model (monthly during construction and semi-annual after construction)
  • Set-up of detailed operating assumptions with varying inflation rates and growth rates
  • Set-up of financial assumptions including
  • Multiple draw-downs in different currencies
  • Use of debt commitment rather than debt leverage assumptions
  • Inclusion of up-front fees and commitment fees
  • Alternative re-payment options
  • Dividend lock-up covenants
  • Cash flow sweeps
  • Debt service reserve accounts
  • Varying credit spreads (with DSCR or life of project)

Computation of Periods and Dates

  • Computation of months in period from the construction and operation switches
  • Use of period codes from construction start date, completion date and retirement date
  • Use of EDATE and EOMONTH functions to model start and end dates for each period
  • Conversion of periodic model to annual model using SUMIF function

Computation of Working Analysis

  • Development of growth rates using daily compounding with different periodic assumptions (cannot use simple growth indices)
  • Use of MATCH and INDEX for modeling varying inflation rates

Detailed Construction Period Modeling

  • Computation of interest during construction with and without capitalization of interest
  • Modeling debt-draws using debt commitment and drawdown schedule
  • Alternative modeling of S-curve during construction
  • Modeling of uncommitted debt and commitment fees
  • Modeling of construction and draw-downs in alternative currencies
  • Inclusion of debt service reserves in uses of funds

Detailed Debt Schedule Modeling

  • Computation of commitment as well as debt balance
  • Adding total debt borrowed line
  • Provision for cash follow sweeps
  • Calculation of interest with different periodic assumptions (monthly, semi-annually) using DAYS360 function
  • Use of Debt repayment switch
  • Computation of debt outstanding after scheduled tenor using SUMPRODUCT with period test
  • Incorporation of varying credit spreads

Reserve Section

  • Computation of required debt service balance with expirations
  • Computation of needed to-ups through comparing opening balance with required balance
  • Addition of provisions for reducing account through with-drawls during cash flow deficits
  • Setting up the cash lock-up account
  • Inflows into cash lock-up account for covenant violations
  • Outflows from cash lock-up account when covenants met
  • Operating reserve account
  • Difficulty of computing operating reserve account with inflation and periodic outflows

Detailed Asset Modeling

  • Basic asset balance schedules
  • Use of tax versus book depreciation
  • Computation of fee balance in similar manner to asset balances
  • Separation into different asset categories

Detailed Profit and Loss Statement

  • Inclusion of fee amortization and interest income provisions
  • Computation of net operating loss for tax calculations

Detailed Cash Flow Statement with Cash Flow Waterfall

  • Operating cash flows from EBITDA and taxes paid
  • Inclusion of construction period cash flows in cash flow statement
  • Begin cash flow for waterfall with subtotal
  • Remove cash flow put into operating reserve account
  • Computation of defaults and required use of debt service reserve account using MAX and MIN functions
  • Computation of cash available for cash sweep and cash sweeps using sub-totals and MIN function
  • Computation of covenants using switch to determine whether covenant is violated
  • Calculation of cash flow sweep for subordinated debt

Computation and Basis for LLCR, PLCR and Average Debt Life

  • Reason for LLCR with cash flow sweeps
  • Problems with LLCR for use in covenants
  • Computation of LLCR and PLCR using SHIFT, CNTL, ENTER
  • Calculation of Average Loan Life with Periodic Model

Alternative Specifications of the Debt Service Coverage Ratio and IRR’s

  • Use of XIRR and MIRR
  • Alternative specifications of DSCR
  • Senior and subordinated DSCR

DAY 3

Risk Analysis from Alternative Perspectives

Economic Value Drivers in Projects

  • Calculation of volatility and construction of downside cases
  • Development of scenario analysis for key variables
  • Use of downside case to define debt capacity

Sensitivity Analysis and Flexible Graphs

  • Use of OFFSET function to create flexible range names
  • Incorporation of flexible range names in graphs
  • Extending scenario analysis to compute spider diagrams
  • Creating tornado diagrams

Re-Financing

  • Economics of Re-financing
  • Discussion of the importance of re-financing for some projects
  • Option aspects of re-financing
  • Softness of equity IRR with re-financing

Structuring a Model for Re-Financing

  • Creation of basic model to demonstrate re-financing issues
  • Inputs for re-financing -- date of re-financing, DSCR to size re-financing, term of re-financing, interest rate on re-financing, fees for re-financing
  • Switches for re-financing – pre-re-financing and post-re-financing
  • Sources and uses for re-financing
  • Retirement of existing debt and breakage fees
  • Incorporation of interest and repayments of re-financed debt in P&L and cash flow
  • Incorporation of proceeds from new debt and dividends from re-financing
  • Sensitivity analysis for finding optimal re-financing

Credit Spreads and Monte Carlo Simulation

  • Theory of credit spreads and probability of default
  • Simple analysis of minimum credit spread using Monte Carlo simulation
  • Computation of simple Monte Carlo without macros
  • Use of simple macro for Monte Carlo
  • Incorporation of Monte Carlo into other spreadsheets
  • Addition of inputs for mean reversion and boundary conditions
  • Problems with simulation

Day 1

Introduction & course objectives

  • Revision of best practice in model structures
  • Best financial modelling practice
  • Overall structure of the model
  • Separation of inputs, calculations and outputs
  • Logic flow within the model
  • Use of switches to allow option selection
  • Use of flags to control timing factors
  • Set-up to ease flexibility
  • Accommodating multiple options at early stages of project
  • Use of corkscrews
  • Checks and totals, and error reporting

Inputs & assumptions

  • Building assumptions off the term sheets
  • Using the assumptions sheets as a sign-off document
  • Building-in ability to change and work changes through the model
  • Restricting ranges of inputs and validation criteria
  • Version control
  • Tracking changes

Exercise – creating an assumptions input sheet with built-in flexibility

Revenue & cost build-ups

  • Build-up of construction or other capital costs
  • Correct matching of units
  • Treatment of fixed and variable costs
  • Use of Debt Service Reserve Accounts and Maintenance

Reserve Accounts

  • Pricing assumptions
  • Use of lookup functions to change expenditure timings
  • Building in sensitivities

Exercise – building in flexibility for capital spend timing changes and sensitivities

Brief overview of modelling taxes

  • Tax treatment of costs
  • Allowing for deductibility and non-deductibility
  • Capital allowances
  • Cash versus accounting treatment
  • Example - Review of an example of tax modelling for an investment project

Interest and fee calculations

  • Circularity and consequences
  • Calculations of interest and fees
  • Timing of payments
  • Cash flow payment vs amortisation in the P&L
  • Capitalised fees and interest

Exercise – from a given term sheet of interest rates and fees, model interest and fee cash flow and P&L effects

Building the cashflow financing section of a model

  • Cash flow driven
  • Cash positive periods and interest earned
  • Debt service reserve accounts
  • Fees to be included in drawdown amounts
  • Use of multiple facilities for different purposes


Day 2

Brief overview of modelling taxes

  • Tax treatment of costs
  • Allowing for deductibility and non-deductibility
  • Capital allowances
  • Cash versus accounting treatment

Example - Review of an example of tax modelling for an investment project

Interest and fee calculations

  • Circularity and consequences
  • Calculations of interest and fees
  • Timing of payments
  • Cash flow payment vs amortisation in the P&L
  • Capitalised fees and interest

Exercise – from a given term sheet of interest rates and fees, model interest and fee cash flow and P&L effects

Building the cashflow financing section of a model

  • Cash flow driven
  • Cash positive periods and interest earned
  • Debt service reserve accounts
  • Fees to be included in drawdown amounts
  • Use of multiple facilities for different purposes

Financing section

  • Leverage, risk and the debt/equity equation
  • Calculating the cost of different types of debt capital
  • Cost of equity capital
  • Use of Debt Service Reserve Accounts (DSRA)
  • Use of the cash flow waterfall
  • Modelling issues arising:
  • Timing of debt and equity funding
  • Fee costs, upfront and spread
  • Interest costs, capitalised interest
  • Interest rate ratchets
  • Debt repayment profiles
  • Rate switches or refinancings at various stages of deal
  • DSRA interest margin
  • Debt repayment profiles and built-in options
  • Dividend and other equity returns
  • Constraints on dividend payments
  • Overall risk profile

Exercise: creation of simple model to reflect debt costs, DSRA, repayment profiles, and returns to equity under constraints

Inflation / escalation factors

  • Use of indices
  • Controlling start time of inflationary pattern
  • Applying multiple rates to different cost & revenue items
  • Varying inflation rates over life of the project
  • Comparing the effect of actual inflation vs modeled
  • Introduce exercise to do outside class – model multiple, variable
  • rates and analyse a separate set of actual rates
  • Exercise: from a given P&L

Exercise: from a given P&L


Day 3

Creation of balance sheet

  • Link between modeled cash flow and P&L
  • Key balance sheet items and their calculation
  • Non-cash items: depreciation, deferred tax
  • Assumptions required to be made
  • Use of existing figures or opening balance sheets
  • Creation of check totals
  • Exercise from a given P&L and cash flow statement, calculate the balance sheet

Derivation of ratios

  • Cash available for distribution and free cash flow
  • Debt service coverage ratio
  • Interest cover ratio
  • Equity returns: IRR & NPV calculations
  • Best practice in calculation & presentation of ratios in the model

Exercise – from a given cash flow and balance sheet, calculate the above ratios

Comparing different updates & versions of the model

  • Separate runs and variation of inputs
  • Ability to compare results
  • Reviewing future implications of variances

Example – creating a comparison worksheet to enable variance analysis of any two versions of a model

Sensitivity analysis

  • Break-even calculations
  • Stress-testing of model
  • Varying inputs to assess effect on results
  • Use of goal seek
  • Use of statistical techniques – probabilities and Monte Carlo simulations
  • Version control to allow comparison of outputs
  • Comparison of actual results against forecast as a sensitivity analysis

Excercise: from a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even

Risk reviews

  • Use of risk matrices
  • Relationship to model and sensitivity analysis
  • Probability analysis
  • Risk-adjusted returns – equity view & lender’s view

Excercise:For a given model, calculate risk-adjusted returns from potential project risks; Perform a risk assessment applicable to participants’ own projects, and model probability-weighted outcomes.

Documenting the model

  • Setting up base case model
  • Recording changes to model structure
  • Recording changes to assumptions
  • User guides
  • Running scenarios: descriptions, comparisons to base, version control


Day 4

Overview of PPP

  • What is PPP and how is it different?
  • The objectives of PPP deals
  • The parties to a PPP deal
  • The structure of a typical PPP deal
  • Different forms of service delivery and / or construction delivery: BOT, BOO, BTO, DBFO, BBMT
  • Risk profiles of PPP deals
  • Examples of, and reasons for, PPP failure. Only when the nature of PPP is understood, can modelling of its structure commence.

Structure & layout of models

  • Review the objectives of PPP, and the implications for model structure
  • Implications for deal of fixed deal end-dates
  • Tax implications and unused capital allowances
  • Practical tips in building models

Financing section

  • Simple financial structures used in PPP deals:
  • Debt finance:
    - Local Currency
    -
    Foreign Currency
    -
    Mezzanine
  • Equity finance
  • Preference Capital
  • Convertibles
  • Contingent
  • IPOs/Floats at project start or at exit
  • Government finance:
    -
    Credit Guarantees
  • Buy-Back/Put Options
  • Renationalisation Rights
  • Leasing/Leveraged Leasing
  • Monoline Insurers
  • Financing constraints:
    - use of covenants
    -
    restrictive ratios
    -
    debt service cover factors
    -
    loan cover factors
    -
    loan to value calculations
  • Returns to equity – NPV, IRR, other measures

Comparators

  • Public sector comparators (PSC) and their role
  • Using comparators to assess overall deal
  • Review of actual PSC example
  • Wrap-up
  • Overall review
  • Key points to re-iterate
  • Brief introduction to further exercises
  • Final questions and issues to discuss

Course summary and close

Day 1


Project finance modelling

What are financial models used for in project finance?

Introduction to the modelling exercise

  • Developing a project finance model for an independent power project (IPP)
  • How the exercise applies to other types of project What is involved in building a financial model?
  • How to approach the problem
  • Gathering the information you will need
  • Understanding the requirements of the model
  • The structure of a model
  • Materiality

Good modelling practice

  • How to structure your models so that they can easily be understood and audited
  • Separating assumptions from calculations
  • Assumptions required for construction phase calculations
  • Sources of information
  • Project timings
  • Costs and timing of costs

Practical exercise: participants will start to construct their project finance models. They will be taught how to use named ranges and learn how to establish a timeline using date functions. The course director will provide guidance on the use of Excel where necessary and will break to highlight key learning points.

Modelling the effects of inflation

  • The use of real and nominal values
  • How to tackle indexation
  • The importance of timing of the costs

Modelling in multiple currencies

  • Setting up your model to work with more than one currency
  • The use of forward exchange rate curves and purchasing power parity

Construction phase sensitivities

  • Discover how to set up sensitivities

Using lookup functions

  • Introduction to the choice of lookup functions
  • Why we use lookup functions
  • Problems associated with lookup functions

Construction phase funding

  • Modelling interest during construction
  • How to calculate commitment and arrangement fees
  • Building a debt tracking account

Circular references

  • What are they?
  • How do they occur?
  • Why should we avoid them?

Practical exercise: participants will develop a construction funding worksheet including idc, commitment fees and a debt tracking account

Day 2

Review of first day’s topics

  • Summary and Q&A session

During day 2, participants will continue to build their models, adding operating phase revenues and costs

Modelling project revenues

  • How are the revenues of project financed projects structured
  • The reasoning behind tariff structures
  • The differences between capacity, availability and output
  • Modelling bonus and penalty mechanisms
  • Dealing with multiple currency tariffs

Operating revenue assumptions

  • Source of assumptions
  • Operating revenue sensitivities
  • The effects of over / under performance

Practical exercise: participants will build operational revenue calculations including fixed/variable tariff elements and bonus/penalty mechanisms. The course director will also show real life examples of thesemechanisms.

Setting up the operating costs calculations

  • Source of assumptions
  • Operating cost sensitivities

Practical exercise: participants will develop operational cost calculations including fixed and variable components applying the relevant indexation to each of the cost items.

Modelling taxation

  • Modelling different types of depreciation
  • Carrying tax losses

Practical exercise: participants will develop tax calculations including depreciation and a tax loss tracking account.

Day 3

Review of second day’s topics

  • Summary and Q&A session followed by quiz

Determining the project’s debt capacity

  • Sources of funding

Practical exercise: participants will develop funding calculations including base and standby debt tracking accounts and debt service reserve account.

How to model different senior debt structures

  • Refinancing
  • Cash sweep mechanisms
  • Bullet loans

How to construct a cashflow statement

Key funding ratios

  • Introduction to NPV and IRR
  • Calculating cash available for debt service
  • Learn how to calculate annual debt service cover ratios and loan life cover ratios

Practical exercise: participants will develop a cashflow worksheet including calculation of key funding ratios. They will learn how to calculate the debt capacity based on these funding ratios.

Model optimisation

  • Adjusting repayment profiles to maximise debt capacity

Use of macros

  • How they are used in project finance deals
  • The dangers and precautions that could be taken Income statement
  • Pulling together relevant information to produce an income statement
  • Dividends and other points related to equity financing

Balance sheet

  • Constructing a balance sheet
  • Techniques for balancing
  • Using the balance sheet as an internal check on your model

Techniques for project appraisal

  • Evaluating investor returns
  • Using the offset function


Day 4

Review of first 3 day’s topics

Summary and Q&A session followed by quiz

Output from financial model

  • Producing reports
  • Examples from real life projects developed by the course director

Risk assessment

  • Sensitivity and break-even / default analysis
  • Pre-completion and post completion risks

Problem solving session: participants are invited to ask questions related to using the models they have built in their own workplaces, or ask questions related to models they have inherited.

Course summary and close

Day 1

Introduction and course objectives

  • Brief overview of project finance
  • Review of models and their objectives
  • Introduction of a simple model and its components

Overall model structure

  • Best financial modelling practice
  • Overall structure of the model
  • Separation of inputs, calculations and outputs
  • Logic flow within the model
  • Use of switches to allow option selection
  • Use of flags to control timing factors
  • Set-up to ease flexibility
  • Accommodating multiple options at early stages of project
  • Checks and totals, and error reporting

Inputs and assumptions

  • Principles of model structure
  • Building assumptions off the term sheets
  • Using the assumptions sheets as a sign-off document
  • Building-in ability to change and work changes through the model
  • Restricting ranges of inputs and validation criteria
  • Version control
  • Tracking changes


Exercise: Creating an assumptions input sheet with built-in flexibility

Revenue and cost build ups

  • Build-up of construction or other capital costs
  • Correct matching of units
  • Treatment of fixed and variable costs
  • Use of Maintenance Reserve accounts
  • Pricing assumptions
  • Use of lookup functions to change expenditure timings
  • Building in sensitivities

Taxes

  • Tax treatment of costs
  • Allowing for deductibility and non-deductibility
  • Capital allowances
  • Cash versus accounting treatment

Interest and fee calculations

  • Circularity and consequences
  • Calculations of interest and fees
  • Timing of payments
  • Cash flow payment vs. amortization in the
  • P&L
  • Capitalised fees and interest
  • Exercise – from given term sheet of interest
  • rates and fees, model interest and fee cash flow and P&L effects


Day 2

Financing section

  • Leverage, risk and the debt/equity equation
  • Calculating the cost of different types of debt capital
  • Cost of equity capital
  • Use of Debt Service Reserve Accounts (DSRA)
  • Use of the cash flow waterfall
  • Modelling issues arising:
  • Timing of debt and equity funding
  • Fee costs, upfront and spread
  • Interest costs, capitalised interest
  • Interest rate ratchets
  • Debt repayment profiles
  • Rate switches or refinancings at various stages of deal
  • DSRA interest margin
  • Debt repayment profiles and built-in options
  • Dividend and other equity returns
  • Constraints on dividend payments
  • Overall risk profile

Exercise: Creation of simple model to reflect debt costs, DSRA, repayment profiles, and returns to equity under constraints.

Modelling mulitple drawdowns

  • Cash flow driven
  • Cash positive periods and interest earned
  • Debt service reserve accounts
  • Fees to be included in drawdown amounts
  • Multiple facilities


Exercise: Given a pattern of cash flows,
calculate facility drawdowns and related interest cost and earned

Multi-currency modelling

  • Modelling foreign exchange rates
  • Dealing with changes in exchange rates
  • Comparing actual with forecast

 

Exercise: For a given pattern of cash flows in various currencies, model the amounts to be drawn in each currency


Separate exercise: Compare modelled rates and actual and separate effect of exchange rate changes from other variances

Inflation / escalation factors

  • Use of indices
  • Controlling start time of inflationary pattern
  • Applying multiple rates to different cost & revenue items
  • Varying inflation rates over life of the project
  • Comparing the effect of actual inflation vs. modelled

Exercise: Model multiple, variable rates and analyse a separate set of actual rates


Day 3

Creation of balance sheet

  • Link between modelled cash flow and P&L
  • Key balance sheet items and their calculation
  • Non-cash items: depreciation, deferred tax
  • Assumptions required to be made
  • Use of existing figures or opening balance sheets
  • Creation of check totals

Exercise: From a given P&L and cash flow statement, calculate balance sheet

Derivation of ratios

  • Cash available for distribution and free cash flow
  • Debt service coverage reserve ratios
  • Interest cover ratios
  • Equity returns
  • IRR & NPV calculations
  • Components of the weighted average cost of capital ("WACC")

Exercise: From a given cash flow and balance sheet, calculate the above ratios

Comparing actuals to previously modelled results

  • Separate runs and variation of inputs
  • Ability to compare results
  • Reviewing future implications of variances


Exercise: From a modelled forecast and actual results, calculate variances and project future model changes resulting

Sensitivity analysis

  • Break-even calculations
  • Stress-testing of model
  • Varying inputs to assess effect on results
  • Use of goal seek
  • Use of statistical techniques – probabilities and Monte Carlo simulations
  • Version control to allow comparison of outputs
  • Comparison of actual results against forecast as a sensitivity analysis

Exercise: From a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even.

Risk reviews

  • Use of risk matrices
  • Relationship to model and sensitivity analysis
  • Probability analysis
  • Risk-adjusted returns – equity view
  • Risk-adjusted returns – lender’s view


Exercise: For a given model, calculate risk-adjusted returns from potential risks in the project


Exercise: Perform a risk assessment applicable to participants’ own organizations, and model probability-weighted outcomes.

Documenting the model

  • Setting up base case model
  • Recording changes to model structure
  • Recording changes to assumptions
  • User guides
  • Running scenarios: descriptions, comparisons to base, version control

Wrap up

  • Overall review
  • Key points to re-iterate
  • Brief introduction to further exercises
  • Final questions and issues to discuss

Course summary and close

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


 


 
USE OF YOUR INFORMATION - Click to show/hide including your marketing options.

Euromoney Training is part of the Euromoney Institutional Investor PLC group of companies.
Click here for more details on our group’s activities.

We will use the information you provide here to process your order/registration/request including communicating with you about it. We may monitor your use of our website(s). As an international group, we may transfer your data on a global basis. Subject to your choices below, we may also use your data for marketing purposes. By submitting your details, you will be indicating your consent to the use of your data as identified here. Further information is set out in our privacy policy on this site.

Your marketing choices:
Please tick if you don't want to receive offers from our group companies by:
Or from companies outside our group

By submitting this form you agree to the Privacy Policy and Terms & Conditions.

Close

Euromoney Institutional Investor PLC group companies provide a range of business to business products and services. These are focused on international finance, metals, commodities, telecoms, law, tax, insurance, energy, transport and emerging markets. Products and services include magazines, newsletters, books, directories, electronic information and data, training, seminars and conferences. Full details are available at www.euromoneyplc.com

We will use the information you provide here to process your order/registration/request including communicating with you about it. If you are registering for a trial, you will receive follow up emails and/or a telephone call about your trial. As part of the trial, you may also receive email updates and other features as specified for that product. We may monitor your use of our website(s). As an international group, we may transfer your data on a global basis. Subject to your choices on the form, we may also use your data for marketing purposes. Further information is set out in our privacy policy on this site.

Close