Euromoney Audit Training

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.

DAY ONE

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 TWO

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 THREE

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

• Ed Bodmer

  Edward Bodmer 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.
  
  Mr. Bodmer's 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.
  
  Mr. Bodmer 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.
  
  Mr. Bodmer 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.
  
  Mr. Bodmer 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.

  Courses run by this instructor

  • Financial Modeling for Mergers & Acquisitions - New York City
  • Real Estate Modeling - New York City

Interested in holding this course in-house? Please fill out your details and a member of our team will be in touch with more information.

Renewable Energy Project Finance - New York City

12-14 Mar 2012 (New York, United States)
15-17 Oct 2012 (New York, United States)

This program will discuss approaches to financing wind, biomass /biofuel, solar and other renewable projects in many parts of the world, focusing on how these transactions differ from more conventional thermal-based deals.

International Project finance & Cash flow Modeling - New York City

11-15 Jun 2012 (New York, United States)
12-16 Nov 2012 (New York, United States)

This program is designed to help participants break the project analysis process into its component parts. While differentiating between countries and industrial sectors, sessions introduce techniques for assembling market information and making qualitative judgments, as well tools for conducting thorough quantitative financial analysis.

Public Private Partnerships (PPPs) Infrastructure Project Finance - Miami

16-18 Apr 2012 (Miami, United States)

An essential element of PPP is the desire to link public sector accountability with private sector discipline, expertise, efficiency at delivering such services, and financial resources. This is equally true in both developed and developing countries. Participants in this hands-on program will become thoroughly familiar with the array of PPP models from Supply and Service Contracts, Management Agreements, Leasing, DBO, BOT, BOO, Privatization and a continuum of other approaches.