Project Finance Modeling Masterclass

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 Who Should Attend? Credit Managers Project Finance Managers Financial Analysts Corporate Banking Officers Business Development Managers Structured Finance Managers Financial Consultants Business Analysts Investment Managers Legal Advisors Supported By:
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