The Detection of Human Spreadsheet Errors by Humans versus Inspection (Auditing) Software

Previous spreadsheet inspection experiments have had human subjects look for seeded errors in spreadsheets. In this study, subjects attempted to find errors in human-developed spreadsheets to avoid the potential artifacts created by error seeding. Human subject success rates were compared to the successful rates for error-flagging by spreadsheet static analysis tools (SSATs) applied to the same spreadsheets. The human error detection results were comparable to those of studies using error seeding. However, Excel Error Check and Spreadsheet Professional were almost useless for correctly flagging natural (human) errors in this study.Comment: 14 Pages, 4 Figure

Information and Data Quality in Spreadsheets

The quality of the data in spreadsheets is less discussed than the structural integrity of the formulas. Yet it is an area of great interest to the owners and users of the spreadsheet. This paper provides an overview of Information Quality (IQ) and Data Quality (DQ) with specific reference to how data is sourced, structured, and presented in spreadsheets.Comment: 15 Pages, 3 Tables, 1 Figur

Investigating Effects of Common Spreadsheet Design Practices on Correctness and Maintainability

Spreadsheets are software programs which are typically created by end-users and often used for business-critical tasks. Many studies indicate that errors in spreadsheets are very common. Thus, a number of vendors offer auditing tools which promise to detect errors by checking spreadsheets against so-called Best Practices such as "Don't put constants in fomulae". Unfortunately, it is largely unknown which Best Practices have which actual effects on which spreadsheet quality aspects in which settings. We have conducted a controlled experiment with 42 subjects to investigate the question whether observance of three commonly suggested Best Practices is correlated with desired positive effects regarding correctness and maintainability: "Do not put constants in formulae", "keep formula complexity low" and "refer to the left and above". The experiment was carried out in two phases which covered the creation of new and the modification of existing spreadsheets. It was evaluated using a novel construction kit for spreadsheet auditing tools called Spreadsheet Inspection Framework. The experiment produced a small sample of directly comparable spreadsheets which all try to solve the same task. Our analysis of the obtained spreadsheets indicates that the correctness of "bottom-line" results is not affected by the observance of the three Best Practices. However, initially correct spreadsheets with high observance of these Best Practices tend to be the ones whose later modifications yield the most correct results.Comment: 16 Pages, 5 Colour Figures; Proc. European Spreadsheet Risks Int. Grp. (EuSpRIG) 2012, ISBN: 978-0-9569258-6-

Spreadsheets and the Financial Collapse

We briefly review the well-known risks, weaknesses and limitations of spreadsheets and then introduce some more. We review and slightly extend our previous work on the importance and criticality of spreadsheets in the City of London, introducing the notions of ubiquity, centrality, legality and contagion. We identify the sector of the financial market that we believed in 2005 to be highly dependant on the use of spreadsheets and relate this to its recent catastrophic financial performance. We outline the role of spreadsheets in the collapse of the Jamaican banking system in the late 1990's and then review the UK financial regulator's knowledge of the risks of spreadsheets in the contemporary financial system. We summarise the available evidence and suggest that there is a link between the use of spreadsheets and the recent collapse of the global financial system. We provide governments and regulating authorities with some simple recommendations to reduce the risks of continued overdependence on unreliable spreadsheets. We conclude with three fundamental lessons from a century of human error research.Comment: 17 Page

How do Range Names Hinder Novice Spreadsheet Debugging Performance?

Although experts diverge on how best to improve spreadsheet quality, it is generally agreed that more time needs to be spent testing spreadsheets. Ideally, experienced and trained spreadsheet engineers would carry this out, but quite often this is neither practical nor possible. Many spreadsheets are a legacy, developed by staff that have since moved on, or indeed modified by many staff no longer employed by the organisation. When such spreadsheets fall into the hands of inexperienced, non-experts, any features that reduce error visibility may become a risk. Range names are one such feature, and this paper, building on previous research, investigates in a more structured and controlled manner the effect they have on the debugging performance of novice spreadsheet users.Comment: 14 Pages, 6 Table

The Future of Spreadsheets in the Big Data Era

The humble spreadsheet is the most widely used data storage, manipulation and modelling tool. Its ubiquity over the past 30 years has seen its successful application in every area of life. Surprisingly the spreadsheet has remained fundamentally unchanged over the past three decades. As spreadsheet technology enters its 4th decade a number of drivers of change are beginning to impact upon the spreadsheet. The rise of Big Data, increased end-user computing and mobile computing will undoubtedly increasingly shape the evolution and use of spreadsheet technology. To explore the future of spreadsheet technology a workshop was convened with the aim of "bringing together academia and industry to examine the future direction of spreadsheet technology and the consequences for users". This paper records the views of the participants on the reasons for the success of the spreadsheet, the trends driving change and the likely directions of change for the spreadsheet. We then set out key directions for further research in the evolution and use of spreadsheets. Finally we look at the implications of these trends for the end users who after all are the reason for the remarkable success of the spreadsheet.Comment: 13 Pages, 1 Tabl

The Importance and Criticality of Spreadsheets in the City of London

Spreadsheets have been with us in their present form for over a quarter of a century. We have become so used to them that we forget that we are using them at all. It may serve us well to stand back for a moment to review where, when and how we use spreadsheets in the financial markets and elsewhere in order to inform research that may guide their future development. In this article I bring together the experiences of a number of senior practitioners who have spent much of their careers working with large spreadsheets that have been and continue to be used to support major financial transactions and manage large institutions in the City of London. The author suggests that the City of London is presently exposed to significant reputational risk through the continued uncontrolled use of critical spreadsheets in the financial markets and elsewhere.Comment: 11 pages, with reference

Managing Critical Spreadsheets in a Compliant Environment

The use of uncontrolled financial spreadsheets can expose organizations to unacceptable business and compliance risks, including errors in the financial reporting process, spreadsheet misuse and fraud, or even significant operational errors. These risks have been well documented and thoroughly researched. With the advent of regulatory mandates such as SOX 404 and FDICIA in the U.S., and MiFID, Basel II and Combined Code in the UK and Europe, leading tax and audit firms are now recommending that organizations automate their internal controls over critical spreadsheets and other end-user computing applications, including Microsoft Access databases. At a minimum, auditors mandate version control, change control and access control for operational spreadsheets, with more advanced controls for critical financial spreadsheets. This paper summarises the key issues regarding the establishment and maintenance of control of Business Critical spreadsheets.Comment: 4 Page

Spreadsheet Hell

This management paper looks at the real world issues faced by practitioners managing spreadsheets through the production phase of their life cycle. It draws on the commercial experience of several developers working with large corporations, either as employees or consultants or contractors. It provides commercial examples of some of the practicalities involved with spreadsheet use around the enterprise.Comment: 6 page

Spreadsheets in Financial Departments: An Automated Analysis of 65,000 Spreadsheets using the Luminous Technology

Spreadsheet technology is a cornerstone of IT systems in most organisations. It is often the glue that binds more structured transaction-based systems together. Financial operations are a case in point where spreadsheets fill the gaps left by dedicated accounting systems, particularly covering reporting and business process operations. However, little is understood as to the nature of spreadsheet usage in organisations and the contents and structure of these spreadsheets as they relate to key business functions with few, if any, comprehensive analyses of spreadsheet repositories in real organisations. As such this paper represents an important attempt at profiling real and substantial spreadsheet repositories. Using the Luminous technology an analysis of 65,000 spreadsheets for the financial departments of both a government and a private commercial organisation was conducted. This provides an important insight into the nature and structure of these spreadsheets, the links between them, the existence and nature of macros and the level of repetitive processes performed through the spreadsheets. Furthermore it highlights the organisational dependence on spreadsheets and the range and number of spreadsheets dealt with by individuals on a daily basis. In so doing, this paper prompts important questions that can frame future research in the domain.Comment: 14 Pages, 6 Tables, 4 Colour Figures; Proc. European Spreadsheet Risks Int. Grp. (EuSpRIG) 2011 ISBN 978-0-9566256-9-