Michael Zimmel February 9, 2021 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. Always remember:
All models are wrong, but some are useful.
(George Box)
1. Always plan ahead
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:
- What is the financial problem the model needs to solve?
- Who are your end-users are and how are they likely to use the model?
- What are the model’s key inputs, outputs, and processes?
- How can you ensure that all inputs are in one place, and quick and easy to access during development?
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.
2. Ensure your financial model is logically structured
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:
- Assumptions
- Calculations
- Output
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.
3. Keep your financial model simple
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.
- Limit the number of implicit assumptions to 10 or 15. The more assumptions you make, the less realistic your decisions are likely to be.
- Work towards having formulas that are no longer than about half the length of the formula bar. Longer formulas increase the chances of making a mistake.
- Do not overuse cell names. While using cell names and range names might seem a good way of reducing the complexity of formulas, it can create more problems. This can leave your formula littered with phantom names and confuse users. Also, you might soon run out of relevant names to use and resort to random names with no real meaning.
- Use the same formula across rows. This helps making the model easier to alter, more error-free, and more straightforward to test and review.
- Follow a consistent formatting. All your column headings should be identical from sheet to sheet. Your fonts, borders, labels, and the overall style should all be the same. To avoid any confusion, rows and columns should be clearly described.
- Always add an executive summary. An executive summary allows you to outline your model’s assumptions and drivers. It should include references to balance sheets, profit and loss, and a graphical representation of the cashflow. You should also consider creating a table of contents and having as many instructions and explanations as you deem necessary to make the model easier to use.
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:
- Using flags
- Avoiding nested IFs
- Simplifying IF statements using different functions
- Considering logics like MIN, MAX, OR, AND
- Employing functions such as VLOOKUP, HLOOKUP, INDEX, or (with care) OFFSET
4. Avoid using values instead of formulas
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:
- The model becomes over-complicated and difficult for readers.
- Assumptions are not obvious, and this might be confusing to end-users.
- The flow of reasoning becomes less clear.
- End-users are less confident in the data’s reliability since they are unaware of where the data is coming from.
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.
5. Ensure your cash flow calculations and balance sheets are fully integrated
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.
The reliability of a spreadsheet is essentially the accuracy of the data that it produces, and is compromised by the errors found in approximately 94% of spreadsheets.
(Ruth McKeever)
6. Check the accuracy of your model
There are several steps that should be taken to ensure the accuracy of your model:
- Group your rows. It is almost inevitable to have rows of redundant data and calculations that are not required in the final version of the model. Hiding them might give you a cleaner-looking presentation, but it often leads to incorrect calculations and can cause you to miss crucial data and inconsistent formulas. Grouping rows is a much better solution than hiding them, but even this should be limited.
- Check and double-check your formulas. Incomplete information or copying errors will lead to inaccuracies. That is why the writing of formulas needs to be undertaken with the utmost care. You should also pay particular attention when you insert rows or columns close to the edges of ranges, or where references in the cell are fixed.
- Establish a systematic naming convention for your file versions. The numeric and alphabetic order of your file version names should align with the chronological order of your file. You should in general also avoid including the date in your file names, and do not add words like ‘final’ or ‘latest’ to those names.
- Do not neglect the Excel audit function. It makes it easy to run automatic error checks on your financial model. It is good practice to carry these out regularly across the entire model.
- Perform sanity checks. Here you should answer some basic business questions. For example, will your sales double if you recruit an extra sales professional into your team of two? If your model shows adding the extra person would triple sales, a sanity check would help verify the accuracy of an outcome that appears, on the face of it, far-fetched!
- Ask a third party to check your model. A fresh pair of eyes can find problems you might have overlooked. You can ask a colleague that did not participate in the development process. A check by an external consultant will be more time-consuming but is likely to be worthwhile. It is important that whoever checks the model understands its purpose, reviews the model line by line and sheet by sheet, and tests to see what happens when the inputs are changed.
In summary
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 in Excel is complicated, with so many details 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 previously appeared on FP&A Trends as “6 Best Practice Tips for Building Great Financial Models”.)
Business & Finance Articles on Business 2 Community
(24)
