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
Spaghetti links
in a model.
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).
FIGURE 14-5:
Deleting a block causes #REF! errors when spaghetti links
are used.
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 cor- rectly 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 vali- dating 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 com- plexity 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 calcu- lated. 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 prac- tice. 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 col- league’s desk, trying to look calm. You take a look and, sure enough, there’s defi- nitely 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 eff ely 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.
and the “clean” copy with the same inputs, comparing the two versions will get
» 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 diff answers from the “broken” copy
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.
