Financial modelling with Excel (II)

Best practices for useful and reliable financial modelling

In a previous article(Financial modelling with Excel I ) we referred to the importance of adopting a rigorous methodology for building financial models that meet two basic requirements:

1. Make models understandable and their logic can be followed by people other than those who created the model,
2. Models should have a long useful life, i.e. updates and modifications should be easy and safe to introduce outside the people who initially created the model.

Many of us have had the frustrating experience of being confronted with models so convoluted that, after a few hours of trying to understand the underlying logic, one ends up coming to the conclusion that it is better to start from a blank sheet of paper, and start from scratch, than to take on board the legacy received.

It is also often believed that this situation is irremediable and inherent to Excel. In other words, its usefulness does not go beyond being able to improvise ‘ad hoc’ schemes to use and throw away, so it is not valid for building models that will last in the long term.

However, and with the limitations that we will see later on, it is possible to create solid, traceable, structured and secure models that withstand the passage of time and successive updates by different analysts.

While this depends largely on the skill and experience of the analyst who initially builds the model, there are good practices that we can adopt to ensure that this is the case.

Indeed, there are bodies that seek to standardise and generalise these best practices to create a common knowledge base. Perhaps the best known standard is FAST(https://www.fast-standard.org/), although our recommendation is that anyone wishing to learn more about it should download the Financial modelling code published by the Institute of Chartered Accountants in England and Wales(https://www.icaew.com/) with very useful and clear rules and recommendations.

Without pretending to provide an exhaustive review of all the good practices that can be adopted, we will indicate some of those that, in our usual practice, we consider most relevant.

Avoid links between different Excel workbooks

All data and model logic should be present in a single, self-contained, self-referenced workbook. In fact, it is also advisable to avoid the proliferation of sheets within such a workbook, as this adds more possibilities for error and makes it difficult to follow the logic of the model.

If the workbook is to be based on information imported from another workbook, the ideal is to have a first tab called ‘input’ or similar where the input information from another Excel workbook (or other source of information) is dumped into values.

It is true that Excel has dynamic import capabilities, but here we will surely have to evaluate whether to use these capabilities or delegate them to other tools, such as Power BI, which can also perform a more sophisticated and robust ETL process and then export the result of the process to Excel in the desired value format.

Clearly mark the input cells of information

Ideally, these cells should be distinguishable in a highly visible way (usually by a different cell colour). An additional measure that is easy to implement is to protect all cells in the workbook except those that are input cells, in order to avoid unintentional manipulation of the formulated cells.

On the subject of colours, the ‘less is more’ rule also applies here: the colour palette should be very limited (three or four colours) of a couple of shades at most, such as greys and blues. Assigning meanings to colours and using too many shades is not a good idea. Neither in terms of clarity nor in terms of aesthetics.

A proliferation of lines, framing, merged cells, etc. should also be avoided. In addition to making editing difficult, an overloaded aesthetic is more difficult to follow than a more austere aesthetic (except perhaps for the author).

Avoid complex formulas

Complex formulas are time-consuming to understand and act in practice as a ‘black box’ as they are often not checked out of laziness in subsequent revisions. One possible practice in such cases is to expand the calculation into two or more steps that are more easily understandable and traceable, rather than compacting these steps into a single formula.

In addition, sometimes complex formulas often reveal bad practice and poor knowledge of Excel functions. For example, typical formulas with multiple nested ‘IF’ conditionals are exhausting to follow and sometimes give more insight into the reasoning of the author who perpetrated the formula, rather than the underlying business logic. Formulas such as ‘IF.SET’ or ‘ADD.IF.SET’ often resolve the intent of conditional nesting more clearly and efficiently.

Consistent structuring of information

If there are several sheets in a workbook, the first of them should be an index sheet, which should show, best graphically, the linking scheme between them.

Within each sheet the information should be read from left to right and from top to bottom. Backward jumps in this scheme should be avoided as much as possible.

In the financial modelling sheets, the x-axis (the columns) should be reserved for time, so that each column signifies a specific time span (a month, a quarter, a year). The columns should be homogeneous and continuous blocks avoiding for example quarterly totalisers in the middle of monthly columns.

Avoiding errors with signs

Perhaps the error that sends the biggest shivers down the spine when it is detected is when it is found that the profits predicted by the model are higher than what we should expect in reality because an expense has been added when it should have been subtracted.

Using absolute values and giving them the sign in the formula is a bad idea. It is advisable that each amount has its natural sign and that the totalising formulas only add figures with the signs already incorporated.

The desirable natural signs are: in the income statement, revenues have a positive sign and expenses have a negative sign; in the cash flow, cash inflows have a positive sign and cash outflows have a negative sign.

Only in the balance sheet can we treat the balances (assets and liabilities) by their absolute value to facilitate a natural reading and taking into account that the balance sheet already incorporates an intrinsic mechanism to avoid errors: the balance sheet must be squared, i.e. the sum of the assets must be equal to the sum of the liabilities plus the equity.

Creating checkpoints

A key aspect of ensuring the consistency of a model and detecting potential errors is to introduce checkpoints that provide visual evidence of compliance with the underlying business logic of the model.

For example, a basic business rule is that everything that is billed as revenue must be collected. It may be objected that there may be late payments, or that in one period invoices from previous periods may be collected, or that invoices from previous periods may be uncollected for later periods. However, this only means that we have to take into account the change in the customer balance or allow for late payment losses in the income statement. However, the rule that we cannot charge more or less than what we invoice is still always there and it is worth detecting if, for some reason, the model erroneously provides for something different from this.

The most comprehensive and obvious control in a financial model is undoubtedly the balance sheet. All economic magnitudes and flows simulated in a model have an impact on the balance sheet, which must be squared at the beginning and after each time milestone of the model (i.e. in each column).

In addition to being an element that shows the equity situation at each moment in time, the importance of the Balance Sheet as a balancing element is so fundamental that it is difficult to call a financial model a model that does not have perfectly developed the three basic elements that financially describe the reality of a company: the Income Statement, the Cash Flow and the Balance Sheet.

The best practices described above make Excel an optimal tool for financial modelling, but it is necessary to detect when we are exceeding the limits of its reasonable use. Excel should not be used as a database, nor as a support for programming repetitive tasks, nor even as an efficient information visualisation tool.

Tools such as Power BI or robust programming developments should be the most reasonable alternative, despite the fact that Excel has the capabilities to provide an initial response to the planned problems. In this sense, it is necessary to evaluate very well the purpose of the model and the output we want to achieve before starting a complex project based on Excel.

It is also desirable to plan initially the degree of sophistication we wish to give to the model. A model should aim to be a simplified representation of reality, not an exact representation of reality. Practical experience shows that the superimposition of multiple business rules, each time more detailed and to cover all possible cases, does not always result in greater precision, but in many occasions in a complexity of dubious usefulness and very difficult to manage.