Circular references

A circular reference is where a calculation refers to itself directly or indirectly. For example, it’s easy to accidentally create one with interest, which is cash times an interest rate, but since interest is paid from cash, cash depends on interest which in turn depends on cash. Modellers know to avoid this by calculating interest from cash before interest, but sometimes circular references aren’t so obvious or so easy to avoid.

Detecting circularities

Openbox will automatically detect and warn about circular references during validation . It will tell you whether there are any and which calculations are involved. Note that it will detect all circularities but only tell you about one at a time. 

Dealing with circularities

Circular references are generally seen as bad practice in financial models. The options here are:

1. Change the formulas to remove the circularity – for example, by using opening balances rather than closing balances. You will need to judge whether this is a reasonable approximation for your modelling.
2. Ignore it and build the model anyway
3. Use a copy/ paste block with associated macro to iterate the model. This is the standard option.

For Option 3, you need to specify where to break the circularity – at which calculation. In a future version of Openbox, it will suggest where to break it.

Openbox will then add new items with “copy” and “paste” suffixes, and a macro. When you get to the Excel model, you press a button to run the macro. It calculates the spreadsheet, then copies the  values in the “copy” cells and pastes them into the “paste” cells. Then it calculates again and checks if the “copy” cells have changed. If they have not changed (much) then it stops, otherwise it repeats the process. This should make your model converge to the expected answer.

This is an expert feature, and we recommend you familiarize yourself with the approach before using it. You can find more information in the Financial Modelling Handbook here. 

Breaking circularity manually

If you know already that your model contains a circularity, you can break it by right clicking on the calculation you want to break it at, and selecting “Break circularity”. 

Openbox will insert copy/ paste blocks, and the circularity macro, as described earlier.

Ignoring the circularity

While building models with a circularity isn’t best practice, we recognise that sometimes you might want to do it. So you can click “Ignore” in the circularity window, and Openbox will create the model anyway. You can then use Excel’s iterative calculation mode. 

Note that the Openbox circularity detector is more cautious than the one in Excel. So sometimes, Openbox will highlight a circularity that Excel doesn’t. For more information on this advanced topic, click here.

Circularity in the preview

When Openbox creates a preview of a model with a copy/ paste macro, it will ask you if you want to run that macro.

For very large models, this could take 30 seconds or more, because the model has to be recalculated many times.

Troubleshooting

Because the “copy” and “paste” blocks are standard Openbox calculations, they could be modified or deleted. If this happens, Openbox will warn you during validation.

As part of the process, Openbox tries to insert VBA into your model, as noted above. Excel’s security settings can prevent this. To allow access:

1. Open Excel
2. Click “File” then “Options”
3. Click “Trust Center” then “Trust Center settings”
4. Click “Macro settings”
5. Tick the “Trust access to the VBA object model” box.
6. Click OK as necessary until you get back to the main Excel window.
7. Close and restart Excel.

Note that this can cause security risks, so please confirm with your organization before doing it. We only offer these steps as information and don’t take responsibility for any consequences.

See https://answers.microsoft.com/en-us/msoffice/forum/all/trust-access-to-the-vba-project-object-model/fb184cc1-ea20-443d-9a2a-9d68e6ef7564 for more information.