If you are lucky, you will get to build your own model from scratch, but most often you will be handed someone else's model to validate, modify, and make your own. One of the toughest tasks for any financial modeller is trying to use, understand, audit, or validate an existing model. While it may seem like a simple enough task, where the hard work is already done, the real challenge will be to understand how the model was designed.
Excel is such a flexible tool, and there are so many different ways to achieve the same end, that trying to understand the way a model has been built is like trying to get inside another modeller's mind! Give three different modellers a problem, and you'll undoubtedly have three different model designs, as each modeller will approach the model design in a different way and almost certainly using different formulas.
Obviously, the best place to start when trying to understand a model is to talk to the original designer if that is at all possible. There is no one who understands a financial model better than the modeller who designed it in the first place. However, the original modeller is often unavailable, and we therefore need to design the model in such a way that it can speak for itself.
If financial modelling best practice has been used, understanding a financial model in Excel should be a fairly straightforward task. However, many inherited models have not been built by professional modellers but rather cobbled together by different areas of a business to achieve a (usually short-term) outcome. These kinds of inherited models can be a real nightmare to unravel, and often it's simply easier to start from scratch. If this is not practical, there are some tricks and techniques in Excel that can help you to take apart someone else's model. By using these techniques, you can make the task of rebuilding an inherited model more achievable. We will explore some of these techniques in this section.
Common problems modellers encounter when rebuilding an inherited model are redundant assumptions and sources in the data model. This can happen when modellers need to use source information that is already in the spreadsheet and recreate it without realising that they are entering a duplicate set of assumptions or data. Often, as a model evolves, parts of the model calculations are deleted, but the assumptions that fed into those calculations remain, despite no longer being needed.
The easiest way to see if any formulas are linking to an input cell is to use the trace dependents formula auditing tool. This tool will allow you to trace forwards and backwards throughout the model. One technique you can use to remove redundant cells is to colour code all the input assumption and calculation cells, then go to the model outputs and trace back the formulas. As each input assumption is validated, change the colour of the input cells back to their original colour. At the end of the process, if any cells are still coloured, you will know that they are redundant and can be deleted.
If you think a section of the model is not being used, check it using the trace dependents formula auditing tool as described in this chapter before deleting it. Be aware that there are a few tools and formulas—such as INDIRECT and OFFSET—that can sometimes rely on a cell but do not show up in the formula auditing tool. For this reason, make sure you save before deleting and check the output of the model for #REF! or other errors before continuing. If the deletion has caused a problem, you can simply undo or close without saving to revert to the version of the file prior to deletion.
One of the most effective ways to start understanding a financial model—especially one that you did not build yourself—is to look at the formulas and identify the precedents and dependents. This will give you an idea of the logic used by the previous builder of the model. Usually, the best place to start in this technique is the final output. Take a look at each output and trace back the predecessors until you eventually find out what calculations, source data, and assumptions have been used by the original designer.
If you have a formula and you want to know which cells it uses, an easy way to do this is to use formula auditing. You can access these features in Excel from the Formula Auditing ribbon under the Formulas tab, as shown in Figure 10.1.
Tracing Precedents To trace the precedents, select the cell you want to audit and click on Trace Precedents in the Formula Auditing ribbon. See Figure 10.1.
The trace precedents feature allows you to see all the other cells whose values are used in the formula in the present cell. It is possible to understand the calculation just by observing the cell formula too, but the arrows make it easier to locate the cells, which reduces the chances of errors in auditing the model. See Figure 10.2.
Tracing Dependents Just like precedents, you can also trace the dependents to a particular cell. These are all the cells that use the value in the current cell for the calculations.
Select the Trace Dependents button, which is located directly beneath the Trace Precedents in the Formula Auditing ribbon under the Formulas tab. This is particularly useful if you are considering deleting a cell and want to know what this will affect.
While arrows are great visual aids to understand formulas, sometimes they can become a distraction, especially if there are too many of them. You can clear all the arrows by clicking on the Remove Arrows button below the Trace Dependents button. This is also important if you plan to print the spreadsheet. If you don't remove the arrows before printing, they will show up in the printed document.
There are times when we unintentionally introduce errors in the financial model. This could be because we did not comprehend the logic completely or because we mistyped the formula. Excel provides an error-checking function that helps identify the problem. This is extremely useful when your incorrect formula is syntactically correct. Excel indicates the anomalies in the model with a green triangle in the top left corner of the cell.
To understand the nature of the error, select the cell and click on the drop-down menu. There are many categories of such errors, but inconsistent formulas is one of the most common errors, as is omitting adjacent cells, as shown in Figure 10.3.
You can change the kind of error checking you want to perform on your model by selecting Error Checking Options, as shown in Figure 10.4. This will display the screen where you can control the kinds of checks you want to be performed.
You can also access the Excel Options dialog box by selecting the File tab or Microsoft Office button, then Options or Excel Options. In Excel for Mac, access Excel Options by clicking Excel, then Preferences, then Error Checking.
You can review all the errors in the financial model using the error-checking feature in Excel, as shown in Figure 10.5.
Error checking offers two options:
Evaluating Formula Tool Sometimes it is difficult to decipher a complex nested formula just by looking at precedents and dependent relations. It is easier to actually evaluate it step by step to understand how it works. Excel facilitates this with the use of the Evaluate Formula feature.
To evaluate a formula in any cell, select the cell and click on Evaluate Formula in the Formula Auditing ribbon under the Formulas tab.
This will display an Evaluate Formula dialog that will show you the complete formula in the cell. Keep clicking on the Evaluate button to see how Excel calculates the value step by step. See Figure 10.7.
Once you have looked up the source of the parameter, you can click Step Out to continue the evaluation.
By then pressing F9, the formula will display the result of that expression, which in this case is 250. See Figure 10.8.
If you then hit the Enter key, it will paste that value into the formula (usually not what we want to do), but if you hit Escape, it will return to normal. This is a handy little trick that is sometimes easier and less complicated than using the Evaluate Formula tool. For greater detail on why you would want to break up a formula in this way, see “How Long Should a Formula Be?” in Chapter 4.
Viewing All the Formulas If you want to look at all the formulas and not the evaluated value, you can use the Show Formulas option in Excel. This is also a very effective tool to understand the general logic in the model and can sometimes make anomalies more evident.
This can also be done using the CTRL+ shortcut key. Note that is usually located below the Esc key at the top left of the keyboard, and should not be confused with the CTRL+ shortcut used to edit cells. See Figure 10.9.
Direct Editing By default, Excel allows users to edit formulas directly in a cell when they double-click that cell. However, while auditing any inherited spreadsheet, this may not be a good idea, as we could inadvertently mess up the formulas that are already in the cell. Excel allows users to disable direct editing, which protects the cells to a degree, but it still allows users to view the formula. It is quite different from protection, which does not let you do anything at all in the cell, and is much easier to turn off and on than protection.
Using this tool is also quite useful for auditing, because you can double-click on the cell to trace back, and ensure that you understand the assumptions made by the original modeller and take care that you are not making any inadvertent changes to the formulas in the cell. You can still make any changes necessary directly in the formula bar.
You can disable Direct Editing from the File menu. Click Options, then Advanced, and select “Allow editing directly in cells”. See Figure 10.10. In Excel for Mac, select Preferences from the Excel menu, and then Edit.
Once this box has been unselected, now when you double-click on the cell it will highlight all the cells used as parameters in the formula, but not let you edit the cell directly. Figure 10.11 shows what happens if you double-click on cell F4 when direct editing has been disabled.
Excel also has an often-overlooked tool called Inspect Workbook, which is useful for finding out more information about a model or hidden feature that you would not otherwise easily find—particularly one that has been inherited from someone else. It can help find potentially problematic features that would otherwise be very difficult to find, such as embedded documents, invisible content, and hidden rows and columns.
To use Inspect Workbook, open the file, then press File and on the Info tab, click on the Check for Issues button as shown in Figure 10.12. Inspect Workbook is not yet available in Excel for Mac.
The text under Inspect Workbook will tell you immediately the potential issues you should know about. Then, under the Check for Issues button, there are three options to choose from: Inspect Document, Check Accessibility, and Check Compatibility.
Note that whilst the Inspect Workbook feature is great for identifying problem-causing features of a model, it doesn't help with resolving them. It provides a summary of all the items the tool has found, and gives you the option to remove these immediately from the workbook—which is not a good idea. Instead of simply stripping a whole lot of features and the accompanying data from your model, you should take the time to understand what each of the features is, think about why they might have been included in the model, and whether they belong in the model or should be removed.
For example, the Inspect Document tool might reveal that there are hidden sheets in the model and it gives you the option to delete them immediately without looking at them. These hidden sheets might well contain important information and your formulas quite possibly will link to cells in those hidden sheets and so, accepting the recommendations of the document inspector will very likely cause more problems than it solves.
One of the problems that we often have to deal with in inherited files is that of slow, unwieldy, and inefficient models. If the model has not been built efficiently and calculates slowly, there are a number of checks you can perform to improve the model's performance. Most of the time, the performance problems are due to the size of the model. Let's explore some ways of reducing the file size, and hence speed up the model's performance.
By far the quickest and most effective way of reducing file size will be to check your file type. Although the .xlsx file type has been around since Excel 2007, models often still end up saved as .xls files. This can sometimes happen when data is extracted from other systems, it defaults to a .xls file type, and the modeller inadvertently uses this as a basis for his model. Because the .xlsx is a much more compact file type, this can make a huge difference to your file size.
If you have tried the tips suggested above and still have sluggish Excel models, it might not just be the file size that is slowing things down. Often, it's the memory being used and when things get really bad, you might even get a “Not enough memory” or “Not enough system resources to display completely” error message. When you get this frustrating message, the only solution is to close Excel and restart it, so let's look at some ways to improve the performance of your Excel models. Typically, you might consider upgrading your RAM or switching from a laptop to a desktop to counteract these problems, but these are unlikely to help as it's quite possibly the way that Excel is being used that is holding you back—not your machine.
Understanding Excel Memory Allocation If you get a memory error message, check the task manager for memory utilisation. If you have a RAM of more than 4GB you'll know that your computer has ample memory—although Excel is complaining about none being available. The reason for this is that Excel has its own memory manager and memory limits, regardless of the memory capacity of your machine. In fact, all 32-bit Excel is designed to use a maximum of 2GB memory. So, while your computer may have 4GB or even 8GB RAM, Excel can only use 2GB of that. If it reaches this limit, you get the error messages. This is why upgrading your computer's memory probably won't help! The 64-bit version of Excel has boosted this memory limit exponentially. Hence, 64-bit Excel versions can use the entire system memory, giving you much better performance, but read the section “32-Bit versus 64-Bit Microsoft Excel” in Chapter 1 before you jump straight in and upgrade.
Countering Memory Exhaustion Problems Now that you know why you have these limitations, we'll see how to minimise their occurrence. Check the section above on “Reducing File Size”, and here are some more tips to reduce the working memory used by your Excel file.
When you are building a model in Excel, there are a few ways of working that are not only good practice, they will also reduce the chances of encountering memory problems in your model.
Any model used to support the output required for decision-making purposes should be checked and tested at some level by an independent party (whether internal or external to the organisation) for both logic and accuracy before it is handed over to the end user. Reviewing, checking, and auditing a model are important parts of the model-building process. As discussed in the section on financial modelling best practice, checking should be done by the modeller during the model-building process. However, once the model is complete, or near completion, it should be reviewed by another party not involved in the model build.
How detailed the audit needs to be will depend on the importance of the financial model to the organisation using it. Whether it's a simple pricing model or a multi-million-dollar merger, at least some level of checking and testing appropriate to the model needs to take place in order to identify and eliminate spreadsheet error and to ensure that the calculations, assumptions, and logic within the model are correct. Below are listed a few different levels of testing and audit that may be appropriate for a model.
The type of audit that needs to take place will depend on the size, scope, and importance of the financial model you have been working on. Unless you work for a model audit firm, you are unlikely to be involved in an external model audit, so let's take a look at how to go about the internal checks you are most likely to be involved with.
Let's face it; the majority of financial models built will only get an informal check from colleagues. Depending on how much time they have available, they could spend anything from an hour to several weeks testing your model. Below are some techniques that can be used to test the integrity of the model on an informal basis.
Working on a QA procedure is the most labour-intensive type of audit most modellers are likely to be involved with. Below are some detailed instructions on how to go about a financial model QA procedure. The QA of a model involves two main tasks:
Step 1: Scope of the QA Work Before you begin, the scope of the QA needs to be established. The person undertaking the QA should clarify what parts of the model need a QA and, in particular, whether there are any parts that need special attention. For example, certain calculations may be crucial to the model and the model developer may want assurance that the logic of the formulas used is appropriate. In some cases, the model developer may seek feedback on possible improvements to the model, so as to enhance transparency, clarity, or some other aspect.
It is important to ensure that all supporting documentation is available to undertake the QA process. The model developer and/or the project team should provide all the documentation and source data in either hardcopy or softcopy.
Step 2: QA Checks of Calculations and Logic Logic checks are carried out in order to:
To check the logic of a model, quality assurers must first understand the output the model is required to produce and determine if the calculations correctly produce that output. Some of the tasks required in undertaking a logic check are therefore the same as those that would be done in the model-building process.
To check the calculations and logic, the following five steps should be followed:
Another way of testing this is to use historical figures as input and test whether the model can replicate actual results. For example, key outputs such as profit, growth rate, and other financial ratios that are calculated by the model can be compared to known historical results.
Yet another way is to check the sensitivity of outputs (dependent variables) to changes in inputs (independent variables). This involves checking that the direction and magnitude of the change are in accordance with how you logically expect the outputs to behave.
To assist with the logic and calculation checks, you may choose to write down the formulas in Excel notation using cell names and also in plain English by substituting the cell names in the formula with the actual variable names. For example:
Excel notation: J25=J6*J9
Plain English: Total variable cost = Variable cost per unit × Number of units
Logic: Number of units drives total variable cost
This is a simple and effective method to check and document the logic of the formula used by the model developer and pick up any cell-referencing errors. Sometimes a formula can be quite long, especially if Excel nested functions are used. Writing it out and breaking it down into its components makes it easier to thoroughly check each component (e.g., ensuring that they are referenced correctly, consistent in years, units, etc.). Another third-party tool called Dependency Auditor can help with this auditing by providing the Excel notation.
Step 3: QA Checks of the Model's Structural Layout and Documentation Apart from its underlying calculations and logic, each model's structural layout, appearance, and documentation should be checked.
Checking the structural layout of each sheet and workbook involves reviewing these to make sure that they are simple, transparent, and easily navigable. Elements to look out for are:
Checking the model's appearance and documentation involves checking the spelling and formatting, as well as that the documentation and notations in the model are clear, easily understood, and accurately reflect what is in the model.
Step 4: Document Your QA Work The QA procedure needs to be documented. The quality assurer should note all issues (minor and major), bring these to the attention of the model developer, and record the actions taken to remedy any problems.
At the start of the process, quality assurers should make a copy of the model to be audited and insert a new worksheet called QA Log (see the template in the Appendix at the end of this chapter).
All issues should be documented in the QA Log sheet. The quality assurer may also wish to insert comments near the actual cells in the model where issues have been found, as this can be useful for the model developer when locating and fixing the problems identified.
Step 5: Report Your Findings Once the QA work has been completed, any issues should be discussed with the model developer. Alternatively, issues can be progressively discussed and addressed as they arise.
Normally, the model developer will be able to advise whether an identified issue must be corrected, or whether it is intentional and should be left as is. All decisions and reasons should be recorded in the QA Log. Agreed changes should be checked again to ensure that they have been made correctly. This should also be recorded in the log.
Some changes may be very minor. Common sense should be exercised as to which changes can be left to the model developer to change without further final checking.
While logic checks focus on the calculations, structure, and layout of the model, input checks focus on the data entered into a model that is already built. Checks of input will usually involve:
As with a logic check, documenting the QA work and reporting the findings is required when conducting a QA of data inputs.
QA of Model Inputs The aim of checking input data in a model that will be used to produce a report is to ensure that the model and its output are accurate. The following are things to look out for:
While reviewing, auditing, or taking apart other people's models is certainly not the most fun (or sexy) part of financial models, chances are you'll need to be involved in the process at some stage. Even if you are fortunate enough to be charged only with the model build, it's critical that a good financial modeller understand the model review and audit process, and build the model in such a way as to make the audit process easier for the reviewer.
The following is a sample QA Log template.
Name of Model_Name of Modeller_Name(s) of QA Staff [File Names and File Path Location] |
Purpose of model |
1. |
2. |
3. |
Have the following items been checked in the QA? |
Yes/No/NA – Are timing assumptions consistent throughout the model? |
Yes/No/NA – Are calculations consistent and clearly marked? |
Yes/No/NA – Have units been provided for all values and clearly marked? |
Yes/No/NA – Have adequate error checks been built into the model? |
Yes/No/NA – Other … |
Specific areas of the model that the model developer/project team want assurance on: |
QA staff notes: |
Details of issues found in the QA that need to be discussed with the model developer/project team:
Item Number | Date Issue Identified | File Tab/Sheet | Cell Reference | Issue Description | Responses by Team | Date Issue | ||
Comments | Model Developer | Action Taken | Resolved | |||||
1 | ||||||||
2 | ||||||||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 |
3.137.183.10