Inheriting a File: What to Check For
When you’re inheriting someone else’s model, the best-case scenarios is a model handover meeting where you can sit down with the person who built it and he can take you through all the various moving parts. Unfortunately, this kind of meeting is rarely practical. Usually, the original modeler is unavailable and you’re thrown in the deep end, having to figure out how the model works on your own. A good modeler will have built the model in such a way that it can speak for itself, without the need for additional explanation. It will flow logically and have good documen- tation, labels, and instructions that make it easy to navigate and figure out how to use. Any model you build in the future will contain these features, but the model you inherit may or may not include them.
In the absence of any training or handover documentation, when you first start using someone else’s model, there are a few things to look for when you first open up the file.
Meeting a model for the fi time
When you open a financial model someone else has given you for the first time, take some time to get acquainted with it. If you’re planning to make this model your own, and take responsibility for its outputs, you’re going to be spending
quite a bit of time together. Familiarize yourself with the layout and how it’s built. In particular, pay attention to the following:
» Formatting: Get used to the formatting, and decide whether you’re happy with the formats they’ve used, particularly the color scheme, and decide if you
want to change it. Are all formulas and hard-coded values formatted diff – ently? Does it match your company colors?
» Formulas: Take a look at the formulas. Are they consistent? Do they contain any hard coding? A good way to see all the formulas at once is to select Show Formulas on the Formula Auditing section on the Formulas tab on the Ribbon,
or just press Ctrl+`. For more information on formula auditing, see the “Using Audit Tools to Find and Correct Errors” section later in this chapter.
» Workbook calculations: Most of the time, calculations happen automatically, so that when you change something in the model, the formulas change as well. Sometimes, however, when a fi is very large, or a modeler likes to
control the changes manually, the calculation has been set to manual instead of automatic. I’ve spent many happy hours auditing a formula, wondering why the numbers aren’t changing, only to discover that there was nothing wrong with the formula at all — the automatic calculation was simply turned off
To check this setting, choose File ➪
Options. In the Options dialog box, select the Formulas section and check that the calculation options at the top of the dialog box have been set to automatic instead of manual. Alternatively, a quicker way to access these settings is from the Calculation section of the Formulas tab on the Ribbon. Select the Calculation Options button and you can switch between manual and automatic calculation from the drop-down options that appear. If you see “Calculate” in the status bar in the lower-left corner of the screen, you know that the workbook is set to manual calculation.
» Error checking: An easy way to see at a glance whether there are any Excel errors in a sheet is to press the Error Checking button in the Formula Auditing section of the Formulas tab of the Ribbon. This will look across the entire
sheet and alert you to an Excel error values that you should be aware of. See Chapter 13 for more information on what kind of errors you might encounter and how to handle them.
» Named ranges: Many models contain named ranges, which is not a problem, but they often harbor errors due to redundant names and external links.
Review the named ranges in the Name Manager, which is on the Formulas tab of the Ribbon, and delete any named ranges that contain errors or links to external fi or that are not being used. For more information about the use of named ranges in a fi model, see Chapter 6.
Figure 5-1 shows an example of a model showing multiple error values in the Name Manager.
FIGURE 5-1:
The Name Manager showing multiple named ranges containing error values.
Excel has a fantastic tool called Inspect Workbook that’s a great way of getting to know the hidden features of your model and identifying potentially problematic features that would otherwise be very difficult to find.
To use Inspect Workbook, press File. On the Info tab, click the Check for Issues
button, as shown in Figure 5-2.
FIGURE 5-2:
Accessing the
Inspect Workbook tool.
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.
Inspect Document
This feature is by far the most useful when it comes to checking inherited files. It checks for the sorts of things that can cause problems for you if you’re planning to use this file in the future. The great thing about it is that it checks all these items in one go — you don’t have to go in and check each item one at a time.
When you select Inspect Document, you’re prompted to save the file. Click yes, and the Document Inspector dialog box (shown in Figure 5-3) appears. This dialog box lists all the things the Document Inspector can check for. Leave all the check boxes selected, and click Inspect.
FIGURE 5-3:
The Document
Inspector dialog box.
After you’ve clicked Inspect, any potential issues will be flagged, as shown in Figure 5-4. Scroll down the list and take a look at anything that has been flagged. You can decide whether you’d like to remove the features from the workbook here and now, or ignore them and come back to it later.
FIGURE 5-4:
Potential issues flagged in the Document Inspector.
The inspect workbook feature is great for identifying problem-causing features in your model. It’s not that helpful for resolving them. It provides a summary of all the items the tool has found, and it gives you the option to remove these immedi- ately from the workbook. This is not a good idea. Instead of simply stripping a whole lot of features (and accompanying data) from your model, take the time to understand what each of the features is, think about why the modeler might have included them in the model, and whether you need them going forward.
For example, the tool may discover that there are hidden sheets in the file. If so, it gives you the option to delete those sheets immediately without looking at them. The sheets probably contain important information, and your formulas may link to cells in those hidden sheets, so accepting the recommendations of the Docu- ment Inspector will very likely cause more problems than it solves.
If you do remove features through the Document Inspector, there is no Undo
option. Make sure you save a copy of the file first, just in case!
Another use of the Document Inspector is to remove additional information before passing on a file to someone else — sort of like selling your old cellphone on eBay and making sure all personal information is removed first.
Table 5-1 lists the potentially problematic features the Document Inspector checks for. If the Document Inspector finds any of these features, refer to this table to find out why you need to be concerned about each item and what to do about them.
TABLE 5-1 Inspect Document Features
|
Feature |
Why It Matters |
What to Do about It |
|
Comments and annotations |
Quickly fi any comments a previous author has included. This might include any instructions or documentation about model inputs or assumptions on how it has been calculated that you need to know. |
Read any instructions another modeler may have left for you. |
|
Document properties and personal information |
Every fi contains “metadata” such as the name of the author, last saved data, and fi path and so on, which is all useful information, especially when you don’t know where the model has been. |
See who built the model originally, see when it was last saved, and make sure you’re using the latest version. |
|
Data model |
If you’ve used the data model (which is unlikely unless you’re using Power Pivot), it will check for embedded data not visible on the sheets. |
Take note if these exist. |
|
Content add-ins |
Unless you know specifi what the add-in is doing, you should remove it. |
The easiest way to remove it is to allow the Document Inspector to remove it. |
|
Task pane add-ins |
Unless you know specifi what the add-in is doing, you should remove it. |
The easiest way to remove it is to allow the Document Inspector to remove it. |
|
PivotTables, PivotCharts, cube formulas, slicers, and timelines |
You might not realize that the model contains PivotTables and related features. This is important because PivotTables don’t refresh automatically the way formulas do. |
Be sure to refresh any PivotTables, make sure they’re still accurate, and ensure that any slicers or timelines still work properly. Remove them if no longer needed. |
|
Embedded documents |
Someone may have embedded fi within the document. It’s an unusual practice, but if they’re there, you probably want to know about it. |
Open the embedded document to see if it contains information you need. |
|
Macros, forms, and ActiveX controls |
These contain executable code, and if they’re contained in the model, you’ll want to know about it. I don’t advocate the use of these tools unless absolutely necessary. Keeping models as simple as possible is important, and these tools are quite diffi for the casual modeler to use. |
Find out what these tools are doing in the model, and remove them if no longer necessary. The code might be critical for model functionality so you should get an expert who knows VBA (the macro coding language) to take a look before you remove it. Saving the fi in XLSX format instead of XLSM will remove any executable code. |
|
Feature |
Why It Matters |
What to Do about It |
|
Links to other fi |
These are dangerous because if the source fi changes, the data could change in your model without your realizing it. |
To see if links exist in your model, select Edit Links from the Connections section of the Data tab in the Ribbon. The Edit Links dialog box will display a list of all external links in the fi If links exist, check to see if they’re accurate and still necessary. If not, remove them by selecting the link in the Edit Links dialog box, and pressing Break Link. This will quickly convert all cells containing external links to their hard- coded values throughout the model. |
|
Real Time Data (RTD) functions |
The RTD functions will refresh data automatically from real-time data servers into Excel, such as stock prices. |
Locate them by searching for RTD within the sheets’ formulas. Review the accuracy of the formula and delete if no longer necessary. |
|
Excel surveys |
Someone may have created an online survey that feeds into your model. The questions may be entered in Excel Online, but not visible in the workbook. If a survey is there, you’ll want to know that that’s where your data is coming from. |
Test the form and make sure it’s still working. Remove the survey functionality if it’s no longer needed. The easiest way to remove this is to allow the Document Inspector to remove it. |
|
Defi scenarios |
Scenario Manager may have been used on this model, and all the scenario information may be stored in the scenario and may not be visible on the sheet. |
Select Scenario Manager from the What-if Analysis button on the Forecast section of the Data tab. You can view the scenarios separately, or click Summary to see a summary of all the inputs and outputs that have been saved in the Scenario Manager. If scenarios are needed, consider using an alternative method of scenario analysis (see Chapter 8 for more information on scenarios). |
|
Active fi |
If fi have been activated, some data is not visible on the sheet. This can cause problems when using formulas or copying and pasting. For example, you might add up a list of items, not realizing that a fi has been applied and that you aren’t looking at the entire list. |
The fi won’t cause you any problems unless they’re applied, so there is no need to remove them entirely. Clear the fi by selecting Clear from the Sort & Filter section on the Data tab. This way you can apply the fi in future if needed. |
|
Custom worksheet properties |
Someone may have customized the worksheet properties. Unless you know specifi why he’s done it, you should remove it. |
The easiest way to remove custom worksheet properties is to allow the Document Inspector to remove it. |
(continued)
TABLE 5-1 (continued)
|
Feature |
Why It Matters |
What to Do about It |
|
Hidden names |
If hidden names exist in your model, they’re unlikely to be of much use to you. Unless you know specifi why someone has used them, you should remove them. |
You can’t manually remove hidden names without using VBA, so the easiest way to remove them is to allow the Document Inspector to remove them. |
|
Custom XML data |
Unless you know specifi why this is in your model, you should remove it. |
The easiest way to remove this is to allow the Document Inspector to remove it. |
|
Headers and footers |
Headers and footers are easily missed because they don’t show up in the soft copy unless you print or print preview. |
Choose File ➪ |
|
Hidden rows and columns |
Hidden rows and columns are very common and are the cause of one of the most common errors in fi modeling. Not realizing that hidden rows are in the model and might be inadvertently included or not included in sum totals is extremely dangerous. You defi want to know if hidden rows or columns are contained in the model. |
Highlight the row or column before and after the hidden one, right-click, and choose Unhide to unhide them. It’s okay to hide rows and columns to tidy up the model and not show everything you don’t need to see, but it’s better practice to use grouping instead. To apply grouping, select the entire row or column you want to hide, and select Group from the Outline section of the Data tab. |
|
Hidden worksheets |
Hidden worksheets are less dangerous than hidden rows or columns, but if you’re taking over a model, you’ll want to know exactly what’s on the hidden sheets. |
Right-click one of the sheet tabs at the bottom and choose Unhide. You’ll need to unhide each hidden sheet separately. |
|
Invisible content |
A common cause of invisible content is when people copy and paste data from a website and invisible objects come with it. Removing invisible content is highly unlikely to cause a problem. |
To fi objects, you can use Go To Special (Ctrl+G) and select objects, but the easiest way to remove it is to allow the Document Inspector to do so. |
Check Accessibility
This option checks to see whether people with disabilities might have trouble accessing any of the features of the file. For example, all visuals and tables should have alternative text to help people who can’t see the screen understand the image.
Check Compatibility
This option checks whether the file uses new tools available in this version of Excel that aren’t available in previous versions of Excel (see Chapter 2).
If you’re not planning to share the file with anyone using previous versions of Excel, you can leave any new features in, but if you aren’t sure what version of Excel your users may have, you may want to consider replacing them. For exam- ple, if you’ve created a waterfall chart using the template that first became avail- able in Excel 2016, you might want to replace it with a chart using the dummy stack or up/down bars method so that users of previous versions of Excel will not simply see a blank square where the chart should be. For instructions on how to do this, see the article at www.plumsolutions.com.au/waterfalls.
