Spreadsheet Models for Managers

This reading is especially relevant for Session 7Design Reviews and Spreadsheet Inspections

When we use spreadsheets to provide support for enterprise-scale decisions, especially financial ones, it’s essential to take care that the contents of the spreadsheets are indeed what we believe they are. Design reviews and spreadsheet inspections, as adapted from software engineering, provide a useful means of enhancing spreadsheet quality and reliability.

Six contamination control engineers conduct the receiving inspection of the James Webb Space Telescope’s Mid-Infrared Instrument. Engineers from the European Space Agency are wearing blue hoods, and engineers from NASA’s Goddard Space Flight Center are wearing the white hoods. As part of the standard receiving inspection, they are looking for the tiniest traces of dust or contamination which would have to be remedied because cleanliness is a priority for such a sensitive instrument; MIRI passed its inspection review. Photo by Chris Gunn, U.S. National Aeronautics and Space Administration.

Reviews and inspections are a means of applying the combined knowledge of a small group of people to check that the work of one or two of them is correct and adheres to all guidelines the group has adopted. A design review focuses on the large-scale structure of the work inspected; an inspection focuses on the detailed implementation of that design. The output of either one is a list of issues to be addressed by the authors of the work reviewed.

In this course, we focus more on spreadsheet inspection than on design review. In a design review, there is an examination of the algorithms, structure, and component relationships of the model. The focus of the discussion is at a rather high level. In a spreadsheet inspection, the focus is more on the level of detailed technique and mechanics.

• Reviews and inspections: how and why
• Excel commands that help with reviews and inspections
• A checklist for reviewing Excel spreadsheets
• Tools for reviews and inspections
• Reference

Reviews and inspections: how and why

It’s difficult to imagine a business today — even a small business — that’s run effectively without the use of spreadsheets. We use spreadsheets to track and report financial status, to forecast business performance, to model business processes — even to do engineering computations. Yet with all their uses, little is written about how to ensure that what is captured in a spreadsheet is correct, or that it is what we meant to capture.

To address the analogous problem in the software industry, software engineers conduct reviews and inspections, which are now recognized as an effective way to increase quality, enhance reliability, and manage costs. Much has been written about the advantages of reviews and inspections for software engineering: see the Web site of the Software Engineering Institute for a valuable overview.

Although reviews and inspections aren’t universally used, even in the software industry, organizations that do employ reviews and inspections are typically the leaders in quality and innovation.

A review or inspection of a spreadsheet entails a detailed examination of every one of its features — all constants, all macros, all styles and formatting, all formulas — everything. A small group of no more than eight knowledgeable colleagues conducts the review. Their task is to identify issues to be addressed by the creator of the work product.

Below are some FAQs (frequently asked questions) about reviews in general, and some additional specifics about reviews and inspections for spreadsheets.

• What is a review? What is an inspection?
• Why do reviews and inspections work?
• Who decides when to review or inspect a work product?
• What roles are needed to conduct a review or inspection?
• What is the output of a review or inspection?
• Who sees that issues are resolved?
• What is the role of management?
• Won’t reviews just create more bureaucracy?
• It might work for software, but aren’t spreadsheets different?

What is a review? What is an inspection?

A review of a work product is a structured discussion of its purpose, contents and presentation. It’s conducted by a small group of colleagues whose sole responsibility and focus is the completeness and correctness of the work product relative to a predefined set of standards.

An inspection of a work product is a review that’s a more detailed look at the elements of the work product. It may cover less territory, or fewer of the dimensions of the work product. For example, it might cover only the formulas and constants, but not the formatting of a worksheet.

<snip>…

Some of the page you’ve been reading has been suppressed from this sampler. To see the complete version of this article, why not order the full course? It’s available at this Web site or in downloadable format.

Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. Add this item to your order

Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. Add this item to your order

More Info

…<end snip>

A checklist for reviewing Excel spreadsheets

When you inspect a spreadsheet, you must examine lots of detail. It’s impossible to remember what to check. A checklist for reviewing worksheets includes all aspects that you want to make certain are right.

Naturally, it can vary from organization to organization, and even from project to project. This checklist is therefore just an example of what might be one organization’s list of checkable items, but it’s the one we’ll use for the homework on reviews and inspections.

Workbook issues

Workbook issues are global to the workbook, and do not relate to any specific worksheet that’s part of the workbook design.

WB-1. Version number

The workbook contains a sheet that gives the correct version number of the model or tool.

