This page includes project finance related excel files and videos that allow you to learn how to build a project finance model. Whilst I can tell you without any question at all that in-person courses are much, much, much better than on-line videos as a way to learn, I have included sets of videos and exercises that allow you to understand various basic and advanced issues. In general, all of the project finance model exercises are arranged to correspond to the structure of a project finance model where timing is developed first, followed by analysis of operations, capital expenditures, revenues and operating expenditures (this matches the contract structure of project finance with an EPC contract, O&M contract and Revenue [e.g. PPA] contract). The difficulty in project finance models often arises from financing and many of the exercises deal with financing issues including cash flow waterfalls, debt repayment structuring, debt sizing, interest during construction, fees, reserve accounts, cash flow sweeps and covenants.
The files and videos are arranged by different subjects and different levels of complexity. Separate sets of videos and analyses are presented for:
(1) REVISED COURSE for Building a standard project finance model from A-Z;
(2) Building a rooftop project finance model where portfolios of projects must be analysed;
(3) Dealing with circularity and other issues that can arise from sculpting debt repayments in a project finance model;
(4) Evaluating cash flow waterfall issues that arise when a monthly model is built and debt repayments occur on a semi-annual or a quarterly basis;
(5) Evaluating project finance and circularity issues associated with the construction phase of a project (not complete);
(6) Constructing a structured finance model (a close cousin of a project finance model);
(7) Analysing other project finance issues such as IRR/ROI reconciliation, flip structures, advanced timing.
For each of the subjects below video links and files that you can use in working through the issues are included. In most (not all) of the lessons, there is both a completed version of the file and a file or sheet that has blanks for you to fill in. For some of the exercises related to to project finance model exercises that are associated with sculpting, cash flow waterfalls and circularity resolution, if you can fill in all of the blank exercise, you should be considered a very good modeller.
The on-line lessons are free which may be a really bad idea for me. I have been told that if I charged money for the courses you would think they are more valuable. This is complete rubbish and I expect that you can get a lot more out of these exercises than other companies that spend a lot of money on marketing and being flashy. But, if you have completed exercises and you want me to verify that you really are a good modeller I will do this for a modest fee. If you want to be registered as a modelling expert that will be published in the website and verified for your employment, send me an e-mail to edwardbodmer@gmail.com.
Revised Lesson Set 1: Building a Standard Project Finance Model
After I published my exciting novel on project finance modelling, I made a lot of videos to go along with the videos. I did not do this in a professional manner — too much dog barking in the videos and too much swearing. Most importantly, the videos did were difficult to follow, they did not have any real theory as to why you are following various procedures and they did not have examples of what not to do. After various comments about background music, quality, unprofessionalism etc. I have made a set of revised videos. These videos and the associated excel sheets and the associated power point slides are presented below. I have included advanced issues as well as basic issues in these videos.
If you are starting out in modelling and have not yet made a project finance model, the files and videos below are intended to allow you to see the project finance structure, the importance of timing assumptions, the essential nature of separating operating cash flows from financing cash flows and other issues including cash flow waterfalls. The set of videos and files also introduce you to some difficult financing issues including circularity associated with funding during construction. Files associated with the videos are shown first followed by video links that walk through the various lessons.
If you fill in all of the exercises and send me the files along with a small fee, I will publish your name on my website so you can show it to your boss or your future employer. I will also get you an official badge. An illustration of how the models work is shown below. The yellow tabs in the excerpt show the items to fill in. The completed sheets are next to the yellow tabs (I hope I did not make mistakes).
The first set of videos are associated with a file named david lee.xlsm that is included below. The videos are supposed to be long because I am taking you through a complete model. Later on, I will make some really short videos on a “backpack” that can help you in modelling. The first four videos below do not even include debt, but go through the financial modelling, timeline and structure of a model which are essential ideas.
David Lee.xlsm
A-Z Modelling Course.pptx
The second set of videos complete the model with no debt and begin adding debt to the models. The last video in this section begins to add a circular resolution template to the model. This is an important idea in project finance where you would like to maintain flexibility in the face of natural circular references.
A-Z Part 2 Circular Exercise.xlsm
Circular Mirror Template.xlsm
I promise, I will finish this soon
The third set of videos continue on tricky issues associated with debt. The first video in this set complete the model with no debt and begin adding debt to the models. The last video in this section begins to add a circular resolution template to the model. This is an important idea in project finance where you would like to maintain flexibility in the face of natural circular references.
Lesson Set 2: Leaning from Project Finance Models that are not Flexible, Not Accurate, Not Structured and Not Transparent
When using case studies to study risks and project finance structure, I think you can learn a lot from cases that were originally thought to be good business strategy or well structured and became dismal failures. Examples are Dabhol and Petrozuata. Similarly, a different way to learn project finance models is to see what not to do. This may be somewhat less boring than working through each of the detailed exercises.
Winson Churchill said something like — success is going from one failure to another without loss of enthusiasm: i.e. learn from mistakes and keep going. I think you can learn a lot more from looking at ways not to do thing rather than following the boring instructions above. So, I have made videos and provided files on what not to do in making financial models. Looking at a badly structured model and fixing the model is demonstrated in the videos and the files below. (I made this because I was a little angry after working for a while on the model and then not being thanked).
Basic Bad Solar Example.xlsm
Financial Model Re-formatted.xlsm
Lesson Set 3: Comprehensive Sculpting and Circular Reference Resolution
The third lesson set describes a host of issues associated with debt sculpting in project finance (maybe you can think of this as a lesson in art). Sculpting debt and sizing debt together with accounting for interest income on a debt service reserve account, fees on letters of credit, multiple debt issues, income taxes with interest expense deductions, alternative timing of operating and financing cash flows, consideration of DSRA changes as cash flow available to pay debt service and other issues. To effectively and artistically solve problems with these issues I think you need two things. First, you should understand equations for sculpting (not only the basic sculpting equations of Target Debt Service = CFADS/DSCR and Debt at COD = NPV(Interest Rate, Target Debt Service). There are different equations for changing timing, reflecting multiple debt issues, reflecting debt fees, and incorporating changes in the DSRA. Second, because of multiple circular refereces that arise from taxes, interest income, fees and changes in the DSRA I think that you should use my method of functions rather than clusmsy copy and paste macros. This will make you scuppting much more elegant from an artistic perspective. This lesson set walks through very basic sculpting exercises to modelling challenges that I think are quite difficult.
The lesson begins with fairly simple cases without taxes, letter of credit fees or interest income from a debt service reserve account. These lessons use the fact that target debt service = cash flow/DSCR and the present value of debt service equals the amount of the debt. After working through mathematical equations for sculpting, more complex items are addressed. An initial issue is how to use sculpting if a debt constraint applies. Other issues include how to apply models where monthly operating cash flows are used and the debt repayments are semi-annual. Some of the most difficult issues involve interest income and fees on letters of credit. The various lessons evaluate pros and cons of copy and paste macros relative to functions for solving circularity that arises with sculpting. The different lessons build up to a final case includes options for funded DSRA accounts, net operating loss, bullet repayments and other issues. If you can work through all of the exercises in this lesson I think you can be considered a genuine top notch modeller. I have completed just about all of the videos for this lesson but they are in the process of editing the files.
Sculpting Course Final.xlsm
Comprehensive Sculpting Analysis
Videos for sculpting lessons are divided into two sections. The first section is the comprehensive set of lessons that begins with a simple case and moves to complex and quite difficult issues. All of the videos in the top set refer to the file name “sculpting course final”. The second set of videos is a bit redundant and includes my earlier attempts to explain sculpting in a less organised manner. If you have the google drive, you can find the files in a subdirectory of Chapter 1 as shown below.
Subject in Structured Sculpting Course | Sheet Name in Sculpting Course File | Video Link | ||
Sculpting Course Overview — Introduction to Course File, Videos and Exercises | Overview | |||
Basic Sculpting with No Tax, No DSRA, No Bullet | NPV-Basic | https://www.youtube.com/watch?v=kmY9wZPa1zI | ||
Use of LLCR with Debt Capacity Constraint | Debt to Cap and LLCR | https://www.youtube.com/watch?v=Yq3t3veRZdE | ||
Multiple Debt Issues and Sculpting | Multiple Debt Issues | https://www.youtube.com/watch?v=Qd480DSUTHk | ||
Sculpting Discount Rate Adjustment with Monthly Model | Sculpting and Timing | https://www.youtube.com/watch?v=RZr2a7PEOiQ | ||
Sculpting and Debt Fees | Sculpting and Fees | https://www.youtube.com/watch?v=kAZczGh4U9w | ||
Sculpting DSRA and Interest Income | Interest Income on DSRA | https://www.youtube.com/watch?v=caiP0UpLiwo | ||
Sculpting and Fees on Letter of Credit for DSRA | LC Fees and Copy/Paste Macro | https://www.youtube.com/watch?v=gWoOhZM4-2A | ||
Function for LC Fees or Funded DSRA with Interest Income | LC Fees, Interest Income | https://www.youtube.com/watch?v=7HZA9EDcBaw | ||
Sculpting with Income Taxes | Sculpting and Taxes Function | https://www.youtube.com/watch?v=Gdbc6-Vx1cw | ||
Sculpting with Taxes Adjusted for Depreciation | Taxes and Depreciation | https://www.youtube.com/watch?v=4xiIPJgGfac | ||
Sculpting with Bullet Payment and DSRA used as final payment | Sculpting with Bullet Payment and DSRA | https://www.youtube.com/watch?v=MypbC_Kc_qE | ||
Changes in DSRA as Cash Flow Available for Debt Service | DSRA Movements | https://www.youtube.com/watch?v=PfytvaUGGjs | ||
Other Sculpting Videos | File for Other Sculpting | Video Link | ||
Modelling Debt Sculpting – Simple Introduction | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=gAlIIzgIUyc | ||
Debt Sculpting – Changing Interest Rates and Varying Tenor | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=bVfFiJ5WUeM | ||
Debt Sculpting with Interest Income on DSRA | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=Z5B6r-oelBo | ||
Sculpting and Income Taxes – Copy and Paste Method | Exercise 16: Debt Sculpting – Basic | https://www.youtube.com/watch?v=91Mzu0jhM5U | ||
Sculpting and Income Taxes – Function Method | Exercise 17: Debt Sculpting – Advanced | https://www.youtube.com/watch?v=E4TT6tymIVE | ||
Sculpting with Negative Cash Flows and Pct of Time Function | Negative Sculpting.xlsm | https://www.youtube.com/watch?v=YsmN_KFKVeo | ||
Sculpting with Negative Cash Flows and Constraints | Negative Sculpting 1.xlsm | https://www.youtube.com/watch?v=cZf9Cr_gqOE | ||
Sculpting and Leverage Constraint | Sculpting with Constraint.xlsm | https://www.youtube.com/watch?v=YforXVLqfSA |
Files associated with Lesson Set 3: Comprehensive Sculpting Analysis
Exercises associated with sculpting are included in a single comprehensive file which is the first file named “sculpting course.” The file which is the only one you really need includes many pages that work through the various different sculpting issues. The file has a lot of different tabs that move from simple cases without circular reference problems to more complicated cases. For the various tabs, I have put a second tab for you to try and complete the exercise. As shown below, the completed exercise shown on the video is on one sheet and the exercise is shown on the subsequent sheet with a yellow tab. Key take away points for each part of the sculpting analysis are shown at the top of exercise page. If you can fill in each of the exercises including the functions and macros you should feel really good. Other files are a mishmash of different sculpting exercises and files that I have constructed over the years that are probably redundant but may help you out a bit.
Sculpting Course Final.xlsm
Sculpting with Negative Cash Flow 1.xlsm
Exercise 14 – Debt Sculpting.xls
Sculpting with Negative Cash Flow.xlsm
Sculpting and Leverage – Simple.xlsx
Exercise 16 – Basic Sculpting.xlsm
The videos use a few key formulas to derive debt repayments and the level of debt from a target DSCR that are described below:
Basic Formulas
One of the main ideas about the repayment process in project finance is that the modelling is much more effective when you combine formulas with other excel techniques. If you try and solve these things with a brute force method that uses a copy and paste method or goal seek things will get very messy. Formulas used for repayment and debt sizing are listed below: The fundamental two sculpting formulas are:
(1) Target Debt Service Per Period = CFADS/DSCR
(2) Debt Amount at COD = PV(Interest Rate, Target Debt Service)
Non-Constant Interest Rates
However this is by no means the only formula you should use when working on repayment. In cases when the interest rate changes, a simple present value formula cannot be used. Instead, an interest rate index can be created that accounts for prior interest rate changes as follows:
(3) Interest Rate Indext = Interest Rate Indext-1 x (1+Interest Ratet)
(4) Debt Amount at COD = ∑ CFADSt/Interest Rate Indext
Use of Synethetic LLCR with Target Debt to Capital when the Debt is given or the Target DSCR is below the Minimum DSCR in the Term Sheet.
A typical situation in sculpting is where the debt to capital is given, but repayments are driven by sculpting. In this case you cannot input a DSCR which itself determines the debt size. Instead, you can use the idea the with sculpting, the LLCR = DSCR. Here the issue is how to compute sculpted debt repayments when debt is sized with the debt to capital ratio and the DSCR is not given. When the Debt is Sized by Debt to Capital the LLCR = NPV(CFADS)/Debt can be used to size the debt. Formulas in this case include:
(5) Target Debt Service = CF/LLCR
(6) LLCR = NPV(Interest Rate, CFADS)/Max Debt from Debt to Capital
(7) DSCR Applied = MAX(Target DSCR,LLCR with Max Debt)
Multiple Debt Issues (Bullet Repayment)
Another issue arises when there are multiple debt issues and one of the debt issues (defined as Last or the sculpting capture issue) is used for sculpting. In this case the basic formula can be adjusted and the process if straightforward. You can start with the DSCR formula and derived the debt service for the last formula. Note that if you are sculpting two debt facilities at the same time and these facilities have different interest rates and different tenures, then the process is difficult because for the NPV formula you need a common interest rate.
(8) DSCR = CF/(Other DS + Last DS)
(9) Other DS + Last DS = CF/DSCR
(10) Last DS = CF/DSCR – Other DS
(11) DS for Last Facility = CF/DSCR – Other DS
Note that if there is a bullet repayment at the end of the debt term (say 15% of the repayment), then the bullet repayment can be considered a separate debt facility. So, if the bullet repayment is 15% then the PV of the repayment is a separate facility with interest over time etc. The NPV of the remaining debt should subtract the interest and the repayment on this separate debt. Since the bullet repayment affects the amount of sculpting and the NPV of the debt multiplied by 15% drives the bullet repayment, the bullet repayment causes a circular reference.
Discount Rates and Model Timing
When a monthly model is used but the debt repayment is semi-annual, the discounting can become more complicated. (Note that is is not a very common problem because you should usually put the timing in your model to correspond to the repayment dates of the debt). As usual, when you are working with interest rates you simply divide by the number of months in a period. However when you are discounting target debt service to arrive at the amount of debt, you need to use different discount rates. The adjusted equations for discounting the target debt service is shown below.
(12) Annual: Rate for Discounting in Semi-Annual Model = (1+Annual Rate/2)^(1/6)-1
(13) Monthly: Rate for Discounting = (1+Monthly/12)^(1/12)-1
Adjusting Sculpting Equations for Debt Fees
Debt fees such as the fee on a letter of credit is part of debt service. To include the fees in the sculpting equations, you should subtract the fees when you compute the net present value of debt, as the fees reduce the amount of debt service that can be supported by cash flow. This is just the same as deducting the interest to come up with the repayment. The fees reduce the amount of debt associated with CFADS compared to a situation without fees. Because the PV of debt service uses the debt interest rate without the effective rate that accounts for fees (which would be a higher interest rate), you can deduct the PV of fees at the debt interest rate and the debt schedule will work. To make the sculpting work you should also make the repayment lower by the fees as shown below:
(14) Repayment = CFADS/DSCR – Interest – Fees
(15) Debt = NPV(Interest Rate, Debt Service-fees) = NPV(rate, Debt Service) – NPV(rate, Fees)
A problem here is that the NPV of the debt depends on the fees, but the LC fees depend on the DSRA, which in turn depends on the size of the debt and the NPV. This is a clear circular reference. Note Debt Service in the above equation means debt service without fees and debt is reduced by PV of fees.
Adjusting LLCR for Debt Fees
Very often in sculpting, the debt is given and the repayments must be sculpted. When the debt is given, the fees affect the synthetic LLCR that is used to compute the debt service from the CFADS. In this case, the amount of repayment must be reduced because of the fees and the synthetic LLCR should be reduced. The sculpting analyses include calculation of the LLCR to evaluate whether the debt to capital constraint is driving the constraint. In this case the PV of CFADS is not the correct numerator for the analysis. Instead, the PV of the LC fees should be added to the denominator of the LLCR as follows:
(16) LLCR = PV(CFADS)/(Debt + PV of LC Fees), where
(17) Debt = Project Cost x Debt to Capital
Sculpting and Changes in the DSRA balance including Final Repayment
After working through letters of credit for the DSRA, taxes, interest income and other factors that cause difficult circular references, the final subject addressed is using the DSRA to repay debt. A similar result occurs when changes in the DSRA are included in CFADS. Incorporating these changes in a financial model without massive circularity disruptions can be tricky, but it can be solved by separately computing the present value of changes in the DSRA. Changes in the DSRA can be modelled using the following equations:
(18) Debt Adjustment = PV(Interest Rate, Change in DSRA/DSCR)
(19) Repayment = Repayment from Normal Sculpting + Change in DSRA/DSCR
Lesson Set 4: Incorporating Actuals in Project Finance
The fourth lesson set demonstrates how to incorporate actual data in project finance. The two excel files below demonstrate how to construct models that contain actual balance sheet and/or income statement data. The actual data is inserted in a new sheet and a historic switch (flag) is used to make the model flexible where actual data can be automatically updated as new data arrives. Closing balance of accounts is computed with a formula that is different depending on historic switch. During the historic period, the closing balance is the actual amount. After the historic period, the closing balance is the sum of the opening balanace and computed changes in the account.
One of the most important and difficult things to compute is the adjusted forecast that depends on the actual data. For the case where work in progess and the debt is computed from actual data, the forecast can be structured to meet contract amounts. The remaining amount to spend on construction is derived from the EPC and other contracts. The remaining amount of debt corresponds to the debt to capital ratio and the debt commitment. For using actual data in the construction period, tricky circular reference issues arise.
Construction with Actual and Forecast.xlsm
Actual and Forecast.xlsm
Actual and Forecast in Construction.pptx
Videos associated with Lesson Set 1: Building a Project Finance Model
This section includes my original attempt to work through project finance models that I began a few years ago. I hope my work is not like a circular reference were things go around and around, but instead gets better over time. This stuff is therefore largely obsolete. However if you are looking for something very basic there may be something useful and I have not deleted the videos or the excel files.
Subject in Project Finance Lesson | Name of Course File with Blank Exercises | Video Link | |||
Overview of Project Finance Model Structure | Exercise 1: Basic Project Finance Model | https://www.youtube.com/watch?v=C_v8cnNw1VM | |||
Model Timing – Basic with Monthly and Semi-Annual Payments | Exercise 2: Flexible Dates | https://www.youtube.com/watch?v=3BjZLMfmwag | |||
Structuring Inputs for Project Finance Model | Exercise 3: Project Finance Inputs | https://www.youtube.com/watch?v=vMRP5bzP4bw | |||
Working Analysis of Revenues, Expenses and Capital Expenditures in PF Model | Exercise 4: Project Finance Working | https://www.youtube.com/watch?v=Ds65g1DGbHM | |||
Tax Depreciation and After Tax IRR with Net Operating Loss | Exercise 5: Tax Depreciation and Project IRR | https://www.youtube.com/watch?v=yllIDpNNb1U | |||
Debt Sizing with Equal Installments and Completion of Model | Exercise 8: Incorporating Debt with Level Repayments | https://www.youtube.com/watch?v=b7OyQ3yuXOE | |||
Computation of Financial Ratio Statistics | Exercise 9: Project Finance Statistics | https://www.youtube.com/watch?v=yTNK7ohg3vg | |||
Debt Sizing in Level Payment Case with Goal Seek and Function | Exercise 10: Debt Sizing in in Project Finance | https://www.youtube.com/watch?v=kIg37mLGIUM | |||
Up-front Fees, Commitment Fees, Development Fees | Exercise 11: Fees in Project Finance Model | https://www.youtube.com/watch?v=BEg3SwBBtVE | |||
Debt Funding with Equity First Financing and EBL | Exercise 12: Funding of Construction | https://www.youtube.com/watch?v=iU-SBRzidbU | |||
Cash Sweep and Credit Statistics | Exercise 13: Cash Sweep in Project Finance | https://www.youtube.com/watch?v=qjmOXSFF564 | |||
Debt Service Reserve Account (DSRA) | Exercise 14: DSRA in Project Finance | https://www.youtube.com/watch?v=0tFgLkAXB5g | |||
Maintenance Reserve Account (MRA) | Exercise 15: MRA in Project Finance | https://www.youtube.com/watch?v=gOA-s_ZTt-Q |
Exercise Files associated with Lesson Set 1: Building a Project Finance Model
The files listed below are the associated with the project finance A-Z lesson. Unlike the case for other lessons where I put everything in different sheets of the same file, for the project finance lesson I created separate files. One sheet in the file is a completed model and another is a sheet with blanks that you are supposed to fill in. The first file is very simple, the second deals with timing and switches (some call masks or flags). The lesson then moves to computing pre-tax cash flow, depreciation and project IRR. After project IRR is established, the lesson covers financing. Financing includes project finance outputs, the five components of debt: 1 — the debt size; 2 — the debt funding during construction; 3 — the debt repayment; 4 — interest and fees between funding and repayment; and 5 — credit enhancements.
Exercise 1 Simple Project Finance Model.xlsm
Exercise 2 – Flexible Dates.xlsm
Exercise 3 – Project Finance Inputs.xlsm
Exercise 4 – Project Finance Working Analysis of Capex and EBITDA.xlsm
Exercise 5 – After Tax Cash Flow and Project IRR.xlsm
Exercise 8 – Project Finance with Debt Structure.xlsm
Exercise 9 – Project Finance Outputs.xlsm
Exercise 10 – Debt Sizing with Goal Seek.xlsm
Exercise 11 – Fees in Project Finance.xlsm
Exercise 12 – Funding of Construction.xlsm
Exercise 13 – Cash Sweep Exercise.xlsm
Exercise 14 – Debt Service Reserve.xlsm
Exercise 15 – Maintenance Reserve Account (MRA).xlsm
Lesson Set 5: Structured Finance and Project Finance
The fifth lesson set evaluates issues associated with structured finance. Structured finance involves the same cash flow waterfall issues with special purpose vehicles that are part of project finance modelling. This lesson walks through building a structured finance case along with the associated risk analysis. The final case includes options for funded DSRA accounts, net operating loss, bullet repayments and other issues. If you can work through all of the exercises in this lesson I think you can be considered a genuine top notch modeller. I have completed just about all of the videos for this lesson but they are in the process of editing the files.
Videos associated with Lesson Set 4: Building and Analysing Structured Finance Models
Subject in Structured Finance Analysis | File Associated with Structured Analysis | Video Link | ||
Creating Strutured Finance Model | CDO Exercise 1 | https://www.youtube.com/watch?v=6giYxjcmCUU | ||
Two Way Data Table with Structured Finance Model | CDO Exercise 2 | https://www.youtube.com/watch?v=iGnjWYW-zIc | ||
One Way Data Table with Structured Finance Model | CDO Exercise 2 | https://www.youtube.com/watch?v=Tip6i7pYEt4 | ||
Break Even Analyis with Strctured Finance Model | CDO Exercise 3 | https://www.youtube.com/watch?v=TSDpLt0-k3I | ||
Scenario Analysis with Structured Finance Model | CDO Exercise 4 |
Files associated with Lesson Set 4: Structured Finance Analysis
The following files are associated with the structured finance analysis. The pdf file explains some of the theory behind underlying the analysis and how models can be used. The main modelling task is to evaluate cash flow waterfalls.
CDO Exercise 1.xlsm
CDO Exercise 3 – Break Even.xlsm
CDO Exercise 2.xlsm
Structured Finance Modelling and the Financial Crisis of 2008.pdf
Lesson Set 6: Semi Annual Debt Repayment in Monthly Model and Cash Flow Waterfalls
This lesson involves calculation of debt service accounts and cash flow sweeps in a case where the model is monthly but the debt payments are semi-annual. I think some of the videos are somewhat long and detailed. But if you look carefully at the sheets you can see how cash flow is accumulated in a debt service account and then released to various other accounts depending on the debt repayment period, covenants and cash flow sweeps. For this lesson I did not include separate exercises when I was making the videos.
Videos associated with Lesson Set 5: Monthly/Semi-Annual Analysis and Cash Flow Waterfalls
Subject | File with Subject | Video Link | ||
Computing Repayment Months for Semi-annual debt | Monthly Debt | https://www.youtube.com/watch?v=Z-e1aQhXUUc | ||
Constructing Monthly Time Line with Switches for Repayment | Monthly Debt | https://www.youtube.com/watch?v=JYzSdN99chg | ||
Debt Service Account to Accumulate Cash Until Next Repayment | Monthly Debt | https://www.youtube.com/watch?v=sskJxtdbWBw | ||
Waterfall – Default on Debt Payment in Monthly Model | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=W4oAuEmrcZs | ||
Debt Service Reserve Account in Monthly Model | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=6FfRT3A9DkI | ||
Monthly Debt Analysis with Waterfall – Lock-up Covenant | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=pwn7Y0-A4mQ | ||
Monthly Debt Analysis with Waterfall – Cash Sweep Account | Monthly Cash Flow Waterfall | https://www.youtube.com/watch?v=1aSoJuG5s5I | ||
Roll-up to Annual for Monthly Cash Flow Waterfall | Quarterly and Annual Roll-up | https://www.youtube.com/watch?v=xPCCuzgmC1Y | ||
Roll-up to Quarterly for Monthly Cash Flow Waterfall | Quarterly and Annual Roll-up | https://www.youtube.com/watch?v=5PVn_gjQNOw |
Files associated with Lesson Set 5: Monthly/Semi-Annual Analysis and Cash Flow Waterfalls
Quarterly and Annual Roll-up.xlsm
Monthly Debt.xlsm
Monthly Cash Flow Waterfall.xlsm
Lesson Set 3: A-Z Rooftop Solar Model and Building Portfolios
This lesson set walks you through a rooftop analysis beginning with establishing a portfolio from multiple installations to evaluating financing and the ability of the project to repay debt. Parts of the model and lesson are pretty standard but there are quite a few tricky issues. If you make it all the way through all of the lessons you may learn something about both modelling and finance. As different solar facilities on rooftops are assumed to be constructed at different times, the model involves evaluation of how to compute cash flow and production items when rates change over the lifetime of a project such as degradation. This set of videos and files also introduce you various other tricky modelling issues including using sumproduct for S-curves with asset portfolios, inflating in alternative periods, accounts receivable and bad debt when outstanding accounts receivable are longer than a month, allocation to interest during construction and difficult financing issues in a monthly analysis where cash flows can be positive or negative. Video links associated with the lessons are shown below. The second section includes the file with different sheets that should be used together with the videos.
Solar Model
Subject | Sheet Name in Course File | Video Links | ||||
Rooftop Model Overview — Introduction to Course File, Videos and Exercises | Overview | https://www.youtube.com/watch?v=jEt_WlEh0e4 | ||||
Rooftop Model Inputs — Structure and Organisation | Assumptions | https://www.youtube.com/watch?v=lnCJwDBW0sM | ||||
Setting-up Portfolio Equations in Rooftop Model | Portfolio Set-up Exercise | https://www.youtube.com/watch?v=hgUuXpevw5E | ||||
Computing Capacity and Generation for Solar Facilities | Production Exercise | https://www.youtube.com/watch?v=WD-z71Bqtls | ||||
Degradation Part 1 – Vintage Array | Degradation Example | https://www.youtube.com/watch?v=wYuDK6GCps0 | ||||
Degradation Part 2 – User Defined Array Function | Array Functions | https://www.youtube.com/watch?v=KszpkgFpeX8 | ||||
Degradation Part 3 – Application of Function in Model | Degradation Calculation | https://www.youtube.com/watch?v=WKI2psTxZZI | ||||
Calculation of Capital Expenditures and SUMPRODUCT Trick | Capital Expenditures | https://www.youtube.com/watch?v=jIn7nzHnTe0 | ||||
Revenues, Periodic Inflation, Accrued Bad Debts | Revenue Calculation | https://www.youtube.com/watch?v=PKJdRR4AFbg | ||||
Working Capital in Monthly Model with Forward Calculations and OFFSET | Accounts Receivable | https://www.youtube.com/watch?v=47TzvXmLYjE | ||||
Operating and Maintenance with Function to Compute by Age | Operating and Maintenance | https://www.youtube.com/watch?v=crUa-TN1LKw | ||||
Compute Cash Flow, Depreciation and IRR and Operating Taxes | Pre-tax and After Tax Cash Flow | https://www.youtube.com/watch?v=dw_BpWmP0MQ | ||||
Overview of Financing | Financing | https://www.youtube.com/watch?v=Q7Y0c6McupQ | ||||
Financing 1: Cash Flow and Balance Sheet | Financing | https://www.youtube.com/watch?v=qSjUqIDYShw | ||||
Financing 2: IDC and Fees with Financial Statements | Financing | https://www.youtube.com/watch?v=XRHFRpFfqWY | ||||
IDC Accounting and Cash Balance Issues | Financing | https://www.youtube.com/watch?v=rpMs_wVknWY | ||||
Scenario Analysis | Scenario Exercise | https://www.youtube.com/watch?v=Pu-idlY-5lc |
File associated with Lesson Set 2: Rooftop Solar Model
Exercises associated with the rooftop model that you can complete are included in a single comprehensive file which is the first file named “rooftop solar course file” listed below. The file includes many pages that work through the steps of completing a model. The completed exercise discussed in the video is on one sheet named the monthly consolidated model. The exercise is shown on a later sheet (with a yellow tab). Key take away points are shown at the top of exercise page that you can work through. If you make an effort to fill in the entire model you should be proud of yourself and I will send you an official badge. I have included the generic macros that has SHIFT,CNTL,R and SHIFT,CNTL,C which are used a lot in the videos.
Rooftop Solar Course File.xlsm
Generic Macros.xlsm
Rooftop Solar.xlsm
Rooftop Solar.xlsm
Lesson Set 6: Selected Tricky Subjects in Project Finance Models
This lesson describes some difficult details of Project Finance models. Subjects include S-curves with delay or acceleration in construction; dealing with interest during construction, fees and the associated funding needs and construction; equity bridge loans; and, sculpting in situations where the amount of debt is given.
Videos associated with Lesson Set 6: Tricky Subjects in Project Finance Models
Miscellaneous Project Finance Exercises | Files with Exercises | Video Links | ||
Converting from Monthly to Quarterly and Monthly/Semi-Annual | Exercise 7: Advanced Timing | https://www.youtube.com/watch?v=G3dEapTxaqk | ||
Reconciliation of IRR and ROI | Exercise 6: ROI and IRR Reconciliation | https://www.youtube.com/watch?v=yD0LfAAoxPU | ||
Multiple Equity Owners in Project Finance | Exercise 22: Equity Allocations | |||
Multiple Equity Owners and Cash Flip | Exercise 23: Flip Exercise Pre-tax | https://www.youtube.com/watch?v=gFERDTjqtXg | ||
Find the flip period with fraction of year | Flip period.xlsm | https://www.youtube.com/watch?v=IqghCkfMg1I | ||
Create a payback function with period fractions | Payback.xlsm | https://www.youtube.com/watch?v=7Cmbh_XMnks | ||
Annual Fees and IDC | Annual IDC Fees.xlsm | https://www.youtube.com/watch?v=mUrS-bFk2SE | ||
End of Quarter Function | End of Quarter Function | |||
Half Year and Monthly Function | Half Year and Month Function | |||
Percent of Time Function | Percent of Time Function | |||
Complex Working Captial in Project Finance Model | Gas Plant Expample | https://www.youtube.com/watch?v=v5BXBpnFbXQ | ||
Computing the outages from periodic maintenance periods | Gas Plant Expample | https://www.youtube.com/watch?v=0Xt7p5z3o18 |
Files associated with Lesson Set 6: Tricky Subjects in Project Finance Models
Timing is one of the keys to project finance. Different stages have different risks and must be defined in a model. Exercise 2 demonstrates how to structure inputs in a basic project finance model (it is associated with the project finance inputs video). The second exercise addresses how to structure a model with flexible dates so that different construction periods and project lives can be included in the analysis. The flexible dates and the advanced timing exercises demonstrate alternative ways to deal with different timing structures.
Exercise 2 – Flexible Dates.xls
Exercise 7 – Advanced Timing Issues.xlsm
Exercise 16 – Periodic Exercise.xls
Exercise 19 -Timing Exercise.xls
Exercise 9 – Delay Exercise.xls
Exercise 10 – Liquidated Damages.xls
Corporate Finance involves comparing the ROIC with WACC while project IRR is computed in project finance. This file demonstrates how to reconcile the ROIC and the IRR over the life of a project (it is associated with the IRR reconciliation video). The project IRR is related to WACC (although it is not very useful to evaluate WACC in project finance) and should be computed after tax. After-tax IRR is computed without IDC but after depreciation on the base plant. This file demonstrates how to compute after-tax IRR in a project finance model (it is associated with the project finance after-tax IRR video).
Exercise 28 – IRR Exercise.xls
The exercises below address various issues associated with cash flow waterfalls. Exercise six addresses the issue of adding cash sweeps to a model. The first part of the exercise shows how to model a basic cash sweep and evaluate the benefits of the cash flow sweep when cash flow falls off a cliff. One of the key issues with cash sweeps is to not include the cash sweep in the debt structuring part of the model. The fourth exercise illustrates how to create a cash flow waterfall that includes a default on debt and repayment of the default. Including provisions for a default on debt allows computation of the break-even point for things like delay in construction and other items. It also enables the evaluation of debt IRR for alternative types of securities. Exercise eight describes various modelling issues associated with cash lock-up accounts.
Exercise 4 – Debt Default and Debt IRR.xls
Exercise 6 – Cash Flow Sweep.xls
Exercise 8 – Cash Lock-up Covenant.xls
Exercise 17 – Subordinated Debt.xls
Exercise 18 – Sweep and trap.xls
Sizing debt is a key issue in a project finance model. This file demonstrates basic sizing from the DSCR (it is assocated with the debt sizing video).
Exercise 19 – Level Debt and Goal Seek.xlsm
Exercise 10 – Debt Sizing with Function 1.xlsm
Exercise 11 – Debt Sizing Macro.xls
Exercise 3 – IDC and Debt Commitment.xls
Exercise 12 – Break Even.xls
Exercise 13 – Net Operating Loss Carryforward.xls
Exercise 15 – Average Life and Duration of Debt.xls
Exercise 21 – Re-Financing.xls
Exercise 25 – S-Curve with Normal and Weibull.xlsx
Exercise 3 – Project Finance Working.xlsm
Sample_Project_FM_Test_2016-01-3-2016.xlsm
Related Pages on the Site Related to Project Finance:
Other Featured Models
Other Project Finance Model Examples
Project Finance Model Collection
Template Models
Scenario and Senstivity Analysis
Circular Resolution
Generic Macros
Auditing Files