Exercises for Modelling Class

On this website I have included a number of project finance issues that I consider quite advanced ranging from sculpting with changes in the DSRA to sculpting with multiple debt issues to computing net operating loss with expiration to working with different currencies. These advanced modelling issues are covered in various different sub-menus on the website and can be a little difficult to find (at least for me). Because some of the pages may be tricky to find with the menus, I have included the links to more than twenty subjects that I think are more advanced than the basic building of a model with funding, circular references, taxes and sculpting. I have included links to the different places where you can find selected advanced structuring subjects. Most of the links include an excel file with an example that you can work through as well as at least one video that explains my ideas. I have also included a link to a webpage where you can work through a complete exercise to create a project finance model. I note that the first twelve issues are or can be resolved with the parallel model. By using the parallel model you can either see how to structure the various issues in the model or you can just use the model to solve the problems and then you do not have to bother doing all of the programming by yourself.

At the bottom of the list of advanced issues I have included some playlists of videos for subjects on sculpting, project finance theory and debt sizing. The debt sculpting videos include 22 videos that work through sculpting issues beginning with simple sculpting and addressing issues such as multiple debt issues, sculpting with non-constant DSCR and taxes. The following files are used for the modelling course.

I have included two files that are used to make the excel exercises work more smoothly. The first is a file called Read PDF which allows you to grab data from PDF files and then convert the data to excel files. To run this file you copy stuff from the pdf file and then operate the macro with SHIFT, CNTL, A. When you copy data from a PDF to excel, make sure that you copy and paste special as UNICODE text. There are different formats that you can use to resolve the PDF. If you are reading from the Lazard LCOE stuff you can use the first green box. If you are reading from the PVGIS you can use the second green box.

The second file is a file that has a whole lot of macros to prevent you from wasting time on formatting and copying formulas to the right. This file is called GENERIC MACROS. I have revised the generic macro file in the link below so that you are not prevented to open it because of something called the auto open — a macro that operates when you open the file and excel considers dangerous. When working through the exercises, it would help a lot if you have this file open and enable the macros. The big things that this GENERIC MACRO allows are to press SHIFT, CNTL, R to copy to the right and also CNTL, ALT, C to open the window that has a whole lot of formatting options. There are a lot more utility macros in the GENERIC MACROS file. You can go to https://edbodmer.com/excel-utilities-and-backpack/generic-macros-file/ and see some of the other stuff including a few user defined functions.

.

Read PDF to Excel File that Allows you to Format Data After Copying from PDF File (Press Shift, Cntl, A)

.

Generic Macro File for Copying to Right (SHIFT, CNTL, R), Formatting (CNTL, ALT, C) and Other Macros (UDFs)

.

.

Section 2: Model Structure and FAST Modelling Standards

The file attached to the buttons below include examples of how to structure Models with time lines; with InputC; with InputS etc.

.

Project Finance Modelling Exercises -- Simple FAST Model of Solar Project with Blanks to Fill In

.

Project Finance Modelling Completed Case -- Simple FAST Model of Solar Project with Complete Equations

.

Section 3: Nuances and Upside in Project Finance

The third exercise has more nuanced issues for project finance analysis. The general idea of this exercise is to demonstrate that the IRR in the initial analysis is just a first step. Because the risk of renewable projects declines, it is possible to realize upsides. These upsides include use of development fees, use of re-financing and use of the sale of a plant to investors who are interested in paying for safe investments. This third exercise also includes the true definition of IRR, the theory of development risk and development fee; upside option from re-finance; the upside option from selling a project; computing performance measures and equity bridge loans. As with the first exercise, the first file is completed and the second exercise has equations that you should fill in.

.

.

Part 3 - Excel File with Blank Equations for Project Finance Issues including Development Fee, Re-financing, Plant Sale

.

Part 3 - Excel File with Filled Equations for Project Finance Issues including Development Fee, Re-financing, Plant Sale

.

.

Section 4: Exercises for Course Including A-Z Model, Circular Exercises, Cash Flow Waterfall, Mini-Perm

As with the the other exercises, the button attached to the first file is the file with equations for you to fill in. The second button is attached to the file with the completed exercise.

.

Project Finance Modelling Course File with Blanks - File with Exercises for A-Z Model, Circular References, Mini Perm etc.

.

I have include a file and a video below that works through some of the various different project finance issues. I use this file to go from a basic analysis to a more sophisticated analysis. The workbook attached to the button has many of the subjects discussed below.

.

Excel File with Comprehensive Project Finance Exercises and Model Mechanics from Basic to Advanced

If you want the files associated with levelised cost I have the files in a folder (chapter 1, project finance and circular references) of the resources. Just drop and email to edwardbodmer@gmail.com and I will send a google drive with a whole lot of stuff (probably too much). But you can unzip the files and look for what you want and save the google drive in the cloud….

