Search

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 Pages

[Spreadsheet] Models are invaluable tools for strategic planning. Models help key decision makers develop a shared conceptual understanding of complex decisions, identify sensitivity factors and test management scenarios. Different modelling approaches are specialist areas in themselves. Model development can be onerous, expensive, time consuming, and often bewildering. It is also an iterative process where the true magnitude of the effort, time and data required is often not fully understood until well into the process. This paper explores the traditional approaches to strategic planning modelling commonly used in organisations and considers the application of a real-options approach to match and benefit from the increasing uncertainty in today's rapidly changing world., Comment: 7 Pages

The Cell Error Rate in simple spreadsheets averages about 2% to 5%. This CER has been measured in domain free environments. This paper compares the CERs occurring in domain free and applied domain tasks. The applied domain task requires the application of simple linear algebra to a costing problem. The results show that domain referent knowledge influences participants' approaches to spreadsheet creation and spreadsheet usage. The conclusion is that spreadsheet error making is influenced by domain knowledge and domain perception. Qualitative findings also suggest that spreadsheet error making is a part of overall human behaviour, and ought to be analyzed against this wider canvas., Comment: 15 pages

Despite strong evidence of widespread errors, spreadsheet developers rarely subject their spreadsheets to post-development testing to reduce errors. This may be because spreadsheet developers are overconfident in the accuracy of their spreadsheets. This conjecture is plausible because overconfidence is present in a wide variety of human cognitive domains, even among experts. This paper describes two experiments in overconfidence in spreadsheet development. The first is a pilot study to determine the existence of overconfidence. The second tests a manipulation to reduce overconfidence and errors. The manipulation is modestly successful, indicating that overconfidence reduction is a promising avenue to pursue.

The Sarbanes-Oxley Act of 2002 has finally forced corporations to examine the validity of their spreadsheets. They are beginning to understand the spreadsheet error literature, including what it tells them about the need for comprehensive spreadsheet testing. However, controlling for fraud will require a completely new set of capabilities, and a great deal of new research will be needed to develop fraud control capabilities. This paper discusses the riskiness of spreadsheets, which can now be quantified to a considerable degree. It then discusses how to use control frameworks to reduce the dangers created by spreadsheets. It focuses especially on testing, which appears to be the most crucial element in spreadsheet controls., Comment: 45 pages, 7 figures

Human error research on overconfidence supports the benefits of early visibility of defects and disciplined development. If risk to the enterprise is to be reduced, individuals need to become aware of the reality of the quality of their work. Several cycles of inspection and defect removal are inevitable. Software Quality Management measurements of defect density and removal efficiency are applicable. Research of actual spreadsheet error rates shows data consistent with other software depending on the extent to which the work product was reviewed before inspection. The paper argues that the payback for an investment in early review time is justified by the saving in project delay and expensive errors in use. 'If debugging is the process of removing bugs, then programming must be the process of putting them in' - Anon., Comment: 7 pages, 1 table, 1 figure

This paper maintains that archiving has been overlooked as a key spreadsheet internal control. The case of failed Jamaican commercial banks demonstrates how poor archiving can lead to weaknesses in spreadsheet control that contribute to operational risk. In addition, the Sarbanes-0xley Act contains a number of provisions that require tighter control over the archiving of spreadsheets. To mitigate operational risks and achieve compliance with the records-related provisions of Sarbanes-Oxley, the author argues that organisations should introduce records management programmes that provide control over the archiving of spreadsheets. At a minimum, spreadsheet archiving controls should identify and ensure compliance with retention requirements, support document production in the event of regulatory inquiries or litigation, and prevent unauthorised destruction of records., Comment: 16 Pages. Recovered by OCR from paper conference proceedings

