Project Finance Modelling

3 days 29-31 May 2017, Rio de Janeiro Brazil $3,500.00 Download brochure Add to basket
3 days 10-12 Jul 2017, Hong Kong Hong Kong $4,490.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).


* Claim back your VAT
Find out more


FTS Eligible - Financial Training Scheme for Singaporean citizens and PRs only (more information)

Course objectives

This course will review lessons on valuation, risk assessment and forecasting that can be derived from the financial crisis. Participants will discuss mistakes made in valuing sub-prime loans and other famous valuation errors. This provides context for other subjects in the course.

Summary of course content

  • Valuation lessons from the current financial crisis
  • How structured financial models can be created that clearly define risks and value without being overly complex
  • The fundamental factors that underlie valuation and how can they be used in practice
  • What lies behind multiples such as the P/E and the EV/EBITDA ratios and how these multiples can be used in valuation
  • How to effectively present risk analysis in valuation analysis models
  • Whether Monte Carlo simulation and mathematical techniques can realistically be used to assess risk and compute value
  • The valuation of debt and measurement of debt capacity to provide alternative ways to value investments
  • The creation of models specifically used to evaluate LBOs, project finance and M&A


Case studies, hands-on analysis and template models will be used as the primary teaching tools in the programme. If you are interested in practical mechanics of excel (macros, combo boxes, offset and indirect functions etc.) these can also be discussed after the course.

Other than the instruction in how to build, use and analyse financial models, you will receive a comprehensive suite of financial modeling software on CD that includes a number of template models and Excel add-ins.

The software consists of corporate models that accept historic financial data and generate alternative valuation measures; M&A models that consolidate two companies using alternative financing assumptions and produce accretion and dilution estimates; project finance models that measure the effect of alternative elements in a cash flow waterfall including debt service reserves, junior debt, covenants, defaults and pre-payments; LBO models that measure the debt capacity of a transaction; option pricing models that account for alternative structures; and debt valuation spreadsheets, Monte Carlo simulation models, tornado diagram and sensitivity analysis.

Computer-based exercises

This course will make extensive use of modelling exercises in Excel®
All delegates should bring their own laptops loaded with Microsoft Excel® 2010 or later to facilitate inclass
studies and exercises.

Free CD to take-away

Delegates will receive a comprehensive suite of financial modelling software on a CD that includes a number of template models and excel add-ins.

Pre-course survey

Book early and fill in our pre-course survey to ensure your specific needs are met in the course by the trainer.

FTS Eligible

This programme is approved for listing on the Financial Training Scheme (FTS) Programme Directory and is eligible for FTS claims subject to all eligibility criteria being met.

Please note that in no way does this represent an endorsement of the quality of the training provider and programme. Participants are advised to assess the suitability of the programme and its relevance to participants’ business activities or job roles.

The FTS is available to eligible entities, at a 50% funding level of programme fees, subject to a cap of $2,000/participant/programme and all eligibility criteria being met. FTS claims may only be made for programmes listed on the FTS Programme Directory with specified validity period. Please refer to for more information.

Who should attend

  • Corporate Finance/Corporate Treasury
  • Capital Markets
  • Audit/Product Control/Risk Management/ALM
  • Research and Analysis
  • Sales and Trading
  • Investment Management
  • Origination
  • Securitisation/Syndication
  • Structured Finance
  • Money Markets/Repo
  • Systems Programming
  • Funding
  • Government/Agency Funding and Investment
  • Regulation/Compliance/Documentation
  • 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.

    Hong Kong
    Alan Brooke

    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.

    Rio de Janeiro
    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.


    Rio de Janeiro

    Rio de Janeiro

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


    Hong Kong

    4-5 Star Hotel in Hong Kong

    All of our courses are held in 4 – 5 star hotels, chosen for their location, facilities and level of service. You can be assured of a comfortable, convenient learning environment throughout the duration of the course.

    Due to the variation in delegate numbers, we will send confirmation of the venue to you approximately 2 weeks before the start of the course. 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



    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.

    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.



    Agendas are localised, please select your preferred location.

    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


    • 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 and course objectives

    • Brief overview of project finance
    • Review of models and their objectives

    Overall model structures inputs and assupmtions

    • Principles of model structure
    • Best financial modelling practice
    • Overall structure of the model
    • Separation of inputs, calculations and outputs
    • Logic flow within the model
    • Use of flags to control timing factors
    • Set-up to facilitate flexibility
    • Accommodating multiple options at early stages of project
    • Checks and totals, and error reporting
    • 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
    • Use of switches to allow option selection
    • Version control
    • Use of the corkscrew technique
    • 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
    • Use of lookup functions to change expenditure timings
    • Building in sensitivities
    • Pricing assumptions

    Example: Model with flexible capital expenditure and sensitivity functions

    Inflation/escalation factors

    • Principles of modelling inflation escalation
    • Use of indices
    • Controlling start time of inflationary pattern
    • Applying multiple rates to different cost and revenue items
    • Varying inflation rates over life of the project

    Exercise: Model the build-up of costs and income, and apply growth and inflation indices

    Day 2

    Interest and fee calculations

    • Circularity, consequences and solutions
    • Calculations of interest and fees
    • Timing of payments
    • Capitalised fees and interest

    Example: Solving a circular reference in a given cash flow model

    Exercise: Create a DSRA calculation, solving for a circulation reference


    • Different types of taxes
    • Tax treatment of costs
    • Allowing for deductibility and non-deductibility
    • Capital allowances
    • Cash vs. Accounting treatment
    • Tax losses generated
    • Timing of tax payments installments
    • Modelling techniques

    Example: Review of tax calculations from an actual project finance model

    Leverage, risk and cash flow

    • Leverage, risk and the debt/equity equation
    • Calculating the cost of different types of debt capital
    • Cost of equity capital
    • Concept and calculation of the weighted average cost of capital (WACC)
    • 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
      • Debt repayment profiles
      • Refinancing at various stages of deal
      • Debt repayment profiles and built-in options
      • Dividend and other equity returns
      • Constraints on dividend payments
      • Overall risk profile

    Exercise: Creation of cash flow waterfall to reflect debt costs, DSRA, repayment profiles, and returns to equity under constraints

    Day 3

    Comparing actuals to previously modelled results

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

    Exercise: Demonstration of setting up a comparison sheet to review and compare different versions of a model

    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
    • Modelling techniques to ensure the balance sheet always balances
    • 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/XIRR/MIRR and NPV/XNPV calculations

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

    Sensitivity analysis

    • Purpose of sensitivity analysis
    • Break-even calculations
    • Stress-testing of model
    • Varying inputs to assess effect on results
    • Use of Excel tools, including goal seek, data tables and scenario manager

    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'sview
    • Risk-adjusted returns – lender's view

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

    Documenting the model

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


    • Overall review
    • Key points to re-iterate
    • Further reading
    • Brief introduction to further exercises
    • Final questions and issues to discuss
    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