Introduction to Parallel Circular Reference Template Model

This article introduces the circular reference template parallel model works.  The circular reference parallel model is one of the most important files on this website both in terms of concepts and in terms of practical implementation in real models. The general idea of the parallel model is to do three things. First, you can get around the limitations of excel in terms circular reference problems. Second, it is an auditing tool that allows you to verify debt calculations by summarising data with a set of consistent inputs and outputs. Third, you can do the whole thing easily and quickly.

The process involves linking a few variables from your model and running a second model in parallel. The idea or the model is that you can make the calculations in your own project finance model in whatever structure you want.  Then you go to your model and find things like interest rates, EBITDA, model flags.  These things are put into the parallel model as inputs to the UDF.  Finally, you link variables from the parallel model page to items like interest during construction, CFADS and funding needs that are often fixed from copy and paste macros.

Explanation of Parallel Model Features in Associated Web Pages

When I first developed some UDF’s I made the UDF’s with really long lists of inputs that were painful to implement.  But the process worked.  Then I met with my friend Hedieh and she told me to make a template model.  My first reaction was that template models really suck.  But then I realised that she was absolutely right. I have tried to convince you that the parallel model/UDF concept is realistic to implement and can be applied to many different circumstances in the web pages associated with this section. My goal is to make you interested enough in the concept to become motivated to be a really advanced and efficient modeller.  So here is a demonstration of how easy the process is to make.

First, with your financial model open, open the first file below that you can download by clicking on the box.  This file contains the UDF code for making a parallel model.  Once you have opened the file, copy the code to your model (in English you can use the ALT, E, M short-cut) and click the button for creating a copy.

 

Add-in File that Includes UDF for Making a Parallel Model and Resolving Circular References in a Project Finance Model

To illustrate how this process works, I have included a second file that is a very simple project finance model.  As usual, the simplest model is a solar model (although I seen amazingly long and absurd models for simple projects – an impeachable crime). The file that I will use in the example is available for download by clicking on the button below.

 

Basic Project Finance Model that is Solution to the Case Study and Used for Illustrating Parallel Model

 

  • The first exercise with the parallel model involves working with simple annual model with a one period construction duration.  This case is used to demonstrate that the parallel model can be applied for case with no circular reference or a case with circular references only for DSRA and fees.  In this case, the model is annual model.
  • The second exercise with the parallel model involves working with multiple debt issues in the context of a simple model. In this exercise, the parallel model is attached with multiple debt issues in alternative sculpting scenarios.  In the first case there is no sculpting.  In the second case there is one sculpted issue and multiple non-sculpted issues.
  • The third exercise using the parallel model addresses re-financing.  Re-financing can be tricky if taxes are combined with sculpting to determine the amount of debt that can be supported at alternative assumed repayment dates.
  • The fourth exercise using the parallel model addresses various more complex issues including periodic construction on a monthly basis, semi-annual operations, an EBL, withholding taxes, tax timing, alternative depreciation for financing costs, multiple debt issues with sculpting and other factors.

Overview of Step by Step Process for Applying the Circular Reference Template

To make the process work, you just follow the few steps below:

Step 1: Copy the template sheet into a new sheet of your model. The template has the required inputs that go into the UDF (most of which can be left blank or unchanged) and outputs generated from the UDF. After you have opened both of the models, the first step of copying the parallel model to your project finance model and using the copy check box is illustrated in the first screenshot.

 

 

 

Step 2: Copy the UDF functions related to the parallel model into your model. First go to the copy macro button in the parallel mode (not your model) and the press the copy macro button. After pressing the button, the VBA code will appear in a box. Go to the box an press CNTL A first and then CNTL C. This is illustrated in the first screenshot below. After pressing CNTL A and CNTL C, you should exit the screen so you can paste the code.  To do this press the x or the close button on the screen.

 

 

Step 3: Once you have copied the code, go back to your model. Find the VBA page with the functions and then copy the functions to a VBA page in your file. Then type any name for a macro in the box shown below.  This could be something like TEST or COPYPASTESUCKS.  Then, delete anything that excel puts there and make sure you paste the VBA code to the top of the page. More specifically, the OPTION BASE 1 should be at the top of the page. The two screenshots below illustrate the process of copying the UDF from the parallel model to your model.

 

 

Step 4: Make a timeline in the new page of your model that contains the UDF. The timeline should include switches for the development, construction and operation period. The periods in the time line (e.g. months during construction) should be consistent with interest calculations in the base model.

 

Step 4: Go to the input section of the UDF and find the required inputs such as periodic interest rates, EBITDA and DSRA requirements. These inputs should be lined from the financial model. (It is possible that you may have to make a couple of calculations in your model to compute things like effective interest rates on different debt tranches).

Step 5: Enter the function and use the SHIFT, CNTL, ENTER sequence

Step 6: Link various circular references from your model such as funding needs, CFADS, and DSRA to the parallel model

 

To illustrate how the circular reference model works in general, I have presented a short discussion of the inputs and outputs below along with a discussion of how to

 

Inputs to the Circular Reference Parallel Model

Inputs to the parallel model are shown in the screenshot below. Most of these inputs could be left alone at the default values.

 

 

The second set of inputs shows how to get data into the parallel model.

 

 

Model Outputs

Model outputs created by the parallel model are shown below.  To produce this output page you use the SHIFT, CNTL, ENTER sequence after you enter the function arguments.

 

 

You may ask, if it is so good why in the hell would I put this stuff on the website for free when the method is a radical change in the way project finance models work. (Sorry about the arrogance). You can make your complex model, but resolve all of the circular references.

So, here is my marketing concept.  If I do not publish the method it will not get any traction and nobody will use the idea. But I also think that if you really want to use the method you need an intensive course that explains how the general concept works; how to create UDF programs for sculpting and tax issues; how to read the template UDF; and, most importantly, how to modify the UDF for your particular circumstance.