When you decide your financial models are not as good as they should be, should you immediately send your team on an advanced Excel training course? Whilst this is certainly helpful, there’s a great deal more to financial modelling than being good at Excel!
No, it’s not a silly question and since the “Modern Excel” was introduced in 2010, the changes have been pretty subtle and it can be a little difficult to tell the difference.
There are a few rules for model design that should be followed when designing the layout of a model. Most experienced modellers will follow these instinctively, as they are generally common sense.
Extract from Using Excel for Business Analysis, Chapter Two
Model design and structure can be one of the most difficult parts of building a financial model. Typically, modellers will work from back to front when building their model. The output, or the part they want the viewer or user to see, will be at the front, calculations will go in the middle, and source data and assumptions should go at the back. The image below shows an example of what your tabs in a well-structured model might look like.
I’ve been showing this video supplied by Microsoft in my training recently, and lot of people have asked me for a copy, so here it is! Some of my favourite new features in Excel 2016 are:
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.
The Table feature in Excel (not to be confused with PivotTables or Data Tables!) is a great tool for organising and analysing large amounts of data. For this reason, like PivotTables, they are sometimes not used in pure financial modelling, but as a very useful tool in reporting and analysis, they are also worth a brief mention. See this article for more information about the use of Tables in Financial Modelling.
The two useful features of a Table are:
Those new to named ranges sometimes struggle to see the benefits of including them in financial models. It’s true that most of the time named ranges are not entirely necessary, but there are some good reasons to use named ranges when building a model:
Considering holding a course in-house? There are many advantages of arranging an
(more…)
Now that the Introduction to Financial Modelling course as well as my book Using Excel for Business Analysis has finally been updated for Excel 2013 after several months of hard work, you can imagine my surprise and delight when the preview of Excel 2016 became available for download 🙂
