You will most likely spend almost as much time checking a model for errors than you spent building all of it. Here’s how to find mistakes in excel before your team finds them.
Why you should always spend time looking for them
I remember getting so frustrated by this in the beginning. I made tons of mistakes, and I felt like I was working like crazy to identify them every time. But I really wasn’t.
Ther where two issues I had to deal with. Issue number 1: I didn’t actually spend that much time looking because I was impatient after finishing something. You’re not done until you know that your model can give the correct results.
Issue number 2: I didn’t have the tools to know where to look, and I didn’t know how to find mistakes in excel, or which mistakes were the most common.
The first problem is one of attitude. No one can solve that for you. But you should read on to get the tools to find the most common mistakes investment bankers make in excel.
Check if you link to the correct cells
Use F2 to go through cells and check that they are linking to the correct places. If you have copied the same formula over a large area of cells, start by checking the first cell. Then go one column to the right and one column down.
If you have used F4 correctly, this cell should now link correctly. If it doesn’t, most of your references will be wrong. Checking the cell down and to the right is the most effective way to identify several mistakes related to linking.
Trace precedents: Press Alt+m+p if you want to see which cells the one you have marked links to. You will get some beautiful blue lines going from your cell to whichever cells it is fetching information from.
Trace dependents: Press Alt+m+d if you want to see which cells are dependent on the cells you have marked. It’s the opposite of the formula above, and very useful if you are checking an input cell that is supposed to feed information through the rest of your model.
One simple hint that something is wrong, is if an input cell has no other cells linking it. You should either remove the input cell altogether, or you need to go looking for errors.
Use standard tests
There are some simple tests you could implement as you go as well. These are the most common ones:
Make sure your balance sheet balances. The difference between Equity + Debt and Total Assets should be 0. If it isn’t, something is wrong.
It all seems very basic, and everyone should know it. Still, I find analysts turning in models that don’t balance. And then they ask me why they should put in time fixing it when the balance sheet isn’t important.
Having a balance sheet that is wrong may not be that big of a deal since the cash flows are usually what’s important for valuation.
However, it usually means something is wrong somewhere else in the model as well because the inputs aren’t flowing the way they should. Also, no one will trust anything you give them if the balance sheet doesn’t balance.
If you are in the unlucky scenario that it doesn’t, try checking the change in the error between each year. For example, are the assets increasing by $100 more than the equity and debt each year? Maybe you haven’t financed the Capex in your model properly.
When you have these changes, try looking up the changes between the years of each balance sheet item.
If you’re lucky, one item will be the exact amount you are adding or subtracting wrong (or more often, it will be half of the number you are looking for, because of double accounting).
Cash flows should never disappear or come from nowhere. One simple check is to make sure that the change in cash balance each period equals the cash flow of the period.
If the company created $85m in cash flows, your cash account cannot increase by $95m.
Highlight errors with conditional formatting
Use Alt+h+h to create flags. The flags can either tell you something about which period you are in, or it can tell you about an error.
Are you developing, producing, or maybe you are selling one of your assets during the chosen period. Make sure the flags are only lighting up when they are supposed.
It can also be created to light up if something is wrong with your model.
You can make it turn red if the sum of Equity and Debt is different from the Total Assets on your balance sheet. Or you can make the font yellow if the value in the cell is different from the cell next to it.
Try extreme inputs
Change the inputs of your model and see that everything updates as it should. If you are using flags to tell you if you are selling to period 1 pricing, then try changing when period 1 starts and stops.
You have a few rules to check most scenarios. Check the start of a period or the lowest value. Then check the end or the highest.
At last, check what happens if you are between two values, or your period falls within two different periods.
Always try to flex your other inputs as well. What happens if you have too high debt? Or what happens if you have super high tax rates? What about no taxes at all?
This is usually where you find out that your model hasn’t taken into account other scenarios than the most standard one.
Use your common sense
This is something that gets easier over the years. As you have created more models and looked at more valuations, you know which range different values are supposed to lie within.
If they are outside this range, you need to double check if there is a legitimate reason for them to be different, or if you have made a mistake.
What do you think the number is supposed to be? Does it make sense that the value of a company increases if you increase the costs? No? Then go and figure out what is wrong with either the way your model treats costs or how your valuation works.
How big are costs, investments, or debt repayments usually? What is usually the P/E, EV/EBITDA or EV/EBIT in that sector? Is your IRR or MoM crazy high?
The person you give your model to will ask you about all of these outliers. Make sure you have checked them first.
Put in the time it takes
These tests won’t help you avoid all mistakes, and you should always get someone else to double check your work.
It helps to ask someone who is just above your level and has worked on the same type of projects as you have.
Try to be the person the people above you in the hierarchy can trust. It is very painful and embarrassing to send some numbers to a client that you have to correct later. Or even worse, your client points out your mistake to you.
Your boss will never blame you in front of a client, but your superior likely won’t give you much work to do in the future.
More articles in the same series:
This was the final article in the ExCel series for investment bankers. If you want to go through all of them, start at the top and work your way down the list below.
- Basic ExCel shortcuts you need to learn when you start in investment banking
- How to quickly nail the formatting of your first EcXel model in banking
- Understanding how to link correctly and roll out big ExCel models quickly
- How to avoid the unnecessary pitfalls in your ExCel cash flow valuation
- Error checking in EcXel: Tools to discover your mistakes before it’s too late