IN THIS CHAPTER

Identifying common pitfalls with financial modeling in Excel

Side-stepping those pitfalls to make your life — and your models — better

Throughout 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 strategies 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 prevent 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.

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

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

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. Sometimes 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 calculation, 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 file inserted in a model can easily add 10MB to an Excel file 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 files. 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 file 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 functions 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 file type. XLSX is a much more compact file type, and you’ll see a huge difference in speed and file size if you use it. XLSB is an Excel binary workbook file 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 something 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.

Your Model Is Full of “Spaghetti” Links

A link that links to a link, which then, in turn, links to another link, and so on throughout the model is called a spaghetti link. Spaghetti links can be confusing and difficult to follow, and they aren’t good modeling practice. Wherever possible, you’re always much better off linking directly to the source.

For example, in Figure 14-4, I have a model that’s calculating student fees at a university. It’s laid out in a repetitive, block format, which is a good way to lay out a model because it’s logical and sequential. Each block leads on to the next one, which makes it easy to follow the calculations. You can take a look at the links in this file yourself by downloading File 1401.xlsx at www.dummies.com/go/financialmodelinginexcelfd and selecting the sheet labeled 14-4.

At the top of each block, the dates have been laid out across the top. Linking these dates is a good idea, because it will make it easy to change if you want to reuse or update the model. However, as you can see in cell B38, I’ve linked the dates in row 38 to those in row 29. Row 29 is linked to row 20, which in turn is linked to row 11. Row 11 links to the top row, row 2, a perfect example of spaghetti links.

This works well, until I decide to remove a block. Later on, I decide that showing the average load per student separately is unnecessary, so I delete this block. This causes havoc in the model, meaning that any links subsequent to the second block become #REF! errors, because the cell they referenced has been deleted (see Figure 14-5).

Note that this hasn’t happened to the list of faculty names on the side. Although these names are also repetitive and could have easily been connected, I’ve correctly linked them to the source instead, so they aren’t affected when the second block is removed.

This is a very simple example of what can go wrong when using spaghetti links, but there are other reasons to link directly to the source. When checking or validating a model, spaghetti links make it much more time-consuming to audit the formula. If you’re using trace precedents (see Chapter 5), imagine how frustrating it is to trace back multiple layers of dependencies instead of simply jumping to the original source data.

The Formulas Are Unnecessarily Long and Complicated

Keeping formulas as simple, clear, and straightforward as possible is always good practice. Remember that the model isn’t only for you to use — it should be a tool that anyone in your team can pick up and understand with minimal explanation from you.

As you continue your journey as a financial modeler and learn very complicated and sophisticated Excel functions, you may be tempted to show off your Excel skills by creating fancy and complicated formulas. The problem is, this isn’t very clever at all. A model should be as simple as possible but as complex as necessary.

This means you should start with a simple solution, and if that does the job, great! If you need additional functionality, you might need to add to it — but do it in a separate cell or a separate calculation block.

Deciding at what point to break a formula into separate cells is tricky because it also can make the model bigger. But as a general rule, and depending on the complexity of the functions used, don’t try to do more than two or three things in a single cell. Break it down into separate rows or calculation blocks and lay it out so that it’s easy to trace back and see exactly how the numbers have been calculated.

For example, this calculation to come up with student fees seems simple enough:

Student Numbers × Load per Student × Fees per Load Unit = Total Fees

But when converted to Excel, it’s more difficult to follow:

=B3*VLOOKUP($A12,Assumptions!$A$2:$B$7,2,0)*VLOOKUP($A12,Assumptions!$A$1:$C$7,3,0)

Even though the formula is only multiplying three numbers together, which is not difficult to follow, the fact that the first two numbers are derived from a VLOOKUP function makes it difficult to check and understand. The formula is much easier to follow if the calculation is laid out in separate blocks.

As you’re building a model, take a moment to consider the user who needs to make sense of your formulas afterward. Anyone using this model will want to check the numbers for herself and make sure she follows how they’ve been calculated. A long and complicated formula may make perfect sense to you at the time, but it needs to be as clear and easy as possible for someone else to understand — and for you to come back later to figure out how the formula works again if you need to modify it.

No One Is Paying Attention to the Model

Imagine you’ve spent weeks slaving over your financial model. It flows beautifully, the numbers are perfect, and the scenarios work. Finally, the time has come to present your pride and joy at the weekly management meeting. You hand out hard copies, show the results on the projector, and launch into a detailed explanation of what the model is telling you about the business. “That’s great,” someone says, smiling politely. “Thanks very much.” “What’s for lunch?” someone else asks.

