What to Do if Someone Messes Up Your Financial Model
You’ve finished with your financial 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.
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.
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. 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.