Project Finance Modelling

3 days 29-31 May 2017, Rio de Janeiro Brazil $3,500.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).

{{alternativeRequestSuccess}}
{{alternativeRequestError}}

* Claim back your VAT
Find out more

Overview

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

Methodology

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 www.ibf.org.sg 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.

    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.

    Venue

    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.

     

    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. It might even prove to be more cost effective.





    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 and we’ll be here to support you every step of the way. From the initial consultancy through to evaluating the success of the full learning experience. We'll 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 specialising in:


    Complete a call back request to speak to a learning specialist or learn more about our offering

     

    Agenda

    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
    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