Project Modeling in Excel
 Edward Bodmer


Objective
Project Modeling in Excel 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
  • Evaluate debt capacity and model different types of debt re-payment structures
  • Quantify project risks using different techniques and understand mistakes in risk assessment made in famous project financial failures and the financial crisis
  • 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
  • Measure the appropriate credit spread for projects with different risks and the effects of the option to refinance

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. Participants will be provided with 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.

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.

Prerequisite
This program makes extensive use of computer case studies, therefore all participants are required to have a solid, basic knowledge of Excel prior to attendance. An academic or equivalent background, and/or relevant professional experience is required.

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

Additional information
Participants will need to bring a laptop computer equipped with Microsoft Excel 2000 or higher for use during the program. The Excel Solver add-in and the Analysis Tool-Pack add-in should be installed. If requested, a laptop can be provided at an additional charge.

Faculty
Edward Bodmer teaches a number of modeling courses and is a consultant who specializes in financial analysis and modeling. He is also a lecturer at the Center for Energy Economics at the University of Texas at Austin.

For information about admission, please see our Practical Information.

Program Content
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.

Issues addressed in this session include:
What is the big deal about project finance and what makes project finance different from other forms of financing?

What are the benefits of project finance relative to the high fees received by lawyers and bankers?

What type of risks should be evaluated in project finance to test when projects such as Eurotunnel, Euro Disney, Merchant Electricity Plants, Mexican Toll Roads and many others go bad?

What financial criteria – DSCR, IRR, PLCR, LLCR are used to quantify project financings and what do the criteria mean?

Lectures

  • Classic errors in analysis and analogies to sub-prime crisis
  • Background on risk measurement and financial modeling
  • Comments on spreadsheet layout style and conventions
  • Review of Excel techniques in project finance modeling

Exercises

  • Basic Mechanics of Project Finance Models
    • IRR, MIRR, XIRR, and present value exercises
    • Separation of construction period from operating period
    • 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 and use of a balance sheet in auditing the model
    • Structuring joint ventures and sharing agreements
    • Equity IRR with development fees

Day 1 - Module II
Debt Structure and Cash Flow Waterfall Exercises
As project finance is a type of debt, the second 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 two include debt modeling, debt capacity, debt structure and credit measures.

Issues addressed in this session include:
What type of covenants are included in project finance transactions?

Is it more important to trap cash during good times or develop covenants that prevent cash from leaving the project when times are bad? How much safer is senior debt than junior debt?

What is the appropriate level for various covenants, and what financial ratios should be used in the covenants?

Exercises

  • Debt Sizing and Capacity Exercises
    • Discussion of debt capacity
    • Debt capacity from debt service coverage
    • Debt capacity with macro
    • Debt sculpting with solver
  • Debt Structuring Exercise
    • Cash flow sweep exercise
    • Covenant exercise
    • Debt default exercise
    • Senior and junior debt exercise
  • Simulation Exercise
    • Time series discussion
    • Simple Monte Carlo set-up

Day 2 - Module III
Continuation of Model Structuring Issues
The third 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.

Issues addressed in this session include:
How can we make the models flexible enough to evaluate the credit impact of inevitable delays in construction, alternative retirement dates, liquidated damages and cost over-runs?

What should we do to incorporate alternative debt structures and interest during construction into the model?

What difference does it make that project finance models show construction schedules on a monthly basis? How to efficiently program conversion of monthly data to annual data.

How can we apply look-up tables in modeling construction profiles, and depreciation?

How can we effectively incorporate macros in project finance models to compute required contract prices, debt capacity and other items?

Exercises

  • Monthly Construction and Delay
    • Setting-up a model with alternative time periods and switches
    • Modeling interest during construction using alternative periods
    • Computing liquidated damages with alternative delay scenarios
    • Converting Periodic Models to Annual Models
  • Monthly Draw Downs and Semi Annual Debt Service
    • Reports for monthly construction
    • Reports for semi-annual debt service
    • Analysis of subordinated debt
  • Debt Service Reserves and Re-financing
    • Modeling required debt service balances
    • Withdrawals from debt service reserve
    • Topping-up of debt service reserves
    • Re-financing case
  • Circularity Macros
    • Alternative methods to resolve circularity
    • Working with range names
    • Alternative ways to enter data
    • Use of range names

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.

Issues addressed in this session include:
What are some of the Excel guidelines that guide accurate and efficient development of models so that when you pick up a model after not working on it for a few weeks, you can understand your work?

How can project finance models received from others be audited to check errors that we will make?

If we sign a contract that changes the risk profile of a project, how should we value the contract?

What are the three fundamental drivers of the value of projects?

Where should we start in looking for the most important risks? What are examples of risks that have caused major projects to fail such as AES Drax, Loy Wang, U.S. merchant electric plants and Enron’s Dabhol LNG plant?

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

Case Study Exercises

  • Presentation of model results
  • 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.

Issues addressed in this session include:
What are some of the Excel guidelines that guide accurate and efficient development of models so that when you pick up a model after not working on it for a few weeks, you can understand your work?

How can project finance models received from others be audited to check for errors?

How can sensitivity analysis and tornado diagrams be added to project finance models?

How can scenario analysis be incorporated in project finance models?

Case Study Exercises

  • Develop a general model layout
  • Construct transparent model titles
  • Evaluate titles for model details such as amortization of debt fees, deferred taxes, and minority interest
  • Build model equations in segments
  • Compute key model outputs

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.

Issues addressed in this session include:
How can volatility be applied in project finance models?

What is the probability of loss and the loss given default for project finance?

How can Monte Carlo simulation be incorporated into project finance models?

Lectures

  • Time series Analysis of Prices and Economic Data
    • Economic theory behind alternative time series models
    • Definition and application of volatility
    • Brownian motion time series
    • Mean reversion time series
  • Economics of Commodity Prices
    • Long-term forward prices and economic theory
    • Equilibrium production cost
    • Productivity changes and resource supply
    • Demand changes

Case Study Exercises

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


Dates & Fees
October 27 - 29, 2010
€ 3,300
 
(AIF programs are not subject to VAT)