This kind of disinterest from those not involved in the model-building process can be disheartening, but there are a few things you can do to gain their interest:

• Involve them in the project if you can. Ask their opinion on the subject and have them review the assumptions in advance of the meeting. If they’re involved in the inputs and if they have a stake in the outputs, it’s going to mean more to them.
• Show some visuals. A boring black-and-white table with model outputs will put even the most hard-core bean counter to sleep. Spend some time making the outputs more meaningful with some charts and visual analysis.
• Don’t just present an information dump, or bore them with unnecessary detail.

For more ideas on how to better present model output, see Chapter 9.

You Don’t Want to Let Go

If you’ve spent a lot of time on a financial model, designing it, building it, and making sure it works properly, handing it over to be operated by others can be stressful. After all, it’s your creation, your “baby,” and no one knows the workings of the model better than you. You’re proud of it and you know it works well, but you’re not sure someone else will appreciate it or take care of it the way you do.

It’s natural to be worried about letting go of your model, but don’t allow yourself to fall into this trap. Keeping a model to yourself is dangerous, and being dependent on one person — even you! — for any process is poor business practice. So, how do you share your models without putting them at risk? You need to teach the people who are going to be using the model how to use and maintain it from the start.

Instead of building your model in isolation, start building the model collaboratively. You’ll be the model designer, the one responsible for the building of the model, but you should involve your team members in the process right from the start. Ask for their advice. Collaborate on design and assumptions input during the decision-making process. If you bring everyone onboard, you’ll lessen the model’s dependency on you, which means that other people will take ownership of it, too. And when they own it, they care about it.

There are some other benefits to sharing your model. Not only will it be better for the team, but by collaborating with your colleagues, you’ll get the benefit of a fresh pair of eyes and a different perspective, which is extremely valuable. Your colleagues may be able to suggest developments and help you to improve the model or identify where there are errors or opportunities for a more efficient process.

Put together a user guide for your model. A user guide can help make the model sustainable and support its use, covering times when you aren’t in the office, or after you’ve moved on because of the promotion you’ll inevitably receive due to your superior financial modeling prowess.

Instead of selfishly keeping a model to yourself, you need to make sure that others understand and use your model. By using your model, they’ll learn and improve their own modeling skills in the process. And that’s something you can be proud of.

Someone Messes Up Your Model

You’ve finished with your model and convinced yourself to hand it over to the rest of your team to review or use on a regular basis. Then, one day, you hear the words that strike fear into the heart of every model designer: “Sorry to bother you, but the spreadsheet isn’t working.”

You try hard not to panic, but you can’t help it: You break out in a cold sweat. You think to yourself, “I thoroughly checked that model and spent hours explaining and documenting how to use it. How did they manage to mess it up? That model was bullet-proof.”

Despite a growing feeling of unease, you nonchalantly stroll over to your colleague’s desk, trying to look calm. You take a look and, sure enough, there’s definitely something wrong.

Take a deep breath and start with the process of elimination. Here are some places to start:

• An audit trail of changes will allow you to quickly and effectively analyze the root cause of the issue: Is the problem with the data, or the model? You turn to the audit log on the front page, but it has been left blank. At handover, the users were asked to document any changes, but the last logged entry was your own.
• Of course, you kept a clean copy of the completed model at handover, with every change that was made, so you can compare the current copy of the model with the last one you had. If you get different answers from the “broken” copy and the “clean” copy with the same inputs, comparing the two versions will get you closer to the source of the problem.
• A review of any error checks you created when building the model will also identify the source of any obvious errors the users may have missed. Review Chapter 4 for more on error checks.

If you have multiple users, it becomes more challenging to determine who may have changed the model and whether it’s a manual error, an unexpected activity, or an underlying problem in the model design. Tracing back to find the error is a process that may be a quick fix, or it could be quite complicated. Check out the tips in Chapter 5 — the techniques for reviewing your own model and an inherited model are essentially the same.

After you’ve checked and corrected an error, you can figure out what caused that particular error to happen. This, in turn, allows you to put in place further error proofing within your model or support structure. If you haven’t done so already, make sure the model contains an audit log, a clean version, and error checks. Also, consider adding protection to the model and data validations for any inputs (for more information on how to do this, see Chapter 6). You can then decide to either correct the problem in the current version of the model or go back to the original by transferring the new data to a clean copy.