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 project 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's 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 development: 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. The review is conducted by a small group of no more than eight knowledgeable colleagues. Their task is to identify issues for the creator of the spreadsheet to address.
A good way to describe this process is in a question and answer format. Below are some frequently asked questions about reviews in general, and some additional specifics about reviews for spreadsheets.
What's a review? What's 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 takes 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 may cover only the formulas and constants, but not the formatting of a worksheet.
Why do reviews and inspections work?
Organizations that make a regular practice of reviewing and inspecting work products find these benefits:
- They uncover defects, omissions, and deviations from standards early in their projects. Early discovery reduces the cost and frequency of repair work.
- Work products adhere more closely to standards of style and design, which makes it easier for anyone in the organization to understand how they work and what they're intended to do.
- The people who participate in reviews and inspections learn about how the things they review are designed and intended to work, which makes them more valuable to the organization because they know more.
Who decides when to review or inspect a work product?
The answer to this one is unequivocal: the author. Not management, not the project leader — the author. It's important to leave this decision to the author to guarantee that "we review no product before its time" and to ensure that we don't waste the time of the reviewers.
What roles are needed to conduct a review or inspection?
The review team is led by a Moderator, who is responsible for recruiting the team, finding a meeting room, setting a time and date, and running the meeting itself. After the meeting, the Moderator works with the author to resolve any issues that are uncovered in the review.
What's the output of a review or inspection?
The output of a review or inspection is a list of Issues — items that the team considered to be worthy of note. They may be major issues or minor issues. When minor issues are repaired, no further review is required — they're considered to have been resolved.
Major issues are a bit trickier. If the review team thinks some issue or issues are important enough, it can require that the product re-enter review when the issues are resolved. Alternatively, the team can decide that further review is unnecessary, as long as the issues are resolved.
Thus, the output of a review or inspection is twofold:
- An Issues list, with each issue categorized as Major or Minor
- A recommendation as to the necessity of reconvening the review once the Issues are resolved.
Who sees that issues are resolved?
There are at least two possibilities:
- The Moderator can work with the author to make sure that all issues are addressed.
- You can designate an "issues bloodhound" [Freedman 82] to follow up on the author's resolution of the issues.
Whatever you do, make sure someone other than the author has this responsibility. Most important: the issue resolution monitor should not be affiliated with the team that produced the work product.
What's the role of management?
Management's support is critical to the success of any program of reviews or inspections. For example, management can assist in making review efforts enough of a priority to enable the members of the review team to set aside time to execute the review. That said, what about the question of active management participation in a specific review or inspection? Generally, management should not take any role in any specific review or inspection. Even mere presence in the room can be problematic.
Often, management has a conflict of interest relative to participation in a review or inspection. That conflict can arise from responsibility for personal performance reviews, from sponsorship of the product, or from affiliation with those who have such responsibilities.
Even if a manager feels personally capable of resolving these conflicts of interest, the other members of the review team may feel certain inhibitions in the presence of management. Such inhibitions inevitably degrade the quality of the review output. And since it's difficult to determine whether or not team members feel these inhibitions, the presence of management in a review creates an immeasurable risk.
Won't reviews just create more bureaucracy?
A reasonable question — bureaucracy is a thing to be avoided. So let's think about this carefully. What is really bad about bureaucracy?
- It adds cost without adding value.
- It slows us down without justification.
- It frustrates those of us who are just trying to do our jobs — to get real work done.
Whenever we add formality and controls to a process, we risk adding bureaucracy. But the risk is minimal for reviews of the kind we're describing. Control of reviews is left in the hands of those executing them. Without management involvement, review issues remain local to the review team. This makes it difficult for the review process to drag out, frustrating the author or the author's team. Even so, since the review team itself could be viewed as an element of bureaucracy, lets examine the "three negatives" of bureaucracy itemized above.
Does the review process add cost without adding value? I think not. The added value is clear — defects are identified early, when they can be fixed at much lower cost than otherwise. Does it slow things down without justification? It does indeed slow things down, but with justification — reduced defect rates. Does it frustrate us? At first, it will seem frustrating, especially to authors. But as we experience its benefits, we come to welcome the reviews, because they save us from the even more frustrating experience of having to correct a design after it's implemented.
As an example of this saved frustration, imagine this scenario. You're a general contractor building a home. You have a choice — you can bring in the city inspector to review the wiring either before you install the wallboard, or after. If the inspector finds a problem after the wallboard is installed, you have to rip out the wallboard to fix the wiring, then reinstall the wallboard. If the inspector finds the problem before you install the wallboard, then you just fix the problem. Now which way would you rather do it?
Those who say "Why bother with the inspector?" might want to consider what happens to your construction business if an undiscovered wiring problem remains and the house bursts into flame two weeks after you turn it over to a customer.
It might work for software, but aren't spreadsheets different?
Spreadsheets really are software. They are indeed a different kind of software, but they are software.
When a spreadsheet is reviewed or inspected, it's compared with a standard of quality. That standard is represented by a checklist of properties that the spreadsheet should have, or should not have. These properties can vary from organization to organization, or even within an organization depending on the work product. I've prepared a sample of desirable and undesirable properties, in the form of a checklist for spreadsheets.
- Freedman 82
- Freedman, Daniel P. and Gerald M. Weinberg. Handbook of Walkthroughs, Inspections, and Technical Reviews, 3rd Ed. Boston: Little, Brown, 1982. Order from Amazon.com
Would you like to know more about reviewing or inspecting spreadsheets in your organization? Could you benefit from some expertise in developing checklists specifically for your needs? Through consulting, workshops, or coaching, I can help your people learn to use reviews and inspections to reduce defects, reduce effort, and increase reliability of spreadsheet work products.Contact me to discuss your specific situation, by email at rbrenfPisQwhtshXzxJVvner@ChacUYLEiQVcXCoqJzufoCanyon.com or by telephone at (650) 787-6475, or toll-free at (866) 378-5470 in the continental US.
- Spreadsheet clinic: custom-focused on your current projects and problems.
- Spreadsheet coaching: learn only what you need to use right now.
- Spreadsheet techniques: learn advanced modeling techniques.
- Gathering Spreadsheet Data: learn how to manage enterprise data.
- Inspections: learn how to enhance reliability and reduce effort.
- A checklist: reduce maintenance costs and the incidence of errors.
- "Rick is a dynamic presenter who thinks on his feet to keep the material relevant to the
— Tina L. Lawson, Technical Project Manager, BankOne (now J.P. Morgan Chase)
- "Rick truly has his finger on the pulse of teams and their communication."
— Mark Middleton, Team Lead, SERS