A-Z Project Finance Modelling On-Line Course

An on-line project finance modelling course is presented through videos, excel files and screenshots on this page. The on-line project finance model course is structured to include both theory and practice where the project finance and/or modelling theory and/or accounting is discussed before working through technical modelling issues. The course is presented with a series of videos and associated files.

If you are starting out in project finance 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 mechanics, the essential nature of separating operating cash flows from financing cash flows and other issues including cash flow waterfalls. I have seen people try to learn modelling by copying from large actual models. This is a complete B.S. way to learn modelling. The models are often wrong, and worst of all, young people waste time on the format of the models without understanding key principles.

The set of videos and associated files also introduce you to some important and difficult financing issues related to raising funds during construction, evaluating debt size using different approaches, simulating different repayment and re-financing and measuring the effect of credit spreads and interest rate hedging. As soon as you start modelling debt you will hit a wall because of all sorts of circular references that can mess up the analysis.  Instead of using typical copy and paste macros after you hit the wall, the course uses an advanced user-defined function approach to resolve the circular references.  Using the UDF is the key to advanced project finance structuring analysis.

Learning Project Finance Modelling and TLNV or TLNR

If you want to learn project finance modelling in half an hour you can build some sort of crude model of a solar project.  This may be good enough for your interview or for a simple analysis of a project.  But you will not have any deep understanding of the nuances that arise in both project finance theory and the associated project finance models.  If you want to become a good modeller, you need patience and you cannot watch a few five minute videos from your phone on the metro (as you cannot lean from large actual models).  Because of the complexities in modelling and in understanding the theory, the videos presented below are long. A lot of people tell me about Too Long Not Read (TLNR) or the perhaps the cousin Too Long Not Viewed.  This is  because I am taking you through a complete project fiance model.

After I published my exciting novel on project finance modelling, I made a lot of videos to go along with the subjects in the book. I did not make the videos in a very professional manner — too much dog barking in the videos, music in the background 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. I received comments about background music, quality, unprofessionalism etc.. So now I have slowly started to make a set of revised videos.

There are a few files that are necessary or helpful if you are brave enough to work through the lesson set.  One file is a power point file that is referred to in the videos and that documents the modelling techniques.  The second file is the excel exercise file that goes along with the on-line course where you can try and enter formulas for the blank rows. You can download theses files by clicking on the buttons below.

Power Point Slides that Accompany Project Finance Modelling A-Z Analysis and On-line Course

Excel File that Incoudes Completed and Blank Exercises Discussed in the Video

The first four parts of the course do not even include debt, but go through the financial modelling, timeline and structure of a model which are essential ideas.

Introduction to Course, Modelling Principles and Key Functions

The first part of the course just reviews some modelling principles of being lazy with excel formulas; looking at some bad practices in other models and discussing some theory.  I also included a couple of files that you can use to prove if certain practices make a file “heavy.”  A “heavy” file may be too big or it may be too slow.  In the file that you can download by clicking on the button below, you can test whether an excel practice of clicking on an entire line slows excel down or makes your file larger.  The answer is that using an entire row or column with LOOKUP and/or INDEX does not.

 


Part 1: Timelines and Consolidating Periodic Periods into Annual or Quarterly Statements

Developing dates and timelines and putting the timelines at the top of each sheet is a standard part of financial modelling and included in all of the regular old blah blah blah classes that you can get elsewhere.  Formulas for establishing dates and switches (or flags or masks or whatever you want to call them) is very standard.  One issue that is a bit tricky is being careful about annual analysis when moving from monthly to semi-annual cash flows.

For the fiscal year used in the annual page, you want to find the end of the month before the COD.  You can just use EOMONTH function and enter -1 for the month.  Then find the month of this date using the MONTH function.  Once you have the month end for the fiscal year, enter a switch that is true whenever the end month in the main time line is equal to that month.  Finally, make a year counter that increments whenever the fiscal year end is true.

 

 

Part 2: Volumes and Capacity and use of the Interpolate Function

In a model, whether an agriculture model, a model of a toilet paper factory in Jamica, a model of an airport or an electricity project, do not start with any thing that has money (Euro, USD, CFA etc.) Instead, start with the capacity and volumes and what the machine or the project is really doing.  This is the subject of the second part of the course.

In this section the INTERPOLATE UDF function is introduced to illustrate how to make volumes, capacity factor or other items gradually increase or decrease (a simple way to interpolate is not in excel).  You can download a file with the interpolate function below so that you can add the function to any of your models and do interpolate really fast.

Download Excel File with the Function for Interpolation Using Either Compound Growth or Linear Interpolation

 

Part 3: Pre-Tax Operating Cash Flow

The third part of the course begins with some equations that involve money.  In any project from a PPP for a University, a toll road, a co-generation plant or any electricity project, you should start with EBITDA and Capital Expenditures (and, maybe working capital).  These items define the pre-tax project IRR of the project.  With the pre-tax project IRR you have the essential information on the project.

