A spreadsheet model without circular references can be called “triangular”. Excel can form a dependency graph and ordering such that recalculation can be done in one iteration. If there are circular references the spreadsheet needs to perform more iterations before the results converge.
I was given a very large spreadsheet implementing an agricultural country trade model. The question was how near triangular is this? With the Excel tool to find circular references, we only find a single case:
Luckily I have a tool that takes an Excel spreadsheet and parses Excel formulas and produces a GAMS representation of this. Essentially each cell forms an equation:
The whole spreadsheet can be viewed as a fixed point expression:
This is of course just a system of nonlinear equations:
When we solve this spreadsheet model as an NLP with Conopt we get some statistics:
Graphically, after reordering rows and columns, we have:
This certainly gives an indication we are not close to a triangular model and we need a simultaneous equation solver to handle this type of model.