This page includes files and videos that we are using in the weekend sessions. The weekend sessions are one weekend day for two hours with some flexibility. We start from basic time lines and operating cash flow for a single project. Then we move to more and more complex issues. For each session I try to include some theory to begin the discussion and then we practice some excel so that you can learn the fundamentals of modelling. If you consider yourself and expert and too good for some of the subjects, I will try to convince you some other ways to think about various modelling issues. This means that the modelling may be easier for beginners who do not have to unlearn modelling techniques. I do record sessions, but I do not find these too helpful. I think the summary videos for the sessions are more useful.
Files and videos for the first session
In the first session I have tried to convince people of issues with time lines. In particular I show that IRR’s are growth rates where the growth includes re-investment of cash at the IRR itself. This results in some distortions where if there is a high IRR, the value of cash flows far into the future is low because the out year cash flow is discounted at the IRR itself. To demonstrate issues like this, I use flexible time lines which are part of any financial model. For a project finance model I demonstrate the importance distinguishing between the construction period and the operating period. I also want to show the effect of different cash flow on the IRR and to do this I introduce the concept of an InputC sheet. For the first session, we started from a blank model and then worked through timelines without the ridiculous complicated equations that are not necessary (the second video shows how you can incorporate the timelines in more complicated situations with partial year construction and partial year operation). The excel file that has been completed in the first session is attached to the blue button below.
.
.
.
.
Files from the second session
In the second session we extend the file from the first session to include debt. We begin with a simple structure and illustrate the fundamental concepts of a cash flow waterfall. As part of the session we will compute and more importantly understand the LLCR and PLCR ratios and compute the equity IRR. In the second session we will not waste time on typing in titles and focus on the equations and the model structure. The file will be used for Monte Carlo analysis in section 3.
.
Excel File on Fundamental Debt Structuring with Blanks that will be Completed in the Session
.
.
I will continue to make videos that recap each session. I go a little more quickly than the course and of course you can pause the video if you want work through the case.
.
Files and Videos for Session 3 – Completion of Ratios, Sensitivity with VBA, Monte Carlo Simulation
The third session will move to sensitivity and Monte Carlo simulation. We will do some VBA to complete the analysis. The file with blanks to complete the session is attached to the button below. I have also included a solar project finance model that uses the same kind of format, time lines, InputC and InputS and other things as the file we have been working on. The solar model is attached to the second button below. I have made an entire video of the third session because I think there was more discussion about the theory of how volatility and mean reversion affect the target DSCR to achieve a BBB- rating or equivalent.
.
.
.
.
.
.
Excel File with Completed Equations and Completed Sensitivity for Session Three with Monte Carlo
.
Session 4 – Valuation of Projects, Transaction Analysis and Pro-Forma M&A Model
.
In the fourth session we cover subject of valuation, constructing a transaction analysis with possible re-financing and accounting issues and finally a pro-forma analysis with in an M&A context. I emphasize the theory behind project finance valuation which depends entirely on the discount rate applied. I suggest that a big issue in valuation is the difference between the initial discount rate and the discount rate when the plant is sold. We will show that traditional corporate finance ratios are not useful and go even further to suggest how these ratios are distorted in other contexts. The session will cover basic debt sculpting and how you can quickly develop financial statements.
.
.
Power Point Slides with Theory Discussion and Introduction to Various Sessions for Weekend Course
.
.
- Step 1: Start at end after stable period with required return for buyer
- Can get data from transactions
- If not can add a little bit to the interest rate
- Step 2: Decide on a reasonable holding period
- Should have some history
- The shorter the period the higher the returns
- Step 3: Make an estimate of how much higher an IRR you need for taking resolved risks
- Risks are development risks, construction over-run and delay, volume estimation, operating and maintenance levels etc.
- To think you can somehow quantify these risks with the Capital Asset Pricing Model and Beta is ridiculous
- For example, add 4% for initial risk to get the target equity IRR
- Step 4: Run goal seek for price with target IRR
- Go to the portion of the model with the IRR after the plant sale and the holding period
- Compute the NPV of the holding period cash flow using the target IRR from Step 3
- Use goal seek to find price to meet the target return that covers these risks
.
.
Session 5 – Debt Sculpting, Financing a Transaction and Re-Financing
.