Editor’s note: In the second of a two-part series, guest author Michael Zimmel lists out the remaining seven of the 13 financial modelling errors that finance professionals would do well to remember to avoid.
7. Excessive use of names and range names
Many modellers try to reduce the complexity of their formulas by using names and range names. But there is always some form of a trade-off when using Excel.
When you name a cell, the trade-off is when you no longer know its location without checking on the name manager. Excel will retain these names unless you are proactively deleting them.
This means you could end up building a formula containing dozens of phantom names. And this could make your model confusing.
But the use of names and name ranges can create even more problems. First, it is time-consuming and may delay the development of the model. Second, you will run out of alternatives fast and end up using redundant and ambiguous names. Third, the model will become more difficult to review because of trace precedents.
8. Hiding rows and columns
Obvious as it seems, hiding rows and columns is another fatal error. In a model, you will have rows with data and calculations unwanted in the final version or presentation.
You may be tempted to hide them to achieve a cleaner presentation. The problem is you may miss crucial data or make an inaccurate presentation.
It may also result in incorrect calculations and inconsistent copy/pasting of formulas. Rather than hiding rows, group them and do it sparingly.
9. Inconsistent formats
The formats in a model should be consistent both when using and presenting it. The sheets will have the same year-by-year, quarter-by-quarter, or month-by-month profile. You should make sure that the column headings are identical across the sheets.
Choose the same fonts, bordering, descriptions, labels, and style where possible. This is important for consistency. If the rows and columns are not clearly described, you might confuse the reader.
Formatting each sheet in Excel differently is a big mistake. It makes the model look irregular and increases the chances of an error. When preparing the sheets, format in a clear and consistent manner.
Consistency builds a high level of comfort for the reader. And the reader will be more comfortable referring the model to a friend if they understand it.
A good financial modeller will minimize the number of unique formulas in a model by using the same formula across a row.
This will make the model easier to change, less prone to errors, and simpler to test and review.
10. Not fully integrating balance sheet and cash flow calculations
The balance sheet and cash flow statements are always important in a financial model. You could be tempted to think that you can leave them out for calculating isolated business cases or building early start-up models. But they play a crucial role in the process.
Failure to integrate balance sheet and cash flow calculations means that you are overlooking your stock turnover, creditor days, debtor days and more.
Not modelling these correctly means difficulty forecasting the cash flow position of the business with confidence.
11. Lack of systematic naming convention for file versions
The naming conventions for your file versions should be clear. In particular, the alphabetic and numeric order of your version names should match the chronological order of your file.
Make sure the format you use is consistent. And avoid using name additions like “latest” or “final.” There is no need to include the date in the file name.
12. Not adding an executive summary
Adding an executive summary makes the model more understandable. It allows the reader to understand the key assumptions, drivers, and other factors immediately.
The summary provides a snapshot of the model. It allows the reader to decipher the key findings and the underlying logic quickly.
On your part, the financial summary will help you determine the focus for presenting your financials.
The executive summary is where you outline the considered key drivers and assumptions. You should include various accounts like profit and loss, balance sheet, and cash flow in the form of a graph.
Your goal should be to make the life of the model’s user easier. Present the information in a way that allows users to get the level of information they want with ease.
Consider including a table of contents, instructions, explanations, and guidance. These make it easier for the reader to use the model.
13. Lack of error and sanity checks
It is easy to set up automatic error checks and checking your model using the audit function in Excel. Of equal importance are the sanity checks.
These can be applied to aspects like average revenue per salesperson. They help answer questions like, can you really double your sales by adding an extra salesperson to your team of two?
When working on financial models, check for errors at regular intervals. The Excel auditing function will help you assess the entire model.
Sanity checks will point out fundamental errors in the model. Suppose your model shows adding a salesperson to an existing group of two would triple sales. Such an outcome looks outrageous prima facie. The sanity checks will help verify it.
Similarly, it would not make sense when your projected revenue per employee is $400,000, while the industry norm is $150,000. And the sanity checks will prevent these errors.
Since financial models are usually complex, testing is critical. Even though a test may not prove the model is error-proof, it can show if there are errors in the model.
Don’t just check the model yourself but get someone else to have a thorough look at it. Some important tips for conducting checks on the model include:
- Understand the purpose of your model
- Utilize the auditing functions of Excel’s formula
- Use pen and paper to outline the structure of the model; break down the complicated formulas
- Review the model’s code in detail, from line to line and sheet to sheet
- Change the model inputs and see what happens. You can use testable values like 0, 1, 100, etc.
- Who should do the model testing? Ideally not the same person that built the model in the first place.
Financial modelling expert Dr Achim Korten even suggests that all financial models that are used as a basis for strategic decisions should undergo an independent third-party review:
“A thorough model review requires checking every single formula and cell-by-cell recalculation is time-consuming. If this happens internally among colleagues, objectivity may be at risk. Adding usual time pressure an internal check is quite prone to errors.”
He also rightfully states that by applying a systematic approach to testing and the use of special testing tools, an external consultant can identify model errors with a much higher probability.
Additionally, the result of the review is documented in writing and made available to decision-makers and potential investors, who rely on the model to make their decisions.
Summary
Building a financial model with Excel is a complex task. With so many considerations to make, you will likely make certain errors.
Among the most common and fatal errors include failure to plan, lack of structure, use of unnecessarily complex formulas, using inconsistent formulas or formats, and more.
Avoid these to create both a useful and practical model to your users.
This article first appeared on LinkedIn. Click here.
To read the first part of the series, click here.
