Spreadsheet Check
and Control
47 key practices to detect
and prevent errors
Author: Patrick O’Beirne
Published by Systems Publishing, County Wexford, Ireland 2005
198 pages
ISBN: 1-905404-00-X
~US$26 from Amazon
Summary
Spreadsheet Check and Control addresses a woefully underrepresented aspect of information security, namely integrity. Whether in the hands of ordinary untrained computer users or professional software
developers, spreadsheets are deceptively easy to program and all too easy to program incorrectly. The author provides a compelling and clearly-written set of tips and techniques for finding and resolving
programming errors that, unchecked, could cause serious consequences. This book would make a good teaching aide to accompany a training course on spreadsheet programming. It should be required reading
for anyone developing spreadsheets in a corporate environment, especially those which contribute to the financial reports and operational information on which the business depends.
About the book’s author
Patrick O’Beirne BSc, MA, FICS is a consultant specializing in spreadsheet quality training and reviews. As chairman of the European Spreadsheet Risks Interest Group (www.EUsprig.org), Patrick is well aware of the risks arising from mistakes and frauds involving spreadsheets. EUsprig itself was formed in 2000 as a
European discussion/conference forum for a team of experts working on spreadsheet accuracy, arising from Professor Ray Panko’s pioneering research in Hawaii.
Intended audience and scope
The intended audience includes all spreadsheet users plus software testers and “managers of end user
computing” (surely an endangered species!). The content may be somewhat challenging in places for the
most naïve of users but the book contains important lessons for all. It would be a suitable text to accompany training courses on spreadsheet programming and auditing, and is fine for self-study by those
working in Finance and managers in general.
The scope and structure of the book broadly follow the conventional systems development lifecycle from
specification and design to post-implementation review. The ‘47 key practices’ noted in the subtitle are
collected under headings such as fundamentals (3 key practices), error identification (4 practices) and error
correction (11 practices), all within chapter 3 on calculation. Previous chapters cover specification and inputs, the remainder cover outputs and review.
The section on charting errors covers the basics but could perhaps have been expanded a little. Personally, I
would have appreciated more advice on selecting and correctly using appropriate mathematical and financial formulae but that is presumably too advanced for the intended audience.
Content
After a brief introduction, the bulk of the book steps through a series of 47 common spreadsheet problems, consistently addressing each one. The reader is given just enough explanatory information to understand
the issue at hand, followed by advice on how to avoid or fix the problem. There are numerous examples throughout the text, illustrated with Microsoft Excel screenshots (the reader can download the same
example spreadsheets from the author’s website to try out the techniques described). There are review
questions at the end of each of the key practices with answers provided at the end of the book, and a good selection of annotated hyperlinks to further information sources.
Where applicable, the text notes differences between the current and previous versions of Excel. Improvements in the way Excel 2002/2003 reports certain errors are one good reason to upgrade.
Many of the practices are accompanied by relevant case studies – real world security incidents drawn from sources such as the RISKS list managed by Peter Neumann and a number of public audit reports which
identified spreadsheet errors. It focuses the mind to realize that a simple spreadsheet mistake can lead to errors amounting to millions of dollars.
It all seems very straightforward and indeed it is with simple spreadsheets. The book barely addresses the
complexities of developing or reviewing very large or linked spreadsheets where it can be impracticable to apply the same techniques systematically. The underlying principles are sound but the approach does not
scale easily, with consequent risk that the review process lacks integrity. Automated software tools for auditing spreadsheets are unfortunately not within the scope of this book.
It is implied throughout the book that spreadsheet developers should progress stepwise through the development process, a subtle piece of advice that might perhaps have been emphasized. References to
preparing designs and test scripts, and documenting any assumptions and development tricks, for example, will hopefully persuade readers to adopt software engineering methods that most amateur developers (and
even some seasoned software professionals) tend not to appreciate. The very idea that spreadsheets are application programs is probably alien to a large part of the intended audience.
Style
This is definitely a practitioner’s book, not a theoretical or academic tome. With minimal preamble, each
section gets quickly down to the nitty-gritty tools and techniques of finding and eliminating errors. The
writing style is clear and easy to read. The inclusion of plenty of screenshots is most welcome. It helps but is not essential to have a PC to hand to run through the exercises as one reads the book.
Value for money
The book helps ordinary spreadsheet users identify and fix the mistakes we often make when designing, developing and using spreadsheets (scientific research shows that an amazing 40 to 50% of end-user
-written programs typically contain non-trivial mistakes). More than that, it encourages us to be more
careful about the way we go about our task by using the built-in validation tools that make our job easier. It
should be easy to cost-justify the price of the book given the time it will save in finding and correcting
mistakes. Avoiding multi-million-dollar errors, court appearances and bad publicity is the icing on the cake.
Conclusion
This book may not be a conventional information security text but could be considered essential reading for
many amateur and professional spreadsheet users alike. As the case studies throughout the text so ably demonstrate, even simple spreadsheet errors (integrity failures, remember) can prove enormously
expensive and embarrassing. Save red faces all round by buying, absorbing and passing-on this book, especially if you personally develop spreadsheets or if your organization is subject to Sarbanes Oxley and
related regulations. Avoiding even a trivial spreadsheet mistake may well pay for the book. Avoiding a large one may save your career.
|
