Financial modelling with Excel

Keys to useful and reliable financial modelling.

Spreadsheets, and Excel in particular, are the universally used modelling tool in finance.

A financial model is a simplified representation of the economic-financial reality of the company and allows us to answer the question: What will happen in the future if…?

For example, we want to launch a new line of business that involves some investments and for which we have some sales expectations. A financial model will be useful in the first instance to evaluate the profitability of the investment and the financial needs it will require over time.

Given that the future is uncertain, it is also logical to question what would happen in circumstances other than those we consider most plausible a priori: what is the effect if the sales growth curve follows a slower pattern, what happens if sales do not exceed a certain limit, what happens if supply costs are 10% higher than expected, etc.?

All these questions can and should be answered with a financial model that also helps to visualise and understand the possibilities and limitations of this uncertain future.

When is financial modelling necessary?

Financial modelling is often particularly relevant for the determination of the cash flows we can expect in the future. We tend to think statically and look at the overall profit that, for example, a new expansion initiative can provide, translating a balance sheet as an income statement. However, our intuition often betrays us by underestimating the dynamic investments in working capital that this new initiative implies.

Sometimes this mistake means having to improvise solutions on the fly and, provided that the financial gap is not large and that profitability is positive, ensuring that the financial difficulties that arise can be managed and end up becoming an anecdote.

On other occasions, financial suffocation can have more serious consequences. In all cases, it is always better to assess in advance the difficulties we may face than to react to a fait accompli.

Financial modelling also helps third parties other than the company to clearly and accurately visualise the future cash flow generation capacity of a company. This is particularly useful in the process of bringing in external financing (equity investors or bank financiers), as well as in transaction processes where the modeller becomes a key component of the Corporate Finance and M&A teams.

The advantages and limitations of Excel

Excel’s ubiquity and flexibility are both its great advantage and its main limitation. Every finance manager, and virtually every manager in a company, uses Excel on a regular basis as a “lingua franca” to communicate numerically from reporting tables, to sophisticated business models, to the most trivial calculations.

Excel’s flexibility to build numerical relationship diagrams very quickly and to modify them in a progressive and agile way is the second feature that explains Excel’s success and its special suitability for financial modelling tasks.

There are ERP’s that incorporate specific modules for making financial projections, basically focused on the preparation and monitoring of budgets, and which have their main advantage in that they are perfectly connected to the data required to make the projection. They are, however, rigid tools that serve a very specific purpose. When a “what if” question arises that exceeds the limits of the predefined questions, we are faced with the need to make adaptations and parameterisations in the ERP that can be extremely slow and costly to implement.

Excel, on the other hand, allows adaptations to be made or new business rules to be introduced into a model in a progressive, fast and cost-effective manner. This is why reliable, but not very flexible, ERP’s usually provide for the export of datasets to be processed and manipulated in Excel.

This flexibility to make modifications and add new functionality to an Excel model is, if not properly managed, its main weakness. We have all experienced the frustration of getting wrong answers from a financial model for the most trivial reasons, such as a user unintentionally manipulating a cell, or a sign confusion and treating an expense as income or vice versa.

The importance of good modelling

The fact that Excel as a tool is extraordinarily user-friendly and intuitive in an initial learning phase may lead us to believe that the construction of financial models in Excel is a simple activity that can be undertaken by anyone who knows its rudiments, that is, by practically everyone.

This belief is the main cause of the aforementioned fragility attributable to Excel. The truth is that the construction of good models implies both the mastery of the multiple possibilities offered by Excel as a sophisticated tool (with nearly 400 predefined functions), and the adoption of good practices and a rigorous methodology in its construction in order to minimise the possibilities of error.

Mastery of Excel and a suitable methodology are the basis not only for avoiding errors in the construction of the model but, above all, for making it as structured and legible as possible.

Models that produce counter-intuitive but correct results are particularly useful as they help to make alternative decisions to those that would have been taken initially on the basis of intuition alone. However, such models are highly dangerous if their complexity makes them in practice a black box that is difficult to understand and verify.

In this respect it should be noted that the maxim “less is more” applies here. Achieving as much simplicity as possible in a model leads to a better understanding of the model, which is a key point both to transfer confidence in the model to third parties and to be able to have a tool that is easy to modify and maintain in the long term.

Building simple (but not simplistic) and well-structured models is therefore the most important key to making them useful and reliable. In other words, a good model is one that, when faced with a result that deviates from our intuition, allows us to understand the sequence of steps that lead to that result and to be sure that this result is correct and why.+

In a way, a good financial model helps us learn and manage better. In a subsequent article we will describe a series of good practices that we apply in the financial analysis area of AddVANTE and which can be adopted to achieve this purpose.

Although we have referred so far to technical and methodological aspects, the quality of financial modelling does not depend solely on the above, but also on the modelling team being made up of generalist and experienced profiles with an in-depth knowledge of accounting, tax, financial mechanisms, etc., as well as the overall strategic vision of a company’s operations.

It is this transversal knowledge that makes it possible to interact effectively with experts in the different areas of the business and to carry out the simplification that any model implies in a correct manner, without distorting the fundamental vision of the company’s reality.