When creating a financial model, there are always multiple ways to write a formula which will all give you the same result. The key is to write formulas intelligently so they are easily deciphered by yourself at a later date, or by another party reviewing your model. As well, writing a formula one way may cause problems for your model at a future date when you start inserting rows and columns, and expecting the new data to automatically be included in calculations.
Don’t get branded as a novice
As a financial modeller and trainer, I’ve seen formulas bad enough to make the even the most seasoned modeller shudder. Below is a sample of the sorts of formulas that if used in your models will brand you as an inexperienced or novice Excel user.
- Ongoing addition of multiple cells such as =A1+B1+C1+D1+E1 instead of the sum function: =sum(A1:E1). Time consuming and prone to error. Need I say more?
- Nesting a fixed number, such as a percentage: =.75*A1 where .75 may be cost of goods sold. Instead, use an assumption cell where the .75 can be changed easily. This is going back to the golden rule of never entering hard-coded numbers into formulas.
- Creating an average by adding all the cells and dividing by a set number.
E.g. =(A1+B1+C1+D1)/4 instead of =Average(A1:D1). - Unnecessarily long formulas. As far as I am concerned, the new resizable formula bar is a completely redundant feature of Excel 2007. Your formulas should never be that long! They should be broken down into logical steps.
- Excessive use of the IF formula. You can have up to seven nested IF statements within one formula but that does not mean you should!
As simple as possible and complex as necessary
Most of these are examples of inefficient use of Excel where a more sophisticated function is available. However, there is absolutely no point in using a fancy function just for the sake of it. “As simple as possible and as complex as necessary” is a good rule to follow when it comes to formulas and modelling.
Remember when building your financial model you want to make your formulas as transparent and easy to follow as you can. You also want to give the user as much flexibility and power as possible while avoiding confusion or potential for error down the road.
Hi Danielle,
We clearly live in the same world and could agree and agree and agree with all of your comments and I am sure you could have gone on some!
I would add that the breach of the concept behind point 2 is in my view the fundamental difference between a ‘decision support model’ (of any system not just $’s) and a ’spreadsheet’.
On the flip side, I might take point 3 one step further and suggest:
1. SUM(values) / COUNT() or
2. SUM(values) / Sum(1,0 inclusion counter) – my preferred.
This might sound pedantic until your models are translated and used in different languages where certain functions although technically translated do not always work as well as you plan – especially at the eleventh hour!
I agree in many respects. However, your comment:
“As simple as possible and as complex as necessary”
I like this comment but one major factor when determining the most appropriate method is to understand how excel evaluates each method. For me the best method is the one that will operate most efficiently. Take volatile worksheet functions for instance. I frequently revert to what seems to be a more complex method but I have chosen to do so because I am determined to avoid volatile functions unless absolutely necessary! See this article to understand what a volatile function is and why they are best avoided: http://www.decisionmodels.com/calcsecretsi.htm
Further, it is worth noting how Excel will treat a sum function and how it will treat cells added using the + method. The SUM method will ignore text values whilst adding a text value with a numeric number will yield a #VALUE! error. This suggests that SUM should almost always be the preferred method. For instance, lets say I want to add A1 with A3. Most will use =A1+A3. I would rather opt for =SUM(A1,A3) because if either of the references houses a text value it will be ignored in the evaluation (treated as zero value). Just thought I would add this to support your recommendation.
With respect to the size (length) of a formula: I agree that a formula should be as short as possible but the decision to use a shorter method should not be driven by the desire to produce shorter formulae. Again I want to choose the most efficient method and if this means choosing a formula that will involve more characters then so be it. We need to appropriately name formulae where they may be used repeatedly and include in your model a list of all names, what they refer to and a brief description of each. This makes complex formulae more auditable. We can also use carriage return in our formulae to break it into steps to make it easier to follow, something I do regularly if I feel that my formula is becoming too long and quite complex.
As for creating a list of names in a workbook, it involves very simple VBA. The following in a standard module or run from the immediate window:
ActiveSheet.Range(”A1″).ListNames
This one-liner will populate a list of all names in your workbook and their references. The list will be provided in the Active Sheet in columns A & B. Then all you need to do is provide a description of each.
I like your comment about using too many IF functions in a formula. There is very often a shorter and more efficient alternative. And where there is not we also need to consider how a stepped approach might make our models easier to follow. A steppped approach doesn’t necessarily have to compromise the efficiency of the model.
An illustration of an unnecessary complex IF formula, and one that I frequently encounter, would be to return a value category for a given cell value:
=IF(A1>100,”101+”,IF(A1>75,”76-100″,IF(A1>50,”51-75″,IF(A1>25,”26-50″,”0-25″))))
which could be substituted with a lookup:
=LOOKUP(A1,{0,”0-25″;26,”26-50″;51,”51-75″;76,”76-100″;101,”101+”})
And although I have used an inline array in the LOOKUP one should really look to occupying a table instead which will make it easier to update/maintain, and make the formula considerably shorter.
With respects to using constants within formulae: I completely agree! If you need to refer to a constant then stick it in a name that can easily be updated and flow through all of the dependent formula. For instance, VAT 17.5% is fine to house in either a cell or a name, but should never be used as a constant in a formula! Good point!
Some good points here made Jon. At the risk of starting a never ending loop (!) I might suggest a couple of points that I take into account when managing the models we produce (daily) in the world of Project Finance.
1. Computational Efficiency
This used to be a primary factor, driving formula and model structure. Although still a consideration, desktop computing has far exceeded the current requirements of a typical project finance model. The number of models taking an hour to ‘calculate’ in the market these days is very small. Even our largest and most complex models take less than 5 seconds to calculate – and that’s a long time. Accordingly, I drive our companies development philosophy to be more aligned with whatever makes it easier for the user/auditor to understand rather than speed of calculation. I started modelling on Quattro and SuperCalc5 so have been through the pain of slow models.
2. Formula Length
For me sophistication is a definite plus if it makes it easier and quicker for the user/auditor, for example SUMPRODUCT() rather than building up A.B+C.D+E.F is arguably better but if it was just A.B+C.D this might not be the case.
Warning, the following may make you cringe…, we have a rule at Navigator which is ‘the rule of thumb’:
“if the formula is longer than your thumb when held up to the formula bar, its too long, use another line”.
..of course from time to time a rule such as this gets breached but if it is generally followed you end up with the model being composed of lines that a user/auditor can generally understand and check in their heads – financial modelling does not need to be any more complicated than this in my view (for 99.9% of the time, the other .1% requires a rocket scientist!)
Your LOOKUP is a perfect example of this, the vast majority of users would not understand { } and models are built for the user to be able to use and understand.
“Doesn’t this mean we use lots of rows?”
Yes it does but in our philosophy lines are ‘free’ but formula length and complexity is very ‘expensive’
..the cost of many lines can be managed with good layout, headers and data grouping. Complexity has no solution and a high cost (lack of transparency, higher error rate, time needed to explain it..etc)
No direct conclusion but wanted to share with you that the shortest calculation isn’t by definition the best, within the constraints of our market anyway, the market where the builder is never the only ‘user’ and the cost of an audit is watched very closely.