Model with Consistent Formulas
If you’re getting a model audited by a third party, one of the many things they’ll check for is the number of unique formulas in the model. A good modeler will cre- ate as few unique formulas as possible as he’s building a model. In the following example, I’m calculating portfolio returns of different amounts. Figure 4-7 shows the formulas, rather than the calculated values. The way this block has been cre- ated in this instance, nine different formulas have been used, which is a very slow and inefficient way of creating this calculation block.
Download File 0401.xlsx at www.dummies.com/go/financialmodelinginexcelfd
and select the tab labeled 4-7 or simply open Excel, re-create this example and try it out for yourself. You’ll see that creating the formulas shown in Figure 4-7 is a slow and tedious process.
If the formulas aren’t all showing when you download and open this file, click the Show Formulas button in the Auditing group on the Formulas tab in the Ribbon. Or, you can use the Ctrl+` shortcut (the ` symbol can be found to the left of the 1 on most keyboards, under the ~ sign).
FIGURE 4-7:
Calculating portfolio returns using nine unique
formulas.
The task is made much simpler by merely including a few dollar signs in the for- mula in cell B2 and then copying. Using one single formula and then copying it across and down the calculation block, as shown in Figure 4-8, is much quicker and less prone to error.
FIGURE 4-8:
Calculating portfolio returns using one single
formula.
Using mixed referencing within the formula — by putting a dollar sign before the row or column to be anchored — is a far more efficient way of modeling. For more information about how to use relative, absolute, and mixed cell referencing, and for step-by-step instructions for completing this example, see Chapter 6.
This concept of building your models with consistent formulas wherever possible saves time, avoids error, and is much easier to audit. And it’s a key component of good financial modeling. If you only pick up one modeling technique from this book, this is it!
Modelers should strive for consistency of formulas within models for all the rea- sons I outline in this section, but consistency, in general, is something to aim for in all aspects of the model build. Use consistent colors and formatting, consistent labels, and even consistent layouts. If sheets are similar, work on the sheet until you’re completely happy with the layout, and then copy the sheet. This way, the design, formatting, and layout will be identical. Then when you need to update it, group the sheets to make global changes. Have columns and rows matching on multiple sheets — for example, on calculation sheets, if January’s calculations start in column F, then January should always start in column F on every calcula- tion sheet.
Build in Error Checks
Even if you’ve only just started modeling, you’re probably well aware how easy it is to make a mistake in a financial model! There are three ways to prevent errors in a financial model:
» Avoid making the mistakes in the fi place. In this book, I describe several techniques that you can employ to avoid making mistakes in the fi place, such as being consistent with your formulas.
» Check the model for errors. Despite your best eff errors inevitably may slip through, so check, double-check, and have someone else check your model after it’s complete.
» Include error checks. As you’re building the model, include error checks that prevent inadvertent errors from slipping into the model due to incorrect entries or user error.
For more examples of different types of commonly made mistakes, and some ways to avoid making these errors in your models, see Chapter 13. This section focuses on the first two points: techniques for model building to reduce error, as well as ways to check the model for errors.
Error checks are a critical part of a well-built financial model so that the user or modeler can see at a glance if the formulas are calculating correctly. For example, when creating management reports, check that the sum of each individual depart- ment’s report adds to the company-wide total. This can be done by inserting a simple IF function, among other methods.
In the example shown in Figure 4-9, a capital budget has been built with esti- mated spend dates in column E. In the capital spend schedule shown in columns F through Q, the spend gets spread out over the full year. The modeler knows that the total capital spend amount of $124,700 shown in cell D17 should be the same as the total capital schedule amount shown in cell R17, and if the two amounts do not equal each other, then the model is not calculating properly. So the error- checking cell E1 contains the very simple formula =R17-D17.
In Figure 4-10, you can see that a user has entered an incorrect value in cell E4. Feb-20 is not a valid entry because the capital spend schedule only allows for dates during 2019. This means that the financial model shown in Figure 4-10 is incorrect — the user has entered $124,700 worth of capital expenditure into the model, but only $115,700 has been allocated across the year. The number showing in cell E1 (9,000) alerts the user to the fact that there is a problem. Download File 0401.xlsx at www.dummies.com/go/financialmodelinginexcelfd
and select the tabs labeled 4-9 and 4-10 to try triggering this error check for yourself.
FIGURE 4-9:
A simple error
check.
FIGURE 4-10:
An error check
triggered.
This error check is very simple and quite discreet. To make it more obvious, you may prefer to include the description “error check” next to the error checking cell in cell D1, which would make it more obvious to the user what has happened when the error check is triggered.
The error check shown in Figure 4-10 is my preferred method of error checking, because it’s so simple and quick to build. Because it returns a value in the case of an error, it may be a little too discreet for your tastes — it doesn’t necessarily alert the user immediately that an error had been made. However, it’s certainly quick and easy to follow and, for this reason, a fairly common error check favored by many modelers.
If you use this kind of error check, format it using the Comma style (found on the Home tab in the Numbers group) and remove the decimal place and format it with a red font. This way, the zero won’t show if there is no error, and a red number will show if there is an error.
Alternatively, you may prefer the other error checking formula such as =D17=R17, which will return the value TRUE if they are the same or FALSE if they aren’t. However, this method is also subject to a false error, as shown in the following section.
=IF(D17<>R17,”error”,0) is a superior error check, but every now and then it can return a false error result, even though the values are the same. (See Chapter 7 for how to use an IF statement in a formula like this.) This “bug” in an error check is 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. To avoid this potential issue, you could use an absolute value formula, which would allow a tolerance for error.
=IF(ABS(D17-R17)>1,”error”,0) will allow the values to be off by $1 before it reports an error. If you use the ABS function in Excel, this will take the absolute value of the result, so it doesn’t matter if it’s a positive or negative number.
There are many variations of this formula. Some modelers prefer to show the
word OK if the numbers are right, and Check if they aren’t.
