Ten Common Pitfalls to Avoid
hroughout my career as a financial modeler, I’ve seen countless things go horribly awry. Many of these problems can be attributed to at least one (or sometimes several) of the pitfalls I describe in this chapter. Why focus on
the negative? So you can learn from the mistakes of those who’ve come before you! If you’re aware of these potential problems, you can work to avoid them.
The Numbers Don’t Add Up
“So, are we confident in these numbers?” Often, that’s the first question you’ll be asked when presenting a model or drawing conclusions from one. You need to be absolutely sure of any numbers you’re presenting, and you need to be able to explain exactly how you came up with the results.
Confidence in the numbers comes from an intimate understanding of the process and calculations that make up the model. The audience or those you’re working with will be able to detect any uncertainty on your part. Read through the strate- gies to reduce errors in Chapter 13. You can employ these strategies when building your model. And if you’re inheriting someone else’s model, error checking is even more important so you’re completely confident that the numbers are right.
You’re Getting #REF! Errors
#REF! errors are the worst kind of errors to get because it means that the cell(s) the formula is referring to have been deleted or that the formula is referencing off the sheet. These errors are the most difficult to fix because the offending cell is no longer there, so the entire formula needs to be rebuilt. To fix this type of error, you need to go back to the beginning, assess what the formula was trying to do, and rebuild it from the ground up.
You may be tempted to wrap an IFERROR function around the formula to suppress any error from showing at all. If you do this, you can end up masking errors that need to be corrected.
The best way to avoid having errors in your model is to never let anyone else touch it. In a corporate team environment, of course, this isn’t practical; in fact, it defeats the purpose of building a financial model in the first place. You can pre- vent Excel errors from appearing in your model when others use it by restricting the model’s use with data validation and protection. For more information on how to do this, turn to Chapter 6.
If errors do appear, they aren’t anything to stress about but the worst thing you can do is ignore or suppress them. You need to understand why they appeared, get to the bottom of the problem, and fix it.
You Have Circular References
Circular references are the result of a formula that somehow links back to itself. Contrary to what some modelers claim, you should not allow a circular reference in your model. If you’re building a model, the best way to avoid circular references is to simply undo what you just did as soon as the circular reference warning appears. If you’ve inherited a file that contains circular references (or if you didn’t solve the problem in your own file when the problem first appeared), you can end up spending a lot of time trying to get them sorted out.
The most common circular reference is when you accidentally include a cell’s reference in its own formula. For example, in Figure 14-1, I’ve built the formula =SUM(B3:B8) in cell B8. An error message appears immediately. If I ignore the error message by clicking OK, the formula simply won’t calculate correctly. Although this might seem really obvious, it’s surprising how easily these kinds of circular reference errors can be made.
FIGURE 14-1:
A circular reference.
Take a look at a more complex example. In Figure 14-2, I have the opening and closing balance of a cash flow. In row 5, I’d like to calculate the interest income. Because I don’t know the exact timing of when the cash was paid or received dur- ing the month, it’s most accurate to calculate the interest income based on the average of the opening and closing balance. So, the formula in cell B5 is =AVERAGE(B2,B6)*$J$1. However, one of the inputs to cell B6 is cell B5. And one of the inputs to cell B5 is B6. And so it goes, culminating in a circular reference.
FIGURE 14-2:
A more complex example of a
circular reference.
If this circular reference error warning is ignored, the formula won’t calculate correctly, and the words Circular References will appear on the status bar at the lower-left side of the screen. You need to find and remove the circular reference from your models.
To locate circular references in a model, click the Formula tab on the Ribbon, and find the Formula Auditing section. Then, from the Error Checking drop-down box, select Circular References.
Note that one solution to the problem shown in Figure 14-2 would be to get around it mathematically by inserting a “working cash at end” calculation, as shown in row 5 of Figure 14-3. You can then use this number to calculate the interest pay- able or receivable, and then add or deduct the interest to arrive at the ending cash balance in row 7. You can download the worked solutions to the example in File 1401.xlsx at www.dummies.com/go/financialmodelinginexcelfd.
FIGURE 14-3:
The solution.
If your model contains a circular reference, you can’t trust the calculations. You need to find and remove the circular reference from your model.
The Model Has Too Much Detail
Attention to detail is an important attribute for a successful financial modeler to have. In fact, many modelers are detail-oriented people because big-picture thinkers seldom have the patience for the intricacies and technical minutiae
required for complex financial modeling. However, it’s easy to lose yourself in the detail and get completely bogged down by it.
When you’re building a model, you need to maintain a sense of perspective and remember that a financial model is only a representation of reality — it’s not actual reality.
Putting too much detail in a model — for example, calculating the exact number of customers without rounding to the nearest whole number or nearest hundred — creates a false impression that you know more than you really do. The model is just an estimate of what you think might happen. There’s only one outcome you can be sure of: Whatever the output of the model, you can be sure it won’t be exactly correct.
Think about the level of detail that’s required by the users of the model. Some- times you may need to go down to unit cost level in order to calculate revenue, but if no one will need or use these assumptions, then unless you need it for calcula- tion, you don’t need to put them in. A model that provides more detail than is required or needed can be counterproductive.
Information is powerful, and a detailed approach allows you to draw on this power. Just be careful that you aren’t giving yourself extra and unnecessary work, or confusing, overloading, or misleading the model users.
The File Size Is Out of Control
When you start getting a fair amount of data in your financial model, it’s pretty easy to end up with an enormous Excel file that takes a long time to calculate, making it difficult to review or share with others.
If the large file size is due to a large number of rows (say, more than 100,000), consider using Power Pivot to store the data instead. (Turn to Chapter 2 for more on this tool.)
If you find your model getting out of control (and you’re using standard Excel, without the help of Power Pivot or any other add-ins), here are a few tried-and- tested tips you can use to keep that file size down:
» Remove any unnecessary formatting. Colors and formatting should only apply to the necessary range, not an entire row or column. Avoid manual formatting and use Styles instead.
When you’re clearing cells that you’re no longer using, you probably do so by selecting the cell and pressing the Delete key. This action clears the contents and formulas, but it doesn’t clear the formatting. If you suspect this is a problem, you can press Ctrl+A to select all cells; then on the Home tab of the Ribbon, in the Editing section, click the Clear drop-down and select Clear Formats.
» Make sure formulas are referencing only the range they need to (not selecting the entire row or column). If your formulas are referencing more cells than they need to, this will use more memory. For example, use the
formula =SUM(A1:A1000) to allow for additional rows instead of =SUM(A:A). Alternatively, you can format the data as a table and refer to the automatically expanding table ranges in your formula instead.
» Remove (or at least check the size of) any logos or images that you’re using. A single JPG fi inserted in a model can easily add 10MB to an Excel fi size.
» Avoid PivotTables. PivotTables really chew up memory. If you have multiple PivotTables, make sure they’re using the same data source, and not creating a whole new one.
» Remove external links to other fi To check if there are any external links in your model, go to the Data tab on the Ribbon and click Edit Links in the Connections section. If the Edit Links button is grayed out, that means there
are no external links. If they exist, click the button and when the Edit Link dialog box appears, click Break Links to paste the data from all external links as values, and the external links will be removed. If you absolutely have to use external links, have the source fi open at the same time; this will speed things up.
» Check for redundancies in inputs and calculations. Sometimes a model evolves, and there may be parts that are no longer used or parts where information has changed. See Chapter 5 for techniques on how to check for
redundancies.
» Avoid volatile functions. Some of the most commonly-used volatile func- tions are OFFSET, INDIRECT, RAND, NOW, TODAY, ROW, and COLUMN.
Overuse of these particular functions in your model can really slow down your calculations. If you must use them, try to limit their appearance in the model. For example, =TODAY() is very useful for giving today’s date, but instead of using it multiple times in a formula, have it in one single cell and continually reference back to that one cell for today’s date.
» Make sure you aren’t using the XLS fi type. XLSX is a much more compact
fi type, and you’ll see a huge diff in speed and fi size if you use
it. XLSB is an Excel binary workbook fi type that is even more compact than XLSX.
If you’ve tried all these tips and you’re still having problems, consider switching the calculation to manual (you can do this by clicking the Formulas tab on the Ribbon, going to the Calculation section, and selecting the manual calculation option). Then press F9 only when you need to recalculate. You’ll know if some- thing needs to be calculated, because you’ll see Calculate in the status bar.
Finally, as a last resort, a little trick is to leave one cell at the top of the column with the live link and paste all the other cells as values. Copy the cell down and recalculate when you need to refresh. This certainly isn’t a preferred option, because it’s time-consuming and prone to error, but that’s why it’s a last resort.
