End-user and corporate developers, software testers, and risk auditors discovered best practices in managing spreadsheet risks by attending the European Spreadsheet Risks Interest Group (EUSPRIG) Fourth Annual Conference.
This was our most successful EUSPRIG conference yet. The quality and content of the presentations and the 170-page proceedings received very high ratings. The conference was opened with an introduction from Garry Cleere of the European Computer Driving Licence Foundation which was the chief sponsor, and the support of the Irish Computer Society and KPMG Ireland were acknowledged.
'Reducing Overconfidence In Spreadsheet Development' by Ray Panko of the University of Hawaii was for many delegates the first introduction to concepts of human error that are now accepted in the world of software quality. Professionals have learned through inspection and introspection to expect error rates of 1-5% and they work hard to reduce errors through good preparation, awareness in working, and review. Spreadsheet end-users do not measure their error rate and so have naïve and overconfident expectations of success. Engaging in risky behaviour is self-reinforcing, for as long as one’s luck lasts. Computer lab experiments have shown how to reduce overconfidence by warning people of previous typical error rates.
'Audit and Change Analysis of Spreadsheets' by John Nash of the University of Ottawa was remarkable in its introduction of the concepts of change control into spreadsheets. It is innovative in its use of Open Source Software and server-based spreadsheets. The use of cross-platform browser clients also eliminates the virus risk. It offers a potential solution to the endemic problems of merging spreadsheets - which have been distributed for filling-in by users - back into a central database. Their tool SSSCAN tracks all changes over the life of a spreadsheet, and auditors can review and filter the log to perform integrity checks.
'VBA Tools For Excel 2000' by Chris Gorham of London caused that “I didn’t know Excel could do THAT” moment in a conference where people sit up. In this case it was the explanation of “Very Hidden” sheets which are invisible to the “unhide” command. Although I knew of that, in the next minute I heard about how individual worksheet recalculation can be turned on or off independently of workbook automatic/manual recalculation, which was news to me.
'Correctness Is Not Enough' by Louise Pryor of Edinburgh explained the importance of quality attributes such as auditability, usability, maintainability, and performance. Her Excel add-in is at www.xlsior.com
'User Computing In Financial Regulation' was the keynote speech by Dean Buckner of the Financial Services Authority (FSA) in London. He is a proponent of end-user compuing (EUC), but calls for a “Highway Code” to go along with the “Driving Licence” in using computers. His description of “data citizenship” is comparable with the “responsible computing” term used by the European Computer Driving Licence (ECDL). He sees similar problems now arising with Access databases as are already happening with spreadsheets, as users get to grips with this technology. His aim is to reduce the two or three major problems (that go unreported) he sees every year. One of the FSA’s firms already links the level of a business’s capital charges to accreditation in EUC.
'The Wall and The Ball' by Richard Irons of Central Queensland University had a startling start by showing a tax calculation mistake in a model in a published book by Benninga now in its second edition. Irons has created two simple spreadsheet test examples and he is looking for people to take them as research into the causes of errors. Be warned if you decide to try it – the average cell error rate for the simple “Wall” problem is 1.67% and for the more conceptually difficult “Ball” it is 11.86% !
'Accuracy In Spreadsheet Modelling Systems' by Tom Grossman of the University of Calgary gave an analytical overview of various classes of errors. These include input data quality, imperfect models, implementation errors, and bias in interpreting unexpected results. Tom also presented 'Research Strategy & Scoping Survey on Spreadsheet Practices' where he outlined a survey for collecting data on spreadsheet attributes, importance, motivation, and development practices.
A feature of the conference were the exhibits & demonstrations of Code Tracer, a spreadsheet visualiser and analyser ( www.codetracer.com/demo ), Atebion, a simultaneous equation modelling solver ( www.atebit.co.uk ) and EXChecker, a spreadsheet auditing tool ( www.spreadsheetauditing.com ).
'Getting Spreadsheets Under Control - Practical Issues And Ideas' by Barry Pettifor of PWC in London revealed that “in 7 years reviewing models, the PwC team have NEVER failed to find errors in client models”. He predicted that the Sarbanes-Oxley Act in the USA should mean that managers can no longer ignore their un-controlled dependency on spreadsheets. He described an approach of identifying key applications, consolidate them, lock the spreadsheets down, develop “spreadsheet champions” to instil good practices, and equip auditors with a risk assessment framework such as CoBiT.
'Issues in Strategic Decision Modelling' by Paula Jennings of London described sensitivity analysis, what-if scenarios, Monte Carlo simulation, optimisation, and real-options modelling.
'Investigating The Use Of Software Agents To Reduce The Risk Of Undetected Errors' by Simon Thorne and Mukul Madahar of the University of Wales in Cardiff (UWIC) was controversial. They described how software agents could monitor users as they work with spreadsheets, detect certain patterns, and – this is what caused the sharp intake of breath – “make appropriate changes”!
'TEAM work: A CobIT Approach To Quality' by David Chadwick of Greenwich summarised previous EUSPRIG work in the key categories of Tools, Education, Audit, and Management. Grenville Croll reported on his EuSpRIG presentation at EURO/INFORMS Conference 2003 in Istanbul. We need to make more contact with the OR/MS world - may I ask anyone in that area to get in touch with regard to future conferences?
'Spreadsheet Debugging' by Yirsaw Ayalew of the University of Addis Ababa described research on Interval-Based Testing and Fault Tracing. Interval testing uses a parallel spreadsheet with ranges of expected values specified to aid validation. The fault tracing strategy is based on the expectation that a cell which has many faulty precedents is more likely to contain the most influential cells than the one with few faulty precedents.
We concluded with a panel discussion on “Quality Engineering” and an invitation by Roland Mittermeir to next year’s conference at the University of Klagenfurt, Austria.