Posted September 23, 2013
Should Excel Spreadsheets be Subject to Rigorous Peer Review?
In a word, yes! But why? And how?
At SmartBear, we specialize in software tools designed for developers and testers. So we were a bit surprised to see our enterprise-level peer code review tool, Collaborator, being adopted by professionals who have nothing to do with creating software. Turns out, they are using it solely for the purpose of reviewing Microsoft Excel docs.
After reflecting on this a bit, it actually makes a lot of sense. Why shouldn’t spreadsheets—which can often be as complex as software in design and implementation—be subject to the kinds of peer review and quality processes that other enterprise deployments are subject to? No technical leader would question peer code reviews for software at various phases before a project deployment. Critical Microsoft Excel spreadsheets should not be treated any differently!
Excel spreadsheets, from the simple to the massively complex, have become so embedded in business, government, and education practices that even undetected, seemingly minor errors can have impact. An Excel spreadsheet with logic, design, or formula errors can cause the same issues in an organization as defects in enterprise software deployments.
Just how bad could an Excel spreadsheet error be?
In April 2012, a faulty reference in a Utah state education planning spreadsheet caused a discrepancy in tracking student enrollment with an impact of $25 million. The error forced two top state finance officials to resign. In 2003, as Fannie Mae moved to a new accounting system, a spreadsheet error caused them to have a $1.3 billion discrepancy in earnings publicly reported. And in one of the most famous recent issues, the 2012 “London Whale” controversy, JP Morgan Chase miscalculated the amount of risk traders were actually taking and that error impacted losses by $3.1 billion or more.
Factors that contribute to Excel spreadsheet errors
Human error will happen regardless of experience and intellect of the spreadsheet author. There are many factors that contribute to human error percolating too far down the line and making it into to a production spreadsheet. Here are two major factors:
- We don’t ‘want’ to see errors: Some have a tendency to design and implement Excel models with our subconscious bias expecting, or at least hoping, to see certain kinds of results. (For example the better than expected risk profile or fewer full-time-enrolled students than expected in the examples above.) After all, at the conclusion of days or weeks of hard work, if the bottom line matches the hypothesis and team expectations, how many people would double and triple check their work? Or request a formal 3rd party peer review? Especially if no outside influence like management or industry rules or regulations specifically require spreadsheet peer review and signoff?
- Peer and expert review processes are nonexistent or not up to snuff: Unlike most modern software development, processes around spreadsheet development, change-management, review, validation, and signoff are often limited or non-existent. Even when informal peer review processes are in place, factors like complex numerical models with many dependencies and very large Excel projects should require formal reviews, reviewer roles, and tracking checklists and timelines.
Most software development teams, pharmaceutical firms, designers, and engineers, and many other professions have formal ways to hold peer reviews along the path to project release. Why not enable Excel spreadsheet authors to work the same way?
SmartBear Collaborator 8.2
SmartBear’s Collaborator Version 8.2 provides a way to add formal, iterative review and signoff processes to Excel spreadsheet development using tried-and-true group review techniques. It was originally designed to help computer programmers participate in formal code reviews and has already helped hundreds of thousands of programmers successfully hold peer reviews to detect and fix defects. This latest version of Collaborator extends the same formal peer review and signoff rigor to Excel spreadsheet authors.
With Collaborator 8.2, Excel spreadsheet designers can inject a highly controlled, auditable, formal peer and expert review methodology into existing corporate workflows. It extends a number of facilities to Excel spreadsheet review that have already been honed and proven in software development team-review processes.
Among other things, SmartBear Collaborator 8.2 allows:
- Multiple reviewers to see both data and formulas and make context sensitive, cell specific comments and queries and specific change suggestions
- Reviews can happen in real-time or asynchronously in one or more locations.
- Comments are attributed to specific individuals and date and time stamped. They can be read in the order they were made.
- You can set up a hierarchy of individuals and groups doing the reviewing.
- A single review copy exists to comment on, eliminating version control issues of the spreadsheet being reviewed.
- Re-work and re-review workflow
- Electronic signatures for audit and owner or manager responsibility and accountability
SmartBear Collaborator doesn’t interrupt the existing business workflow of your document management system—it just adds a formal way to create iterative and trackable peer review and approval processes. When signed off on, the author can then reflect the agreed on changes in an updated Excel spreadsheet and return it to the document management or file repository already deployed in your organization.
Add Microsoft Excel spreadsheet peer review to your workflow
Download and try SmartBear Collaborator 8.2 for a free trial to see how easy and flexible it can be to add Excel spreadsheet peer review processes to your organization’s workflow.
What errors have you made in your Excel spreadsheets in accounting, price modeling, statistical analysis, commission calculations, or any other task Excel is commonly used for on your team? Have the errors embarrassed you professionally? Or worse, have they caused a business issue? Feel free to share your experiences in our comments section.