Dr. Yusuf Jafry (Chief Technology Officer), Fredrika Sidoroff, Roger Chi, Risk Integrated, Isle of Man

We present Risk Integrated's Enterprise Spreadsheet Platform (ESP), a technical approach to the near-elimination of spreadsheet risk in the enterprise computing environment, whilst maintaining the full flexibility of spreadsheets for modelling complex financial structures and processes. In its Basic Mode of use, the system comprises a secure and robust centralised spreadsheet management framework. In Advanced Mode, the system can be viewed as a robust computational framework whereby users can “submit jobs” to the spreadsheet, and retrieve the results from the computations, but with no direct access to the underlying spreadsheet. An example application, Monte Carlo simulation, is presented to highlight the benefits of this approach with regard to mitigating spreadsheet risk in complex, mission-critical, financial calculations.

Michael Purser, University of Greenwich, UK

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.

S. Thorne, D. Ball, University of Wales Institute Cardiff, UK

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

Keith Bishop, Qtier Software Limited, UK

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.

Kenneth R. Baker, Stephen G. Powell, Barry Lawson, and Lynn Foster-Johnson, Dartmouth University, USA

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.

Paul J. Blayney Faculty of Economics + Business, University of Sydney 

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.

Simon Murphy, Codematic Limited, UK

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.

Alan Rust, Brian Bishop, Kevin McDaid, Dundalk Institute of Technology, Dundalk, Ireland

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.

Dr. Raymond R. Panko, University of Hawaii, USA

This paper presents the author’s 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.

Don Price, HM Revenue & Customs, UK

Her Majesty’s Revenue & Customs (HMRC) was born out of the need to create a UK tax authority by merging both the Inland Revenue and HM Customs & Excise into one department. HMRC encounters spreadsheets in tax-payers’ systems on a very regular basis as well as being a heavy user of spreadsheets internally.
The approach to spreadsheet risk assessment and spreadsheet audit is by the use of trained computer auditors and data handlers. This, by definition, limits the use of our specialist spreadsheet audit tool to such trained staff. In order to tackle the growing use of spreadsheets, a new way of approaching the problem has been piloted. The aim is to issue all staff who come across spreadsheets with a simple to use analysis and risk assessment tool, based on the departmental software SpACE (Spreadsheet Audit & Compliance Examination).

Andrew Kumiega, Ph.D. and Ben Van Vliet, USA

The objective of this paper is to develop a standardized methodology for software development in the very unique industry and culture of financial markets. The prototyping process we present allows the development team to deliver for review and comment intermediate-level models based upon clearly defined customer requirements. This spreadsheet development methodology is presented within a larger business context, that of trading system development, the subject of an upcoming book by the authors of this paper.

B. Sentance Xenomorph, UK

Spreadsheets in financial markets are frequently used as database, calculator and reporting application combined. This paper describes an alternative approach in which spreadsheet design and database technology have been brought together in order to alleviate management and regulatory concerns over the operational risks of spreadsheet usage. In particular, the paper focuses on the rapid creation and centralised deployment of statistical analytics within a software system now in use by major investment banks, and presents a novel technique for the manipulation in spreadsheets of high volumes of intraday market data.

Richard Brath, Michael Peters, Oculus Info, Inc., Canada

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-users the layout and formatting already present in the spreadsheet.
 

Brandon Weber, Microsoft Corporation, USA

Most organizations today use spreadsheets in some form or another to support critical business processes. However the financial resources, and developmental rigor dedicated to them are often minor in comparison to other enterprise technology. The increasing focus on achieving regulatory and other forms of compliance over key technology assets has made it clear that organizations must regard spreadsheets as an enterprise resource and account for them when developing an overall compliance strategy. This paper provides the reader with a set of practical strategies for addressing spreadsheet compliance from an organizational perspective. It then presents capabilities offered in the 2007 Microsoft Office System which can be used to help customers address compliance challenges.

V. R. Vemula, D. Ball, S. Thorne, University of Wales Institute, Cardiff

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.

Grenville J. Croll, Spreadsheet Engineering Ltd;  Raymond J Butler, EuSpRIG

There is overwhelming evidence that the continued and widespread use of untested spreadsheets in business gives rise to regular, significant and unexpected financial losses. Whilst this is worrying, it is perhaps a relatively minor concern compared with the risks arising from the use of poorly constructed and/or untested spreadsheets in medicine, a practice that is already occurring. This article is intended as a warning that the use of poorly constructed and/or untested spreadsheets in clinical medicine cannot be tolerated. It supports this warning by reporting on potentially serious weaknesses found while testing a limited number of publicly available clinical spreadsheets.

Jocelyn Paine, Emre Tek, Duncan Williamson, Spreadsheet Factory

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.

Louise Pryor, EUSPRIG

We give a brief characterisation of the purposes and forms of documentation in and of spreadsheets.