My mission is to demystify financial models. Models should never be black boxes: they are simply made up of many layers, which themselves must always be clear and logical. Poor function choice is one of the main reasons behind incomprehensible models.
Of course, some functions can easily replace others. However, there are also some needlessly complicated formulae out there caused by poor function choice.
Excel has 467 functions. I use 23 of these on a regular basis.
Here are some tips about formulae and the top 7 functions, which I used more than 5% of the time in my advanced semi-annual course model:
Around 60% of my formulae use no function at all
For these, I simply use ( ) ^ * / + –
Remember the order of calculation (for example BODMAS, BIDMAS, BIMDAS or PEMDAS). Many a formula is incorrect because of the poor use of brackets.
Of the remaining 40% of formulae, I use three functions for nearly 50% of these:
These are SUM, MAX and MIN
Depending on the time lines, I might use SUMIF
However, SUMIF is one of the chief causes of black box models, so please use it with care. I would only use it to convert, for example:
- quarterly costs or revenues in columns to quarterly costs or revenues in rows in a semi-annual model, or
- semi-annual accounts to annual accounts.
Approximately 12% of my functions are used to select different options:
For selecting options, I either use CHOOSE (if there are up to four different choices) or INDEX with MATCH (if there are many choices or for example a row of ratios where one needs to be isolated).
No mention of IF yet. That’s because I hardly ever use it, but it will get a mention in the next blog when I’ll deal with the functions that I use for less than 5% of the time.