In this chapter, we'll take a look at some of the more frequently used techniques, as well as some of the common errors (and avoidance strategies), issues, and decisions a financial modeller encounters when building models in Excel.
As discussed in Chapter 1, in “Tool Selection”, Excel is by far the most commonly used tool for financial modelling due to its wide acceptance and ubiquity in the market. However, it most certainly has its flaws, mostly to do with the prevalence of undetected mistakes in Excel-built models. Astute financial modellers need to understand the dangers of a poorly built financial model and the ease with which mistakes can be made.
A well-built financial model or piece of analysis is an invaluable tool in decision-making and financial management, but all too often a wrong calculation can have disastrous consequences. Many executives and decision-makers rely far too heavily on Excel models—probably because they don't fully understand the risks involved.
There are many well-documented cases of high-profile Excel model blunders. Some of those documented by EuSpRiG, the European Spreadsheet Risks Interest Group,1 include:
The list of Excel horror stories continues to grow, reminding us that simple mistakes in spreadsheets and financial models can cause companies to lose significant amounts of money—not to mention the resulting embarrassment.
Figure 4.1 is an example of one of many very simple and yet common formula mistakes often found in Excel models. In the figure, row 13 has been entered, but it has not been picked up in the summary total below it.
To avoid this error, when creating summary totals, leave an extra gap row between the last row of values and the sum formula. Therefore, when new rows are inserted, the summation formula will automatically include the newly inserted rows. This will minimise the chance of error when the model needs to be expanded. The use of error checks can also help to alert the user or modeller when this happens.
Excel is just such an amazingly flexible program that you can do anything with it; however, it is for this very reason that it is so prone to user error. So, what's the solution to Excel's error problem? It is extremely difficult to ensure an error-free model, but by employing certain strategies, you can most definitely reduce their number and impact. When building a model in Excel, it's recommended to proceed carefully and build and use it with caution.
There are certain strategies the modeller can employ during the build process that will help identify errors as they occur. Even extremely skilled modellers make mistakes all the time. A good, experienced modeller, however, will pick up these errors as they are made and avoid situations where mistakes are likely to happen.
Silly formula mistakes are the easiest to avoid and the most embarrassing when they are not picked up. Commonly, it's the wrong cell being picked up or a dollar sign in the wrong place.
Use the Enter Key Many Excel users get into the bad habit of clicking elsewhere in the page when they have finished typing a formula. This is a dangerous practice. Instead, always hit the Enter key when you've finished with a formula. Most of the time you'll get away with neglecting this, but every now and then it can cause problems, so you must get out of the habit.
In Figure 4.2 you can see that the modeller had his or her cursor in the middle of the formula, and by clicking on another cell has inadvertently changed the formula entirely.
As you can see in Figure 4.2, it's easy to accidentally pick up the wrong formula if your cursor happens to be in the wrong place. More importantly, however, the modeller is moving on to another cell without thinking about the formula that has just been written. You need to be deliberate about entering the formula and checking it before moving on to the next cell. When modelling quickly, it's easy to mentally move on to the next task before completely finishing the last one.
Once you've finished the formula, hit the Enter key and look at the formula. Does it look right? Does it look sensible? Is this what you intended to show?
Check Your Work and Use the F2 Shortcut Key The worst kind of error is one that you are not aware of. Constantly performing rough checks on the results of the formulas as you build will minimise the chance of an error. Every time you hit the Enter key (and you must use the Enter key), check the result. Is the number what you expected? Before you add anything else to the formula, make sure the first part is right.
A lot of modelling is trial and error, and it's okay to make mistakes—just make sure you pick up mistakes before someone else does. The only thing worse than finding a mistake in your model is not finding the mistake in your model, and your model being wrong. Worse than that is having your boss find the error! There's no faster way to lose credibility as a modeller or analyst than having your mistakes pointed out in public.
Clicking in the cell or hitting the F2 key (as demonstrated in Figure 4.3) will also show the source cells or precedents of a formula. This is most useful if the source cells are nearby and on the same sheet. When you've copied down all the cells in a block of data, do a spot check with the F2 key to make sure it's picking up the correct cells. Note that this shortcut now works in Excel for Mac, or you can also use the Control+U shortcut on a Mac as well.
Have Someone Else Check Your Work Once you've checked and checked again, it's good to have your model reviewed or audited by someone else, as that person will bring a fresh perspective. Sometimes you've looked at your model for so long you just can't see the glaring error in front of you. Working in pairs can be very effective.
If the model is high-profile or critical to the business, it might be worth getting it audited by a model audit firm. There are many organisations that specialise in conducting professional model audits. This is really the best way to be confident that there are no errors and your model is working correctly.
Now that the reality of spreadsheet error has been established, the next step is to ask what we can do to reduce spreadsheet errors. Unfortunately, only one approach to error reduction has been demonstrated to be effective. This is code inspection, in which a group of spreadsheet developers checks a spreadsheet cell-by-cell to discover errors. Even this exhausting and expensive process will catch only about 80 percent of all errors.2
Stress Testing Test the technical workings of the model by stress testing it (i.e., varying the inputs to see how much the outputs change). Insert some ridiculous numbers into the inputs and see if the results are what you expect. The following are some stress tests you can perform:
Thorough stress testing, along with scenario and sensitivity analysis, will provide your business case with the rigour and robustness to cope with various fluctuations in economic inputs. Greater detail on how to perform scenarios and sensitivity analysis is provided in Chapter 11.
Avoiding Logic Errors Compared to formula mistakes, logic errors can be more difficult to spot. They include things like incorrect timing, inserting the wrong inputs and source data assumptions, and using pre-tax instead of post-tax inputs, to name a few. Sometimes the mistakes can be a combination of both formula and logic errors, and we need to diligently guard against both types of errors.
Validate Your Assumptions A model is only as good as the accuracy of the assumptions. The phrase “garbage in, garbage out” has never been more relevant than in the context of financial modelling. Even the most beautifully built, best-designed model will be completely worthless if the assumptions that go into it are not validated. This is one of the most important points of financial modelling best practice, and different technical assumptions documentation methods are detailed in Chapter 3, under “Methods and Tools of Assumptions Documentation”.
Important decisions are made based on the outputs of the model, and it is absolutely critical to list clearly (and often painstakingly) the assumptions that have gone into the model. While a good model can aid your business or decision-making process significantly, it's important to remember that models are only as good as the data they contain, and the answer they produce should most certainly not be taken at face value. When presented with a model, a smart manager or decision-maker will query all the assumptions, and the way it's built.
As a financial modeller, ensure that all the assumptions have been validated to the best of your ability. Document clearly where the numbers came from so that there can be no possible misinterpretation of the assumptions you have used; the assumptions can then be revisited and possibly revised at a later date.
Document Your Methodology Using a flowchart format, document the inflows and outflows of your data within the model. This helps to check your logic but is also useful in explaining the methodology of your model to other people.
For example, you can calculate the customer acquisition of a business case using the following methodology. If you start with the total population, apply the number of pet owners and then a take-up rate, you get the number of acquired customers. This methodology can be documented as in Figure 4.4.
Use Models in the Right Context Some managers treat models as though they are able to produce the answer to all their business decisions and solve all their business problems. It's absolutely terrifying to see the blind faith that many managers have in their financial models. Anyone with any real experience in model building will appreciate how incredibly easy it is to get a dollar sign in the wrong place and end up with the wrong result. Models should be used as one tool in the decision-making process, rather than the definitive solution.
All too often, back-of-the-envelope models turn into something more permanent. They'll then perhaps be used to determine pricing. Then used as a cash flow forecast. Then later, when it's budget time, the same model gets used for that too, and you end up with an important model based on hastily gathered assumptions and a flaky design structure. Models should always be built for a specific purpose. If you do pick up a model to use for another purpose, make sure you understand the model fully before making any changes. See “Rebuilding an Inherited Model” in Chapter 10 for more information on how to take someone else's model apart.
Sensitivities and Scenario Analysis Do lots and lots of sensitivities and scenario analysis. If the absolute worst happens, what happens to my bottom line? How sensitive is my model to changes in key assumptions? This will help to test the logic and robustness of your model, as well as the soundness of the business, product, or project the model is representing. At the minimum, you should do at least a best case, a base case, and a worst case.
This can often flush out anomalies in the model. Look carefully at the results of your scenario analysis. Is it what you would expect to see? Compare the sensitivity results side by side. If you increase the escalation factor from 2 percent to 3 percent, do costs increase by the same margin as if you increase escalation from 3 percent to 4 percent?
See Chapter 11 for more information on stress testing, scenarios, and sensitivity analysis in financial modelling.
Sense-Checking Methodology Let's look at an example of using sense-checking methodology.
Figure 4.5 shows a pretty common one. Let's say you are adding up these totals and accidentally double-count by including the subtotals in your grand total. If you look at the subtotals you can see that the grand total should be around $170,000. Instead of doing this in your head, you could highlight the two subtotals using the Control key, and the total will appear at the bottom right-hand side of the screen, as shown by the highlighted section at the bottom of Figure 4.5.
Highlighting cells and checking totals using this method is a good way of sense checking for errors, and by employing a sense-checking methodology, you are less likely to let errors slip through the cracks.
If you've been modelling for a while, you'll know that it's very easy to end up with a really long formula. Excel will handle them—as long as they are constructed correctly—but they can be pretty tricky to follow, especially for other people. Even if you've written a lengthy formula yourself, you'll struggle to follow it at a later date.
As with most concepts in financial modelling, a formula should be as simple as possible and as complex as necessary.
The way to make a formula shorter, of course, is to break it down into several steps, by adding additional working cells. Too much of this can make your model cumbersome; therefore, the good modeller needs to strike a balance between the following two extremes:
If you do have a very long formula, one way to make it a bit easier to understand is to put the cursor in the middle of the formula and then press Alt+Enter to force the next section of the formula onto another line (note that this shortcut is Control+Option+Enter in Excel for Mac).
For an example of what this looks like, see Figure 4.6.
Of course, a formula of this length is not recommended. One of the new features in later versions of Excel is the expandable formula bar, but it is generally accepted best practice that a formula should not exceed about half of the width of the formula bar. This does not include file paths if the formula is linking to an external file that is currently closed, and therefore the full file path is included in the formula (as shown in Figure 4.7). Keep formulas particularly short when linked to external files for this reason.
Figure 4.8 reveals two different ways of achieving the same result. The first example shows that revenue has been calculated by:
Alternatively, the whole calculation could be done in row 9 using the complex formula
You could argue that this is a more succinct, sparse way of modelling. However, it makes it very difficult to take apart this formula for checking or debugging. Calculating the revenue step by step, as shown in the first example, is a much better way of modelling.
One of the important principles in Excel modelling best practice is to link to the source data. Wherever possible, hardcoding should be avoided, and you should always try to link your cells so that as variables change, the results remain accurate.
However, if your source data exists in an external file, this can cause many problems, such as broken links, incorrect data, and error messages. In fact, some analysts avoid linking to external files at all costs. However, if done properly, external links add value to your models.
The following is a list of reasons you should link to external files:
Table 4.1 shows the problems and solutions as to why many analysts don't link to external files.
TABLE 4.1 Why External Links are Not Used
Problem | Solution |
The links break | Avoid moving files. Use Edit Links to fix. Use named ranges to minimise the number of formulas to fix when this does happen (and it will). |
People move the data and my formulas are wrong | Use named ranges to refer to external files, which will minimise this. Use more robust formulas such as INDEX & MATCH. |
The numbers keep changing | Yes, they should. Consider saving in one location to maximise version control. |
One of the main issues with linking to external files is that if users insert or delete rows or columns in the source file, this can easily mess up the files that are linking to it.
Imagine you want to use a pricing threshold in your financial model, which is being generated in another model. Using best practices, you decide to create a link, rather than hardcoding the number. This will make your model more auditable, so that we can see exactly how that threshold amount was calculated. You create a link from your financial model to another source file, using the link
If you are using Excel for Mac, your file name might look more like this, as it does not show the entire file path:
If both files are open at the same time, and you insert a row in the Financial Statements file, the link will automatically update from to . However, if your file is closed, your model will not update. This means that next time you open it, your model will be picking up the wrong cell!
The way around this issue is to create a named range in the source file (e.g., threshold) and then if that cell moves in the source file, the cell will still retain its name, and the formula in your model will still be correct.
See “Named Ranges” in Chapter 5 for how to do this.
The next time your model tries to update the link, it will look for the name threshold, rather than , and the integrity of the link will be maintained. This is why using named ranges when dealing with external links is considered best practice; it is a much more robust way of linking files together.
If you have linked two files together, you should not start moving either of the files around into other drives or directories. If you do, Excel will not be able to find the file that has been moved, and the link will be broken. If a file has been moved or had a name change, you can try to amend this by using Edit Links as described in the following text.
How to Edit Links Click on Edit Links on the Data tab, in the Connections group. Use the Change Source button to tell your model the new location of the file it has been linked to.
Before you start linking workbooks together you need to think about what will happen to the workbooks in the long term. Will other people be using them? Will they be making copies? Will they be mailing copies to other people? If so, then perhaps, in that case, you should avoid creating a link to an external spreadsheet.
Linking Dos and Don'ts Here are some common dos and don'ts to keep in mind when linking:
Basic reconciliations can be built within a model, and a well-built financial model should have error checks included where possible so that the user or modeller can see at a glance if the formulas are calculating correctly. For example, when creating management reports, check that the sum of each individual department's report adds to the company-wide total. This can be done by inserting a simple IF function, or several other methods, as shown in the next section.
Note that error checks are not a substitute for good practices such as checking and auditing your formulas. Error checks are most appropriate for capturing errors a subsequent user has made; they are less likely to highlight a model-building error. See “Error Avoidance Strategies” in this chapter for greater detail on how to reduce errors when building models.
Let's say, for example, that you have the following cars in stock in a showroom. You want to know how many of each type of transmission you have in stock, so you have created a SUMIF formula to summarise how many of each type are in stock (see Figure 4.10). “Aggregation Functions” in Chapter 6 discusses how to create a SUMIF formula.
If someone is updating this model and accidentally types Autmatic in cell B3 instead of Automatic, your totals at the bottom will be wrong.
In cell C18, we can create an error checking formula that will alert us if something like this happens. There are two different formulas we could put in cell C18 that will alert us if the model is not balancing properly:
is a superior error check, but on a small number of occasions, there can be an issue with this.
This formula usually works, but it can occasionally return a false error result, even though the values are the same. This is a bug caused by the fact that Excel carries calculations to 14 decimal places. After that, it truncates the value and can cause a minute discrepancy, which will report an error when it's only 0.00000000000001 off.
Therefore, to avoid this potential issue, you could use an absolute value formula, which would allow a tolerance for error. will allow the values to be off by 0.1 before it reports an error. If you use the ABS function in Excel, this will take the absolute value of the result, such that it does not matter if it is a positive or a negative number.
In Figure 4.11, if the sum of each individual item does not equal the grand total, the cell will return the word error; otherwise, it will show a zero. There are many variations of this formula, and I'm sure you can come up with various of them. Many modellers prefer to show the word OK if the numbers are right, and Check if they are not.
To make the error check even more prominent to the user, use conditional formatting to add a rule that makes the entire cell turn red if the error check has been triggered. See “Conditional Formatting” in Chapter 7 for how to do this.
Consider adding in an error checking page at the very back of your model that links through to all the error checks in the entire model. This page can be hidden, as the user does not need to see it, and the modeller can unhide it if necessary.
Create a summary cell that will identify, in a single cell, whether or not there are any errors in the entire model. This can be achieved with a COUNTIF formula like this:
See “Aggregation Functions” in Chapter 6 for greater detail on how to use a COUNTIF function. The completed version of this exercise can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.
The COUNTIF function returns a one if there are errors. Now nest this with an IF statement, which will show the text Errors exist within this model if any of the error checks have been triggered:
Now copy this formula (see Figure 4.12) to somewhere prominent at the top of each page (such as within the header) to create a global error check indicator, which will alert users to any error as soon as it is triggered.
Global error-check alerts are particularly useful for a modeller who has users working with the model regularly. The error check will alert the user that something has gone wrong in the model—usually the user has simply entered some incorrect data—and hopefully, the user will be able to correct the input, ensuring the continuing integrity of the model.
Sometimes a formula may return an error message. Usually, you will want to know when a formula error occurs. But now and then, you may prefer to avoid the messages. For example, if you are calculating a percentage in a column and one of the entry fields contains a zero, you will get a #DIV/0! error. Or, if using a VLOOKUP function, the criteria you have entered does not exist in the data, a #N/A error will appear. You can stop these errors from appearing by using an IFERROR function to check for an error:
Note that many modellers do not agree with avoiding error displays and recommend that you always show errors. The reason for this is that using error suppression can mask other—genuine—errors, so use this function with caution.
If you have been using Excel for any length of time, you've probably come across a circular reference. If your formula is trying to refer to itself, you'll end up with a circular reference. A common (and easily fixed) cause of this is when a sum range includes the sum itself. For example, in the formula in Figure 4.13, cell B11 refers to cell B11, and this means that the formula cannot calculate properly and is returning a zero value. You can see in the status bar at the bottom where the circular reference resides.
You cannot rely on a model that contains circular references. The calculations may not work, and you can't be sure the other formulas are calculating properly.
In certain rare situations, you can have intentional circular formulas that you have deliberately allowed in your model (in which case you'll have to enable iterative calculations, as shown in the box below) but it's more likely that your circular reference is unintentional, so you'll have to get rid of it as soon as possible. If you leave it and try to find it later, or worse still, leave it for someone else to fix, the formulas can become more complicated, meaning that the circular reference can become further embedded in the model, making it more difficult to track down and fix.
You'll get a warning message as soon as the circular reference happens, so the simplest way to fix this is to stop and simply undo the last thing you did. You really just need to find the offending cell and remove the link. Sounds simple, but sometimes it's not that easy. The notification in the status bar in the bottom left-hand corner of Figure 4.14 may help find the cell causing the problem.
However, you need to be on the correct tab in order for this message to show. If you aren't sure which tab the circular reference is on, you need to hunt it down by going to each tab to see if it shows on the status bar. Alternatively, you can use the auditing tool to locate the circular reference, as shown in the box below.
There are a few instances where you might wish to keep a circular reference within the model. A common reason for this is when you are calculating interest payments on a profit and loss statement, as shown in Figure 4.17.
When modelling financial statements, this circular reference can be avoided in a few different ways:
If you decide to enable iterative calculations, you must determine how many times the formula should recalculate. When you turn on iterative calculations without changing any of the defaults, Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. However, you can control the maximum number of iterations and the amount of acceptable change.
This chapter has discussed a collection of techniques and strategies commonly used in financial modelling. Those who have been using Excel for any length of time will know how easy it is to make a mistake, and what a huge effect this can have on their business models. Therefore, knowing and using strategies for reducing errors is an important skill for the Excel modeller to have. We also looked at how to avoid overly long and complex formulas, and how to dissect them if we are unfortunate enough to have inherited a model that includes overly complicated formulas. Linking to external files, building error checks, and dealing with circular references are also issues that are commonly encountered in financial models.
3.142.173.238