3 tips for checking and reviewing financial models
Financial modeling is an increasingly important skill for accountants and finance professionals. A good model needs to tell a story and create confidence for the users and decision makers. Unfortunately, many financial models are a mess – which increases the likelihood of errors.
Recently CFO Connect hosted a webinar led by a financial modeling expert who took us through his step-by-step process to find mistakes in models.
Meet the expert
Ian Schnoor CFM, CFA, is based in Toronto, Canada. He started his career in investment banking, but now is the Executive Director of the Financial Modeling Institute, the world’s only financial modeling accreditation program validating the modeling skills of finance and accounting professionals. Throughout his career, he’s reviewed thousands of financial models. He regularly delivers financial modeling webinars, training sessions, and podcasts to audiences around the world.
Ian shared his expertise with the CFO Connect community in an hourlong webinar focused on how to find errors in financial models. He highlighted his best practices to review models and spot sneaky flaws that may be hiding in the spreadsheet.
Much of Ian’s presentation focused on practical tips to use in Excel, including keyboard shortcuts. Towards the bottom of this article, you’ll find a link to his comprehensive list of keyboard shortcuts for both PC and Mac users.
Watch the webinar
If you’d like to watch the entire webinar, we have it available for you here. For the recap of Ian’s top tips, continue reading the article below.
3 hacks for reviewing a financial model
Reviewing someone else’s financial model can be tough. If you didn’t build the model yourself, it takes time to understand it and checking for mistakes can be much harder.
The last person to review and approve the model takes the blame if errors are found later on down the road. So it makes sense why people are reluctant to check financial models. As Ian says:
“If you bless it, you own it.”
Ian presented a list of his favorite hacks for checking a financial model. These include easy ways to find errors so you can “bless” the model in full confidence. Here are his best practices and recommendations:
When checking a financial model, having a bird’s eye view of the entire document can be incredibly helpful. Ian says this view provides insight into how the model was built.
In a traditional model, only a tiny piece of the spreadsheet is viewable at a time and it can take a while to scroll to the bottom or over to the side. Searching for and spotting errors is difficult because you can’t see the whole document at once.
Here’s Ian’s advice to be able to see as much as the model as possible:
Use the Zoom feature to “zoom down” (or “zoom out”) and make the entire model tiny. To do this, click Alt + W + Q to open the Zoom menu. Then, select the Custom field of the Zoom menu, enter 35% and click OK.
Don’t worry, the model will be too small to be able to read any numbers and that’s the point. You want to look at the big picture, or the bird’s eye view, so you can see how the model is built, where the components are, and how deep and wide the model goes.
Look for black dots in the open spaces, any irregularities, rogue numbers, and mistakes that may be lingering outside of the formulas.
By zooming down and getting an overview of the document, you’ll immediately see if there are major structural problems in the model.
Ian’s “zoom down” hack appears at 15:50 in the YouTube video.
Look for white values
While zooming down, check for hidden white cells. White numbers in a spreadsheet can be dangerous. They could be lurking in formulas, mistakenly deleted, or otherwise forgotten.
To check, make the background gray while you are still “zoomed down”. Type Control + A to select the entire document. If you’re using Excel 2019 or earlier, you’ll see any and all white values right away.
For newer versions of Excel, use the paintbrush tool (it looks like a small paintbrush up in the top of the navigation bar) to make the background color gray. Select the entire document with the keyboard shortcut above, go to the homescreen, and use the paintbrush tool to paint the background gray.
White values will pop out immediately. Then you can “zoom up” (revert the document to its normal size) and figure out why the white numbers are there.
Skip to 21:30 in the video to watch the section on finding white values.
Check for dead inputs
This next technique is a favorite of Ian’s. “Dead inputs” pose a huge threat to the integrity of financial models.
Too often, people enter arbitrary numbers into their formulas to see what changes would result if a certain situation occurred. However, it’s more likely than not that you’ll end up forgetting any changes you made, and the model will be riddled with mistakes.
The result? Your formulas won’t work because you changed the assumptions.
Luckily, Ian has a great method to check for dead inputs:
Press F5 or Ctrl + G on your keyboard
In the Go To menu, click Special
In the list that appears, click Constants then click OK
This action highlights every single value on the sheet that was entered as an input cell. Labels and historical values will be highlighted, which is to be expected. You can ignore those.
If you see any numbers that shouldn’t be highlighted, like forecast numbers, that should ring alarm bells. It means that somebody typed a number into the cell that doesn’t belong in the formula. That’s what Ian calls a “dead input”.
This is an easy way to spot errors. Scroll up and down the model to see what’s highlighted and investigate each cell to see where and why dead inputs appear.
This hack appears in the video starting at 26:20.
Keyboard shortcuts to use in Excel
Not only did Ian share these useful hacks for finding errors in financial models, he also used several keyboard shortcuts - saving time and revealing hidden features. You can find the full list of keyboard shortcuts for both PC and Mac users on the Financial Modeling Institute’s website.
Discover more financial modeling hacks
Being able to build a reliable and accurate financial model is an important skill to have and cultivate. But as Ian says, “It’s powerful to be able to find errors.” It may seem counterintuitive, but the ability to review a financial model is just as important as the ability to build one. Both skills are handy to have in your arsenal.
Watch the webinar in its entirety to discover more of Ian’s helpful hints and tops. From his step-by-step process for identifying errors in financial models to including time-saving keyboard shortcuts and secret hacks, don’t miss out.