Workbooks that lack version numbers are likely to create confusion. For users or maintainers, there is no way to be certain that the documentation and the workbook are meant to go together. But what is worse is a workbook that has an incorrect version number. (This can happen when the author neglects to update the version number before releasing it to the world.) With an incorrect version number, users can be trapped into believing that they’re following the correct usage instructions, when actually they aren’t.

WB-2. No empty worksheets

There are no empty worksheets.

Empty worksheets might waste a small amount of the users’ time, when they activate them to see what’s there. Seeing nothing at first, they’ll scroll around a bit and eventually conclude that there’s nothing there. But the real waste of time isn’t user time — it’s reviewer, maintainer, documenter, and tester time. Reviewers have to look to make certain that what is there is in compliance with standards. Maintainers, testers, and documenters have to look or search whenever they make alterations. The time lost here is also small, but it’s time that is directly in the path of releasing the asset. It therefore affects all users, and it can even delay revenue or result in elevated probability of missing market windows. Eliminate all blank sheets.

WB-3. User-modifiable data segregated

All user-modifiable data is segregated. Ranges that contain user-modifiable data do not abut ranges that contain data or formulas that are not intended to be user-modifiable.

Segregating user-modifiable data makes it easy for users to find it, simplifies the documentation, and enables system designers and maintainers to easily exploit protection capabilities to prevent accidental modification of structures users should not modify.

Worksheet issues

These are issues that are specific to a worksheet. Some might apply to more than one worksheet, but if the repair can be done sheet by sheet, then they’re considered worksheet issues.

WS-1. Sheet names

The sheet’s name begins with an alphabetic character. It can contain numbers, letters, and the period (.) character. No other characters are allowed. Each word component of the name follows a pattern of initial upper case letter, followed by lower case letters or numbers. Sheet names are 10 characters long or fewer.

