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.

Project Finance Theory and Marriage Contracts

A-Z Project Finance Modelling

Original (More Basic) Project Finance A-Z On-Line Course

Sculpting and Debt Fees (Including Fees for DSRA L/C)

Monthly Model with Semi-Annual Debt Service

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

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