Help! I have a Cirularity! What Should I Do?”

Potentially you haven’t anchored something, or have linked to the wrong item. This is good because it’s a quick fix.

Or you’ve truly created a circularity by not understanding the model flow (yeah – this is a tricky one, but to understand and to fix), or someone else has created the macro.

Usually what happens is Excel will alert you to a macro with a warning sign. And then it will show you a list of trace dependents. You can follow those dependents around the sheet, double clicking them and popping in and out of sheets.

Finding the circularity

Your options are

• Make note of how the circularity forms. Time required: 30 mins – 3 hours

Make note of what is dependent on what – I suggest you do create a new sheet within the model and have the chain of dependents referenced from here.

• Make a stab in the dark and attack the usual suspects. Time required: 10 mins – 20 mins

Here is where, with a little bit of knowledge, you can test the most likely suspects. They are by the way

Interest income – do you calculate interest income based off the bank account, but the bank account

Any interest calculation that doesn’t calculate off the opening balance, but rather off the average balance, or the closing balance

In a Project Finance model: Debt sizing through gearing, or through DSCR. Or DSRA initial size. Focus on the actual debt amount, and the equity amount that you put in the models.

And tax calculation which tries to pay tax based off a future Taxable Income, and not one in the past (although technically accurate in some jurisdictions, this is a financial modeling simplification we are happy to make)

Anything where the calculation of one item depends on conflicting hierarchies of cashflows – like repayment of a Working Capital account which depends on the cash available to it… which depend on it’s drawdowns… etc etc.  Not a clear example, but hey it can happen.

Ok so armed with this knowledge – you may elect to go with Option 2… however have to divert back to Option 1 if you find it’s none of these items suggested above.

Breaking the circularity

In many case of the above, you’ll need to break the circularity before you find it. That often means removing the link of the items above.

In the case of the tax paid, it’s knowing the model simplification of delaying tax paid by one period after it is calculated, or for interest calculation, calculating interest expense based off the opening balance of the account.

In some cases, you’re going to have to create break the circularity by have a copy line, and a paste line. I.e. we break the circularity not from a model simplification, but from being in control of the iterations that are required to resolve an actual circularity.

And yes, sometimes we do need those iterations, and a circularity is valid. Like in debt sizing (figuring out how much debt you need based off your project cost requirements… but the interest off that debt forms part of the project cost requirements, so a Loan To Cost percentage will calculate a higher debt amount, and therefore a circularity).

Hence we need a copy line and a paste line, to break the circularity. But we need a Macro to bridge that circularity again … so it acts kind of like a lightswitch.

So the next time you have a circularity, hopefully you now have the tools to make it more manageable. And see the blog on Macros here to learn more about how they work.