Part 4: Depreciation and Taxes to Compute Project IRR After Tax

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.

In adding on-going depreciation on future capital expenditures, I have included a function that enables you to select the entire line of future capital expenditures and remaining lives.  With these data items and the lifetime of the equipment, you can derive the depreciation on future capital expenditures.  The future depreciation is either from standard depreciation if the lifetime is greater than the remaining life.  Alternatively it is computed from the remaining life when the remaining life is less than the asset life.  A file that contains the function is available for download by pressing the button below (note that you have to press Shift, CNTL, Enter instead of enter and you cannot use the entire line).  The file also contains a function that is more flexible and will simulate variable declining balance depreciation.

 

Spreadsheet that Includes Functions for Computing Depreciation on Future Capital Expenditures (Use ALT F8 to Transfer)

 

The screenshots below illustrate how to use the function with straight line depreciation.  The first screen shot shows how to function works in the case where the remaining life is greater than the asset life and the asset can be fully depreciated over the remaining life. The second screen shot shows the case where the remaining life is less than the asset life.

 

 

 

 

The video below works through various depreciation and issues related to future capital expenditures and explains why the problem of future capital expenditures and depreciation would be very messy without a function.

.

.

The VBA code for the depreciation function is available for download below. You can get this function with the VBA code into your project finance model in three different ways.

The first way is to open the file above and press ALT, F8.  Then edit the VBA page with the subroutine named A_Depreciation_functions.  Copy the entire contents.  After that, go to your sheet and press ALT, F8 again.  Then create a blank VBA subroutine and copy the contents above that routine.

The second way is to copy the function that is on the form that appears when you open the file.  You just copy the code, go to your sheet, press ALT, F8, create a dummy macro and paste.

The third method is similar to above, but you just copy the code from below.

.

