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.

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.

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.

• Plan your Excel solution designs properly. If you insert a new column in the spreadsheet, it forces Excel to recalculate all the formulas affected by that insertion. Depending on the number of impacted formulas and their complexity, you could run out of memory. A better-planned design ensures you can avoid such changes.
• Work from left to right. Excel starts calculating from the top left-hand corner, then continues across and down the sheet. This means that input values should be to the left and above the formulas that are referencing them.
• Avoid creating formulas referencing the entire columns or rows. Some Excel users create a formula using $D or $AA to reference the entire column, to simplify calculations. While it was fine with older Excel versions, the new versions have over a million rows per column—so entire row or column references cause Excel to calculate the formula a million times, and work much harder than it needs to.
• Keep things neat. Try to keep things in one single workbook, as external links can slow things down, as well as cause errors. Keeping input cells and their formulas on one page will also reduce memory, but from a financial modelling best practice perspective it's a good idea to split inputs and assumptions from their calculations.
• Check your used range. Sometimes Excel “remembers” a used range, even though you have deleted the reference, and this can increase the file size and memory usage. To check this on each sheet, press Control+End. This will take you to the last used cell of the workbook, and if it's far beyond what you need, then delete the rows and columns that are not being used. This can greatly reduce the file size.
• Open every large Excel file on a separate “instance” (i.e., open up a new session of Excel for each workbook). The memory limitation in Excel is for every instance and not for the entire application. Opening every file in a separate instance gives you dedicated 2GB memory rather than shared. Ideally, you'd only have one file open at a time, but if you're running a large process, you might like to keep working in Excel on another file whilst waiting for the large file to finish its process.
• Use smart fill carefully. Besides memory restrictions, Excel also imposes a maximum source cell limit of 32,760 cells for smart filling. If you are planning to use the smart fill feature, you should look at just using the last two or three rows or columns and drag rather than select all the content from the first row. This will help control the number of source cells and ensure you do not reach this limit.
• Check for unwanted shapes. If you are adding pictures from websites into the spreadsheet, you could end up importing a lot of shapes that are not clearly visible but are eating into your memory. You need to open the selection panel (from the Home ribbon, Find & Select—Selection Panel) and remove all the shapes that you do not want.
• Check add-ins and ensure they are updated. Typically, the updated versions of third-party add-ins are more efficient and can help improve Excel performance greatly.

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:

• Check that cell referencing in a formula is consistent and correct.
• Ensure that formulas are mathematically correct.
• Confirm that the formulas used are suitable.
• Ensure consistency within the model in relation to the use of inputs, such as:
  • Nominal or real values.
  • Units (e.g., $'000,000 or $'000; kL or ML).
  • Timing assumptions, where applicable.
  • Labelling, which should be clear.

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:

1. Understand the purpose and limits of the model. This includes ascertaining what the model is supposed to do, what questions the model is supposed to answer, how the model tries to answer the questions, what the limitations of the model are, and what the model cannot do. This will most certainly require discussions with the project team and model developer.
2. Stress test the model as a whole by entering a wide range of inputs and checking that the outputs are logical. This includes testing values at the extremes to ensure the model processes the input as expected, especially where validation and protection are used. While testing a wide range of combinations of input variables may identify some hidden bugs in the calculations, using 0 or 1 is a good way to highlight obvious errors, as the outputs should be simple. For example, a unit price of zero should generate a revenue of zero, and if it does not, you'll need to investigate why not!

   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.

3. Establish which cells contain formulas. This can be done using audit software such as Spreadsheet Detective (an add-in available for purchase), which allows easy identification of which cells are fully hardcoded, which contain formulas, and whether formulas are consistent across rows and columns.
4. Check both the logic and the reference accuracy of each identified formula. This is usually done by tracing the outputs back to the inputs to ensure that calculations produce accurate and logical output. See the “Formula Auditing” section earlier in this chapter for details on how to use the Trace Precedents tool.

   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.

5. Check that the calculations have been suitably applied. For example, an indexation formula may be correct, but may not be suitable where it is applied if, for example, the underlying data is already inflated.

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:

• Logical sequencing of the components in the model.
• Whether calculations are spread out all over the model or if they follow neatly down and across in a logical fashion. (Note that in larger models, not everything will follow neatly!)
• Whether hardcoding is only on the input sheet.

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.

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:

• Check the inputs back to the source data for accuracy of data entry. This includes checking the actual numbers and also the units. For example, a common error is that the source data may be in $'000,000, while the model is set up in $'000.
• Check that the inputs used are consistent with those approved by the users of the report to be produced. Where possible, cross-reference the input data back to the minutes of meetings held to determine the required output.
• Check that the naming and explanations for the inputs are clear and easily understood.
• Check that the units are applied correctly and consistently throughout the model.
• Check for spelling and grammatical errors.