By Bing Chien Quek, Sydney
People often mistake formula length with complexity. Although it's true that longer formulae are harder to read than shorter formulae that only deal with one part of the problem—there is also the use of multiple functions and levels of parentheses that can have you squinting between the lines. In this article I describe some of the different ways you can measure complexity within spreadsheets.
Financial modelling – evaluating Spreadsheet complexity
Why do we even bother?
In an internal financial model peer review process or an external financial model review, the complexity of the financial model plays a big role in determining the time and effort required to review the model. The ability to quantify the complexity of the model offers guidance when allocating time and resources to perform the review. Several drivers determine the complexity of financial models. Two significant drivers are the number of unique formulae and the complexity of these formulae. Many software products or add-ins identify and list all the unique formulae in a financial model, but few are able to evaluate the complexity of the formulae. The main reason for this is that there is no standardized classification of complexity, and each individual has their own definition. Ask different people to define what a complex model is, and chances are you're going to get very different answers. To further complicate the matter, complexity of a model is relative. What is complex to me could be child's play to someone else.
DEFINING COMPLEXITY
Various methods exist to determine the complexity of a spreadsheet. Here are the key aspects one should consider:
- Number of unique formulae;
- Length of the formula;
- Linkages between formulae (within the same sheet, other sheets, or other workbooks);
- Type of formula—this can be a function that is not frequently used, hard to trace, or just plain confusing;
- The intention and reasoning behind calculations made within the spreadsheet;
- The overall non-technical aspect of the model, i.e., the purpose of the spreadsheet being constructed; and
- The existence and dependency to macros.
Clearly, the complexity factor, from the intention and reasoning behind calculations and the non-technical aspect of the spreadsheet, is highly subjective. As the number of unique formulae, general length of the formulae, and linkages between each formula can be easily assessed and quantified, the main focus should be to quantify the complexity of a spreadsheet using the aforementioned key drivers. I believe this method should be used as the foundation when quantifying the complexity in a model. In order to do this we must first understand what a formula is and what it is made of. NB: The macro component of a spreadsheet is not considered here as, albeit being quantifiable, requires specialized knowledge in VBA and appropriate permission settings in Excel for this to be automated.
THE EQUAL SIGN “=”
First and foremost, all formulae in Excel are indicated by the use of “=” as the very first character in the cell's formula. This will set the base of our complexity calculation.
THE OPEN PARENTHESIS “(“
First we have the identifier for formulae, now we need an identifier for functions. All functions in Excel require the use of an open parenthesis. This can be used as an indicator that a function is being used and the number of open parenthesis, though not 100 percent accurate, is a good proxy to indicate the number of functions within a particular formula. The more functions there are within the formula, the more complex it is.
THE COMMA “,”
We've identified formulae and functions, we now need to differentiate one function from another. Parameters in a function are separated by the use of commas. Generally, the more parameters a function requires, the more complex a formulae is. For example,
=VLOOKUP(B1, A1:A1500, 4, FALSE)
is (albeit arguably) more complex than
=EOMONTH(D1, D2)
The comma sign also differentiates formulae with changing or unlimited parameters. For example, the function
=SUM(J200, J201, J202, J203, J204) or
=OFFSET(A1, 3, 5, 1, 1)
would require more effort compared to
=SUM(J200:J204) or
=OFFSET(A1, 3, 5)
THE EXCLAMATION POINT “!” AND THE OPEN SQUARE BRACKET “[“
A formula that contains off-sheet references or off-workbook references definitely scores extra points for complexity for the extra effort needed to move to a new sheet or open a new workbook. Off-sheet references are indicated by the use of the exclamation point, and off-workbook references are indicated by the use of open square brackets.
OTHER CHARACTERS (<, >, +, -, ^, /, *, &, $)
These characters could also be used to indicate a formula's complexity.
HOW DO WE QUANTIFY AND CALCULATE A FORMULA'S COMPLEXITY?
To quantify a formula's complexity, one could simply assign a number or weighting to a particular sign/symbol mentioned above and calculate how many of those signs are used in a particular formula. For example, using the table on the right, the formula =IF(Inputs!J5=G9, SUM(J7:J8),$G$8) would warrant a complexity factor of 11. This is a tedious process to go through if done manually. We can however automate this process by using VBA to create a user-defined-function (UDF) to calculate a formula's complexity. Here's how.
Set up a table comprised of each complexity item and its associated complexity factor (see table) and name these ranges ‘ComplexItem' and ‘ComplexFactor'
Write a VBE code that has the following algorithm:
Identify formula in question.
Going through each character within that alphabet, test to see if it exists within ‘ComplexItem' and if it does, add in its associated complexity factor.
Note: while there are many ways to do this, this is the most straightforward. There are more elegant ways to count the number of particular characters within a string.
Below is an example of a code written to do exactly that.
EXAMPLE: FORMULA COMPLEXITY
OTHER CONSIDERATIONS
Is this sufficient when calculating a financial model's complexity? Certain functions are definitely harder to understand compared to others, such as INDIRECT, OFFSET, and MOD. Should these be included within the calculation of a model's complexity? What about a model's file size or the modeller him/herself?
BEST PRACTICE FINANCIAL MODELLING
A complex model is generally hard to understand and as a result, anyone who uses the model, be it an auditor, your colleague or your boss, will have difficulty following the flow of the model from inputs to outputs. Since financial models are rarely used by one person only, it is important to keep the model simple and transparent (and in most cases, dummy proof). This avoids what is usually called “key man risk” and means that people other than the builder of the model can update and use it.
In conclusion, here are my tips for reducing stress for future users of your model:
- Keep the model simple, have shorter formulae, use basic functions;
- Avoid off-book references;
- Clearly label what's what; and
- Make it neat and pretty (but do not overdo it).
For more information about reducing model complexity and model risk, check out “Reducing the risk in Excel modelling” from CompAct January 2011 by Rickard Warnelid.
Bing is a Senior Analyst at Corality Financial Group, an analytical consulting firm specializing in advisory, financial modelling, Excel model audits, and training. Apart from regular consultation work, Bing is Corality's VBA guru and a trainer for Corality's public training courses. Bing can be contacted on bing.chien@corality.com, www.corality.com. Follow Bing on LinkedIn.