Working through a Model from A-Z with Funding, Sculpting and Circular References

If you want to see an exercise for working through structuring issues with alternative funding you can go to the link below.

https://edbodmer.com/financing-exercise-without-circular-reference/

.

Advanced Issue 1: Structuring a Project with Evaluation of Debt Size from Either P90, P50, or Maximum Debt to Capital

https://edbodmer.com/solar-battery-hydrogen-course/

.

Advanced Subject 2: Inclusion of DSRA changes in the DSCR for Debt Sculpting

https://edbodmer.com/including-releases-or-deposits-to-dsra-in-dscr-and-sculpting/

.

Advanced Issue 3: Resolving Circular References without Copy and Paste Macros or the Iteration Button

https://edbodmer.com/technical-details-of-udf-parallel-model-concept/

.

Advanced Issue 4: Computing Debt Repayment where the Debt Size is Given, and Sculpting is used to Obtain a Minimum DSCR with Varying DSCR’s

https://edbodmer.com/sculpting-with-fixed-debt-and-changing-dscr-goal-seek-and-data-table-method/

.

Advanced Issue 5: Debt Sculpting of Multiple Debt Issues where cash flow is Affected by Interest Expenses and Income Taxes

https://edbodmer.com/multiple-sculpted-issues/

.

Advanced Issue 6: Incorporating options for DSRA funding with an L/C or with Cash Funding

https://edbodmer.com/sculpting-and-debt-fees-including-fees-for-dsra-l-c/

.

Advanced Issue 7: Modelling Sculpting with Balloon Payment at End of Tenor

https://edbodmer.com/separation-into-balloon-payment-and-sculpting/

.

Advanced Issue 8: Adding Re-financing to a Model where Debt Size Depends on Taxes which are Affected by Interest on the Re-financing

https://edbodmer.com/re-financing-analysis/

.

Advanced Issue 9: Evaluation of NOL Time Limits on the Calculation of Income Tax

https://edbodmer.com/expiration-of-nol-in-project-finance/

.

Advanced Issue 10: Computation of the LLCR with Different Debt Issues using the Prospective debt IRR

https://edbodmer.com/llcr-and-plcr-complexities-and-meaning-for-break-even/

.

Advanced Issue 11: Modelling Equity Bridge Loans and the Associated Circular References along and Options for IDC

https://edbodmer.com/equity-bridge-loans-ebl/

.

Advanced Issue 12: Modelling the Effect of Financing in Different Currencies with Translation Adjustments

https://edbodmer.com/currency-adjustments-taxes-and-debt/

.

Advanced Issue 13: Modelling Development Fees with Multiple Investors

https://edbodmer.com/development-fee-timing/

.

Advanced Issue 14: Including Actual Data in Models for the Construction Period and Operating Period

https://edbodmer.com/project-finance/actuals-in-project-finance/

.

Advanced Issue 15: Modelling Mini-perms and Different Re-financing Assumptions

https://edbodmer.com/mini-perms-and-re-financing/

.

Advanced Issue 16: Developing Alternative ways to Model Working Capital Requirements in the Initial Operating Periods of a Project

https://edbodmer.com/working-capital-in-project-finance/

.

Advanced Issue 17: Evaluating Different Structures for Equity Investors including Cash Flow Flips and Incentives to Developers.

https://edbodmer.com/performance-incentives-for-developers/

.

Advanced Issue 18: Accumulation of Projects into a Portfolio or a Fund

https://edbodmer.com/project-with-multiple-spvs/

.

Advanced Issue 19: Modelling Alternative Cash Sweep Structures

https://edbodmer.com/cash-sweep-and-risk-versus-return/

.

Advanced Issue 20: Valuation of Projects with Changing Buyer Target IRR’s and Computation of Holding Period Returns

https://edbodmer.com/irr-with-changing-discount-rates-and-assumed-sale/

.

Advanced Issue 21: Evaluation of Defaults and the Risks of Senior and Subordinated Debt

https://edbodmer.com/cash-flow-waterfall-and-financial-statements/

.

Advanced Issue 22: Creating Data Tables using VBA to Resolve Data Table Problems

https://edbodmer.com/data-tables-with-goal-seek-using-vba/

.

Advanced Issue 23: Time Lines with Different Periods and Required Sums

https://edbodmer.com/timelines-in-project-finance-models/

.

Advanced Issue 24: Effective Control Pages where you can Evaluate Changes to Inputs and Properly Include the Inputs in Relevant Input Sheets

https://edbodmer.com/scenario-analysis-with-diagrams-spinners-and-reset/

.