Contemporary spreadsheets are plagued by a profusion of errors, auditing difficulties, lack of uniform development methodologies, and barriers to easy comprehension of the underlying business models they represent. This paper presents a case that most of these difficulties stem from the fact that the standard spreadsheet user-interaction paradigm - the 'cell-matrix' approach - is appropriate for spreadsheet data presentation but has significant drawbacks with respect to spreadsheet creation, maintenance and comprehension when workbooks pass a minimal threshold of complexity. An alternative paradigm for the automated generation of spreadsheets directly from plain-language business model descriptions is presented along with its potential benefits. Sunsight Modeller (TM), a working software system implementing the suggested paradigm, is briefly described., Comment: 12 Pages, 2 Diagrams

In this paper we consider human factors and their impact on spreadsheet development in strategic decision-making. This paper brings forward research from many disciplines both directly related to spreadsheets and a broader spectrum from psychology to industrial processing. We investigate how human factors affect a simplified development cycle and what the potential consequences are., Comment: 11 pages, 3 figures, 2 tables

As the need for advanced, interactive mathematical models has increased, user/programmers are increasingly choosing the MatLab scripting language over spreadsheets. However, applications developed in these tools have high error risk, and no best practices exist. We recommend that advanced, highly mathematical applications incorporate these tools with spreadsheets into hybrid applications, where developers can apply EuSpRIG best practices. Development of hybrid applications can reduce the potential for errors, shorten development time, and enable higher level operations. We believe that hybrid applications are the future and over the course of this paper, we apply and extend spreadsheet best practices to reduce or prevent risks in hybrid Excel/MatLab applications., Comment: 11 pages, 8 figures

We developed an internet-based questionnaire on spreadsheet use that we administered to a large number of users in several companies and organizations to document how spreadsheets are currently being developed and used in business. In this paper, we discuss the results drawn from of a comparison of responses from individuals with the most experience and expertise with those from individuals with the least. These results describe two views of spreadsheet design and use in organizations, and reflect gaps between these two groups and between these groups and the entire population of nearly 1600 respondents. Moreover, our results indicate that these gaps have multiple dimensions: they reflect not only the context, skill, and practices of individual users but also the policies of large organizations., Comment: 16 Pages, 11 Tables

The research presented in this paper establishes a valid, and simplified, revision of previous spreadsheet error classifications. This investigation is concerned with the results of a web survey and two web-based gender and domain-knowledge free spreadsheet error identification exercises. The participants of the survey and exercises were a test group of professionals (all of whom regularly use spreadsheets) and a control group of students from the University of Greenwich (UK). The findings show that over 85% of users are also the spreadsheet's developer, supporting the revised spreadsheet error classification. The findings also show that spreadsheet error identification ability is directly affected both by spreadsheet experience and by error-type awareness. In particular, that spreadsheet error-type awareness significantly improves the user's ability to identify, the more surreptitious, qualitative error., Comment: 20 Pages, 14 Tables and Figures, many in colour

The hard coding of input data or constants into spreadsheet formulas is widely recognised as poor spreadsheet model design. However, the importance of avoiding such practice appears to be underestimated perhaps in light of the lack of quantitative error at the time of occurrence and the recognition that this design defect may never result in a bottom-line error. The paper examines both the academic and practitioner view of such hard coding design flaws. The practitioner or industry viewpoint is gained indirectly through a review of commercial spreadsheet auditing software. The development of an automated (electronic) means for detecting such hard coding is described together with a discussion of some results obtained through analysis of a number of student and practitioner spreadsheet models., Comment: 10 Pages, 5 Tables

This paper discusses spreadsheets documentation and new means to achieve this end by using Excel's built-in "Comment" function. By structuring comments, they can be used as an essential tool to fully explain spreadsheet. This will greatly facilitate spreadsheet change control, risk management and auditing. It will fill a crucial gap in corporate governance by adding essential information that can be managed in order to satisfy internal controls and accountability standards., Comment: 12 Pages, 15 screen shots

