This website is now obsolete because of being extinguished by horrible wikispaces. Please go to http://edbodmer.com for a much improved website.


||Home|| Book || Featured PF Models || Featured Corp Models || Featured Acq Models || PF Exercises || Corp Exercises || Download to Excel || Scenario Analysis || Tornado Diagrams || Monte Carlo || Flexible Graphs || Real Estate || Acq Exercises || Macros || Reading PDF || Waterfall Graphs || Circular Ref || Depreciation || Financial Ratio || Value Driver || Renewable || Testimony || Pricing Case Studies ||

This circular reference page demonstrates how to resolve circular references through using user-defined functions rather than non-transparent and circular resolution clumsy copy and past macros, goal seeks or solver methods. For a series of circular reference problems, the benefits and problems of various different circular reference resolution methods are illustrated beginning with the iteration button, moving to the goal seek method and finally to two different circular reference methods that use functions.

The first circular reference resolution method that involves pressing the iteration button is demonstrated with files that demonstrate how things can go wrong from the un-stability of your model. The file shows when the iteration button can be reasonable and how you can fix things when things go wrong. The second circular reference resolution method in which you create a copy and paste macro that copies from COMPUTED to FIXED, COMPUTED to FIXED and sets the calculation formula to the fixed item is pretty simple. The important thing is to understand where the circularity comes from and minimise the copy and paste macros. The third circular reference resolution attempts to solve circular references using algebra. This circular method can result in long and tedious formulas but can also be elegant. In some situations like for IDC with an annual model or for simple fees, this can be the best approach. The fourth circular reference method applies user defined functions with a structured approach that can solve any circular reference problem. This is the method that is most documented and it involves re-programming equations in excel that result in the dreaded blue arrows.

Lesson Set 1: Fixing Circular References in Model


Say that somebody was really lazy and used the iteration button. Your task is to fix the circular references. Here are some suggestions:

First, fix easy things with algebra


If the circular reference is coming from a simple fee calculation or from a contingency calculation, just change the calculation to a formula using some simple algebra as shown below.

Total = Total x Contingency + All Else
Total - Total x Contingency = All Else
Total x (1- Contingency) = All Else

Total = All Else / (1 - Contingency)

Second, make sure the closing balance is NEVER part of the interest expense calculation


Look around for the model for the interest expense or interest income calculations. If the calculation uses the closing balance, change the calculation. Make the interest on the opening balance which implicitly assumes that the repayment etc. occurs at the END of the period. If you are worried about this assumption change the timing of the model. For example, change an annual model to a quarterly model, at least for the debt schedule.

Third, in PF Model make sure there is a summary sources and uses statement with IDC, Fees and DSRA funding during construction


The IDC, fees and DSRA cannot be fixed with a simple copy and paste macro. To fix the IDC first use the clumsy copy and paste method. This involves the following steps:

First make a copy and paste near the sources and uses statement. Name ranges and copy the COMPUTED TO FIXED with paste special. Do this with recording a macro.. n this section I explain how you can resolve a couple of circular references that arise in corporate models. The most classic circular reference comes from interest expense and interest income that is affected by financing over the course of the year. In an annual model interest can be computed on the average balance. Developing a user defined function is

Web_circular.JPG

Note: please do not make your macro password protected as if it is something fancy. In fact it ruins your model and destroys the ability to efficiently make scenario analysis.

Fourth, Change the Copy and Past Macros to User Defined Functions


Making a user-defined function is the second-best alternative after the algebraic option. For some resolving some circular reference problems like the classic circular reference from interest expense and interest income, the process is relatively simple. In these cases the blue lines and the circular reference are all a single column and the item in question does not depend on the prior year. For the IDC, fees and DSRA funding, the problem is that IDC defines project cost and you need to work through the entire construction period to find the IDC. This implies that a loop is necessary.

To make the mechanics of user-defined functions easier, a file named “Circular Template” is included here. This file has a lot of the generic programming that you will need to solve the circular reference problem where loops are necessary. So, if you want to build a user-defined function with a loop, first download the file. Next, change the name of Read Array per the instructions and start defining period by period variables as well as accumulated variables. In the example below, the equity is funded before debt during the construction period and a minimium function is necessary to understand when the equity funcing will be comple and the debt funding will begin.


web circular.JPG

Lesson Set 2: General Principles of User-Defined Functions:


To begin the process of solving circular references by functions, I demonstrate some of the principles of user defined functions. I have tried to read books on VBA and I have used a lot of discussion boards. I find these very difficult or sometimes to easy to be useful. I don't care about making range's dimensioned and using complex syntax. All I want to do is make a loop and compute some numbers that can be outuput to excel. This is the idea of my introduction. So, here are some general principles about functions:

