LOOKUP functions, especially VLOOKUPs are very commonly used in financial modelling – sometimes a little too commonly used! Knowing when and how to use them – and use them well is a critical skill for any financial modeller. Let’s explore some of the problems with LOOKUP functions and how to make their more robust.
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.
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:
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
Whilst not the most sexy topic for a blog article, this is probably the thing that I’m asked most about by clients. Because many modellers are self-taught, a lot of them don’t necessarily adhere to best practice. Or perhaps they do – but don’t realise why they are doing it! I thought it might be helpful to lay out my views on the topic, and as always, I’d welcome any reader input…
by Danielle Stein Fairhurst
When you decide your company’s financial models are not as good as they should be, is the first thing you do; send your staff on an Advanced Excel course? Whilst this is helpful, there’s an awful lot more to Financial Modelling though than being good at Excel!
Do you need an Excel Jockey, a Finance Wizard, or a Financial Modeller?
(more…)
I’ve often been asked the difference between a spreadsheet and a financial model, and there is a fine line of definition between the two. In a nutshell, an Excel spreadsheet is simply the medium that we can use to create a financial model. (Of course there are other programs besides Excel that can be used for modelling, but that’s another story!)
OK, you’ve taken the plunge – gone out on your own – told the boss to stick it and set up your own business. You’ve left the corporate world behind and you’ll be playing by your own rules from now on! No more management hierarchies, no boring meetings, no budgets and no getting sign-off to spend any money. From now on, you’re your own boss and no one can tell you what to do.
(more…)
In a volatile economic environment, creating a business case for a new project or product which contains financial projections is an extremely difficult task. Looking at historical data and extrapolating the numbers to create future projections simply doesn’t cut it anymore.
(more…)
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.
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.
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.
“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.
