The upgrade from Excel 2003 to Excel 2007 is probably one of the most significant changes for Excel users yet. There are many new functions, most of which are an improvement to 2003 but they do take some getting used to! As a financial modelling consultant and trainer, I’m often asked by my clients what are the advantages and disadvantage of upgrading to Excel 2007 and whether organisations should even bother.
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.
The array formula is probably one of the most powerful tools in Excel – but also the most under-used. For some reason it is a tool that is shunned by many – even advanced users – of Excel. But just because most people don’t know how to use them, does that mean they should be avoided in a financial model?
Thanks to all of the subscribers who contributed to our Financial Modelling attitudes, trends and uses survey!
Key findings were:
Consistent with our commitment to provide you with the latest in financial modelling tools and resources, Plum Solutions has joined forces with boutique financial planning firm, Majella Wealth Advisers to bring you an Excel-based calculator to help you calculate how much you can benefit from salary sacrificing into your superannuation account.
For a more recent article, please see Excel 2016’s Top Ten Tricks
The upgrade from Microsoft Excel 2003 to Excel 2007 is probably one of the most significant changes for users yet. There are many new functions, most of which are an improvement to Excel 2003 but they do take some getting used to!
How much will you save under the new 2008/2009 financial year
taxation changes? As of 1st July 2008, the tax brackets are changing,
which means we all pay less income tax! Download our handy new
Excel-based tax calculator and find out roughly how much you will save.
“Hmm, these numbers don’t add up…”
Sound familiar? We’ve probably all said this at one time or another, and it can be a frustrating experience.
In this article, we’ll explain the pitfalls of financial modelling in Excel, and give you a few survival tips to help you get accurate and reliable results from your models.
Microsoft Excel is a very flexible, powerful and ubiquitous tool for the purpose of financial analysis, however, astute users understand the dangers of a poorly built financial model.