Last week, I was invited to a podcast about financial modeling. The episode hasn’t been released yet. I’ll share it once it’s available. What I want to discuss here is inspired by my conversation during that podcast. I had a great time during the 45-minute discussion with the host; it was relaxed, and I could be myself without many filters. Reflecting on our conversation, I realized that we touched on a topic I have a deep history and passion for—circular references.

The host asked, “Circular reference or no circular reference?” I said I advocate for the “war on circular reference,” and we laughed! However, after that, I pondered over my stance. Do I genuinely have such a strong issue with circular references that I can declare war on them? I want to highlight that my concern with circular references only pertains to intentional ones. If a circular reference is unintentional and can be resolved by adjusting a link or using algebraic methods, then having that type of circular reference is an error rather than an issue. So, when asked if I have a problem with (intentional) circular references, my answer was: “No, I cannot declare war on circular references.” My interest in understanding how to handle circular references best led me to connect with Professor Edward Bodmer, who has developed methods and tools that enable modelers to address circular references confidently.

So, is a circular reference indeed a problem? The answer that came to mind was a resounding No! The issue is not circular references themselves; it’s how we handle them in our project finance models. To delve deeper, we must first define what constitutes a circular reference is. My friend Mike Flynn, a top-notch financial modeler, gave me this definition: “A circular reference occurs when two objects (or cells in Excel) depend on each other either directly or indirectly, creating a loop of dependencies. Comparing this type of relationship to human relationships, humans are the objects that contain the interrelated dependencies.” Check out Mike’s profile here: Mike Linkedin profile

This brings us to the concept of a relationship. It’s intriguing to compare how we address circular references in our financial models with how we navigate human relationships.

In human interactions, circular relationships involve interdependence and connections, whether between partners, parents, siblings, or professionals. When things are going well, there’s no problem, and everything runs smoothly. However, when issues arise, the circular nature of the relationship becomes more apparent and requires attention.

Here, I’ve identified three typical models for how we humans approach and manage relationships:

Technique 1: The “Let It Be” Approach

This involves tolerating a less-than-ideal situation within the relationship. It’s important to note that tolerance isn’t the same as acceptance. Acceptance falls into the category of Technique 3. This approach isn’t particularly sustainable, as there comes a point where one or both parties can no longer bear the strain of tolerating the situation. Most of the time, this approach doesn’t lead to positive outcomes.

Technique 2: The “Cut It Off” Approach

The name says it all. This is when you opt to end the relationship, freezing memories of the person at a particular point in time and refusing to create new memories with them. However, the existing memories and history with the person remain. Occasionally, triggered by a song or a discussion, those memories resurface, and they might be painful until you “cut off” again.

Technique 3: Creating a Parallel World

This advanced technique isn’t suitable for everyone. It involves self-work and reaching a level of self-understanding where you no longer have relationship issues. You recognize that you’re responsible for your life and cultivate beliefs that grant you access to your inner world. This approach allows you to co-create with people rather than deal with them.

Similarly, we can apply these three techniques to the realm of project finance modeling:

Technique 1: The “Let It Be” Approach – Iterative Models

Working with iterative spreadsheets renders them vulnerable to errors and significantly slows down the model’s performance, making it unsustainable.

Technique 2: The “Cut It Off” Approach – Copy and Paste

This is a common solution adopted in the industry to date. It provides a quick fix by breaking circularity through copying and pasting the parameter causing the issue as values. This severs the relationship between parameters and freezes it in a single scenario. If any scenario changes, the copy-and-paste action needs to be repeated. However, this approach can disrupt the use of Excel’s built-in functions like Goalseek and Datatable, necessitating additional macros to perform these tasks. It also introduces performance issues, especially in complex project finance models.

Technique 3: Professor Edward Bodmer’s Parallel Model

This method, developed by Professor Edward Bodmer, is the optimal solution. It effectively resolves circularity without negatively impacting the model’s functionality. With this approach, you reprogram the equations causing circular references in VBA, then employ this function within the Excel model. This technique not only enhances model flexibility and speed but also aids in debugging. This is because you program the same calculations twice: in Excel and VBA. Often, mistakes originate from Excel formulas rather than VBA code.

If you’re intrigued by the idea of delving into the realm of parallel worlds in financial modeling, I encourage you to visit Professor Edward Bodmer’s website at edbodmer.com. There, you can learn more about this technique. If you are eager to dive deeper into this innovative approach, please don’t hesitate to contact Professor Bodmer or me. We’re here to guide you through the nuances and assist you in developing a more comprehensive understanding.