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.²

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:

• Set inputs to zero and check that the outputs respond as you would expect. For example, by setting price to zero, you would expect revenue to also be zero.
• If you add one unit, is the output increased by the value of one unit?
• Double your headcount. Do your staff costs roughly double?
• If you are indexing costs, try setting the indexation percentage to zero and see if the costs remain flat.
• If you double the pricing, does gross revenue double as well? If not, why not? The answer could be due to the effect of discounting or a complex pricing structure. Make sure that you get to the bottom of any anomalies and discrepancies.
• Chart metrics on a line graph such as cost per head, price per unit, cost to serve a customer, or other metrics relevant to your business. Look at the curve and make sure that you can explain each and every trend, spike, and downturn.

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.

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:

• Do use named ranges when linking to external files.
• Don't email files that contain links. Save another version and use Break Links in such cases.
• Don't change file names and sheet names unless you really need to.