FP&A Week: Breaking the Paradox
October 10-13, 2023
By Michael Zimmel, Senior Financial Professional
Financial models are crucial to the business but building them in Excel can be both complicated and frustrating. Setting up formulas and stipulating conditions takes time. And just when you think your model is water-tight, there is another error.
Although errors are unfortunately very common in Excel models, it is important to understand how you can avoid them. In this article, I would like to share my own financial modelling best practice advice that I hope will help you create reliable financial models that generate accurate financial data.
Before starting working on the financial model, many finance teams neglect the preparation process. As a result, they create over-complicated models that lack critical features and fail to address operational needs.
There are several questions that you need to ask yourself before starting to work on a model:
Having a vision and keeping the goal of the project in front of mind throughout the development process is key to ensuring a successful and useful financial model.
You will inevitably encounter issues and experience changes, so it’s vital for the model-building analyst and the client to work closely together throughout the project lifecycle.
Your financial model is likely to require several spreadsheets and they must be organized logically to avoid navigational difficulties for the end-user.
You need to identify the factors that affect the model and display the content in a way that enables users to easily move between sheets. The model should be built around three basic components:
Following this structure will ensure your financial model is architecturally sound and robust. Users will be able to work out the areas they need to focus on, distinct from the areas where the computer works. And if assumptions are gathered in one place, errors are avoided as your model is unlikely to be compromised due to some old assumptions that were left from earlier analyses.
Most users of financial models will decide whether they like it or not within the first 30 seconds. The easier your financial model is to follow and interpret, the more likely it is to receive positive feedback.
Excel has numerous features to help you simplify your financial models. If creating a simple formula is not possible, you should look to break it down into several cells. Other ways you can reduce complexity on Excel include:
It is always a good idea to avoid hard coding. It may initially save you time, but ultimately it shows a lack of discipline and threatens the reliability and transparency of the model. There are several problems with using hard values instead of formulas:
If hard coding certain values is unavoidable, you should insert a comment against the cell explaining it and revealing where the number originates from. It is easy to remember the value while you are creating the model, but if you come back to it later, you will notice that you likely have forgotten something about it.
Hard values do have their place. For example, when you have to deal with assumptions and inputs that drive key elements of the model, such as revenues and costs. Clearly and consistently formatting such cells as inputs increases a reader’s confidence in the data’s reliability.
Your cash flow and balance sheet calculations are vital components of a financial model. So, do not be tempted to omit them when building start-up models or calculating isolated business cases. They must be fully integrated to ensure proper representation of stock turnover, debtor days, creditor days and other features are part of your model. Lack of integration could lead to problems when the reader needs to confidently forecast a cash flow position.
There are several steps that should be taken to ensure the accuracy of your model:
Your chief goal when building a financial model is to ensure you create a clear and effective tool that users can easily benefit from. Building such models on Excel is complicated, with so many details and features to consider. Errors are almost inevitable, but by following the best practice tips above, your path to creating a useful and practical financial model will be that much clearer.
This article will focus on is the modeling of a company as a whole, its consolidated...
Starting with the end in mind is one of the simplest ideas that is frequently ignored...
Scenario analysis, sensitivity analysis and what-if analysis are very similar concepts and are really only slight...
Disclaimer: Financial Modelling has no strict “right” or “wrong” method of application. It does, however, have...
We will regularly update you on the latest trends and developments in FP&A. Take the opportunity to have articles written by finance thought leaders delivered directly to your inbox; watch compelling webinars; connect with like-minded professionals; and become a part of our global community.