Most spreadsheet surveys both for reporting use and error focus on the practical application of the spreadsheet in a particular industry. Typically these studies will illustrate that a particular percentage of spreadsheets are used for optimisation and a further percentage are used for 'What if' analysis. Much less common is examining the classes of function, as defined by the vendor, used by modellers to build their spreadsheet models. This alternative analysis allows further insight into the programming nature of spreadsheets and may assist researchers in targeting particular structures in spreadsheet software for further investigation. Further, understanding the functional make-up of spreadsheets allows effective evaluation of novel approaches from a programming point of view. It allows greater insight into studies that report what spreadsheets are used for since it is explicit which functional structures are in use in spreadsheets. We conclude that a deeper understanding of the use of operators and the operator's relationship to error would provide fresh insight into the spreadsheet error problem. Considering functional spreadsheet operator usage suggests the value of Example Driven Modelling for Decision Support Systems, Comment: 12 Pages, 6 Figures, 3 Tables

Visualizing spreadsheet content provides analytic insight and visual validation of large amounts of spreadsheet data. Oculus Excel Visualizer is a point and click data visualization experiment which directly visualizes Excel data and re-uses the layout and formatting already present in the spreadsheet., Comment: 10 Pages, 11 Colour Figures

Spreadsheets often need changing in ways made tedious and risky by Excel. For example: simultaneously altering many tables' size, orientation, and position; inserting cross-tabulations; moving data between sheets; splitting and merging sheets. A safer, faster restructuring tool is, we claim, Excelsior. The result of a research project into reducing spreadsheet risk, Excelsior is the first ever tool for modularising spreadsheets; i.e. for building them from components which can be independently created, tested, debugged, and updated. It represents spreadsheets in a way that makes these components explicit, separates them from layout, and allows both components and layout to be changed without breaking dependent formulae. Here, we report experiments to test that this does indeed make such changes easier. In one, we automatically generated a cross-tabulation and added it to a spreadsheet. In the other, we generated new versions of a 10,000-cell housing-finance spreadsheet containing many interconnected 20*40 tables. We varied table sizes from 5*10 to 200*2,000; moved tables between sheets; and flipped table orientations. Each change generated a spreadsheet with different structure but identical outputs; each change took just a few minutes., Comment: 18 Pages, code examples

In this paper, we report some on-going focused research, but are further keen to set it in the context of a proposed bigger picture, as follows. There is a certain depressing pattern about the attitude of industry to spreadsheet error research and a certain pattern about conferences highlighting these issues. Is it not high time to move on from measuring spreadsheet errors to developing an armoury of disciplines and controls? In short, we propose the need to rigorously lay the foundations of a spreadsheet engineering discipline. Clearly, multiple research teams would be required to tackle such a big task. This suggests the need for both national and international collaborative research, since any given group can only address a small segment of the whole. There are already a small number of examples of such on-going international collaborative research. Having established the need for a directed research effort, the rest of the paper then attempts to act as an exemplar in demonstrating and applying this focus. With regard to one such of research, in a recent paper, Panko (2005) stated that: "...group development and testing appear to be promising areas to pursue". Of particular interest to us are some gaps in the published research record on techniques to reduce errors. We further report on the topics: techniques for cross-checking, time constraints effects, and some aspects of developer perception., Comment: 12 Pages, One Figure

This management summary provides an outline of a commercial spreadsheet review process. The aim of this process is to ensure remedial or enhancement work can safely be undertaken on a spreadsheet with a commercially acceptable level of risk of introducing new errors., Comment: 8 Pages, 9 Colour Diagrams

Much of what EuSpRIG discusses is concerned with the integrity of individual spreadsheets. In businesses, interlocking spreadsheets are regularly used to fill functional gaps in core administrative systems. The growth and deployment of such integrated spreadsheet SYSTEMS raises the scale of issues to a whole new level. The correct management of spreadsheet systems is necessary to ensure that the business achieves its goals of improved performance and good corporate governance, within the constraints of legislative compliance - poor management will deliver the opposite. This paper is an anatomy of the real-life issues of the commercial use of spreadsheets in business, and demonstrates how Qtier-Rapor has been used to instil best practice in the use of integrated commercial spreadsheet systems., Comment: 12 Pages, 6 Colour Figures