Option Base 1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''' Depreciation Functions
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub A_Depreciation_Functions()

End Sub
Function depreciation_remaining_life_2(capital_expenditure, remaining_life, max_life) As Variant ' When the output is an array define as Variant

cap_exp_periods = capital_expenditure.Count ' See how many capital expenditure periods are modelled

Dim Depreciation_Expense(5000) As Single ' Make a new array variable that is the output
Dim remaining_life1(5000) As Single

' Determine whether to use remaining life or something shorter

For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset
  If remaining_life(vintage) >= max_life Then
  remaining_life1(vintage) = max_life
Else
  remaining_life1(vintage) = remaining_life(vintage)
End If

Next vintage

For model_year = 1 To cap_exp_periods ' loop around each period and make a square with columns and rows

  For vintage = 1 To cap_exp_periods ' make a second loop to evaluate asset by asset

    age = model_year - vintage + 1 ' calculate the age of each expenditure (the diagonal)

    If (age > 0 And remaining_life1(vintage) <> 0 And remaining_life1(vintage) >= age) Then ' Only when asset is alive

      Depreciation_Expense(model_year) = _
      capital_expenditure(vintage) / remaining_life1(vintage) + Depreciation_Expense(model_year)
    End If

  Next vintage ' Note that the vintage is used for the capital expenditure

Next model_year

depreciation_remaining_life_2 = Depreciation_Expense

End Function

.

Part 5: Balance Sheet and Verification

The balance sheet is a good (but not perfect) way to check your model.  A few years ago a student in my class told me that he puts a balance sheet into the model very early on in the process. At first I did not see the value in this. With complex models and solving circular references I think that suggestion was exactly on the mark.  So much so that I put a test right at the top of the model and believe the saying on the t-shirt below from my friend Hedieh (you can order this t-shirt or a mug if you want. Just send an e-mail to edwardbodmer@gmail.com).

You can put the balance sheet into the model after you have modelled the depreciation expense.  The balance sheet should just collect closing balances from other sections of the model. The video below describes the process of balancing the balance sheet before any debt had been put into the model.

 

Entering Debt in the Model and Unavoidable Circular References

After you have structured the model without debt and without circular references, you can begin to enter debt into the model. If you are structuring the debt — finding the debt size, computing different debt repayments, trying different methods of funding debt, putting in different cash flow sweeps or even changing fees and interest rates, you will most probably run into circular references.  From a philosophical perspective the circular arises because the model drives the debt terms, but the debt terms drive the model.  For example, the model drives the amount of Interest During Construction and is necessary to evaluate the total debt. But the total debt percent is driven the term sheet.
It is nice to say that there should not be circular references when you enter debt, but this is arrogant.  You have to create quite a lot of stuff that is fixed to avoid circular references even if you use a model for credit analysis.

 

 

Part 7: Efficient and Usable UDF for Solving Circular References

I have been working on a comprehensive model with all of the items that are resolved with user defined functions. This file is a continuing process, but you can see how things work if you download the model by pressing on the button below.

Example of Project Finance that Resolving Circular References Using UDF and Parallel Model from Template

 

 

These files are from the wikispaces website.  I am in the process of uploading all of the files. But in the meantime you can send me an e-mail to edwardbodmer@gmail.com to get the resource library. The files will be located in the project finance section of Chapter 1.

 

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.

 

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).

 

A-Z Exercise with Selected Existing Titles for Solar Model

I was asked to prepare an exercise where people could quickly make a model that evaluates bid prices and other model aspects and can get you most of the result without spending too much time on some of the horrible details that can make project finance modelling so difficult.  I was told that making a relatively simple project finance model could not be taught in a single day.  This is not true.  You can see the essentials of making a model including describing inputs, establishing operating cash flow, computing depreciation and project IRR, incorporating debt, making a cash flow waterfall and computing some key financial ratios in a morning.

The file available for download does this for a simple solar case.  To make it really simple I have not even included a construction period and made the model and annual model.  For some items I have not included titles so that you can do the really important part of a model which is to structure the model.  For other parts, I have included titles so that you do not have to waste a lot of time typing.  The exercise hopefully applies some of the fast modelling religion, meaning that it is flexible (except for the one period construction), it is accurate — the balance sheet balances and the debt is paid off; it is structured, where you start with physical operations and then move to revenues, expenses and capital expenditures and then to depreciation which allows you to compute operating taxes and project cash flow.  After project cash flow you incorporate debt with a sources and uses of funds statement, and a debt schedule.  Only after the debt schedule do you create the financial statements.  To download the file, press on the button below.

 

Excel File with Exercise to Create Basic Solar Project Finance Model Demonstrating Structuring Issues

Part 1: Operating Data

In the first part, you go to the operating data.  You use the INDEX function so you can select one of the scenarios.  Also put the developer tab in your excel and make a spinner box with the windscreen wiper method to use the form in other sheets.  The first part is illustrated in the screenshot below.

 

Part 2: Timing

In the second part make a timing switch and use the EDATE function (FETCHA.MOIS) to compute the annual dates.  I have put some summary outputs at the top of the page.  Use the ALT and –> to group the rows and choose to not show the outputs.  In English you can use ALT, E, I, S to get the model started for 40 years.  With the GENERIC MACROS open, you can use the ALT, S combination.  The timing parts of the model are shown on the screenshot below.

 

 

Part 3: Define Inputs

After setting up the time line, pull the relevant inputs from the model from the operating page and from the debt page. The inputs should be discussed and you should see how to change the scenarios with your spinner box.  You should understand that the inputs are arranged in a proper order that separates operation from financing; that begins with physical operations; that includes ways to back into the contract price and that includes logical differentiation of the debt parts of the model.  When you have linked the outputs, use the CNTL, ALT, C sequence from the GENERIC MACROS to show the location of the inputs and to illustrate the structure of the model. The input section is shown on the screenshot below.

 

Part 4: Construct the Operations Section of the Model

In the first part of the model I have not provided the titles.  You are supposed to get to the volumes produced in MWH which is the basis for revenues.  To do this you need to know how much the solar capacity factor or yield will be.  I suggest you enter the driver of the formula in a left hand column, that you put in the units in a left hand column and that you use SHIFT, CNTL, R to quickly copy things to the right. You should also compute an index for degradation that begins in year 1 by taking the prior period and multiplying it by (1+degradation). Then you can divide the capacity by degradation and compute the capacity after degradation.  You can use whatever method you want to insert the rows.  You can create your own short-cut key for the underline.

 

 

Part 5: Revenues, Expenses, Capital Expenditures and Free Cash Flow

In any corporate finance model, M&A model, real estate model or project finance model, you will need revenues, expenses and capital expenditures.  The assumptions that create these three items (along with working capital) will drive all of the rest of the analysis.  In the next section you are to compute revenues, expenses and capital expenditures from the inputs.  I think it is a really good idea to put the drivers of each formula in a left hand column so you can be transparent (I hate looking around for where the numbers came from).  In this part of the analysis I have given you the titles as shown on the screenshot below. Once you have computed the project IRR you can use a goal seek and a macro to evaluate the required price.

 

 

Part 6: Depreciation, Operating Taxes and After Tax Free Cash Flow

If there were no taxes and you did not want to show a profit and loss statement you could eliminate this part of the model. With taxes, the shield from depreciation is an important item. Compute the depreciation by setting up an account with the balance of plant as shown below.  When you compute depreciation you can use the MIN function to make sure the depreciation does not exceed the net plant balance. Once you have the depreciation you can compute the EBIT, the taxes on EBIT, the after tax project cash flow and the project IRR.  The screenshot below illustrates this part of the model.

 

 

Part 7: Sources and Uses Prior to COD

Once you have completed the operating cash flow and operating taxes you can move to incorporating debt in the model.  In every project finance model there should be a sources and uses of funds for evaluating cash flows during the construction period.  You can think of this as a way to evaluate debt sizing.  In the example, you can fill in items on the sources and uses of funds from the inputs and items above the sources and uses.  Note that this is not realistic as items such as interest during construction and fees come from below not above and you get a circular reference.

 

 

Part 8: Debt Schedule

The debt schedule must have calculations for debt repayment, interest expenses and fees.  Repayment is often the most difficult aspect of a model.  There are alternative methods demonstrated to compute the repayment including flat repayment, mortgage repayment and inflated repayment that matches the cash flow when inflation is applied in the price.  Use the INDEX Function to select one of the three methods of repayment.  A screenshot is shown below.

 

 

Part 9: Profit and Loss Statement and Cash Flow Statement

Now you are to the easy part.  The only reason that an income statement is necessary in a project finance model is to compute taxes and to compute net income for purposes of balancing the balance sheet.  The cash flow statement can be more complex if there are cash sweeps, covenants and other items.

 

 

Part 10: Balance Sheet and Financial Ratios

The final part of the exercise is to put together a balance sheet and compute a couple of financial ratios. The balance sheet components should all come from items in the model and the equity balance should be computed.  Ratios like IRR, DSCR and LLCR come from the cash flow statement.

 

 

 

 

 

Exercise that Includes Construction Period

I was asked to prepare an exercise where people could quickly make a model that evaluates bid prices and other model aspects and can get you most of the result without spending too much time on some of the horrible details that can make project finance modelling so difficult.  I was told that making a relatively simple project finance model could not be taught in a single day.  This is not true.  You can see

 

Excel File with Exercise for Creating Combined Cycle Model with Timing and Construction Issues

 

Case Study Exercise on How You Can Evaluate and Existing Model

Some unlucky people have to create models and deal with circular references and many horrible details like withholding taxes on four tranches of debt.  More people have to interpret and use models that is arguably even more disagreeable because of the way models have become cumbersome and overly detailed.

This case study and exercise is designed so that you can more efficiently evaluate models created by other people including dissecting cash flow in the models, computing alternative financial ratios, adding your own scenario analysis, dissecting the model with sensitivity analysis, creating an summary page the conveys the transaction, making effective graphs the illustrate risks and finally formatting the model with nice colours and titles. My hope is that this exercise will be practical for you and that you can use some of the ideas in your current job immediately.

The file that I have used for this case is the completed case from the above model.  But it could be just about any project finance model, corporate model, M&A model or other financial analysis that has a few inputs and outputs.  The base model that I have chosen for this exercise is available for download by clicking on the button below.

 

Introduction to the Model Review Assignment

I have structured this assignment by attempting to explain the details of how to complete the excel stuff.  Then I have included questions that should be completed with only one or two sentences where you tell me the implications of the modelling tasks.  I have also provided titles for many of the items so that you do not have to waste a lot of time typing stuff and the exercise will not take too much time.  I strongly suggest that you do not do this exercise on an Apple computer; that you open and use the GENERIC MACRO file, the LOOKUP INTERPOLATE file and the SCENARIO REPORTER file to make the case go faster.  These three files are available for download below.

 

Generic Macro File that Allows you to Copy to the Right (Shift, CNTL, R) and to Colour and Format Sheets (CNTL, ALT, C)

Download Excel File with the Function for Interpolation Using Either Compound Growth or Linear Interpolation

Scenario Reporter File - With this File, you Open the File, then Copy the Scenario Sheet to Your File and Use the Buttons

 

Part 1: Dissecting Cash flow

Use the SUMIF function to get the data to annual amounts. When you use the SUMIF, click on the entire row.  Question – how does the IRR on the project and equity compare to overall stock market returns. Do you think this is realistic.

 

 

Part 2: Computing Alternative Financial Ratios

I hope that you learn to look at ratios other than the DSCR.  In this case you can examine the LLCR and the PLCR.  The only calculation you have to make for this is to use the SUMPRODUCT function below the cash flow statement with the interest rate index and to find the debt at COD from the sources and uses. Explain how to interpret the DSCR, LLCR and PLCR.

 

Part 3: Adding your Own Scenario Analysis

Use the scenario manager to add a scenario. In this case could you invent a few cases with different inputs.  Change the following: the project cost, the life of the plant, the availability.  Then open the scenario reporter and create a scenario report.

 

Part 4: Dissecting the Model with Sensitivity Analysis

 

Part 5: Making Effective Graphs the Illustrate Risks

 

Part 6: Creating an Summary Page the Conveys Key Aspects of the Transaction

 

Part 7: Formatting the Model with Nice Colours and Titles