Project Modeling in Excel
Practical, hands-on training for understanding and creating project finance models
This program is eligible for 18 CE credit hours as granted by CFA Society VBA Netherlands
Project Modeling in Excel provides participants with the ability to understand and create project finance models. Through building models in a hands-on environment, you will be better able to quantify risks of different types of projects and to use models to design the best debt, equity and contractual structure.
The program is designed so that you can learn to build and interpret
flexible models with effective summary statistics; so that models will be accurate with effective error checks that verify the model; so you can understand and program structured models that incorporate complex cash flow waterfalls, alternative funding cascades and sculpted repayment techniques; and so that models will be transparent and clear to understand by users.
The program includes different kinds of risk analysis and presentation of summary statistics. Additionally, you will learn how to use advanced techniques to resolve circular references associated with funding of a project and debt sculpting that use VBA functions rather than macros.
How you will benefit
Understand project finance models in the context of finance theory related to subtle issues associated with contract structuring, debt sizing, debt funding, debt repayment, debt servicing costs and credit enhancements
Be able to interpret models developed by other people and add master scenario pages to any model using case studies of actual models
Create flexible models with effective summary statistics to evaluate alternative timing, operating assumptions, financial structures, re-financing and contract pricing
Work through the difficult problems in project finance modeling including
- Complex cash flow waterfalls with balloon payments and mini-perm structures
- Sizing of debt with capitalized interest and alternative drawdown schedules
- Flexible debt sculpting with income taxes
- Sizing of debt service reserves and use of DSRA in waterfall
- Re-financing of debt and mini-perm debt
- Debt service reverses that look ahead to future years
Understand the benefits of creating user defined functions rather than copy and paste macros to resolve any circular reference problems in project finance models, including funding problems and debt sculpting
Incorporate structural enhancements into models such as maintenance reserve accounts and and gain insight into the costs and benefits of the alternative features such as cash flow sweeps, covenants and debt service reserve accounts
Learn Excel techniques with some VBA to make better presentations from models and to make models more transparent and efficient
Resources received by participantsOther than the most important item – knowledge of how to build, use and analyze models, participants in the program will receive many other resources, including the following software:
A basic project finance model with macros and instructions so that participants can create a comprehensive analysis
Fully developed project finance software with debt structuring, debt sizing, contract pricing and sensitivity analysis
Time series software that incorporates volatility, mean reversion and other parameters into models
Monte Carlo simulation software that combines times series analysis with project finance modeling
Software that computes implied volatility and option pricing using the Black-Scholes model
Yield spread models that compute required yield spreads on project finance debt form time series analysis
Corporate modeling software that extends project finance models to evaluate valuation of entire corporations
A variety of excel exercises that compute debt capacity, resolve circularity, develop tornado diagrams and construct vintage depreciation
In addition to the software resources, participants will receive extensive data based on actual projects, commodity price history and case studies
Laptop requiredA laptop computer, equipped with Microsoft Excel, is required for this program. It is necessary that participants bring their own laptop, or if requested, a laptop can be provided at an additional charge.
Project Modeling in Excel is a three-day program, divided into seven modules in total. The outline below presents teaching objectives, lectures and case work in each of the different modules.
Optional Excel Session An optional extra Excel session is available for participants who do not routinely use Excel in their day-to-day work. The objective of this session is to assure that all participants become familiar with the Excel tools needed to be able to work comfortably on the class exercises. The optional Excel session will cover short-cut keys, use of forms, one-way and two-way data tables, and three-key Excel functions.
This optional session will take place at AIF on the evening prior to the first day of the program, from 5.00 - 8.30pm.
Introduction and Model Structure
The Project Modeling with Excel program begins with introductory comments about the skills and general objectives in project finance modeling with an emphasis on the difficulty in measuring and valuing risk. After the introductory discussion, participants begin work on construction of a flexible, structured, accurate and transparent project finance model.
• Flexible timing in project finance models
- Timing in the context of project finance theory
- Review of actual models and date inputs and timing
- Modeling project phases with switches and timing switches
- Modeling delay risk and flexible construction periods and S-curves
- Conversion of periodic data into annual and semi-annual presentations
• Incorporating operating assumptions in project finance models
- Setting-up operating assumptions for revenues, operating expenses and capital expenditures
- Alternative methods for presenting time series assumptions
- Modeling inflation rates and growth rates with different time periods
- Calculation of pre-tax IRR and construction of summary page
- Computation of liquidated damages from construction delay
- Calculation of contract price from required pre-tax IRR
• Depreciation, capital allowance and fixed asset module
- Notion of structured models with separate page for depreciation analysis
- Use of timing switches for depreciation and/or capital allowance
- Introduction to verification and auditing for testing balances
- Benefits of separating deprecation on interest during construction and fees from other depreciation and amortisation
- Calculation of after-tax project IRR
- Calculation of project IRR assuming alternative sale dates earlier than the retirement date
Risk analysis introduction and basic debt structure
The second module addresses the theory and practice of risk analysis in project finance models. Different risks that are affected by historic record, mean reversion, volatility, resource risk and political risk are discussed. This is followed by addressing appropriate downside cases for credit analysis. Most of the time for this session is spent demonstrating how to construct scenario analysis, sensitivity analysis and Monte Carlo simulation.
• Major risks of projects in financial models (analysis and mitigation)
- Commodity price risk versus traffic volume risk
- Technology risk (breakdown and obsolescence)
- Evaluation of political risk with models
- Setting-up models for measuring foreign currency risk
- Construction cost and delay risk
• Assumptions and mechanics for basic debt schedule
- Five parts of debt – size, funding, repayment, interest, enhancements
- Set-up of basic assumptions with input debt level
- Debt schedule mechanics
- Cash flow waterfall with debt
• Set-up of master scenario page
- Development of reasonable downside case assumptions
- Use of scenario code number
- Presentation of scenario and custom sensitivity with user-forms
- Use of macros and data tables
• Break-even and sensitivity analysis
- Importance of break-even analysis in project finance
- Use of DSCR, LLCR and PLCR for measuring alternative break-even points
- Alternative sensitivity mechanics
- Use of macros and data tables
• Sensitivity presentation with Tornado Diagram and Waterfall Charts
- Adding sensitivity analysis to scenario table
- Creating cumulative and incremental tables
- Using scenario reporter
• At the end of the day: Optional Monte Carlo Session
- Time series variables in Project Finance
- Measuring volatility of value drivers
- Creating distribution analysis with data table or macro
- Measuring probability of default with Monte Carlo Simulation
Debt structure, sculpting and debt sizing
As project finance is a type of debt, the third module addresses various theoretical and practical issues related to debt financing in general and project debt in particular. Subjects included are setting up a debt schedule, debt sculpting, flexible debt terms, debt capacity, debt structure and credit measures.
• Theory of debt capacity
- Philosophy of debt to capital and “skin in the game”
- Philosophy of debt service coverage and “negotiated forecast”
- Implications of different debt sizing metrics
- Interaction of debt sizing, debt repayment, DSRA and other factors
• Debt schedule and debt capacity mechanics
- Set-up of sources and uses to evaluate debt size from target debt to capital ratio
- Debt capacity from debt service coverage with formulas
- Debt repayment with equal instalments or mortgage repayments
- Presentation of cash flow and debt service on summary page
• Debt repayment analysis
- Alternative debt tenor
- Mini-perm and re-financing
- Verification tests for debt balance and debt repayment
- Inclusion of bond financing and mini-perm
• Debt sculpting
- Computing repayments assuming debt sculpting without taxes and DSRA
- Circularity problems arising from sculpting and taxes
- Resolution of circularity problems using function
Financial structuring during construction and development phase
The fourth module of the program addresses the details of project finance models including funding structure during construction, interest during construction, bond financing and various other exercises relevant to financing during construction in project finance models.
• Review of financing calculations in sample completed models
- Alternative possible financing assumptions
- Notion of funding versus debt commitment from summary sources and uses
- Presentation of sources and uses of funds
• Funding cascade
- Pro-rata construction versus equity up-front versus equity bridge loan
- Funding Needs versus Debt Commitment and Funding Ratio
- Use of MAX and MIN in developing funding cascade
- Modelling of bond financing with flexible timing and cash fund
• Circularity macros and functions for capitalized interest and fees
- Alternative methods to resolve circularity
- Illustration of four methods for resolving circularity with fee example
- Advantage of functions relative to macros for transparency and flexibility
- Use of algebra and functions instead of macros
• Debt schedule during construction
- Interest during construction and interest capitalised using periodic interest rates and credit spreads
- Set-up of summary sources and uses of funds to resolve circularity
- Up-front fees and commitment fees
- Repayment of construction debt at project completion date to avoid DSRA circularity
• Model verification and accuracy audits
- Developing multiple tests for funding needs and funding uses using logical variables
- Aggregation of multiple tests
- Effective presentation of model verification on each page of model
DSRA, MRA and cash flow waterfall
The fifth module moves from debt structuring to risk analysis. The principal issue addressed is how to model a cash flow waterfall where different instruments such as cash flow sweeps reserve accounts and covenants are used to protect senior debt service.
• Cash flow sweep and cash trap covenants
- Incorporation of cash sweep in debt schedule
- Limits on cash sweep using MIN function
- Programming cash trap covenants
- Set-up of cash lock-up account
• Efficient cash flow waterfall modeling
- Setting up debt and reserve schedule combined with cash flow analysis
- Setting up cash flow with positive and negative conditions
- Use of sub-totals in modelling cash flow
- Application of MAX and MIN functions to limit cash flows
- Modeling sinking fund, sweep and bullet repayment
• Debt service reserve accounts
- Modeling required debt service balances
- Withdrawals from debt service reserve
- Topping-up of debt service reserves
- Debt reserve during end of construction period
- Resolving circularity associated with debt service reserve
Taxes and financial ratios
The sixth module completes the project finance model through inclusion of a profit and loss statement and computation of income taxes. Given the income, a balance sheet is computed to verify calculations in the model. Finally, the LLCR, PLCR and the average debt life are computed.
• Profit and loss statement and income taxes
- Structuring profit and loss
- Inclusion of depreciation rn interest during construction and amortization of fees
- Programming basic net operating loss account
- Accounting for expiration of net loss carry forward
• Calculation of alternative financial ratios
- DSCR with and without tax effect of interest during construction and fees
- Calculation and interpretation of PLCR with varying interest rates
- Adjustments to compute LLCR
- Alternative calculations of loan life
• Balance sheet calculation
- Limited need for balance sheet in analysis
- Concept of computing equity balance and using closing balances from other sections of the model
- Use of balance sheet as auditing tool
Re-financing and valuation analysis
The final module completes the project finance analysis through inclusion of valuation section that reflects the changing risk of a project over time and the potential upside from re-financing of a project.
• Theory of project valuation and re-financing
- Changes in risk for project finance versus corporate finance
- Problems with equity IRR as a measure of value
- Value of project in development phase
- Project life, debt tenor and terminal value
- Switches for the re-financing period
- Sources and uses for re-financing
- Sizing of re-financed debt
• Project valuation
- Changes in cost of capital from risk changes
- Measurement of IRR with alternative holding periods
- Sizing of re-financed debt
Who should attend
Project Modeling in Excel targets financial professionals involved in evaluating the economics of energy, infrastructure, real estate and other projects. Bankers, developers, financial advisors, consultants, investors, managers and others interested in creating models or simply understanding how to interpret models created by others can benefit.
For a broader understanding of the field, the program can be taken in conjunction with AIF’s Project Finance program.
Project Modeling in Excel is a hands-on program that will be conducted using numerous exercises in Excel. All participants are required to have a solid, basic knowledge of Excel prior to attendance.
Optional Excel Session An optional extra Excel session is available for participants who do not regularly use Excel in their day-to-day work. The objective of this session is to assure that all participants become familiar with the Excel tools needed to be able to work comfortably on the class exercises. The optional Excel session will cover short-cut keys, use of forms, one-way and two-way data tables, and three-key Excel functions.
This optional session will take place at AIF on the evening prior to the first day of the program, from 17.00 - 20.30.
Edward Bodmer teaches a number of modeling courses and is a consultant who specializes in financial analysis and modeling. He is a former banker and has taught courses for major corporations and financial institutions around the world for many years. Visit his website to see some samples of models:
www.edbodmer.com. He received an MBA specializing in econometrics (with honors) from the University of Chicago and a BSc in Finance from the University of Illinois (with highest university honors).
Dates & fees
25 - 27 September 2018
> Program fee includes all study materials, books and software that are required for the program as well as daily luncheons.
Program fee is exempt from VAT for clients located in the Netherlands. For other EU and Non-EU clients, VAT may be due by client and will not be charged by AIF. Fees may be subject to change.
89% of the attendees in the September 2017 session would recommend the Project Modeling in Excel program to their colleagues.
"Very comprehensive program."
"Very hands-on and practical use/demonstration of modeling techniques. A comprehensive review of the main project finance assumptions."
Deloitte Enterprise Risk Services
"This course helps you to improve you models and makes them more stable, which in turn helps you to make better investment decisions."
Business Analyst, Sustainable Energy Production
"A good explanation of project finance issues through direct application to Excel so that concepts are also captured by non-professionals in project finance."
Eni S.p.A. - Exploration & Production Division