This page and the associated sub-menus explain the UDF parallel model concept that I have developed. The UDF parallel model concept solves the circular references problem and comes along with a whole lot of other very important benefits. Using the UDF Parallel model you can resolve circular references in a project finance model without either copy and paste macros or the iteration button. If you really want to separate yourself from the rest of your friends, you need to learn how to implement and modify the parallel concept in your models you can begin to see how concept works in various videos.
You may well be sick of me talking about circular references, criticizing copy and paste macros and advocating my UDF method that solves all of the circular references without pressing any macro button. I understand this. I understand that people and auditors are used to copy and paste macros. I understand that if the UDF is complicated to integrate into your model it is worthless. I also believe that if the UDF seems like an untransparent black box it is unacceptable. My friend Hedieh Kianyfard has convinced me that if the UDF is painful to put into your model, you will not use it. Finally, Hedieh and I realize that if the UDF method is not completely flexible, allowing you to change things, nobody will apply it. Hedieh and I have spent a year working on these issues with the UDF and we are just about ready to really introduce it in the context of solving the very difficult issue of debt sizing with P50/P90 or debt to capital and also with sculpting that does not have a fixed level.
Some of the innovations include:
- Solving Every Single Circular Reference with No Copy and Paste
- Operation of Model Much Quicker than With Copy and Paste
- Self Audit of Debt Funding, Sculpting, Tax and Other Model Items
- Applying Complex Structuring Methods that are Difficult to Program
- Adding Items such as Re-financing to Existing Model without Re-programming the Model
- Allowing Solution to issues like P90 Debt and P50 Price Target
I have been working on this concept for years and the associated web pages show how the process can be pretty easy to implement in practice. You can also adjust the template code yourself to add other complications that may arise. This is documented in the technical details section.
What this the Parallel Model is Not
The Parallel Model concept is in no way a substitute for your project finance model and it does not change the way you would like to work on your model (except for circular references). It is designed to enhance your work and not replace your work.
This is not a canned program that adds gimmicks to excel like adding input forms or diagrams. It contains real innovations in the mathematics of solving project finance structuring issues related to financing construction, DSRA accounting, sculpting, balloon payments, and other items.
This is not a program for sale that is a black box that you cannot see or modify. You can only get the model if you take a course in how the concept works and gain skills that allow you to understand and modify the code. You can find a descriptions of the in-person course at the following link: https://edbodmer.com/overview-of-courses/ describes in person courses that teach you the financial concepts and the programming techniques in a intensive five day course. You can also sign up for a webinar course with 10 two hour sessions. The webinar courses are described at the following link:
Benefits of Parallel Model Circular Reference
- Solves Every Circular Reference in the Model
- A unique way to verify financial calculations in the model
- Comprehensive and Flexible Method to Compute Sculpting Repayment in Complex Situations
- Run Alternative Debt Sizing Scenarios
- Evaluate Issues that may be Difficult to Program
- Easy to Read Reports
- See Equations for Difficult Sculpting, DSRA and Other Structuring Issues
Circular Reference Resolution — Let’s say you want to make an analysis with where P90 drives debt size but P50 drives price target.
Anybody who has worked in a serious way on project finance models should know about how much the classic solutions to circular references — copy and paste or iteration button — can ruin a model. The problem with copy and paste macros is that your model is suddenly stopped. No goal seeks. No data tables. No simple spinner buttons for sensitivity. No effective summary pages where people can try different scenarios.
Verifying your Model Without Evaluating Every Formula
Imagine you are trying to verify a model. I have heard that some people tell me that they try and verify every single formula of another person’s sheet. If I were to imagine hell, this would be it. With the parallel model you can compare results of the parallel model with your model. Where there is a difference you can isolate the difference and investigate the cause. You can skip all of the items (say taxes and CFADS) that match and focus on individual items that do not match.
Having a parallel model is like having two engines instead of one on an airplane (find better example).
Running Alternative Structuring Options without Programming
Lets say your boss says that the bank may agree to a balloon payment. Or the bank has relaxed the DSCR constraint and now the debt size is driven by the DSCR instead of the debt to capital ratio. Or your company is able to negotiate so that cash flow from the DSRA is included in the DSCR. If you have created your model you say Sh….. If you have implemented the parallel model you have the option to see how these complex things will affect returns and risks without even entering them into your model.
Complex Issues that You May Not Want to Program, But Make a Quick Analysis
You can verify the model and then say you want to do some more complex things like:
- Debt Sizing Using Alternative Methods
- Balloon payments
- DSRA as L/C with fees
- DSRA moves in CFADS for DSCR calculation
- DSRA moves in Debt Service for DSCR Calculation
- Multiple Issues with Sculpting
- Equity Bridge Loan
- Portion of Equity as Up-Front and Portion as Pro-Rata
- Optimising DSCR for Sculpting with Average Loan Life Constraint and Debt to Capital
Effective and Consistent Reporting of Results
Here’s How the Parallel UDF Model Works
It is like you add a sheet to your model and then use the Sum function or any other function that is not related to anything in your model. That is the starting point.
Then collect a few inputs that you have a fixed amount for. This is the only connection.
Use of Manual Calculation and CNTL, ALT, F9
Explanation of the debug switch
Copying Debt Issues
Interpretation of Output
Changing the Output Structure
How many minutes does it take to implement the parallel model.
Workings of UDF and Course Before you Receive the Program
This is the one program that I will not give you for free without a password. If you want the program you will have to take one of my in-person or webinar courses. Sorry about that.
Any program (including excel) has inputs, outputs and calculations
You will see that the equations in your model can be represented in a program.
A corkscrew is represented by accumulation and you must make sure not to over-accumulate.
Copying the formula across columns is represented by a loop
You must include an iteration routine for a circular reference
History of Model Development
Overcoming Resistance
- Not just another VBA program
- Not as a Sale Program
Dislike template models. Try to put everything in the world. But agreed. Put in imaginable things.
- Biggest Point is Circular Reference
Resistance
- This is magic and a black box
- This is like a password protected macro
- This is just another add-in gimick
- History of UDF
Last thing I wanted to do is to program financial model
What you Will Learn — Adding/Changing UDF
Put in the output — work backwards
Create a public variable
Construct a model in excel. Can be a simple model
Replicate your excel analysis
By documenting your calculation, you can check it a lot easier
Soon you will realise that it is not much different than making a model; which can be good or bad.
Verification of UDF
Make tests outside of the model
Balance sheet must balance like any model
Debt must be paid off like any model
Check of totals and things like sources and uses