Introduction to Parallel Circular Reference Template Model

This page 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 two 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.

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.

You may ask 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 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.

Complete Explanation of Parallel Model Features in Associated Web Pages

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.

  • 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 only DSRA circular reference.  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 an EBL, withholding taxes, tax timing, alternative depreciation for financing costs

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.

Step 2: Copy the UDF functions related to the parallel model into your model. Find the VBA page with the functions and then copy the functions to a VBA page in your file (use ALT, F8).

Step 3: 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.