This is where I hold some models for the class and other information for on-line classes in process. I have been updating the file as we work through different sessions and modelling issues. In the class we start with a basic model and then work through more and more complex issues including monte Carlo simulation, cash flow waterfalls, circular references, complex timing issues, debt service reserve accounts, maintenance reserve accounts, cash flow deficits related to interest expenses versus CFADS and other issues. I hope that I discuss some of the ideas for the financial modelling concepts before working through the modelling issues. You can press links to the different class sessions and spend a lot of time watching me mess up.
.
.
If you download the file below you can click on a whole lot of links to videos of course sessions. If you really want to go to town on modelling and torture yourself, you can watch some of these videos.
.
Excel File with Project Finance Model of a Nuclear Plant using Benchmark Data from Lazard
.
The screenshot below shows one of the pages where you can click on the links. Each of these links brings you to a video of a teams meeting that we had for the course sessions.
.
.
One way to learn modelling is to relax during a peaceful evening, pour yourself a glass of wine and work through a model with 30 pages. It is hard to imagine a better evening. The excel files and videos on this approach a selection of completed project finance models that you can work through. By reviewing one or more of the project finance models available below you can understand some of the complex project finance modelling issues and maybe pick-up some ideas. (In my courses I address the more painful subject of reading real models that often have absurdly long equations and bad structuring). An important feature of most of the models is resolution of circular references related to debt funding (interest during construction, fees during construction and DSRA accounts that affect project cost and therefore the debt itself) as well as sculpting (for taxes that depend on interest expense and interest income). With resolution of circular references you can see how different financing features affect required prices in structuring of bids.
Example of Project Finance Model with Parallel Model to Resolve Circular References
The model that you can download by clicking on the button below resolves circular references without the iteration button or copy and paste macros. This makes the model faster, more transparent, more accurate and much more flexible. I have been working on this for a long time and I am going to post a template model that makes it easy to add to your model that has circular references. Please do not think this is difficult to do as I will be adding a lot of examples and videos that demonstrate you can easily add a page to your model and get rid of your copy and pasted values. You can add up to 30 different debt issues; you can use all kinds of different debt sizing, debt funding, debt repayment and interest rate structures. You can put in balloon payments, you can sculpt where changes in the DSRA are in the numerator or denominator of the sculpting formula.
I am continue to perfect this approach so it is easy to implement and can handle more possible of debt and taxes. You can see my progress if you have downloaded the google drive. The location of the files on the google drive is demonstrated in the screenshot below.
Cancun Solar model which works through various issues associated with estimating solar resources includes fundamental modelling structure.
If you want to model the complex features of this model that involve solving circular references associated with sculpting and funding, you should go to the ADVANCED STRUCTURING PAGE.
Solar Project Finance with Circular Reference Resolution and Analysis of Yield
Solar Project Finance Model for Mexican Project with Detailed Cost Breakdown
Link to My Youtube Channel Where You Can Look At All of the Different Videos that I have Made
Natural Gas and Coal Thermal Plant Project Finance Models
The file below demonstrates how to incorporate maintenance timing and other issues in a the model of a thermal plant (a natural gas combined cycle plant). The model demonstrates how to incorporate scheduled maintenance after running a given number of hours (e.g. 40,000 hours) and then has maintenance that lasts a certain amount of time. It has (I think) a flexible detailed print macro that you can apply to your models. I suggest looking at the print model and then the associated video that describes how to implement the macro. The third file below includes demonstrates how to evaluate a classic IPP project with risks associated with incentive elements of a PPA tariff. The file show aspects of a four part PPA tariff with risks, debt sizing and functions for resolving circularity. Risks are associated with availability, heat rate variation, O&M expenses and other factors. In addition to solving circular references with functions, the file also includes methods to present sensitivity and scenario analysis. These files are documented in videos that are listed at the bottom of the page.
The last file in the set below demonstrates the modelling of a coal project without functions for circular references. It uses a case study in the label that resulted in very high returns to U.S. investors who really did not take risk.
http://www.youtube.com/embed/fDuNgdTETF4 Gas Plant Example with Print Macro.xlsm Indonesia Gas Plant.xlsm IPP Model.xlsm Quezon Coal Plant Example.xlsm
Solar Rooftop Models
The files below illustrates how to evaluate rooftop solar projects that include a portfolio of different individual assets (it is arguably analogous to corporate finance or real estate finance). The files below include a number of tricky methods to accumulate production, capital expenditures and operating expenses with different timing of start and end for different solar roof-top assets. For example, one set of rooftops may be producing electricity and yielding positive cash flow while another set of assets is being constructed. Some of the items such as degradation, operating expenses and depreciation may vary as a function of the age of the project. This can cause difficult programming issues. The first file also includes the effect of detailed working capital movements where lags are evaluated from prospective months. Finally, both of the models include complex issues associated with financing where some projects are producing positive cash flow and others require financing and are not yet generating cash. This first file is used for a comprehensive set of videos that allow you to build a model on a step by step basis that I have modelling the solar rooftop on-line course. The video below describes how to set-up financing in the rooftop model where separate SPV’s are not structured and the financing occurs on a consolidated basis.
Rooftop Solar.xlsm
Rooftop Model with Portfolio PW 9498.xlsm
Renewable Project Finance and Price Bidding Analysis
The files below illustrate the effect of different financing structures on the on the levelised costs of alternative technology including solar. It is built with a relatively simple project finance model that evaluates carrying charges. It is meant to demonstrate that the kind of financing terms in a project finance loan document and the returns demanded by equity and debt holders have a dramatic effect on the price of solar power. The file includes a waterfall chart where you can evaluate which aspect of the financing has the most important effects on the bid price. The renewable project finance model is a relatively old model that includes a lot of instructions with data verification labels and comments.
23. Carrying Charge Analysis Revised.xlsm
Renewable Project Finance Model with Instructions.xls
Tax Equity Analysis for U.S. Renewable Energy Projects
The files below are examples of models with tax equity flip structures using alternative methods of determining the investment of tax equity investors. A tax equity structure works as a partnership where cash flows are split between alternative parties. Much of the analysis involves efficiently a cash flow waterfall where the cash flow is split in a different manner before and after the flip. To compute the timing of the flip you can set up an account that maintains the present value of the first senior facility and accumulates the cost of funds at the flip rate. This can be accomplished by using sub-totals in the cash flow statement and then using the MIN function with the balance of the account that maintains the value of the senior securities. The files include some some discussion of how the flip structures work and it demonstrates how to evaluate the flows and risks to alternative investors. The file demonstrates that you can use accounts similar to cash sweeps with subordinated debt to establish flip timing and cash flow waterfalls. The key is to use a MIN function and test the tax equity flip in two steps.
Tax equity structures can be affected by limitations on the tax deductions if an equity capital falls to a value below zero. There are different ways to define capital and the capital must be above zero from both tests in order for deductions to be allowed for the senior tax investor. If have seen a lot of absurd models that over-complicate the tests and have macros or circular references. If the cash flow waterfall is structured correctly and the timing is consistent the constraints can be modelled in a structured manner. I have not put up videos for this issue yet.
Tax Equity Analysis.xlsm
Solar Annual Model.xlsm
Infrastructure Models and Project Finance with Traffic Risk
Infrastructure projects such as toll roads, bridges, subway systems, ferries and tunnels can generate revenues from traffic in a user-pay system, fixed availability payments or some combination. These projects can be public-private partnerships with various sorts of funding, guarantees and other items from the government. As traffic has a ramp-up period and generally grows with the economy thereafter and toll rates can increase with inflation, the cash flow pattern can have a pattern with high growth rates. Various different forms of sculpting, demand notes and other features can be used to resolve low cash flows in early years. The files below demonstrate and the associated video present alternative ways to represent models of project finance of toll roads. The first model is from a HBS case study of the A2 toll road in Poland that was originally structured to take traffic risk. (Later it was converted to shadow tolls and I believe the traffic estimates were optimistic). The second file is for a PPP model and demonstrates issues with a toll road in Ghana that includes how the traffic studies are developed.
Tollway Example.xlsm
PPP Toll road Model.xlsm
Project Finance Model with Commodity Price Risk and Monte Carlo Simulation
The file and video below illustrates how you can incorporate Monte Carlo simulation into a project finance model. The model is set-up to be a model with commodity price risk. Once the project finance model is built, a time series analysis with volatility and mean reversion parameters is added to the model. The Monte Carlo simulation works with a little VBA code that re-calculates the price and by consequence also the cash flow for many simulations. The Monte Carlo simulation can be used to judge the efficacy of various structuring issues including cash flow sweeps and cash flow traps. The model below and the video demonstrates how you can incorporate either scenario analysis and/or Monte Carlo analysis to measure credit risk associated with commodity prices. The file shows that at least in theory, you can use the PLCR and LLCR with Monte Carlo simulation to measure the probability of loss. This can be adjusted for cash sweeps and other features. I am completing a page
Commodity Price Model with Monte Carlo Simulation.xlsm
Credit Analysis of Project Finance with Methane/Diesel Plant
The below file demonstrates how to use a model to evaluate credit analysis in the context of project finance. The model includes a bridge loan and alternative prospective capital investments in two phases. The credit analysis and the model includes currency risk because the bridge loan is in local currency whilst the revenues are received through a contract that is fixed in USD. This means different inflation rates must be forecast and evaluated as well as the other varibles. If you are interested in currency risk, this may be a file to review.
Credit Analysis in Project Finance.xlsm
Multiple Currency Analysis and Development Analysis in Solar Plant Model
The file below demonstrates how to systematically evaluate the effect of different cash flows in different currencies using a solar model example. The model demonstrates that the starting point is to evaluate inflation rates in different currencies and exchange rates. This files uses purchasing power parity as a starting point and then allows deviation from the parity exchange rate. The exchange rate could also be entered using future exchange rates. To implement the model, the revenues, the financing needs, the debt flows and other items are computed in dual currencies and different currencies can be chosen.
Currencies in Solar Model.xlsm
Wind Project Finance Models
The files below show how to create project finance models that evaluate wind projects. The first model includes different wind production scenarios and detailed operating expense calculations (I began by evaluating other models and it has some redundancy and bad equations). As with other models, these files include resolution of circular references related to financing and sculpting. The second file is intended to show some issues that can arise in project financing of off-shore wind. The model includes resolution of circular references, re-financing and the return that can be generated by selling a plant after the risk of the plant declines. The re-financing assumptions allow alternative timing, DSCR and credit spread assumptions.
On Shore Wind Model Example.xlsm
Off Shore Wind Project Finance Model Example.xlsm
The file below is a project model for a representative wind farm in Brazil. It includes financing techniques that are associated with BNDES lending that do not correspond to the tariff structure. I call some of the adjustments made to O&M contracts in this reverse sculpting. This happens because the debt structure is given and the cash flows are adjusted to correspond to the financing rather than the other way around. The file includes functions that resolve circular references associated with financing and the file also demonstrates the modelling of an Equity Bridge Loan during the financing period.
Brazil BNDES Structure.xlsm
Project Finance Model with Commodity Price Risk and Monte Carlo Simulation
The models below are intended to demonstrate issues associated with hydro. Hydro can have a lot of volatility in water flows but also a lot of mean reversion. It demonstrates the importance of re-financing and cash sweep structures in situations with high cash flow volatility.
Hydro Example with Flexible Debt Structuring.xlsm
Hydro Model Complete.xlsm
Evaluating Actual Relative to Forecast in Project Model
The file below demonstrates how to deal with the problem of receiving actual data after a project is placed in service. One method involves typing over existing numbers — this is horrible. A much better approach demonstrated in this model is to put actual data in a separate sheet. After the data is included in a separate sheet, the actual can be compared to the forecast. A third analysis is also required that develops a new forecast though adjusting the forecast data for actual numbers. You really then need a second model as the original project finance model is out of date on the day of commercial operation.
Actual and Forecast.xlsm
| Subject | Excel File | Video Link | Chapter Reference | Page Reference | ||||||
| Navigating and Understanding a Model | IPP Model | https://www.youtube.com/watch?v=fDuNgdTETF4 | Chapter 4 | 31 | ||||||
| Using the IPP Model | IPP Model | https://www.youtube.com/watch?v=QUjc8kyuDoE | Chapter 4 | 31 | ||||||
| Structuring and Risk Analysis | Off Shore Wind Model | https://www.youtube.com/watch?v=eQzUJL11Eyo | Chapter 39 | 491 | ||||||
| Project Finance Commodity with Monte Carlo | Commodity Price Example | https://www.youtube.com/watch?v=rOVNH7TWOaU | Chapter 21 | 278 | ||||||
| Taxes and Financing | Annual Solar Model | https://www.youtube.com/watch?v=MHoI3XzT9Xg | Chapter 11 | 135 | ||||||
| Re-Financing and FAST | Tollway Model Example | https://www.youtube.com/watch?v=I6FWvFvGSZ4 | Chapter 44 | 44 | ||||||
| Analysis of Solar Production | Solar Model Example | https://www.youtube.com/watch?v=NRgwZPewdgs | Chapter 40 | 497 | ||||||
| Alternative Technologies | Renewable Model with Instructions | Chapter 4 | 31 | |||||||
| Problems with Copy and Paste Macros | Quezon Coal Plant Case | https://www.youtube.com/watch?v=Tsm_DTdwFiM | Chapter 39 | 494 | ||||||
| Tax Equity and Flip Structures | Tax Structuring Model | https://www.youtube.com/watch?v=vnwju0-LRtU | Chapter 11 | 135 | ||||||
| Model Structure and Circular Reference Problem | On Shore Wind Model | https://www.youtube.com/watch?v=c9HVevcKNQg | Chapter 41 | 515 | ||||||
| Building a portfolio of Projects | Solar Rooftop Model | https://www.youtube.com/watch?v=q9Jny30z2GU | Chapter 47 | 583 | ||||||
| Overview of Solar Model | Solar Model Example | https://www.youtube.com/watch?v=NRgwZPewdgs | Chapter 4 | 31 | ||||||
| Resolving Circular References in Solar Model | Solar Model Example | https://www.youtube.com/watch?v=jwS-SlLOmkM | Chapter 39 | 494 | ||||||
| Creating Tornado Diagrams in Solar Model | Solar Model Example | https://www.youtube.com/watch?v=A4m_b8t2xW4 | Chapter 18 | 231 | ||||||
| Adding Inflation to a Project Finance Model | Solar Model Example | https://www.youtube.com/watch?v=A4m_b8t2xW4 | Chapter 8 | 85 | ||||||
| Including Depreciation on New Expenditures | Solar Model Example | https://www.youtube.com/watch?v=7c1tcsY6BJI | Chapter 9 | 99 | ||||||
| Cleaning up a model with Table of Contents etc. | Solar Model Example | https://www.youtube.com/watch?v=2fKnIb3yXJA | Chapter 5 | 53 | ||||||
| Using Models to Create Templates | PPP Tollway Model | https://www.youtube.com/watch?v=XXXxK-be5ik | Chapter 8 | 85 | ||||||
| Working Capital in Project Finance | PPP Tollway Model | https://www.youtube.com/watch?v=1BknIdvWVuU | Chapter 9 | 91 | ||||||
| Scenario Analysis in PPP | PPP Tollway Model | https://www.youtube.com/watch?v=6BSU7PI72rI | Chapter 17 | 207 | ||||||
| Tornado Analysis in PPP | PPP Tollway Model | https://www.youtube.com/watch?v=8ytj4YkcMOs | Chapter 18 | 232 | ||||||
| Taxes and Delay in Project Finance Model | Brazil BNDES Structure | https://www.youtube.com/watch?v=TriiaHemB8s | Chapter 9 | 99 | ||||||
| Audit Tests and Audit Page | Brazil BNDES Structure | https://www.youtube.com/watch?v=8hjfLdQTJDU | Chapter 5 | 53 | ||||||
| DSCR, LLCR and PLCR | Brazil BNDES Structure | https://www.youtube.com/watch?v=zighud2F9bI | Chapter 8 | 85 | ||||||
| Equuty Bridge Loan | Brazil BNDES Structure | https://www.youtube.com/watch?v=uqccAvWeQTo | Chapter 43 | 550 | ||||||
| Creating Function to Solve Circular Reference | Brazil BNDES Structure | https://www.youtube.com/watch?v=ehCpew9KZ20 | Chapter 40 | 501 | ||||||
| Working with O&M Expenses to Optimise Struture | Brazil BNDES Structure | https://www.youtube.com/watch?v=rpG3NGBV7xA | Chapter 44 | 44 | ||||||
| Street Lights PPP EBITDA | Street Light PPP Model | https://www.youtube.com/watch?v=L–eUUt-8yo | Chapter 6 | 55 | ||||||
| Street Lights PPP Depreciation and Taxes | Street Light PPP Model | https://www.youtube.com/watch?v=trKG59InIB4 | Chapter 9 | 94 | ||||||
| Street Lights PPP Financing | Street Light PPP Model | https://www.youtube.com/watch?v=RG7tjOPyUhA | Chapter 10 | 119 | ||||||
| Street Lights PPP DSRA and Summary | Street Light PPP Model | https://www.youtube.com/watch?v=I_Epp1t3Jfw | Chapter 43 | 550 | ||||||
| Interest Income on DSRA in Detailed Model | PPP General Model | https://www.youtube.com/watch?v=s8lGryt06S4 | Chapter 43 | 548 | ||||||
| Using Generic PPP Model | PPP General Model | https://www.youtube.com/watch?v=PyU_0DB6Ac4 | Chapter 4 | 30 | ||||||
| … | …………………………………………………………………………. | …. | …………………………………………………………… | …. | ……………………………………………………………………………………….. | …. | …………………………….. | … | ………………………. |