If we were to list out our guidelines for best-practice modelling here (you get it on our financial modelling course) towards the top we’d have to put “No circular references” and “No macros”. But even we think it’s fun, and perhaps almost necessary, to break the rules once in a while.
The “no circular references” rule
You get your circular reference warning in Excel when you make a model calculation depend on its output. Because the output depends on the calculation Excel gets very confused and you get the circular reference warning. What we’d like you to do next is press “Ctrl Z” for undo, go back a step, and think very carefully about what you’re trying to do. Find another way around the problem.
Modelling with a deliberate circular reference
Although best-practice guidelines are helpful, in real-life modelling it can be hard to avoid circular references altogether. We’re going to give you permission to create only one (you’ll be doing well to limit it to that). It wouldn’t be unusual right at the end of your model build to end up with a circular reference around short-term debt or cash balances. Here’s an example for you. If you click on the picture you can download it:
Turning iteration on
Excel is pretty clever and it can cope with a deliberate circular reference. To get the example above to produce results you’ll have to make sure something called “iteration” is turned on. You can find it in your Excel options. Go File > Options then find the second one from the top “Formulas”. “Enable iterative calculation” is at the top right-hand side of the Formulas section: make sure it is turned on and your model will be producing results.
Circular reference health warnings
Modelling with deliberate circular references and turning iteration on is tempting and seductive but it comes with some serious health warnings. If you leave iteration on you’ll get no warning if you, by accident, create further circular references. Your model might start falling over and, with iteration turned on, it will be very hard to work out why. It’ll probably be something to do with the additional circular reference you created two hours ago but, because you had iteration on and you’ve got multiple circular references now, you won’t know when you did it or where it is. Horrendous.
Never ever work with iteration turned on
So if you are going to push the envelope a bit, and add a deliberate circular reference right at the end of the model build, we’re going to have to say: “Never ever do your modelling with iteration turned on”. Take a copy of the deliberate circular reference. Paste a record of it one side and turn iteration off to work with the model. That way, with iteration turned off, you get your regular circular reference warnings as you carry on with your work.
The circular reference circuit breaker
If you’re going to model with a circular reference, and if you’re going to take note of what we’re saying (“Never ever work with iteration turned on”), you’ll forever find yourself manually disabling the deliberate circular reference and turning iteration off. Then you’ll have to reverse that process to get the model to produce results. Repeating that process all the time could become a little tiresome.
A few of the delegates we’ve had through on our courses are used to operating models that contain a circular reference switch (credit to Rebecca for bringing this one up recently). It’s a cool little trick that helps you turn a deliberate circular reference on and off really quickly. You flick the switch off and turn iteration off to work safely with the model. Then when you want the model to produce results you turn the circular reference and iteration back on. You can see an example of the circular reference switch in the next example below (remember you’ll have to turn iteration on):
Adding a macro
Now we know there’s a rule about “no macros” and for good reason. But if a macro makes your life easier and performs a role that’s not core to the analysis then we’re almost OK with it. We can live with a modelling macro if it’s there just to automate something that’s otherwise boring you stupid. In the example above we’ve created an automatic circular reference switch but maybe we’re forever turning iteration on and off.
Recording a macro
Excel VBA macros are super-powerful. They allow you to record (in computer code) any series of Excel actions and play that sequence back at the press of a button. We’re happy to take people through that process on our courses. We think we can get you 85% of the way there on macros really quickly – the secret is making use of the macro recorder. If you can record a macro you can become a computer programmer without programming.
The iteration macro
In this next example we’ve recorded ourselves turning iteration on. You can see the very simple code that results – it’s just three lines that are doing the work.
' This macro turns iteration on
' We have created the macro just by recording while turning iteration on
' It's only one line of code needed to automate iteration!
Application.Iteration = True
' Then we've recorded ourselves typing "TRUE" into cell B3 to turn the circular reference on
' ActiveCell.FormulaR1C1 = "TRUE"
' But we've used a little hard-won experience to simplify that particular code (that's an entirely optional extra)
Range("B3") = True
' Finally we've added a small piece of macro code to remind the user what's happened https://www.financialtrainingassociates.com/courses/macros/vba-code-examples/
MsgBox "You've just turned iteration and circular refences on (you macro guru you)"
Then we’ve connected the macro to a button (so we can play it back and run the macro automatically). We’ve also included the circular reference switch as part of the macro. Finally we’ve inserted a bit of macro code that builds our self esteem and tells the user what the macro has just done. It’s only a few lines of code that’s involved. This is not a macro that’s going to sink any body’s model.
To view the code download the example above and hit the “Alt F11” Excel keyboard shortcut to get the code window displaying:
Can macros ever be good?
So (furiously trying to justify our flagrant rule breaking here) what we think we’ve ended up doing is creating a macro that:
- Simplifies and automates the process of flicking the circular reference switch and turning iteration off or on;
- Is not core to our analysis or directly affects results;
- Makes our life easier;
- Has the effect of warning the user that the model contains a deliberate circular reference (the great big buttons do that pretty well);
- Tells the world that we’re clever enough to know a bit about macros without building a macro that’s going to ever sink a model.
Some things on that list above are more important than others. You can be the judge of that!
Happy modelling (and occasional thoughtful rule breaking)!
You’re reading stories from the world of financial modelling.
Brought to you with love from Financial Training Associates Ltd, purveyor of the very finest modelling courses.
About Financial Training Associates Ltd
At Financial Training Associates we’re lean mean Excel warriors. We’re so rough and we’re so tough that sometimes we even break a few financial modelling rules. Only very occasionally though.
Find out more about Excel VBA macros
If you fancy breaking the rules once in a while and maybe using macros to automate the most mundane of tasks in your Excel life (the ones that are just boring your silly) you might want to check out our VBA macros course.
Go on – see whether you’re tough enough.