Five Excel Formulas to make you cringe
05 November, 2009
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.
I have studied financial modeling in university but never got the chance to use ot practicaly at work.
need a brush up on my study and skills.
Submitted by heliasingh on Tue, 17/11/2009 - 09:19.
Hi Helia,
If you are interested in brushing up on your Financial Modelling skills, make sure you sign up for the newsletter if you have not done so already, and check out the free mini e-course. http://www.plumsolutions.com.au/7-day-video-series-signup Hope to see you there!
Also check out the prior article on "free financial modelling resources" as there are loads of other web resources you can check out.
Have fun with it all!
Submitted by danielle on Thu, 19/11/2009 - 20:40.
Great article Danielle.
I see so many people breach rule number four, unecessarily long formulas. Often I have seen formulas over three lines long coming from big 4 financial modellers and other "experienced modellers". Your point to break formulas down into logical steps is key in providing a clear Excel spreadsheet or financial model.
Submitted by Brett @ Financial Modelling Training (not verified) on Sun, 18/09/2011 - 21:11.
Post new comment