Project Modeling in Excel

Objective
The Project Modeling in Excel training course provides participants with the ability to create and understand project finance models. Through building models in a hands-on environment, you will be better able to quantify risks of different types of projects; to appreciate costs and benefits of different project finance features such as covenants, reserves, sweeps and other factors; and to create efficient analyses.

Key benefits

• Understand the objectives and the structure of project finance models compared to other types of financial models
• Work through the four difficult problems in project finance modeling including
   -  Complex cash flow waterfalls
   -  Sizing of debt with capitalized interest and alternative drawdown schedules
   -  Flexible debt sculpting
   -  Debt service reverses that look ahead to future accounts 
• Quantify project risks using different techniques and understand mistakes in risk assessment
• Incorporate structural enhancements into models and gain insight into the costs and benefits of the alternative features 
• Learn Excel techniques to make better presentations from models and to make models more transparent and efficient

Resources received by participants
Other 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

Project Modeling in Excel is a three-day program. Each of the three days is divided into two modules, resulting in a total of six modules for the entire program. The outline below presents teaching objectives, lectures and case work in each of the six different modules.
 

Day 1 - Module I
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 construct a basic project finance model without debt. The primary objective of building the model is to review basic mechanical issues with building a model and assuring that participants are comfortable with the basics.
 

Exercises

• Basic Mechanics of Project Finance Models
   -  IRR, MIRR, XIRR, and present value exercises
   -  Separation of construction period from operating period using switch variables
   -  Sources and uses of funds statement during the construction period
   -  Cash flow, net income, equity balance, construction financing and income taxes
   -  Internal rate of return on the project and central concept of free cash flow
   -  Construction of a balance sheet for auditing the model
   -  Structuring joint ventures and sharing agreements

Day 1 - Module II
Continuation of Model Structuring Issues
The second module of the program addresses details of project finance models including interest during construction, liquidated damages, debt service reserve movements and various other exercises relevant to project finance models. Subjects include tax depreciation, leases, monthly draw downs of during construction, semi-annual interest costs and resolution of circularity.
 

Exercises

• Monthly Construction and Delay
   -  Setting-up a model with alternative time periods and switches
   -  Modeling interest during construction and capitalized interest
   -  Computing liquidated damages with alternative delay scenarios
   -  Converting periodic models to annual models 
   

• Setting-up Flexible Inputs
   -  Computing age variables in alternative periods
   -  Timing issues and calculation of varying inflation rates, and other inputs
   -  Modeling items as a function of calendar year and project age
   
• Model Verification
   -  Developing tests in model using logical variables
   -  Aggregation of multiple tests
   -  Effective presentation of model verification
   
• Analysis of Sources and Uses
   -  Items in sources and uses of funds
   -  Alternative draw down techniques (debt first, debt last, equal)
   -  Limits on debt and equity issues

Day 2 - Module III
Debt Structure and Cash Flow Waterfall Exercises
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. A central theme of this section of the program is that through measuring debt capacity, cost of capital and risk can be gauged. Subjects included in module three include debt modeling, debt capacity, debt structure and credit measures.
 

Exercises

• Debt Schedule and Debt Capacity
   -  Set-up of debt schedule
   -  Debt capacity from debt service coverage
   -  Debt capacity with VBA
   -  Presentation of cash flow and  debt service
   
• Debt Structuring
   -  Alternative debt tenor
   -  Varying credit spreads
   -  Different methods of debt repayments
   -  Multiple debt issues
   
• Debt Repayment in Project Finance Models
   -  Level debt repayment and verification
   -  Annuity repayment and use of debt repayment switch
   -  Sculpted debt repayment and use of solver or formulas
   -  Analysis of debt repayments

Day 2 - Module IV
Risk Analysis from Alternative Perspectives
The most time spent developing project finance models is generally developing economic assumptions that form a base case. Module four addresses economic analysis behind key value drivers in a project finance model. Various actual cases are used to develop economic assumptions and to demonstrate use of sensitivity analysis, break-even analysis and tornado diagrams. The final part of the section develops mathematical measures of risk from the project finance models.
 

Lectures

• Economic Value Drivers in Projects
   -  Capital expenditures
   -  Forward price
   -  Volume – capacity utilization
   -  Input forward price
   -  Efficiency
   -  Operation and maintenance expenses
   
• Major Risks of Projects (analysis and mitigation)
   -  Commodity price risk versus volume risk
   -  Technology risk (breakdown and obsolescence)
   -  Input supply and availability risk
   -  Foreign currency and political risk
   -  Construction cost
   -  Contract breakage risk

Risk Analysis Exercises

• Set-up of master scenario page
• Break-even analysis 
• Sensitivity analysis and graphs 
• Scenario analysis 
• Tornado analysis

Day 3 - Module V
Structuring and Building a Project Finance Model
The fifth module begins a case study in which participants develop the structure of a project finance model through laying out the structure of the model and writing efficient formulas for the case. It addresses debt structuring aspects of project finance including covenants, cash flow traps, senior and subordinated debt issues, collateral and debt capacity.

• Debt Service Reserves
   -  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
   -  Contract breakage risk
   
• Efficient Cash Flow Waterfall Modeling
   -  Setting up debt and reserve schedule
   -  Setting up cash flow with positive and negative conditions
   -  Use of sub-totals in modeling 
   -  Application of MAX and MIN functions
   -  Testing and presenting cash flow waterfall
   
• Re-financing
   -  Switches for the re-financing period
   -  Sources and uses for re-financing
   -  Sizing of re-financed debt
   
• Circularity Macros
   -  Alternative methods to resolve circularity
   -  Working with range names
   -  Alternative ways to enter data
   -  Use of range names

Day 3 - Module VI
Analysis using Model Created in Case Study
In the final part of the program participants use the model they have created to analyze a series of decisions and evaluate various risks. Participants first address traditional measurement of risk through break-even analysis, scenario analysis and sensitivity analysis. Next, the program moves to mathematical approaches that directly attach a numeric value to risk. Exercises in this part of the program include creation of time series models, Monte Carlo simulation, and credit issues associated with long-term supply contracts.

Case Study Exercises

• Construct a sensitivity analysis
• Construct scenario and tornado diagrams 
• Develop time series equations 
• Build a Monte Carlo simulation

The Project Modeling in Excel training course 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.

Prerequisite
The Project Modeling in Excel training course is a hands-on course that will be conducted using numerous exercises in Excel. All participants are required to have a solid, basic knowledge of Excel prior to attendance. For participants who do not regularly use Excel in their day to day work, AIF is offering an optional Excel session, at no extra cost to the participant.

Optional Excel Session 
The objective of this session is to assure that all participants, including people who do not routinely work with Excel, become familiar with the tools in Excel and work comfortably on the class exercises.  The optional Excel session will cover short-cut keys, effective presentations, use of forms, one-way and two-way data tables, and look-up functions for scenario analysis.

This optional session will take place at AIF on the evening prior to the first day of the program, from 5.30 - 8.30pm.

Amsterdam Institute of Finance is registered with CFA Institute as an Approved Provider of continuing education programs. This program is eligible for 18 CE credit hours as granted by CFA Institute. If you are a CFA Institute member, CE credit for your attendance at this event will be automatically recorded in your CE Diary.

"Very hands-on and practical use/demonstration of modeling techniques. A comprehensive review of the main project finance assumptions."
Manager,
Deloitte Enterprise Risk Services
Belgium

"This program has just the right level of knowledge for a professional with some experience in the field. It really increases your knowledge."
Senior Analyst, Strategic Planning
Brisa Auto-Estradas de Portugal
Portugal