17 Financial Modeling Tips & Tricks
by Andrew Grigolyunovich, CFA, AFM
Currently ranked #12 in the world for financial modeling
Besides my core job – financial modeling consulting, I also do some financial training for CEOs, CFOs and financial analysts. Quite often people ask me for some tips and tricks for financial modeling, so I just decided to put together some of the most useful financial modeling tips in this article.
General rules
1. ASAP rule – keep your financial model as simple as possible. The simpler the model, the less chances for a mistake; Many people know Excel formulas and functions, but very few of them are talented financial modelers indeed. Large file size might justify the amount of work you’ve spent, but it won’t bring your clients back – people like simple rather than complex things.
2. Think of the users. Will these be many different people with little Excel experience using the model you are about to build? Congratulations, you will learn so many ways of how to ruin your model from them! Then you will learn how to use formula protection to fool-proof your model. But then again they would still find a way to ruin it.
3. Follow one of the financial modeling tips called “Rule of thumb” – most of the formulas should not be longer than the length of a thumb finger; Instead of writing a long formula, split it into several helper cells.
4. Use the same project names or codes throughout the model. Name them on the Assumption sheet once and link all the other sheets back to the assumptions. Thus you can forget about re-naming projects on each and every sheet if some changes arise.
5. Adding rows and/or columns is a potential source of errors; One of them could be referencing VLOOKUP/HLOOKUP functions with fixed number of columns/rows as the third parameter in the formula. Be careful with these formulas in the model. On the other hand, adding rows below a table could exclude the row from the range of a SUM function.
6. Instead of adding rows throughout a model when a new project or cost item appears, build in Placeholders (extra rows/columns) for the projects/cost items coming your way in the future. A lot of time will be saved.
7. To make your model look nicer, hide these extra rows/columns with Hide function or, better, with Group/Ungroup function.
8. Try to use separate sheets for inputs, calculations and outputs. However, I strongly believe that in many cases it is much more convenient and transparent to put some of the inputs within the calculations sheets (I might get some counter-arguments from other experienced financial modelers here though).
Working with several files
9. Try to keep all the calculations within one file, as long as it is possible. Linking files together is a source of error and inconvenience, especially if you have various users with different level of experience working on the file.
10. One of important financial modeling tips is to avoid models with size over 40 Mb, they will start working slowly. Models over 200 Mb are almost impossible to use. My personal record was 500 Mb and I am not proud but rather ashamed for that model – the client used it for a very little time.
11. Every time you press Enter, Excel recalculates all the formulas in the model. If a file is working slowly, you can go for manual recalculation of formulas to save time (Formulas – Calculation options).
12. If there are several people providing data and they don’t need to see the big picture – keep all data in one folder and use passwords for the files they shouldn’t see. Or, ideally, appoint a key person to just copy-paste the reports provided by other people into the folder.
13. When there are several files that feed the model, it is a good idea to open them all to ensure that you have updated all the links and formulas.
Other financial modeling tips and tricks
14. Use super-large numbers to check the impact of each input and validity of the model. When your model is operating with thousands, put a number in billions, as an assumption you would like to check. You will easily see which cells in the model are affected and which are not.
15. Excel store dates as numbers. E.g. Aug 22, 2018 is stored as 43334 while being shown as 08/22/18. (Why 43334? Because it is exactly 43334 days after Jan 1, 1900 which is stored as 1) If you enter a date in a way other than the regional settings of the computer, eg. 08-22-2018, Excel might treat it just as text and won’t understand it as a date. This is one of the key sources of errors in models. Good news – you usually spot these errors easily. Even better news – Data Validation is a great tool to control user inputs.
16. If you construct e.g. a Cashflow statement, it is convenient to show cash outflows as negative numbers. Then you just have to sum a corresponding range, without adjusting the signs for each line within the formula itself. This is more relevant to European countries that are more used to operating with positive numbers rather than with negative numbers.
17. Opt-in for our mailing list at CFOTemplates.com so that you get the latest articles and financial modeling tips as soon as they are released. I personally hate spam and will send only a very relevant content on an occasional basis.