This paper extends a spreadsheet visualization technique by using layout information. The original approach identifies logically or semantically related cells by relying exclusively on the content of cells for identifying semantic classes. A disadvantage of semantic classes is that users have to supply parameters which describe the possible shapes of these blocks. The correct parametrization requires a certain degree of experience and is thus not suitable for untrained users. To avoid this constraint, the approach reported in this paper uses row/column-labels as well as common format information for locating areas with common, recurring semantics. Heuristics are provided to distinguish between cell groups with intended common semantics and cell groups related in an ad-hoc manner., Comment: 13 pages, 3 colour figures

Developments in Mobile Computing are putting pressure on the software industry to research new modes of interaction that do not rely on the traditional keyboard and mouse combination. Computer users suffering from Repetitive Strain Injury also seek an alternative to keyboard and mouse devices to reduce suffering in wrist and finger joints. Voice-control is an alternative approach to spreadsheet development and debugging that has been researched and used successfully in other domains. While voice-control technology for spreadsheets is available its effectiveness has not been investigated. This study is the first to compare the performance of a set of expert spreadsheet developers that debugged a spreadsheet using voice-control technology and another set that debugged the same spreadsheet using keyboard and mouse. The study showed that voice, despite its advantages, proved to be slower and less accurate. However, it also revealed ways in which the technology might be improved to redress this imbalance., Comment: 10 Pages, 4 Tables

Very little is known about the process by which end-user developers detect and correct spreadsheet errors. Any research pertaining to the development of spreadsheet testing methodologies or auditing tools would benefit from information on how end-users perform the debugging process in practice. Thirteen industry-based professionals and thirty-four accounting & finance students took part in a current ongoing experiment designed to record and analyse end-user behaviour in spreadsheet error detection and correction. Professionals significantly outperformed students in correcting certain error types. Time-based cell activity analysis showed that a strong correlation exists between the percentage of cells inspected and the number of errors corrected. The cell activity data was gathered through a purpose written VBA Excel plug-in that records the time and detail of all cell selection and cell change actions of individuals., Comment: 12 Pages, 3 Figures

Past research shows that spreadsheet models are prone to such a high frequency of errors and data security implications that the risk management of spreadsheet development and spreadsheet use is of great importance to both industry and academia. The underlying rationale for this paper is that spreadsheet training courses should specifically address risk management in the development process both from a generic and a domain-specific viewpoint. This research specifically focuses on one of these namely those generic issues of risk management that should be present in a training course that attempts to meet good-practice within industry. A pilot questionnaire was constructed showing a possible minimum set of risk management issues and sent to academics and industry practitioners for feedback. The findings from this pilot survey will be used to refine the questionnaire for sending to a larger body of possible respondents. It is expected these findings will form the basis of a risk management teaching approach to be trialled in a number of selected ongoing spreadsheet training courses., Comment: 12 Pages

The risks of spreadsheet use do not just come from the misuse of formulae. As such, training needs to go beyond this technical aspect of spreadsheet use and look at the spreadsheet in its full business context. While standard training is by and large unable to do this, task-based training is perfectly suited to a contextual approach to training., Comment: 6 Pages

Fifteen years of research studies have concluded unanimously that spreadsheet errors are both common and non-trivial. Now we must seek ways to reduce spreadsheet errors. Several approaches have been suggested, some of which are promising and others, while appealing because they are easy to do, are not likely to be effective. To date, only one technique, cell-by-cell code inspection, has been demonstrated to be effective. We need to conduct further research to determine the degree to which other techniques can reduce spreadsheet errors., Comment: 9 Pages, 2 Tables

