European Spreadsheet Risks Interest Group
Annual Conference 2004
Theme: Risk Reduction in End User Computing
Best practice for spreadsheet users in the new Europe
Tutorials July 14, Conference July 15-16 2004
Klagenfurt University, Klagenfurt, AUSTRIA
The decision to hold the 2004 conference in Klagenfurt reflects our emphasis on the European dimension of research, and recognises the considerable interest in European standards of best practice from the accession countries. At the centre of the new enlarged Europe, Austria welcomed delegates to this popular lakeside resort.
The 2004 conference was bigger than ever, reflecting the growing concern in industry and the public sector over risk from the uncontrolled use of spreadsheets.
Click here to download the programme as a 236K PDF.
Presentation Slides now available for download
Photos taken during the conference
Conference report
This amazing event showed our annual conference maturing in strength and content. We were fortunate to have it in a beautiful lakeside location, and to have the benefit of professional organisation that was much appreciated. The number and quality of papers presented was higher than ever before. As a result, our previous two-half-day format was expanded to almost two days!
The presentation slides are available at:
http://www.isys.uni-klu.ac.at/ISYS/eusprig04/11_presentations/
We began with a well-attended tutorial briefing by Ray Butler and Louise Pryor on Wednesday evening on "Spreadsheets, their Use, their Problems and Risks".
Dean Buckner of the UK FSA delivered the keynote address on "Appropriate Control of User Development Solutions in the Banking Sector". As a regulator, he sees many banks who do not know how dependent they are on end-user developed spreadsheet and database applications, and he described how some have suffered severe disruption when the IT administrators migrated systems without taking these thousands of "islands of automation" into account. His talk echoed David Colver's in that risk and impact assessment is needed for a wise choice of appropriate controls.
David Colver of Operis UK stimulated much interest with his talk on "Spreadsheet Good Practice: Is There Any Such Thing?" In this, he took the Operis two-day course in spreadsheet methodology, condensed it to a half hour, and then presented a counter-argument for each point. It made it clear that circumstances alter cases, and that "best practice" depends on the specific context in which a spreadsheet model is used.
Thomas A. Grossman and Özgür Özlük from universities in San Francisco presented "A Paradigm for Spreadsheet Engineering Methodologies". Tom's lively presentation style got the audience involved in a thought-provoking session. So let me now pose Tom's question to you, the reader: "What do see as the value proposition of spreadsheet risk management?" If that sounds like American management jargon, try "What's the benefit of imposing more controls on spreadsheet development and use?" To start you thinking, how about "Stay out of jail by being able to demonstrate for SOX purposes that material risks are managed."
Ralph Baxter of Cluster Seven presented "Auditability and other Benefits Derived from a Temporal Dimension". The idea of change monitoring, as described in the XiGence system, was new to some delegates. Recognising the difficulty of imposing controls, many were interested in at least keeping a close track on what changes are made to spreadsheets on a server.
Louise Pryor's talk on "When, why and how to test spreadsheets" explained the difference between structural review which audit tools do and dynamic testing, which her product Xlsior assists. She described the key concepts of unit test, system test, and regression test.
Hilary Emmett of Decisioneering spoke on "Identification of logical errors through Monte Carlo simulation". This is also a form of dynamic testing, where the range of values used in simulations can stress spreadsheets and show their weak points.
Markus Clermont of the University of Limerick presented "A Toolkit for Scalable Spreadsheet Visualisation". This is a Gnumeric plug-in that looks for structural similarities in spreadsheet formulas, to get a clearer idea of its overall organisation, devise auditing strategies, and assist in fault tracing.
Sabine Hipfl of the University of Klagenfurt presented "Using Layout Information for Spreadsheet Visualization". This took a different approach to Markus Clermont in that it looked for data patterns that might show more clearly blocks of related information. A comment from one of the delegates indicated that she might also use blank rows and columns to help delineate areas.
Andrej Bregar of the University of Maribor presented "Spreadsheet Models Complexity Metrics". This applied and adapted conventional software engineering complexity measures to the spreadsheet application. He used concepts of distance, dependencies, and logic branches to show how one might measure how complex and difficult a given spreadsheet is.
Simon Thorne of the University of Wales in Cardiff won the student paper prize with "A novel approach to formulae production and overconfidence measurement to reduce risk in spreadsheet modelling". This study produced data to show that when given an English description of a calculation, people made fewer errors when producing data to illustrate a correct result, than to create the formula itself.
Garry Cleere of the ECDL Foundation presented a draft version of a new syllabus for "Spreadsheets Good Practice" and invited comments and contributions from the delegates. This should stimulate some debate!
Karin Hodnigg of the University of Klagenfurt presented "Computational Models of Spreadsheet-Development: Basis for Educational Approaches". This probed into the different meanings that spreadsheet operations have, and how they can confuse new users. For example, a cut-and-paste operation has different effects from a copy-and-paste.
John Nash of the University of Ottawa described the recent developments in his research project "TellTable Spreadsheet Audit: from technical possibility to operating prototype". Like the ClusterSeven product, this tracks spreadsheet changes; by contrast, it works in Linux and is strictly server-based.
Gary K. Arakaki could not attend to present "XlStruct: A Tool for Building Structured Error-Resistant Spreadsheets" but Simon Thorne stood in for him.
In the panel session at the end, we agreed that we needed to do more to promote Eusprig. Several speakers from prominent consulting and technology companies offered to mention the next conference in emails to their user base of many thousands of modelling users.
Patrick O'Beirne Chair, 2004 European Spreadsheet Risks Interest Group www.eusprig.org
Abstracts
Click here to download the presentations in PDF format
Tutorials: “The Subversive Spreadsheet”; Ray Butler, Highways Agency [UK] “Being less subversive”; Louise Pryor, Louise Pryor & Co Ltd [UK]
Keynote: “Appropriate Control of User Development Solutions in the Banking Sector”; Dean Buckner, UK Financial Service Authority [UK]
“Spreadsheets Good Practice”; Garry Cleere, ECDLF [IE]
Computational Models of Spreadsheet-Development Basis for Educational Approaches. (Karin Hodnigg[AT], Markus Clermont [AT,IE], Roland T. Mittermeir[AT])
This paper shows the limits of scopes of current computational models. Based on this discussion, research questions are formulated on how to teach spreadsheet programming in such a way that the current user population does not shy away right from the beginning, continuing to use a tool-focused “what’s to be done with the next cell”-approach.
A novel approach to formulae production and overconfidence measurement. S. Thorne[UK], Dr D. Ball[UK], P. Cleary[UK], Z. Lawson[UK]
We discuss an experiment that compares example driven modelling against traditional approaches over several progressively more difficult tests. The results are very interesting and certainly point to the value of investigating the example driven potential being realised in practice.
Testing spreadsheets: who, when, why and how. Louise Pryor[UK]
Different types of spreadsheet (eg, those that are used to perform complex calculations, or those that record activities or processes) are tested in different ways. A good testing process, in which the tests are both auditable and reproducible, and which is an integral part of the spreadsheet development process, increases the confidence of the users in the results of the spreadsheet.
End User Computer Applications – Auditability and Other Benefits Derived from a Temporal Dimension. Ralph Baxter, ClusterSeven[UK]
Locating changes to end-user computer-based documents (as generated within the Microsoft Office suite) is a labour intensive activity. This is caused by the lack of any permanent temporal record. Other problems include the lack of business context or data trend information or an ability to observe changes in document integrity. All lead to an absence of auditability and exposure to error or fraud. These problems have been solved by the creation of a structured history of all document/user interaction, creating a new approach to managing end user data.
TellTable Spreadsheet Audit Logs: from technical possibility to operating prototype. Andy Adler[CA], John Nash[CA], Neil Smith[CA]
At the 2003 EuSpRIG meeting, we presented a framework and software infrastructure to maintain and audit version histories of spreadsheet files. This report describes the results of a pilot implementation of this software (now called TellTable; see www.telltable.com) consisting of two components: a web application for spreadsheet editing (SBOX), and an audit software application.
Spreadsheet models complexity metrics. Andrej Bregar[SI]
In the paper, several product complexity metrics are addressed which are related to logic structure, data structure and size. Most metrics are newly defined, while some are adapted from the traditional software engineering. Their purpose is the identification of cells which are liable to errors. In addition, they can be used to estimate the values of dependent process metrics.
A Toolkit for Scalable Spreadsheet Visualization. Markus Clermont[IE]
This paper presents a toolkit for spreadsheet visualization based on logical areas, semantic classes and data modules. Logical areas, semantic classes and data modules are abstract representations of spreadsheet programs that are meant to reduce the auditing and comprehension effort, especially for large and regular spreadsheets. The toolkit is integrated as a plug-in in the gnumeric spreadsheet system for Linux. It is capable to process large, industry scale spreadsheet programs in reasonable time and is tightly integrated with its host spreadsheet system.
Using layout information for spreadsheet visualization. Sabine Hipfl[AT]
This paper presents an approach that extends the spreadsheet visualization technique reported in [Clermont, 2003] by using layout information. This approach 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 or spurious manner.
“Spreadsheet Good Practice: Is There Any Such Thing?”; David Colver, Operis[UK]
I argue for and against commonly advocated spreadsheeting techniques, making the point that what is valuable in one context is deeply unhelpful in another.
XlStruct: A Tool for Building Structured Error-Resistent Spreadsheets. Gary K. Arakaki[JP]
The problem is the usual way of building spreadsheets makes it easy to make an error. XlStruct provides a new way of building spreadsheets that is much less susceptible to errors. XlStruct is an add-in for Microsoft Excel for building structured, error resistant spreadsheets.
Identification of logical errors through Monte Carlo simulation. Hilary L. Emmett[UK], Lawrence I. Goldman[US]
The primary focus of Monte Carlo simulation is to identify and quantify risk related to uncertainty and variability in spreadsheet model inputs. The stress of Monte Carlo simulation often reveals logical errors in the underlying spreadsheet that might be overlooked during day-to-day use or traditional “what if” testing. This secondary benefit of simulation requires a trained eye to recognize warning signs of poor model construction.
A Paradigm for Spreadsheet Engineering Methodologies. Thomas A. Grossman[US], Özgür Özlük[US]
There is a lack of well-organized, proven methodologies with known costs and benefits for well-defined spreadsheet classes. We present a paradigm for organizing and interpreting spreadsheet engineering recommendations. It systematically addresses the myriad choices made when developing a spreadsheet, and explicitly considers resource constraints and other development parameters. This paradigm identifies gaps in our knowledge that merit further research.
PROGRAMME COMMITTEE
Chair: Pat Cleary, University of Wales in Cardiff, UK
Vice-Chair: David Chadwick, University of Greenwich, UK
Prof. Roland Mittermeir, University of Austria at Klagenfurt, Austria
Markus Clermont, Software Quality Research Laboratory, University of Limerick, Ireland
Grenville Croll, Croll Management, UK
Barry J. Pettifor, Price Waterhouse Coopers, UK
Leon Strous, De Nederlandsche Bank, Holland
Barry Philips, Atebion, UK
Ray Butler, Highways Agency, UK
Committee members
Patrick O'Beirne (Chair) Systems Modelling Ltd.
Graham McDonald (Treasurer) HM Customs & Excise
Jocelyn Paine (Member) University of Bristol
Ray Butler (Member) ISACA Northern UK
Leon Strous (Member) The Netherlands Bank