Building a financial model can be a complex task, especially when you’ve already spent a lot of time setting up formulas, and conditions, and now your model keeps throwing errors that you can’t figure out. A good financial analyst will follow certain discipline while building a financial model and will avoid any typical mistakes that one is prone to make. Below is a list of a few common mistakes that you must keep in mind while building your model in Excel.

Hardcoding Assumptions

Where ever possible avoid hardcoding numbers. When you need to make an assumption about a number, create an input cell in a separate area and refer to that cell in your model or formula.

Sum Formulas

You need to be careful while using sum formulas in excel. For example, if you have subtotals, and totals, ensure that correct cells have been selected while calculating sums. No entry should be included twice, especially the cells containing subtotals.

Arithmetic Errors

Scan for formula errors. For example, check if the formula in parenthesis is the way it should be. Verify for one or two data sets, before you actively use it in your model. (1-X)/0.9 is not the same as 1-(X/0.9).

Large Formulas

If you have a large formula, you are likely to make more mistakes. Simplify the number of steps of large formula into smaller, doable steps that you can check and pinpoint errors far more quickly.

Right Number, Wrong Place

Ensure that the numbers are entered at the right places. Be clear about where to use relative referencing and where to use absolute referencing. Use absolute referencing to lock in places for certain values. This is important especially when you have the same formula replicating in many cells.

Wrong Formula

A common error is to use two similar sounding formulae in Excel for a particular model. For example, you use Count() when you actually want to use CountIf().

Source File Error

Very often models are built from a source file that has come through e-mail. It is extremely important that the source file data is accurate and usable for further modeling or references.

Sign Errors

A very common error is to mix up which numbers should be positive and which ones negative. For example, in a cash flow model, any outflow should carry a negative sign and any inflow a positive sign. The formulas such as NPV and IRR will give wrong results if signs are wrong.

Working with Dates

Date functions can be annoying. Avoid manual calculating such as adding 30 days to reach another month, and instead use intelligent date functions provided in excel.

Circular Reference

This happens when you refer the cell containing the formula into the formula itself. You need to absolutely avoid this.

Decimals and Percentages

You need to be careful about cells containing decimal and percentages. In the entire workbook represent numbers either as 0.07 or as 7%. Choose one style to avoid confusion.