If you would like to use the circular reference template, I suggest that you become comfortable with the process of resolving circular references. I further recommend that you try and apply the template model to simple circular reference cases where you can see what is going on. These simple examples are included in this sheet. Of course, if you cannot get the simple cases to work, how could you possibly trust the complex ones. If you have suggestions about this please e-mail me at firstname.lastname@example.org.
Example 1: IDC Only
The model below is a simple annual model with just about nothing except a circular reference for IDC. The model provided has a circular reference with dreaded blue lines. You are to open this model, insert the circular template (and copy over the UDF) and then resolve the circular reference. My idea is that you do this in a transparent manner sort of like a copy and paste macro where you can see the calculated and fixed (but not fixed as it comes from a UDF).
To resolve the circular reference for this case and other cases, you can use the following four step process.
Step 1: Copy the UDF from the circular reference template (press the ALT, F8 to find the function in the circular reference template) and copy the template into a new sheet (you can use the ALT, E, M short-cut).
Step 2: Attach the different input variables from the model for the interest rate and the debt percent to the variables that are in the circular reference template sheet that you copied from Step 1.
Step 3: Enter the circular template function and use the SHIFT F3 function to find the variable names. When you are finished entering the variables press the SHIFT, CNTL, ENTER keys to get the variables. For this exercise you can only get the first line.
Step 4: Find the variables that a causing the circular reference in the file and attach those variables to the function output in the circular template page.
Example 2: Sculpting and Taxes Only
The model below is again a very simple annual model with no construction period (and therefore no IDC). But this one has sculpting and taxes that create a circular reference. You are to try the same process and again copy and paste the circular reference template into the simple sculpting and taxes model.
Example 3: DSRA Funding Only with Fixed Debt