Systems Modelling Ltd – Spreadsheet Review

Thursday, July 8th, 2010 by Fimodo   1 Comment

As part of Fimodo’s setup we welcome specialists in financial modelling and spreadsheets to contribute and share their knowledge and experience. This week Patrick O’Beirne from Systems Modelling Ltd in Wexford presents his views on Peer Reviews of Spreadsheet. Patrick is one of the driving forces behind EUSPRIG’s (European Speadsheet Risk Interest Group) success, and will be presenting at the conference with “Spreadsheet Refactoring”.

Peer review

Peer review is generally acknowledged by all professional disciplines (Wiegers, 2002) as being the most effective quality improvement action. Formalising a review process is the first step in instituting a change control process with sign-off.

In software development, peer review is a type of software review in which a work product (document, code, or other) is examined by its author and one or more colleagues, in order to evaluate its technical content and quality. Peer reviews are distinct from management reviews, which are conducted by management representatives rather than by colleagues, and for management and control purposes rather than for technical evaluation. They are also distinct from external audit reviews to evaluate compliance with standards.

The objective of a review is to find as many defects as possible in the review time allowed. Issues raised are documented for follow up and re-checking.

The level of review expected for each spreadsheet is proportional to its risk and will always be adjusted in the light of actual experience reviewing the spreadsheet. As reviewers work with the spreadsheet, they gradually learn which types of mistakes are more common, in this workbook, from these developers. Troublesome spreadsheets will attract more review and the cost-benefit may drive a rewrite using simpler and more testable structures than complex and error-prone ones.

There are three kinds of test that can be done: design, data test cases, and structural integrity.

With any normal large spreadsheet with many IF formulas, it is not possible to test every possible calculation path, every dependency, or everything that could go wrong. Use risk analysis, along with discussion with customers, to determine where testing should be focused. This requires judgement skills, common sense, and experience.

Considerations can include (adapted from the FAQ at softwareqatest.com):

  • Which functionality is most important to the model’s intended purpose?
  • Which functionality has the largest financial impact on users?
  • Which aspects of the application are most important to the customer?
  • Which aspects of the spreadsheet can be tested early in the development cycle?
  • Which parts of the model are most complex, and thus most subject to errors?
  • Which parts of the spreadsheet were developed in rush or panic mode?
  • Which aspects of similar/related previous projects caused problems?
  • What do the developers think are the highest-risk aspects of the spreadsheet?
  • What kinds of problems would cause the worst publicity?
  • What kinds of problems would cause the most end-customer service complaints?

What should the report contain?

A one-line statement that “this model conforms to requirements” without any evidence of what amount of checking was done is more likely to raise questions about the effectiveness of the reviewer than the conformance of the product under review. Jerry Weinberg has suggested that “it works” may mean “We haven’t tried very hard to make it fail, and we haven’t been running it very long or under very diverse conditions, but so far we haven’t seen any failures, though we haven’t been looking too closely, either.”

Completed peer review documentation would say what was checked, why, what the result was, and what needs to be done next. Keep it light, focused, and effective. There are many top-down process-heavy peer review standards for technical inspection including processes, measures, and control charts, which would drown a spreadsheet review. The key contents are:

  • what the scope and objective of review was (ie complete review or just one feature);
  • what checks were performed in order to validate the model within the given scope;
  • the results of the checks;
  • actions remaining to close any gaps;
  • Focus on “What are the top three issues here?” “What are the next three?” “What common threads unite these issues?” (Feierman)

The most common problem is to be vague about the standard of test being applied; say what you expected and what you found and produce the evidence that attests that the spreadsheet does or does not conform to the specific standard you applied. That is achieved when the reviewers themselves know what is expected of them, and what their responsibility is in signing-off.

For the first review, a thorough checkout should be performed using a spreadsheet auditing tool such as XLTest. The results can be recorded in a log file along with reviewer’s comments.

Thereafter, minor changes can be verified by comparing the reference workbooks and the log files before and after the change. This will discover unintended as well as intended changes.

Major changes, or simply the intuition of the reviewer, can suggest a thorough re-certification.

For further information on review and testing, contact Patrick O’Beirne at pob@sysmod.com.

References

About the Author

Patrick O’Beirne, 5 July 2010 Systems Modelling Ltd Villa Alba, Tara Hill Gorey, Co. Wexford +353-5394-22294

Recent posts by Fimodo

Comments for “Systems Modelling Ltd – Spreadsheet Review”

  1. David Harper says:
    Jul 9, 2010 at 6:23 am

    Good article, peer review of XLS should be done more often. This is interesting: “There are three kinds of test that can be done: design, data test cases, and structural integrity.” Thanks for references.

Comment on this Article