You have spent days and nights on your model, but something still will not work? That happens all too often. This article gives you a quick overview of the most common mistakes in financial modelling with Excel.
Wrong sums: including values more than once, or not at all
One of the most frequent mistakes when building models with Excel are incorrect sum formulas. A typical example would be the parts of a balance sheet:
Here, Fixed assets are a subtotal of Intangible assets and Plant and machinery; the Current assets are a subtotal that sums up Inventories, Trade receivables and Cash. The two green formulas show how Total assets can now be calculated correctly. The red formula, however, includes the subtotals again. In this case, you have two choices:
- Be diligent, always double-check your formulas and include checks. (Actually, that should not be considered a choice but rather your typical approach towards modelling.) A typical lead for having included values too often is that overall sums are a multiple of the expected sum. In the example, the correct sum is 30,376, and the wrong formula shows twice that (60,752), because every single line item is included twice via the subtotals.
- In cases like the one above, you can use the SUBTOTAL function with function code 9 instead of the SUM function. The SUBTOTAL function over a cell range will never add up the results of other SUBTOTAL functions, so your single line items will not be added multiple times.
A very typical mistake in accounting and finance is mixing up positive and negative numbers - ironically, because a minus can make all the difference. What is most common in financial modelling is mixing them up when building the cash flow statement, because we tend to forget: Is an increase in net working capital added or subtracted? - and so on. You should keep a template of a cash flow statement with line item descriptions such as "+/- Decrease/increase in X" (for example, take the one from this website's article about the cash flow statement). That will spare you the trouble the next time you build a model.
Using hard values where you should use formulas
It happens all too often that you will use a hard value for more than one formula - meaning that if that value changes, you need to adjust a lot of cells. A common example is using an inflation rate in your calculations. To ensure consistency, try to make sure that only cells defined as input cells contain hard input values.
It is hard to generalise this kind of error - but it is one common mistake to put brackets in the wrong spots or forget them where they are required, for example when multiplying sums. Don't forget:
is not the same as
Letting VLOOKUP and HLOOKUP settle with "almost the same"
VLOOKUP and HLOOKUP are powerful functions: They allow you to retrieve value from data tables by using one index value. The "V" and "H" indicate the direction: vertical or horizontal; otherwise, the functions are the same. The syntax is as follows:
A common problem with the two functions is simply forgetting the last parameter, range_lookup. That will not cause an Excel error as such, because it is an optional paremeter. By default, it is set to TRUE - in that case, if VLOOKUP or HLOOKUP cannot find the specifiec lookup_value in the given table, they will settle for similar values. While that may be a good solution for some purposes, it can obfuscate errors in other cases, when VLOOKUP/HLOOKUP should have returned a different value or none at all (because something else in your model is wrong). To sum it up: Make sure to always define the range_lookup paremeter when using VLOOKUP or HLOOKUP!
Errors when working with dates
Dates are not an easy affair. That goes for romantic ones as well as those in a calendar. A typical rookie mistake, for example, is to add 365 to one date to get from one year to the other, ignoring gap years. Spare yourself the trouble and use Excel's sophisticated date functions like EOMONTH.
You should also be aware of which date conventions apply for bank liabilities in your model. Usually interest will be calculated based on a theoretical year with 12 months of 30 days each, resulting in a total of 360 days. Wikipedia has a useful article on day count conventions.
Not breaking links to external files
An embarassing mistake that happens in practice more often than it has to: All calculations work fine on your own computer, but when you submit the model to your client, they get error messages because you were still linking to external data sources. Always make sure to get rid of external links that are not (or not anymore) intentional. Use Excel's "Edit Links" menu:
Or, if you prefer the "old-school" way, just search for "[" or "]" in the workbook, as those indicate links to external files.
When you include the result of a cell in that cell's formula itself, you create a circular reference - for example, like this:
The sum in A4 contains itself. Excel will warn you about this and offer solutions to circumvent this problem. While you can build models with circular references, it is not recommended to do so. That practice is prone to causing problems and can become even more annoying when you distribute that model to other users. That is especially true when your circular references are not as simple and obvious as the example above, but stretch out over multiple sheets with intermediate formula results and so on. Therefore, when you actually need to solve circular problems (such as WACC calculation), you should preferably use VBA macros and iterations.