The restrictions listed here protect system developers from those annoying single-quotes (') that encase sheet names that violate the character restrictions above. This makes formulas easier to read and write, and reduces typographical error rates during development and maintenance. Keeping sheet names short limits the need to scroll the sheet tab area, making the workbook easier to use for all.

WS-2. Characters of user-defined names

All user-defined names begin with an alphabetic character. Names can contain numbers, letters, and the period (.) character. No other characters are allowed, except in the case of the names Print_Area and Print_Titles. All names are in mixed case.

The underscore is often used in user-defined names as a word separator. Although the underscore is a legal character for user-defined names, it is relatively wide, which makes names containing it wider than necessary. This results in long, difficult-to-read formulas, which, in turn, leads to errors. Using a period or a case shift takes much less space.

WS-3. User-defined names in camel case

For user-defined names, each word component of the name follows a pattern of initial upper case letter, followed by lower case letters or numbers.

Using camel case, LikeThisExample, produces readable names without wasting “width,” which keeps formulas a little shorter, making them more readable. The use of mixed case in user-defined names makes the names readable, and helps reduce errors due to typographical errors. See the reading on names.

WS-4. Names are not references

User-defined names are not identical to any cell reference in A1 reference style in Excel 2007+. Nor are they identical to the designation of any column in A1 reference style in Excel 2007+. This restriction applies to workbooks created in all versions of Excel.

The constraint that user-defined names cannot be identical to column designations or cell references in A1 reference style arises in large part from the transition from earlier versions of Excel to the OpenXML versions. In the OpenXML versions, the numbers of rows and columns expanded dramatically. Thus, although users of the earlier versions were able to define a name CPI2012, for example, that name is illegal in the OpenXML versions, because it is a cell reference. Thus, migrating workbooks (and work habits) from pre-OpenXML days to modern times requires considerable care. If we permit user-defined names that are identical to column designations or cell references, Excel’s behavior is unpredictable. For example, when it interprets a formula that contains CPI2012, what should it do? Should it use the user-defined name? Or should it use the cell reference? Clearly we must forbid such names. By the way, although Excel does prevent users from defining names that could be cell references, it permits names that could be column designations. In my view, this is a defect. Such names can cause trouble, though rarely.

WS-5. Names use prescribed abbreviations

Compound user-defined names use these abbreviations whenever possible:

Having standard abbreviations enterprise-wide reduces the chance for error, simplifies re-use, and makes assets easier to read and understand for people other than authors. Improved ease of understanding is important in inspections, and when an asset must be maintained or enhanced by someone other than the original author.

WS-6. Cross-sheet links use names

Links across sheets always use named ranges. There are no cross-sheet links using explicit cell references.

When linking across sheets, we introduce a source of error and increased maintenance costs when we use explicit cell references, because it’s difficult to know what the target of the link is just by visual inspection. Sometimes, we must resort to actually following the link, which is time-consuming and a possible source of error if we misremember the actual reference. In frustration, we sometimes resort to memory, which is an even more prolific source of error. By using a named range in cross-sheet links, we tell the maintainer more about what is being done and why.

WS-7. Row captions right justified

Row captions are right justified, unless they caption an empty row or a row of column headings. If all other cells in the row are empty, the row caption need not be justified. Row caption cells must be directly adjacent to the first cell of the data they caption. Row captions are in mixed case.

The practices regarding row captions are intended to make them more readable. Placing row captions directly adjacent to the data they caption is useful, because most name definition tools use the row caption as a proposed name for the data selected.

WS-8. Column headings centered

Column headings are centered, in mixed case.

Column headings are formatted so as to make them stand out and distinguish them from data.

WS-9. Numeric cells use prescribed alignment

Numeric data cells are right aligned or general aligned. Numeric caption, comment, or heading cells can have any alignment, except as required by other checklist items.

This formatting directive might vary with the intended application. It’s intended as an example, rather than a general recommendation.

WS-10. Monetary amounts use prescribed format

Currency amounts are formatted as #,##0. No decimal fractions are indicated except when the precision is required for meaning, as in the per piece cost of an item or product. The currency symbol is not permitted unless the workbook contains more than one kind of currency. We do this to avoid unnecessary visual clutter.

For assets that contain currency amounts that are all in the same currency, the currency symbol serves only to distinguish currency amounts from other numeric data. When that function isn’t needed, the currency symbol is just visual clutter and should be omitted.

WS-11. Percentages use prescribed format

Percentages are formatted as 0%. Decimal places are permitted, but only as justified by the value. For instance, 3.5% can be formatted as 3.5% but not 3.50%.

This formatting directive might vary with the intended application. It’s intended as an example, rather than a general recommendation.

WS-12. Font styles are simple

No underline or strikethrough font styles.

Elaborate font decorations make data and text difficult to read.

WS-13. Linked blank text displays as blank

Linked blank text displays as blank, not 0.

Occasionally, we replicate text by linking to it through a formula. When the formula links to a blank cell, the dependent cell displays a zero. Blank is probably more appropriate.

WS-14. Spell checked and corrected

Worksheets are spell-checked and corrected. Components of compound words are either abbreviated using conventional abbreviations, or spelled correctly. Correct spelling is important because misspellings will be missed when we use find-and-replace, if we want to make changes.

Spell checking (and correcting the errors) is important for the reason above, but it is also important when defining names. If we intend to use “Hdct” in a name (to abbreviate “Headcount” for example), but instead we use “Hcdt” by accident, then every time we type the name, we have to remember to misspell it. That additional difficulty takes time and energy and it can be a source of error in itself.

WS-15. No error values

No worksheet cells contain error values.

We want to be able to use error values as indicators of trouble. If error values occur only when something bad has happened, then we can search for error values to find difficulties.

WS-16. No numeric constants in formulas

There are no numeric constants in formulas, except in parameter cells, parameter arrays, or arguments of index or offset or similar situations. This is an attempt to reduce parameter sprinkling.

WS-17. No redundant parentheses

Redundant parentheses are parentheses that are not needed for any mathematical or syntactic reason. For example, in the portion of a cell formula following the equal sign, if the first character is a left parenthesis, and its matching parenthesis is the last character of the formula, the pair is redundant. Another example: if a matched pair of parentheses wraps a matched pair immediately within it, one of the two pairs is redundant. A third example: anywhere in a formula, a matched pair of parentheses surrounding an invocation of a worksheet function, as in (OFFSET(A1:B7,2,2)), is redundant.

WS-18. No redundant plus signs

Redundant plus signs include formations such as ++A1, +-A1. In the first case, one of the plus signs is redundant. In the second case, the plus sign is redundant. Another example is a formula in which the first character is a plus sign. In that case, the plus sign is redundant.

WS-19. Numeric constants are in named cells

All numeric constants are held in named cells, and are referred to by name, except for cells containing 0. This is part of our attempt to reduce parameter sprinkling.

WS-20. Total cells sum the correct ranges

All total cells sum the “correct” ranges.

WS-21. Array formulas are used when possible and non-degenerate

Array formulas are used where possible. The set of cells contained in the array, sharing the same formula, is correct. The set of those cells that should be in the array are considered to be a single occurrence. Array formulas must be non-degenerate; that is, the same formula entered as a non-array formula should, in general, yield a different result. For example, {=SUM(A1:A7)} entered into cell A8 is a degenerate array formula, because it always yields the same result as =SUM(A1:A7).

WS-22. Defined names exist

There are no user-defined names that no longer exist (#REF!).

WS-23. No circular reference errors

There are no circular reference errors.

Reporting tools for reviews and inspections

As a manager, you’ll probably want to begin a practice of reviewing and inspecting your spreadsheet models. If you try it, you’ll run into several problems. Changing the modeling process is the most significant difficulty, followed by the relatively simple task of tracking the data that the inspections produce.

The issue of organizational change arises whenever you change anything. It’s a serious issue, and it isn’t a topic for this course. We do have something to offer though, to address the problem. Your chances of success in introducing inspections in your organization are greatly enhanced if you provide the right tools to facilitate the inspections. One of those tools is something like the file report.xlsm.

This file provides each of the reader/reviewers with a convenient place to note all of the issues they find. It contains a worksheet for each worksheet in the workbook they’re inspecting, as well as a complete copy of the checklist.

How to inspect a workbook

The key to a successful inspection is a systematic approach. Two approaches are possible. In the first approach, you inspect every worksheet thoroughly, checking it against all items in the checklist. In the second, you inspect every worksheet for each item of the checklist, one checklist item at a time. It turns out to be much more efficient to concentrate on one item of the checklist at a time, examining every worksheet in turn.

Setting up the inspection workbook

To begin, fill out the Summary page of the inspection workbook. This page includes all workbook issues, and some information about the workbook you’re inspecting. You’ll need to fill in the name of that workbook, and your own name, in the indicated cells. Put a copy of the workbook you’re inspecting into the same directory that holds the inspection workbook. Then load that file into Excel, and inspect it for workbook issues.

Inspecting for workbook issues

When you find a workbook issue, indicate it by entering the name of the worksheet on which it occurs, either in the “first occurrence” column if it’s the first of that issue type, or otherwise in the “additional occurrences” column. If you have more than one entry in an “additional occurrences” cell, separate them by commas.

Inspecting for worksheet issues

This procedure is the same for each issue, but I’ll describe it here for just one issue. Decide which issue you’re inspecting for, and select the first worksheet’s inspection page in the inspection workbook. When you click the Go button, Excel activates the corresponding page in the workbook you’re inspecting. When you then press Command+M (Mac) or Ctrl+Tab (Windows), Excel takes you back to the inspection workbook. Using these two commands, you can hop back and forth between the two workbooks, provided that they are the only two workbooks that are visible in Excel. If other workbooks are present and visible, then Excel cycles through all visible workbooks when you press the keys.

Once you’ve found a range in which there is an issue, select it, and press Command+M (Mac) or Ctrl+Tab (Windows) to return to the inspection workbook. Ensure that the active cell is someplace in the row that corresponds to the issue you’re focusing on. Then click the button Insert Issue References to insert the locations into the “First Occurrence” and “Additional Occurrences” cells.

After you’ve made the entry, you can go back to the worksheet you’re inspecting with the Go button, and return, using Command+M (Mac) or Ctrl+Tab (Windows), to enter what you find. When you’re finished with that sheet, click Next to move onto the next sheet. Clicking Prev takes you back one sheet. You can also cycle through sheets using Command+{PAGE DOWN} and Command+{PAGE UP} (Mac) or Ctrl+{PAGE DOWN} and Ctrl+{PAGE DOWN} (Windows).

If you find no occurrences of the issue, enter “None Found” in the Comment column, so you’ll know later that you did inspect for that item. If you find occurrences that pertain to names, enter those names in the appropriate columns.

For some issues, there’s a handy shortcut for discovering which worksheets and which cells contain instances of the issue. For instance, to find instances of underlining, you can search for underlining using the format specification capabilities of the Find command. But you can take this much further. By selecting all the worksheets of the workbook to be inspected, you can search for instances of underlining (or whatever you’re looking for), and when you find one, abort the search, go back to the inspection workbook, and record the instance. Then resume the search. To select all the worksheets of the inspected workbook, activate the first worksheet, and then Shift+Click the sheet tab of the last worksheet.

Reference

Freedman 82

Freedman, Daniel P. and Gerald M. Weinberg. Handbook of Walkthroughs, Inspections, and Technical Reviews, third edition. Boston: Little, Brown, 1982.

Instructor Contact Information

Voice: (617) 491-6289
Toll-free: (866) 378-5470 in the continental US
Email: rbrenner@ChacoCanyon.com

Rick Brenner
Chaco Canyon Consulting
700 Huron Avenue, Suite 19C
Cambridge, MA 02138

Download vCard

Author Contact Information

Toll-free: (866) 378-5470 in the continental US

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT