Spreadsheet Havoc

by Ben Scheltus, Infonomics Business Development Manager.

Spreadsheets are endemic today.  Used by all levels of management, they are employed for the most banal tasks; for data entry into transactional business systems; and for supporting critical decisions, such as helping your stockbroker decide whether you should buy or sell an equity.  They started life as a really useful personal productivity tool (and that they still are) – unfortunately over time they have been extended well beyond their sensible limits to create some quite inappropriate applications.

The reality is that in the corporate environment:

• People develop spreadsheets which are seldom checked by anyone else for integrity or quality;

• People inherit spreadsheets from predecessors and colleagues and assume that they are correct;

• Some people (especially stockbrokers and advisers) see "their" spreadsheets as their competitive advantage - ensuring that they are never sighted for QA by third parties;

• People almost never do any sort of validation checks when entering data;

• Available statistics indicate that the incidence of badly written spreadsheets is most disconcerting (an article in the May 24, 2004 issue of Computer World indicated that anecdotal evidence suggests that 20% to 40% of spreadsheets have errors, but recent audits of 54 spreadsheets found that 49 (or 91%) had errors according to a research by Raymond R.  Panko, a Professor at the University of Hawaii.");

• It is almost impossible to work productively if spreadsheets become large or consolidation of results is required - eg.  Large budgeting systems just do not work efficiently with Excel;

• It is very difficult to enforce version controls and properly document assumptions;

• If a spreadsheet is shared across an organisation, there is no simple method to ensure that definitions for variables such as "sales" are consistent across the enterprise.

AS8015 Principle 4 states: Ensure that ICT performs well, whenever required

Unfortunately, many spreadsheet systems being used by organisations today have grown into a position of respectability and too often are taken as being infallible.  Consumers of information emanating from spreadsheets should ask some hard questions to the suppliers of the information:

• Who is responsible for the quality of logic and data of this spreadsheet?

• When was it last checked?

• What checks and balances are in place to ensure it stays correct?

A more recent and perhaps more disconcerting development is that many organisations are now also beginning to use PC based simplified programming tools such as Visual Basic and Access databases.  The end result is too often software that has been elegantly crafted by people who have no understanding of the technical characteristics and limitations of the tools.  Unfortunately, such software often fails at the most inconvenient times, as a direct consequence of the development process having lacked adequate rigour.

Our advice is that if you are making critical business decisions based on the results from spreadsheets or small, user built applications,  make sure that the logic and data used is appropriately checked in a professional and auditable manner.

Useful resources to guide those concerned about the appropriate use of spreadsheets can be found at: http://www.eusprig.org/stories.htm.  Another view can be found in CFO Magazine at: http://www.cfo.com/article.cfm/3014023/1/c_0?f=related.  In part the CFO article says: "What are the root causes of these problems? Respondents indicated that human factors such as collaboration among planning participants and uneven technical proficiency were primary causes.  "Over dependence on key personnel" was cited by nearly 50 percent of respondents, "version control" by more than 35 percent, and "collaboration, consolidation of users' work" by nearly 35 percent of respondents.  These issues add time to the planning, budgeting, and forecasting process, thereby reducing the amount of time left to actually analyse data on operational performance."