Professionals working in a finance team might build beautiful reports or financial models which we are enormously proud of, as we have usually put a huge amount of work into. These models are not helpful, however, if other people are not able to actually use or understand them and their messages and use them to make decisions. One of the things – and often the missing piece – for financial modellers is having the skills to present the output of the model in a way that is useful and easy for others to interpret.
One of the most frustrating things about using other peoples’ spreadsheets is that there is no obvious architecture or roadmap. How do you “drive” it and how does the logic get us from A to B? (more…)
Automating repetitive tasks with Excel Macros can dramatically cut down the time you spend building financial models, and can significantly increase your productivity. A well-written (or recorded) macro enables you to repeat operations that you would normally do by hand but much more quickly and reliably. If you find yourself repeating the same action over and over again whilst building or editing your models then the use of VBA can increase speed and accuracy.
At our recent Melbourne Meetup, we heard from from Macro & VBA expert, Marcelo Mendonca from Rixena who specialises in Business Productivity Improvements, and has trained hundreds of professionals from engineering, management, finance, supply chain in Australia, India, South Korea and Brazil. Marcelo gave us a practical demonstration covering:
• Best practice for Financial Modelling with VBA • When to use a formula and when to use a Macro • Tricks for model-building such as finding broken links, or hard-coded numbers using VBA • Case studies of when the use of macros saves time and increases reliability of financial models
Structured Reference Tables have been around for a while, but have become a lot more powerful in recent versions of Excel. They can be extremely useful when using Excel for the purpose of data analysis and modelling but I’m always surprised by the number of people coming along on my training courses who’ve never heard of them, let alone seen them used.
Whether you are a consultant building a model for a client, or an internal modeller, you or the person who has commissioned the model build will – understandably – want to know how long it will take. The answer is never straight-forward, as like many other tasks it really depends on how long you have got (and there’s never enough time!) and how much detail you need to go into. The more time you’ve got, the better the model will be! Some models could take months and months of dedicated work, or you could throw together a very high level model in a day or two.
In a high level model, the assumptions would probably only estimates, as you won’t have had time to validate them with stakeholders, and the calculations will be pretty rough. You also might not have much in the way of fancy colours, formatting, drop-down boxes or tick boxes etc, but the numbers should still be reasonably accurate.
Building a Model Under Pressure
It’s a critical point to remember that even when under immense time pressure, the modeller should never compromise on good working practices. Even in a high level model, best practice should still be followed, correct labeling and documentation of assumptions should be maintained. See Best Practice in Financial Modelling for some guidelines on good practice. If these points have been adhered to, there should be surprisingly little difference in the base numerical outcome between a high-level model that takes a few days, and a detailed model which could take months. If pressed for time, cosmetic features such as those shown below can be omitted.
Time permitting, the detailed model may show:
Detailed assumptions documentation, validated by key project stakeholders
Scenarios and sensitivity analysis, using drop-down boxes, tick boxes or data tables
Table of contents or navigation tools
Colours and formatting, conditional formatting, insertion of company logos
Output summary and detailed analysis of output
Time should be spent on “quick wins” – use your judgement to spend your time on calculations that are material to the model. Don’t waste time on validating minor assumptions which are not material to the outcome of the model.
This article is an extract from the book “Using Excel for Business Analysis” by Danielle Stein Fairhurst
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 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!
Professionals working in a finance team might build beautiful reports or financial models which we are enormously proud of, as we have usually put a huge amount of work into. These models are not helpful, however, if other people are not able to actually use or understand them and their messages and use them to make decisions. One of the things – and often the missing piece – for financial modellers is having the skills to present the output of the model in a way that is useful and easy for others to interpret.
