Let’s keep decoding some key learning points in financial models.

1. Avoid using VLOOKUP

VLOOKUP is one of the most popular functions in Excel. Whilst it is a simple function, it has many limitations including:

• it cannot lookup and return a value which is to the left of the lookup value;
• works only with data which is arranged vertically;
• would give a wrong result if you add/delete a new column in your data; and
• requires more processing power from Excel because it needs to evaluate the entire table array you have selected.

The INDEX-MATCH function can address all these issues. Always choose INDEX-MATCH over VLOOKUP as it leads to fewer errors and gives you a lot more flexibility. At its worst, the INDEX-MATCH method is effectively as fast as VLOOKUP; at its best, it’s much faster.

2. Model dates as numbers

In Excel, all dates have a numerical value (e.g. January 1, 1900 = 1). Once you understand this, it allows you to model your time axis more efficiently as the numerical values allow you to easily reference dates with logical functions.

Enter one date (e.g. the ’model start date’) and then write all other dates as EDATE (returns the first day of the month) or EOMONTH (returns the last day of the month) functions. If your project starts three months later than expected, your model should adjust accordingly via an easy change of inputs.

3. Don’t create ’daisy chains’

A daisy chain is a series of linked links where the last link is not a direct link to the original input or calculation but is linked to an intermediate link, which in turn is linked to the original source. Daisy chains reduce the navigational effectiveness of links by forcing the user to follow a series of steps in order to locate the original input or calculation. Furthermore, daisy chains impair the efficient use of the F5+ENTER ‘return operation’. All links should always refer to the immediate source input or calculation.

Read the first blog on avoiding common Financial Modelling mistakes.