Corality has been contracted to write for the Society of Actuaries newsletter. Second in this series, Bing Chien, Analyst at Corality, presents an article illustrating the complexity of formulae used in financial modelling.
Complexity plays an important role in financial model reviews as it will determine the time and effort required for the review and translates into the dollars you will need to spend on the review. When people think about complex formulae they often mistake formula length as the main indicator of complexity. Although it’s true that longer formulae are harder to read than shorter formulae, the number of unique formulae also plays an important role in the complexity of financial models. In his article Financial Modelling – evaluating spreadsheet complexity Bing Chien looks at the different aspects of spreadsheet complexity.
Key aspects of financial model complexity
Bing points out that the complexity of a spreadsheet depends on various aspects:
Number of unique formulae
Length of the formula
Linkages between formulae (within the same sheet, other sheets, or other workbooks)
Type of formula
The intention and reasoning behind calculations made within the spreadsheet
The overall non-technical aspects of the model
The existence and dependency on macros
It is essential to consider these factors when trying to quantify the complexity of a model. Bing further explains the foundations that make up a formula, including the equals sign, the open parenthesis, the comma, the exclamation mark and open square bracket as well as other characters that are most commonly used and will indicate a formula’s complexity.
The article Financial Modelling – evaluating spreadsheet complexity also contains different examples on how to quantify and calculate a formula’s complexity, automating the process by using VBA to create a user-defined-function (UDF).
"Keep your financial model simple. Making it 'dummy proof' will save you and any user of your model a lot of headaches" said Bing Chen, Analyst.
Corality’s best practice financial modelling methodology, SMART, promotes exactly that and is internationally recognised for helping companies develop more effective, robust and transparent models.
So avoid undue stress for any future users of your model and keep your model simple with shorter formulae and basic functions!
The Society of Actuaries is a professional organisation serving 22,000 actuarial members worldwide. The SOA’s professional interest section publishes newsletters containing articles of specific interest to their members. CompAct is the Technology Section newsletter, published quarterly, containing articles, book reviews and other information useful for actuaries.
Read Bing Chien’s article ‘Financial Modelling – evaluating spreadsheet complexity’
Need more information on reducing model complexity? Check out ‘Reducing the risk in Excel modelling’ by Rickard Warnelid.