Rickard Wärnelid, best practice financial modeller and founder of Corality Financial Group, highlights some of the worst examples of complex calculations he has come across in financial models.
In recent articles I have discussed some of the challenges organisations face when aiming to increase the quality of the financial models they rely on for critical business decisions.
A common theme in these articles has been that complex and non-transparent calculations result in a decreased understanding of the logic behind a decision. To illustrate my point, this article highlights some of the worst examples of complex calculations I have come across in financial models used for investment decisions as well as in forecasting analysis. To put things in perspective, the examples I refer to are multibillion dollar infrastructure and resources projects investments.
I am sure that many people reading this article will think that the examples look too good (or bad!) to be true, but these are genuine examples from the real world. The level of complexity in the detail of many financial models used daily is a magnitude worse than what decision makers and users of these models really know. It is an ‘inconvenient truth’ about financial models – if you ignore the issue, then it is not your problem. Therefore many people choose not to review the detail as it would impact them in a negative way.
Going overboard with nested IF-statements
The example below shows what can happen when a model developer gets too excited with the ‘IF function’. This is a common characteristic of someone who has been working in financial modelling for a couple of years and has built up confidence and a need to show off his capabilities with some long advanced formulae. I say ‘his’ because this is to a large extent a male characteristic!
=IF(sen5_internal_refinance_option="Y",IF(mon_date1> sen5_refinancing_date,DF9,IF(AND(sen5_pmt_flag<>0, mon_date1<>sen5_issue_date),sen5_ob* IF(AND(mon_date1=EOMONTH(sen5_issue_date, ROUND(sen5_months_for_first_interest_calculation,0)), MOD(sen5_term_periods,1)>0), +1 +sen5_rate_periodic)^sen5_pmt_freq)* mon_date1Sen5_issue_date)/365,sen5_rate_periodic),0)), IF(AND(sen5_pmt_flag<>0, mon_date1<>sen5_issue_date),sen5_ob *IF(AND(mon_date1= EOMONTH(sen5_issue_date, ROUND(sen5_months_for_first_interest_calculation,0)), MOD( sen5_term_periods,1)<>0),(1+1+ IF(base_rate_override_option="Y", sen5_rate_periodic_ir_risk,sen5_rate_periodic))^ sen5_pmt_freq)* mon_date1sen5_issue_date)365,IF(base_rate_override_option="Y", sen5_rate_periodic_ir_risk,sen5_rate_periodic)),0))
The formula above should of course be broken up into shorter segments which would drastically improve the transparency, risk of error and other people’s confidence in the calculations. Some good examples of how this problem can be avoided can be found in this article on finance modelling mistakes.
Taking Excel named ranges to the extreme
Using ‘named ranges’ in Excel can be a powerful tool. It should, however, be used sensibly. Below is an example of a formula from a model with an excess of 1,700 distinctly different named ranges. This is of course impossible for a new user to absorb quickly. As such, the confidence in such a model is drastically reduced.
=IF(EOMONTH(I$2,-Tax.VAT.Reimbursement) <>EOMONTH(Project.Close,0),- SUM(OFFSET(I114,0,IF(I2-Tax.VAT.Reimbursement* 30<Model.Start,0,-Tax.VAT.Reimbursement)): OFFSET(I114,0,IF(I2-Tax.VAT.Reimbursement*30< Model.Start,0,IF(AND(I2>=Constr.End, H2<Constr.End),0-Tax.VAT.Reimbursement, -Tax.VAT.Reimbursement)))),0)+IF(AND (EOMONTH(I$2,-Tax.VAT.Reimbursement)>= Project.Close, EOMONTH(I$2,-Tax.VAT.Reimbursement)< EOMONTH(Project.Close,6)),- OFFSET(I114,0,-ROUND((I$2- EOMONTH(Project.Close,0))/(Assumptions!$H$53/CtnP),0))/6,0)
Some tips and tricks on how to work efficiently with Excel named ranges can be found in this range names tutorial.
General complexity exceeds most people’s brain capacity
Some Excel formulae seem to tick all the boxes in terms of complexity.
The formula below sums up many things financial model developers should avoid:
- the formula is far too long for comfort
- complex proprietary ‘range name’ convention makes it hard to sense check
- nested ‘IF statements’ in several levels
- mixing off-sheet reference (links to other work sheets) with links up and down the same page (references to row 11 as well as 920) makes it very hard to track the logic
- using ‘VLOOKUP’ which is prone to calculation errors
=-IF(Debt.Bond1.Flag=1, J$434*IF(AND(J$434=1,K$434=0), I924+SUM(J920:J922), IF(AND(Debt.Bond1.Term*P- (J$11-1)<VLOOKUP($B919,Table.Depreciation,4)*P, Debt.Bond1.Term*P-(J$11-1)>=0),(I924+SUM(J920:J922))/ (Debt.Bond1.Term*P-(J$11-1)+1), (I924+SUM(J920:J922))/( VLOOKUP($B919,Table.Depreciation,4)*P ))),J$368*IF(AND(J$368=1,K$368=0),I924+SUM(J920:J922),IF( AND((Debt.Bank1.Term+Debt.Bank3.Term)*P-(J$11-1) <VLOOKUP($B919,Table.Depreciation,4)*P, (Debt.Bank1.Term+Debt.Bank3.Term)*P -(J$11-1)>=0),(I924+SUM(J920:J922))/ ((Debt.Bank1.Term+Debt.Bank3.Term)*P-(J$11-1)+1),(I924 +SUM(J920:J922))/(VLOOKUP($B919,Table.Depreciation,4)*P))))
Complex and non-transparent financial models in Excel result in decreased stakeholder confidence and bad business decisions. By avoiding some common pitfalls (nested ‘IF statements’, extreme use of ‘named ranges’), improving model architecture and presentation and by decreasing the overall complexity of the financial model, organisations take a big step towards increasing the confidence of both internal and external stakeholders. It could well be what stands between an organisation’s failure and its success.
Link
CIMA in business