1. The name of the function must be defined with and = sign. For example if you make a function named BONJOUR with Function Bonjour, then somewhere in the function you must have the statement BONJOUR = something (for example, BONJOUR = "Bonjour Monde".

Function bonjour
bonjour = "Bonjour Monde"
end function


2. You cannot use RANGES from the excel file. For example, you cannot use RANGE("A1") in your function. Instead, you must read the variable into excel. If you want to put the date into your BONJOUR function and the date is in excel, you must read the date into your function. To do this you would enter something like FUNCTION BONJOUR(Todaydate).

3. You should understand the difference between SCALAR and ARRAY variables. If you read a series of inputs into the function, they are array variables such as the series of capital expenditures, or EBITDA over time. If you read a single variable like the debt to capital ratio, this is a scalar variable that must not have an index like debt_to_capital(i).

This circular reference page demonstrates how to resolve circular references through using functions rather than non-transparent and circular resolution clumsy copy and past macros, goal seeks or solver methods. For a series of circular reference problems, the benefits and problems of

Lesson Set 3: Complex Circular Reference Problems in Project Finance and a "Sort-of" Template:


Introduction to PDF Method for Solving Circular References

Years ago I was teaching a project finance modelling course and we distributed pre-course surveys to people attending. One of the common responses was the desire to have project finance models without the dreadful and un-transparent copy and paste macros. I tried algebra. I tried a reverse logic method. Then I somehow stumbled on the user function method. I tried on more and more difficult problems. The user-defined to solve circular references can always work. When you use this UDF method, you can run goal seeks, solver, data tables, dynamic graphs and all of the other excel tools that are good for bidding analysis. It can be a bit difficult but it when it works you feel like you have really accomplished something.











Making Long-Circular Reference Problems Less Difficult

The problem with solving circular references with user-defined functions is that writing the functions can be painful. This compares to copy and paste macros that are quite simple. My hope now is to make the process less painful through using a template and explaining common problems. My suggestions for now are not to make the user defined functions shorter. Instead, I wonder if you can apply transparency, flexibility, structuring and accuracy principles to the UDF’s. So, to make things more transparent, add a whole bunch of output lines as shown below. Second, to make things more flexible, try to create a macro that has a log of different possibilities. Third, to make things accurate, include checks in the outputs. Finally, be careful and structure the coding in the UDF.
The transparency, auditability and the flexibility of the UDF process depends a lot on the structure of your excel analysis. The more structured, transparent and flexible your model, the easier the UDF function will be to implement.

The exceprt below demonstrates how the output from a user-defined function can be comprehensive, structured and transparent. It can then me be used to audit the model.


Structured Output.JPG




Understanding Arrays and Scalars


Sometimes when working with a function you get the horrible #VALUE output. When you get this #VALUE in the function output, it is generally the result of a problem with an array variable. To understand the description, when I use the word array, I mean a variable that has multiple values and may be dimensioned as an array variable in the code (e.g. EBITDA(1000). When I mention the word scalar variable I mean a variable that only has one value (e.g. debt_percent). The excerpt below illustrates differences between array variables and scalar variables. Array variable can be read in from excel as variables with more than one value.



Now, back to problems that can lead to #VALUE:

1. You entered a scalar when you should have entered a scalar [When you enter data in a model, make sure to understand what is an entire row -- an array -- versus a single number -- a scalar.]
2. You have a dimensioned variable that you are treating like a scalar [When programming the UDF, the array variables must have a dimension. Make sure all the variables have an index (e.g. EBITDA(i))]
3. You have a loop that starts as a negative number [If you make a loop around 1 to 10 but put the array as i-2, then the variable will be undefined].
4. You have a circular reference in the function [If you are referring to a variable that has a circular reference, you can get a value].
5. Have not defined variables

The excerpt below shows what happens if you have made a mistake that leads to the value problem -- it is often a problem with an array function.

Dreaded Value.JPG


The excerpt below illustrates a problem that can cause the dreaded #VALUE!. If you look carefully you can see that the variable equity_draws in the third row does not have an array. Somehow you have to find this problem in a long program.


Array Mistake.JPG


The last excerpt shows how to check the program so that you can find the problem that is causing the #VALUE! problem. The process involves stopping the function and making sure that you get zeros and not values. After you move the end function to different places, you should be able to isolate the problem.


Example of Exit Function.JPG




Lesson Set 4: Applying the Template in Your Project Finance Model:



Step 1: Find the hard-coded row that is used with a copy and paste

If you have a fixed row that is the basis of the copy and paste macro, one way to fix things is to attempt to compute the row with a user-defined function. Typical fixed rows are the total funding needs during the construction period and/or the CFADS during the operation period. You can insert a few lines below the fixed line for total uses of funds by period or alternatively the total CFADS. An example of a model with a fixed line is shown below. Insert about 25 lines below the fixed line.

Step 2: Copy the template function

Download the circular template file above if you need to resolve circular references during the construction period. To read the macro into your file go to an existing macro or make a new blank macro (turn on the record button and then turn it off). Then go to blank macro and copy the contents with ALT F8 etc. (this sheet)




Step 3: Work with only pure cash flow and set the fixed line to the line from the function

Find all of the interest and fees and development fees and set them all to zero. Make sure that you have switches in the model for construction and development (if no development then use no development switch template).

Make sure that you have a line for capital expenditures that includes the costs that do not depend on debt or the total project cost (i.e. not

The switches are the key to being able to read entire lines of data without pressing the F4 a lot.

Compute the difference and make sure the difference is zero.

Use the SHIFT and F3 to find the function names.

Don't forget the SHIFT, CNTL, ENTER

Check the assumptions


Step 4: Link the hard-coded line to the function output



Step 5: Zero out all interest rates and verify the model

Copy the key assumptions next to the original assumptions. Then zero out all of the interest and fees. The difference between the function results and the computed total sources should be zero.

When you use the function, it is an array function with may rows and columns. Copy the default titles from the function as shown below.




Step 6: Start putting in things that are a function of the total cost

Begin with development fees that can be a percent of total cost or begin with the interest while zeroing out the fees. It is generally best to use an array for the fees.

Start with one tranche and senior



Step 7: Begin working on things that are a function of total project cost and financing




Here I walk through some circular reference examples. If you complete the examples, you can earn a badge in circular reference and receive a circular reference certificate that will get you a very big raise. This circular reference page demonstrates how to resolve circular references through using functions rather than non-transparent and circular resolution clumsy copy and past macros, goal seeks or solver methods. For a series of circular reference problems, the benefits and problems of


Lesson Set 4: IDC and Fee Circular References in Annual or Quarterly Model:


Introduction

I generally think that IDC and fees should be computed by assuming that cash flows occur at the end of the period. Thi is consistent with paying suppliers and construction staff at the end of the month (you could even make a model have a 1/2 month if this is consistent with the way money is paid and money is paid). Using the opening balance eliminates many circular references. However, in some cases an annual model can be useful. In this case it is not reasonable to assume that the ending balance is used.

In this set of equations



New construction debt = Total funding - EquityNew debt = Direct Construction + IDC - EquityIDC = Opening Balance * rate + new debt * rate/2IDC = Opening Balance * rate + (construction + IDC - equity ) * rate/2IDC - IDC * rate/2 = Opening Balance * rate + (construction + equity ) * rate/2IDC * (1 - rate/2) = Opening Balance * rate + (construction + equity ) * rate/2

IDC = [Opening Balance * rate + (construction + equity ) * rate/2]/[1-rate/2]




ears ago I was teaching a project finance modelling course and we distributed pre-course surveys to people attending. One of the common responses was the desire to have project finance models without the dreadful and un-transparent copy and paste macros. I tried algebra. I tried a reverse logic method. Then I somehow stumbled on the user

Circular References in Project Finance Model








Videos and Files Explaining How to Solve Circular Reference Problems in Financial Models











Subject

Excel Exercise File

Video

Chapter Reference

Page Reference











Circular Resolution Alternatives Simple Fee Case

Circular Reference 1: Fee Exercise

https://www.youtube.com/watch?v=DR7ehE9VgcY

Chapter 37

468

Circular Resolution for Cash Sweep in M&A

Circular Reference 2: Cash Sweep in M&A

https://www.youtube.com/watch?v=4nBP9jyoCT4

Chapter 37

470

Circular Resolution for Cash Sweep in M&A with Taxes

Circular Reference 2: Cash Sweep in M&A

https://www.youtube.com/watch?v=rkwbgE6nzxE

Chapter 37

475

Circular Resolution for Capitalised Interest and Fees

Circular Reference 3: Capitalised Interest

https://www.youtube.com/watch?v=04pXy2KhL8Y

Chapter 40

501

Circular Resolution in Corporate Model

Circular Reference 4: Corporate Model

https://www.youtube.com/watch?v=HWDtzRWQ5cw

Chapter 38



Circular Resolution in Corporate Model with Target Capital Structure

Circular Reference 5: Corporate Model with Target Cap Structure

https://www.youtube.com/watch?v=su5A0yp8-CE





Circular Resolution in Project Finance Funding with Pro-Rata 1

Circular Reference 6: Funding in Project Finance 1

https://www.youtube.com/watch?v=wNmQiudv43U

Chapter 40

501

Circular Resolution in Project Finance Funding with Pro-Rata 2

Circular Reference 6: Funding in Project Finance 2

https://www.youtube.com/watch?v=pzZCMtVyBdE

Chapter 40

503

Circular Resolution in Project Finance Funding with Pro-Rata 3

Circular Reference 6: Funding in Project Finance 3

https://www.youtube.com/watch?v=2xWvx-ReFx8

Chapter 40

504

Circular Resolution in Project Finance Funding with Fees

Circular Reference 7: Funding in Project Finance - Fees

https://www.youtube.com/watch?v=Wzj5EmZnQM0

Chapter 40

504

Circular Resolution in Project Finance Funding with Equity First

Circular Reference 6: Funding in Project Finance Equity First

https://www.youtube.com/watch?v=4ZhiVxXl0Ic

Chapter 40

505

Circular Resolution in Project Finance Funding DSRA

Circular Reference 8: Funding with DSRA Included

https://www.youtube.com/watch?v=lXGPf-4dkWo

Chapter 43

552

Creating Function to Solve Circular Reference

Brazil BNDES Structure

https://www.youtube.com/watch?v=ehCpew9KZ20

Chapter 40

501

DSRA and Funding without Circular Reference

Exericse 101 - DSRA Set-up

https://www.youtube.com/watch?v=coDja2GnkKc





DSRA and Funding with Copy Paste and Data Table

Exericse 101 - DSRA Set-up

https://www.youtube.com/watch?v=RdHjSgTiov0





DSRA and Funding with Goal Seek, Data Table and Copy Paste Macro

Exericse 101 - DSRA Set-up

https://www.youtube.com/watch?v=5frnM5DV6NM





Real Estate Construction Loan with Copy and Paste Macro

IDC and Circularity in Real Estate with Copy Paste

https://www.youtube.com/watch?v=eOmnx8Dow0g





Real Estate Construction Loan with Function

IDC and Circularity in Real Estate with Function

https://www.youtube.com/watch?v=buGo-1-T7BQ





Resolving IDC and Fees in Construction with a Function

Operating Cash During Construction

https://www.youtube.com/watch?v=xRtiDPMhHJc





Corporate Model with Average Interest, Min Cash

Exercise 1c - Corporate Model - Circular Reference

https://www.youtube.com/watch?v=2WIf30VUCSE

Chapter 12

141

Combining Dynamic Goal Seek and Circular Reference

Exercise 1d - Corporate Model - Target Cap Str

https://www.youtube.com/watch?v=vtYnkvyoeaw

Chapter 12

141

Simple Fee Function to Resolve Circularity









Circular Resolution in Sculpting with LLCR without tax

Circular Reference 9: Sculpting and LLCR



Chapter 41

521

Circular Resolution in Sculpting with LLCR with tax

Circular Reference 10: Sculpting and LLCR



Chapter 41

532

Circular Resolution with Sculpting and Taxes

Circular Reference 11: Sculpting and Taxes



Chapter 41

532

Circular Resolution for Sweep and DSRA

Circular Reference 12: Sweep and DSRA



Chapter 43

552

Circular Resolution Level and Annuity

Circular Reference 13: Level and Annuity



Chapter 42

541

Circular Resolution Sculpting and Funding

Circular Reference 14: Sculpting and Funding



Chapter 43

552

Circular Resolution Sculpting and DSRA Interest

Circular Reference 15: Sculpting and DSRA Interest



Chapter 43

552

Sculpting, DSRA and Income Taxes

Exercise 17: Debt Sculpting - Advanced



Chapter 43

552

……………………………………………………………………………………………………………

……………………………………………………………………………………

……………………………………………………………………………………………..

……………………………………….

……………….





Circular References in Project Finance Model


Videos Associated with Corporate Circular Reference


In this section I explain how you can resolve a couple of circular references that arise in corporate models. The most classic circular reference comes from interest expense and interest income that is affected by financing over the course of the year. In an annual model interest can be computed on the average balance. Developing a user defined function is not that difficult to resolve the circular reference because no loop is required. The video and files below illustrate how to solve this circular reference.



Files Associated with Videos











Exercise 1: Simple Circular Reference Logic Resolution -- Fees



Circular Reference Simple.JPG









Text on Circular Reference 1.JPG











Circular Reference Page.JPG










Circular Sweep with NOL.JPG


Circular Reference Corporate Model.JPG


circular sweep 1.JPG

Exercise 4: Resolution of Basic Circular Reference in Project Finance Analysis -- Debt Funding with Target Debt to Capital Ratio



Circular Reference Exercise 3.JPG














Circular reference resolution in debt sizing






Resolution of Circular References in Bank Model





This model applies the principles in the financial library and solves|the debt funding and the debt sizing problems