It is widely documented that the absence of a structured approach to spreadsheet engineering is a key factor in the high level of spreadsheet errors. In this paper we propose and investigate the application of Test-Driven Development to the creation of spreadsheets. Test-Driven Development is an emerging development technique in software engineering that has been shown to result in better quality software code. It has also been shown that this code requires less testing and is easier to maintain. Through a pair of case studies we demonstrate that Test-Driven Development can be applied to the development of spreadsheets. We present the detail of these studies preceded by a clear explanation of the technique and its application to spreadsheet engineering. A supporting tool under development by the authors is also documented along with proposed research to determine the effectiveness of the methodology and the associated tool., Comment: 11 pages, 5 colour figures, 2 case studies

Spreadsheet programs, artifacts developed by non-programmers, are used for a variety of important tasks and decisions. Yet a significant proportion of them have severe quality problems. To address this issue, our previous work presented an interval-based testing methodology for spreadsheets. Interval-based testing rests on the observation that spreadsheets are mainly used for numerical computations. It also incorporates ideas from symbolic testing and interval analysis. This paper addresses the issue of efficiently debugging spreadsheets. Based on the interval-based testing methodology, this paper presents a technique for tracing faults in spreadsheet programs. The fault tracing technique proposed uses the dataflow information and cell marks to identify the most influential faulty cell(s) for a given formula cell containing a propagated fault., Comment: 13 Pages, 4 figues

Previous research on spreadsheet risks has predominantly focussed on errors inadvertently introduced by spreadsheet writers i.e. it focussed on the end-user aspects of spreadsheet development. When analyzing a faulty spreadsheet, one might not be able to determine whether a particular error (fault) has been made by mistake or with fraudulent intentions. However, the fences protecting against fraudulent errors have to be different from those shielding against inadvertent mistakes. Faults resulting from errors committed inadvertently can be prevented ab initio by tools that notify the spreadsheet writer about potential problems whereas faults that are introduced on purpose have to be discovered by auditors without the cooperation of their originators. Even worse, some spreadsheet writers will do their best to conceal fraudulent parts of their spreadsheets from auditors. In this paper we survey the available means for fraud protection by contrasting approaches suitable for spreadsheets with those known from fraud protection for conventional software., Comment: 16 Pages including extensive references

The spreadsheet paradigm has some unique risks and challenges that are not present in more traditional development technologies. Many of the recent advances in other branches of software development have bypassed spreadsheets and spreadsheet developers. This paper compares spreadsheets and spreadsheet development to more traditional platforms such as databases and procedural languages. It also considers the fundamental danger introduced in the transition from paper spreadsheets to electronic. Suggestions are made to manage the risks and work around the limitations., Comment: 9 pages including references, colour diagrams and comparison tables

Spreadsheets provide many of the key links between information systems, closing the gap between business needs and the capability of central systems. Recent regulations have brought these vulnerable parts of information supply chains into focus. The risk they present to the organisation depends on the role that they fulfil, with generic differences between their use as modeling tools and as operational applications. Four sections of the Sarbanes-Oxley Act (SOX) are particularly relevant to the use of spreadsheets. Compliance with each of these sections is dependent on maintaining the integrity of those spreadsheets acting as operational applications. This can be achieved manually but at high cost. There are a range of commercially available off-the-shelf solutions that can reduce this cost. These may be divided into those that assist in the debugging of logic and more recently the arrival of solutions that monitor the change and user activity taking place in business-critical spreadsheets. ClusterSeven provides one of these monitoring solutions, highlighting areas of operational risk whilst also establishing a database of information to deliver new business intelligence., Comment: 7 Pages including references and diagrams in colour

In the spreadsheet error community, both academics and practitioners generally have ignored the rich findings produced by a century of human error research. These findings can suggest ways to reduce errors; we can then test these suggestions empirically. In addition, research on human error seems to suggest that several common prescriptions and expectations for reducing errors are likely to be incorrect. Among the key conclusions from human error research are that thinking is bad, that spreadsheets are not the cause of spreadsheet errors, and that reducing errors is extremely difficult., Comment: 12 pages including references

