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
.
In the fifth session we continue the discussion of a transaction and we add debt financing to the transaction. We use debt sculpting to size the debt of the transaction. The update point slides are shown attached to the blue button below.
.
Power Point Slides with Theory Discussion and Introduction to Various Sessions for Weekend Course
.
The key sculpting formulas are summarised below.
•Basic Formulas
- DSCR = CFADS/Debt Service
- Debt Service Target = CFADS/DSCR
- PV of Debt Service over Repayment Period = Initial Debt Balance
- Debt Service = Interest + Repayment
- Repayment = Debt Service – Interest
- If you have annual or semi-annual cash flow and constant interest rate you can use the NPV formula
•More Advanced Formulas
- DSCR = CFADS/Debt Service Including Fees
- Debt Service Including Fees = CFADS/DSCR
- Repayment + Interest + Fees = CFADS/DSCR
- PV of Repayment + Interest = Debt
- Debt Balance = PV of CFADS/DSCR – PV of Fees
If interest rates not constant, use an interest rate index (monthly or semi-annual)
- Multiple Issues: DSCR = CFADS/(DS1 + DS2 + DS3)
- DS1 = CFADS/DSCR – DS2 – DS3
- If DS2 and DS3 Given, PV of CFADS/DSCR – DS2 – DS3 = Debt Balance
.
.
Session 6 – Debt Sculpting Complications; Negative Repayment
.
In this session we finish the discussion of debt sculpting and address the issue that occurs when debt service from the formula:
Debt Service = CFADS/DSCR produces a lower debt service than the interest expense.
The most common (and costly) solution is to try and set up a cash reserve account. This can be painful in models because you cannot simply evaluate the amount of negative payment. Instead, the amount set-up in the reserve must be adequate to provide the target DSCR. In addition there can be a problem with the repayment where the negative repayment is removed. If the negative repayment is eliminated, the repayment will increase and the tenure of the debt will be shorter.
•Start with the basic formula:
- DSCR = CFADS/DS
- DS = CFADS/DSCR
- DS = Interest + Repayment
•It is possible that in this formula the repayment will be negative when interest is greater than debt service
•In this case there are a couple things you can do.
- You can set up a cash reserve account to cover the interest
- You can use the cash reserve withdraws to cover the repayment
- In this case there is higher repayment (non-negative) and the tenure of the debt declines
- If the CFADS includes only the cash for repayment, the DSCR is below the target
•Solution
- Amount withdrawn from the reserve account uses the formula
- CFADS = EBITDA – Taxes + Amount Withdrawn
- Target CFADS = Debt Service x DSCR
- Debt Service = Interest Expense
- (EBITDA – Taxes + Amount Withdrawn) = Interest Expense * DSCR
Amount Withdrawn = Interest * DSCR – EBITDA + Taxes
The blue button below is attached to the completed file that is referred to in the video at the end of this section. You can experiment with this file and see how the method produces the target DSCR as well as a debt size that results in a zero balance at the end of the debt tenure. You can also see problems with the iteration button with a large model — the goal seek does not work and the model can become very unstable. The video describes how to create a check box to turn the iteration button on and off.
.
.
If you want to see how I have done this and then try to do the exercise yourself with better methods you can work through the exercise file that has blanks that are in yellow. The video uses this file to illustrate how to create a debt structure that results in the target debt service coverage and also results in repayment of the debt consistent with the debt tenure. This involves computing debt service with CFADS that includes reserve withdrawls and CFADS without reserve withdrawls, computation of the reserve using interest expense * DSCR – EBITDA and debt sizing using a goal seek with the final closing balance being zero.
.
.
In the video I use some clips from the course and I correct some of the mistakes I made in the course. Hopefully with this will allow you to work through the exerice and more fully understand the ideas of solving the problem (maybe you will find a more elegant way to do this).
.
.
.
Session 7 – Consolidating Projects to Model a Corporation
.
To begin the discussion I note that any corporation is a portfolio of different investments. The investments could be factories, advertising, an internet website, investments in R&D, training of employees and many other things. The way I think about this portfolio of investments is a family tree where the family has a history and may last for many generations into the future. If we for some reason want to value the family we would need to not only value the current generation, but also future generations that will be there long after we are gone.
.
.
.
The power point slides I use are below
.
Power Point Slides with Theory Discussion and Introduction to Various Sessions for Weekend Course
.