Follow these financial modelling tips to increase the quality, robustness and ease of use for your spreadsheet financial models.

Build it better first time

Use as many centrally controllable variables as possible.

It is common for a model to pull out a frequently used variable into a front sheet. Whenever that variable is needed, the formulae can be written to pull that variable from the front sheet every time.

This gives the model user an easy way to change or update a variable value, as it saves them looking for all uses in a model and manually updating hardcoded values.

This tip is to maximise the use of such variables in your financial model. Inputs such as interest rate, discount rate, and revenue growth rates are commonly seen in forecast or discounted cash flow models. But this can be taken much further.

You should try to use central variable whereever you want to insert a hardcoded figure. Even if that hardcoded figure is only being used once.

What are the benefits of forcing almost all figures through a centrally controlled spreadsheet?

- Provides a list of your assumptions at a glance to a reviewer, who would otherwise need to click through countless formulae to truly understand all of the model assumptions

- Future-proofs your model for variables that become used more widely. You otherwise may have continued to use hardcoded figures, running the risk of inconsistencies.

- With assumptions at hand, it's much easier to run 'solver' simulations or 'what-if' scenarios on a control sheet than for hardcoded assumptions nested within formulae.

Use redundant (huge) ranges when looking up or pivoting data

A common reason why older models eventually break is that they are given more input data than the formulae was originally built to handle.

Source data begins to slip outside the ranges used by various lookup formulae or pivot tables, and the output of the models begins to suffer from completeness issues as a result.

To solve this, you should always vastly over-estimate the range sizes required for vlook-ups. If your current source table contains 300 rows, then ask your vlookup to search the first 10,000 rows. This won't decrease the performance of the model and helps to avoid a surprise error in future years.

Over-engineer your model rather than manually tweaking individual rows

Another way to future-proof your work is to never make a manual change to formula in an individual row without a much larger model.

Always work to the rule that formula should always be consistent when it is dragged down in an array.

It may be easy for you to remember and keep track of little quick fixes you made to fix bugs or outliers, but these can quickly become unmanageable and they may slow down future updates.

For example, if you are performing calculations on a list of employees, and discover an employee you don't wish to perform the same calculation for, then create an additional column of data to 'flag' that individual, then use an IF statement to drive a different result for rows containing that flag.

This will allow you to continue to use homogenous formulae in all rows, retaining your ability to simply drag all formulae down across employees with a double click, without fear of overwritten manually tweaked code.

Looking for more financial modelling tips?

The best financial modelling books above contain hundreds of practical tips and tricks like these. Begin using these today to vastly improve the quality of your models and save yourself a lot of time in the long run.

Also, check out our related book pages:

• Financial risk management books
• Financial statement analysis books
• Valuation books
• Financial management books