First, let us talk about what a financial model is and what does it mean to audit it. A financial model is a document used to forecast a company’s financial performance, position, and cash flow to make an investment or financing decision or for planning. Why is it important to audit a financial model? The word audit simply means to examine, verify, and vouch the financial model to ensure that it is accurate, reliable, reasonable, dependable, and free from errors. Therefore, auditing a model helps to ensure and ascertain the integrity of the document.

I wish to tell you that it is not enough to be able to prepare a financial model; to stand out among colleagues, you need to be able to audit someone else’s model. Your ability to audit and spot errors and inconsistencies in a model will to a long way to prove your mastery of the craft.

Note that at some point in your career, you will be required to review some else’s model, especially if you work in corporate finance department. You must have at the back of your mind that multi-million dollars (or whatever your currency is) of investment decision may be made by client or your company based on the financial model prepared; hence a little mistake could ruin the model and have a deleterious impact on you career. Have you not seen a situation whereby a modeller link a wrong cell or entered a wrong formula? Unfortunately, in some situations, the model will still balance, so you won’t know there was an error.

Now, for a moment, put yourself in the position of a manager or director, your reputation is on the line and you will be held responsible if the model is badly prepared. To avoid any bad story, your ability to audit a financial model is sacrosanct. Remember, when you review someone else’s financial model, you are simply taking ownership of the risks associated with it even when you did not prepare it.

From my experience of preparing financial models and auding other people’s model, I wish to share with you a few tricks and tips that will be beneficial wherever you work. This for financial modellers alone, it is applicable to any Excel user. Before diving into the tricks and steps to auditing a financial model within the aid of Excel software, let us itemise, succinctly, the reasons why we must audit a financial model.

Why should we audit a financial model?

1. To understand and become comfortable with the model.

2. To make sure the model is reliable for decision making.

3. To make sure the model is error-free and credible

4. To ensure all assumptions and inputs are captured.

5. To check for prudence and reasonableness of the model

6. To check for the presentation and formatting of the model.

Having describe the reasons why we audit financial model; now, here are a few of my tips to auditing a model.

1. Getting a bird eye view of the mode

Why do we need to get a bird eye view of a model? There are two benefits to doing this: one, you get a quick insight to the model structure and format. Second, you can easily sight any hidden “model island.” A model island is a strange table or number within the model, usually at the right side of the model. Have you not seen a situation whereby you scroll to the far right of the worksheet, and you discover a table or a few numbers?

The first thing you must do is to zoom down the entire worksheet from 100% (or wherever it is presently) to about 35% or below. This way, you can quickly sight any model islands. To show the zoom window in Excel, the shortcut is Alt + W + Q or Alt + V + Z.

2. Check for numbers hidden using white font colour

Have you seen a model whereby a bunch of numbers are hidden from you using white colour? There could be several reasons why modellers do this but the truth is that it’s very frustrating. In fact, as a matter of best practise, and in line with FAST standard, no number in the financial model should be hidden from users or readers. Your model should be verifiable and easily understandable. Nevertheless, you can still reveal whatever has been concealed. Highlight the entire worksheet (with Cntl A) and change then use a grey colour fill; in 5 second, you can see all numbers that have been hidden with white font colour.

3. Check for “dead inputs”

When preparing a financial model, usually there are three different font colours: the blue colour is to show the inputs and assumptions, black colour to show normal numbers and formulars and any other colour (sometimes green) to show inputs linked from another cell or sheet. This usually vary from one organisation to organisations depending on the policy and standard. Now, what is a dead input? Dead input are numbers typed into the model to override the linked numbers or the formulas. People do this either by mistake or deliberately to force a model to balance. This pan out to be an incredibly stressful exercise later when there is a problem. As a modeller, you need to have in your tool bag some techniques for resolving a problem like this.

If you ever find yourself in this situation, what you need to do is to highlight the entire block of cells you want to audit, press F5 on your keyboard to bring up the “Go-to” window and on the “Go-to” window click on “Special” and then select “Constant”. If you follow this step properly, the cells with dead inputs will be highlighted automatically.

4. Check for “dead numbers”

Dead numbers like dead inputs are hardcoded into the model but this time, they are hardcoded to an existing formular or link. In this situation the formula or link is not superseded, the number is only added or subtracted. Unfortunately, the above technique will not work; we need a different way to address this problem. You can try any of the following:

a. By a brute force, check each cell withing the block you are examining or the entire worksheet to spot any number hardcoded into formula or link. To make the process less sweaty, use Cantal + ` to show all formulas and links and then try to figure out any hardcode. Notice the symbol “`” could be found just below your Esq button. If Cntl + ` does not work for you, you can try Alt + M + H to show all formulas. This method is a bit tasking but it is just one way of doing it.

b. Secondly, you can highlight the row or block you are verifying and use Cntl +\. This function will highlight cells that does not have a consistent formular. Therefore, when you are trying to spot a dead number, always confirm the formula in the first column and then check for difference across the row with the method described. When you do this, the cells’ where hardcoded numbers have been added will be immediately highlighted.

c. Another way to go about this is to highlight the entire row, then press F5 to bring up the “Go-to” window, from there go to Special and select “Row differences”.

5. Sign-Off verified rows

Having reviewed a particular row of the model and you are comfortable with what you have, you can type “OK” at the extreme right of the model to show that the role has been reviewed. When someone else within the firm picks up the model, he would know immediately that the model has been reviewed and verified.

6. Review financial model in a reversed order

This is the last technique that I will show you today. It is helpful to review a mode in the reverse order i.e., from the model output (i.e., profit or loss, balance sheet and cash flow statements) to the calculations and then to the inputs. Do not be rigid about this tip, you have a different approach.

In conclusion, we have seen the reason we audit financial model and the techniques you need to do so. I know there are more techniques; if you know any, kindly leave your comment in the comment section. If you want me to do a video to show you this or you would prefer a free live class, please let me know.

Written by Segun Adewumi