Spreadsheet Models for Managers


Getting Access to Spreadsheet Models for Managers


If Spreadsheet Models for Managersyou use Excel to model businesses, business processes, or business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze even you. Unrestricted use of this material is available in two ways.

As a stand-alone Web site
It resides on your computer, and you can use it anywhere. No need for Internet access.
At this Web site
If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.

To Order On Line

Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email.
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.
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.

To Order by Mail

Make your check payable to Chaco Canyon Consulting, for the amount indicated:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
And send it to:
Chaco Canyon Consulting
700 Huron Avenue, Suite 19C
Cambridge, MA 02138

To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.

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.

Contamination control engineers conduct the receiving inspection of a space telescope
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

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.

A software engineerAlthough 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?

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>…

Spreadsheet Models for ManagersSome 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.

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.

More Info

…<end snip>

A checklist for reviewing Excel spreadsheets

A checklistWhen 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.

Admin Administrative
Bal Balance
Cum Cumulative
Dep Depreciation
Eqpt Equipment
Exp Expense, Expensed, Expenses
Gen General
Mktg Marketing
Mgt Management
Mon Month, Months, Monthly
PW, PM, PQ, PY, PH per week, per month, per quarter, per year, per hour
Rev Revenue
Sal Salary, Salaries
Sched Schedule, Scheduled, Schedules

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.

You have two basic choices of inspection strategies. In the “Issue First” strategy, you inspect every worksheet, one by one, for a given checklist item, then move on to the next checklist item. In the “Worksheet First” strategy, you inspect a worksheet for every checklist item, one by one, and then move on to next worksheet. By far, the Issue First strategy is the easiest, most efficient, and most effective. The reason for this has nothing to do with the worksheet — it comes about because of the way our brains work. Focusing on a specific checklist item takes some brain effort. Hopping about from checklist item to checklist item is tiring and error prone. Once you’ve focused on a checklist item it’s relatively easy to move from worksheet to worksheet. Moreover, since Excel knows a lot about worksheets and workbooks, we can provide tools to make moving between worksheets, workbooks, and ranges, even easier.

As you inspect a workbook, you’re at risk of falling into a common trap that leads to massive confusion. Let’s say you’re inspecting for examples of violations of Checklist item 17. As you go through one of the worksheets, you notice a violation of Checklist item 20, which you haven’t yet worked on. Because you don't want to forget it, you record it in the appropriate place in the report workbook. Then you realize that there could be dozens of other examples of this violation, and you know of a quick way to find them using the Find commands. So you look for them, find them, and record them. While you're doing that, you find another violation of another checklist item, and so you do the same thing with that. Fifteen minutes later, you realize that you haven't finished Checklist item 17, and you forgot where you were on that one. How much of that worksheet had you inspected? Hmm. Can’t remember. So you have to re-inspect that worksheet, wasting time. Do not fall into this trap. When you notice a violation of a Checklist item other than the one you’re working on, make a note of it. But don’t drop everything to deal with it right then. Wait until you finish the item you’re working on. Someday, I’ll add a make-a-note tool to the report workbook. But for now, make notes in whatever way works for you.

In the real world, you probably wouldn’t rely on the inspection workbook alone. You might also use automation tools, some of your own design, to check workbooks for some of these issues even before the inspection begins. For instance, you might provide tools to look for #REF! errors, and require that spreadsheet authors apply these tools before submitting their work for inspection. Some of the more mechanical checklist items would then be out of the way before inspectors began their work. They would then be able to focus on the higher-level issues. Moreover, in many cases, these same automation tools could even repair the issues the find. Organizations that deploy such practices would find their spreadsheet practitioners producing more reliable work more rapidly than elsewhere in industry.

Reference

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

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

Inspect Your Project Early

Many believe that the main benefit of spreadsheet inspections is that they locate issues so they can be fixed. Certainly they do accomplish that. But spreadsheet inspections, when performed early enough and often enough, can actually prevent problems. And preventing problems is certainly more valuable than locating them.

We hope that you’ll apply what you learn about spreadsheet inspections when you work on your projects. If you’re working in a team, review your project schedule and decide when would be advantageous times to insert an inspection or two. If you’re working alone, ask someone else who’s working alone if they would be willing to inspect your project in exchange for your inspecting theirs.

Since we don’t grade on a curve, helping someone else doesn’t hurt you. Inspection exchanges raise the quality of both projects — and both grades. Whatever you do, don’t wait until the end to do your inspections.