There has been a significant amount of research into spreadsheets over the last two decades. Errors in spreadsheets are well documented. Once used mainly for simple functions such as logging, tracking and totalling information, spreadsheets with enhanced formulas are being used for complex calculative models. There are many software packages and tools which assist in detecting errors within spreadsheets. There has been very little evidence of investigation into the spreadsheet risks associated with the main stream operations within an organisation. This study is a part of the investigation into the means of mitigating risks associated with spreadsheet use within organisations. In this paper the authors present and analyse three proposed models for categorisation of spreadsheet use and the level of risks involved. The models are analysed in the light of current knowledge and the general risks associated with organisations., Comment: 10 pages

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 pages

This paper presents the arguments and supporting business metrics for Enterprise Spreadsheet Management to be seen as a necessary good. These arguments are divided into a summary of external business drivers that make it necessary and the good that may be delivered to business spreadsheet users involved in repetitive manual processes., Comment: 8 pages, includes references

The spreadsheet has been used by the business community for many years and yet still raises a number of significant concerns. As educators our concern is to try to develop the students skills in both the development of spreadsheets and in taking a critical view of their potential defects. In this paper we consider both the problems of mechanical production and the problems of translation of problem to spreadsheet representation., Comment: 9 pages incuding references

This paper presents the results of an empirical evaluation of the quality of a structured methodology for the development of spreadsheet models, proposed in numerous previous papers by Rajalingham K, Knight B and Chadwick D et al. This paper also describes an improved version of their methodology, supported by appropriate examples. The principal objective of a structured and disciplined methodology for the construction of spreadsheet models is to reduce the occurrence of user-generated errors in the models. The evaluation of the effectiveness of the methodology has been carried out based on a number of real-life experiments. The results of these experiments demonstrate the methodology's potential for improved integrity control and enhanced comprehensibility of spreadsheet models., Comment: 17 pages including references

Recent research has highlighted the high incidence of errors in spreadsheet models used in industry. In an attempt to reduce the incidence of such errors, a teaching approach has been devised which aids students to reduce their likelihood of making common errors during development. The approach comprises of spreadsheet checking methods based on the commonly accepted educational paradigms of peer assessment and self-assessment. However, these paradigms are here based upon practical techniques commonly used by the internal audit function such as peer audit and control and risk self-assessment. The result of this symbiosis between educational assessment and professional audit is a method that educates students in a set of structured, transferable skills for spreadsheet error-checking which are useful for increasing error-awareness in the classroom and for reducing business risk in the workplace., Comment: 9 Pages, includes references

All users of spreadsheets struggle with the problem of errors. Errors are thought to be prevalent in spreadsheets, and in some instances they have cost organizations millions of dollars. In a previous study of 50 operational spreadsheets we found errors in 0.8% to 1.8% of all formula cells, depending on how errors are defined. In the current study we estimate the quantitative impacts of errors in 25 operational spreadsheets from five different organizations. We find that many errors have no quantitative impact on the spreadsheet. Those that have an impact often affect unimportant portions of the spreadsheet. The remaining errors do sometimes have substantial impacts on key aspects of the spreadsheet. This paper provides the first fully-documented evidence on the quantitative impact of errors in operational spreadsheets., Comment: 12 pages including references

This paper presents the authors recommended practices for spreadsheet testing. Documented spreadsheet error rates are unacceptable in corporations today. Although improvements are needed throughout the systems development life cycle, credible improvement programs must include comprehensive testing. Several forms of testing are possible, but logic inspection is recommended for module testing. Logic inspection appears to be feasible for spreadsheet developers to do, and logic inspection appears to be safe and effective., Comment: 12 Pages, Extensive References