General Tips for a financial modeller

Here are my two cents regarding how someone must build financial models, in general. These advises are not specific to a type of model (i.e. Valuation, Cash flow etc), but is general advice for good modelling on Excel.

Always remember that your model will be used by non-modellers and might be changed by others, so here are some rules that can make everybody’s life easier. Here we go :

First and foremost have a structure

Think about who your user is, and separate the uses / logic in each of your sheets. Depending on the size of the model there might be sheets for light users (scenario control, outputs) and heavy users (calculations). As well, you may also have inputs, which can be time dependent or time independent and calculations, which can be annual, semiannual, and monthly. Plan your workbook and have a structure in mind when you begin.

No hard inputs in the formulas.

Yes, this is basic, but it’s VERY important.However, we also remind you not to put any hard inputs into the formulas. The reason for this is that in a larger model we cannot trace or make changes in these numbers or understand how they are used.

Use colour to separate your inputs, cell background, and linked cells

Imagine you have a large sheet and you want to change some inputs. Trust me, it will not be easy to find the right cells if you are not prepared….
For this reason we use different colours for inputs or linked cells (without formulas).

From personal experience, the conversion is that valuation analysts are using the know IB blue color for all hard-coded (i.e. manually entered) numbers. This colour is often used for historical financial data and also for many input cells in the “Assumptions” part of models. The light green we use for all direct links to cells in other worksheets or workbooks.

Lastly, we use the regular black for the formulas cells. Now the project finance conversion. In all cases the font color is black and we change the background and use cell borders. For inputs/assumptions we use a background color and border, for linked cells we use just borders, and for formulas(like valuation practice), we use neither background nor borders.

Have the same formula in each row

It is a good practice to have one formula per row. This is more common in project finance as every formula can go up to 100+ columns. If we change anything the user should be informed by changing maybe the cell background to a light grey.

Freeze inputs row/columns ($)

F4 key is your friend. It freeze the cell when it’s an input that is the same for all the rows/columns ($A$4). If you want to freeze the row we put the dollar next to the letter ($A4), when we freeze the column we put the dollar next to the number (A$4)

No links with other workbooks

We don’t leave links to other workbooks. Let’s repeat that

We don’t leave links to other workbooks

The reason for this is that the workbook in the background seeks this data, and when it doesn’t find it throws error messages and it uses memory, which makes your model really slow.

No Circular references

This is an important so let’s be clear: AVOID Circular reference at all cost. It destroys the model, shows that you don’t know how to model, and uses a lot of memory. In a few words, Circular references are created because you are using an input cell that uses a input for the initial cell, making a circle. There are two ways to avoid this. One way is to recreate the original input in another flow, and the second is to create a copy-paste loop, with a macro or basic VBA.

Error checks

Have an error control area where you check the basic model function and financial statements. This is a must as the models become larger and you need to constantly trace the errors. The error checks depend on the model type, but the usual choices are: Balance sheet balance, sources equals uses, finish of funding calculation, consistency of outputs.

Save when you know you have build something correct

When you reach a point where you have built something and you are confident that it is correct, save it in a different file and have it as a check that you will be able to go back to if you have errors that you cannot trace later. These model safe points can be life saving so be consistent with this habit.

Prudent when using IFERROR

This can be a power command to a modeler. We use it to protect ourselves from blown up models in every error, but on the other hand, it hides errors very well, and then the time you saved in the beginning you end up losing it in the end.

Do not use OFFSET

I am not going to explain what offset does, google it. The main issue is that Offset cannot be traced. Even if sometimes it’s useful, it is difficult to trace errors later and for this reason, try to avoid it.

Avoid VLOOKUP / Use INDEX-MATCH

OK, I don’t have a solid reason for this guideline, but this is what I was taught. I think the reason is that you can trace the formulas in two ways or because in this way you learn both index and match functions