Even small mistakes when building financial models can significantly affect your analysis. According to a recent survey, 70% of financial models contain errors that affect their results. So let’s decode some major mistakes in financial models.

1. Wrong sums

If you have subtotals and totals, ensure that the correct cells have been selected while calculating sums. No entry should be included twice, especially the cells containing subtotals. You can use the SUBTOTAL function instead of the SUM function. The SUBTOTAL function over a cell range will not add up the results of other SUBTOTAL functions, so your single line items will not be added multiple times.

2. Hard values

A common error but repeated time after time. Never hard code! If that value changes (e.g. inflation rate), you will need to adjust a lot of cells, which increases the risk of potential errors. When you need to make an assumption about a number, create an input cell in a separate tab and refer to that cell in your model.

3. Circular references

While you can build models with circular references, it is not recommended to do so. Such practice can cause problems and is very annoying for users, especially if your circular references are not simple and are linked to multiple sheets. When you actually need to solve circular problems, you should preferably use VBA macros.

4. Nested Ifs

Never use nested Ifs as they reduce transparency. They take a long time for users to understand and they are likely to contain errors as there are multiple combinations of logical steps.

Flags can be used to evaluate elements of the logic and break them into separate steps.

5. NPV function

NPV in Excel is widely used but commonly misunderstood. The NPV function calculates the present value of a series of cash flows, but it does not net out the original cash outflow at time period zero. This original cash outflow needs to be manually subtracted out when using the NPV formula in Excel. The XNPV function can be used instead of the NPV function.