EuSpRIG 2013 Conference report

4-5 July 2013, University of Greenwich, London.
http://www.eusprig.org
We had a slightly smaller number of attendees this year than we expected, considering the publicity that attended recent spreadsheet errors, such as the London Whale or the Reinhart & Rogoff debacle. Tell me: what would attract you to the EuSpRIG conference? What would you want from it that would make it worthwhile to spend a day learning from experts on spreadsheet risk, development, and audit?
The following are my notes on the presentations. They reflect my personal interests and so are not in any way speaking on behalf of EuSpRIG.
“Multidisciplinary Engineering Models: Methodology and Case Study in Spreadsheet Analytics”; David Birch, Helen Liang, Paul H J Kelly, Glen Mullineux, Tony Field, Joan Ko and Alvise Simondetti, Imperial College London, UK.
David Birch presented a methodology and toolkit for analysing Integrated Resource Management (IRM) spreadsheet models, using the ARUP model as an example. IRM models are large and getting larger all the time, handling a thousand variables in 250,000 formulas over many worksheets. Each worksheet represents one discipline which has a specific team and there is a key role in integrating updates of these sheets into the main model. They analyse a slice of the model, and produce a visualisation of the links between the various disciplines. This enables a knowledgeable reader to validate whether the spreadsheet matches a conceptual model of communication dataflow. They also performed input sensitivity analysis, eg 2,563 simulation runs each taking less than a second on a fast machine. And applied it to a system of 500 connected spreadsheet files. This is, I think, the first paper we have had from the engineering world, it is a welcome change from the traditional focus on financial models. It certainly gave me an idea for an enhancement to my XLTest addin!

“XLSearch: A Search Engine for Spreadsheets”; Michael Kohlhase, and Corneliu Prodescu, Jacobs University Bremen, Germany.
This was a serious computer science paper. I suspect Michael lost a number of the audience at the word “ontology”, but this is a really clever way, smarter than regular expressions, of searching for any given formula pattern in an index collected from spreadsheets. Remarkably, it searches in about 50ms regardless of how many formulas there are. To test it, he is looking for a corporation with at least 1 billion formulas; to get that, they would need several million spreadsheets. If you work in one of the Fortune 100, that may well be true of your organisation, so if you ever wished you could search them all for any given formula, call him.
http://kwarc.info/kohlhase/papers/eusprig13-xlsearch.pdf

“Practical Challenges with Spreadsheet Auditing Tools”; Daniel Kulesz, and Jan-Peter Ostberg, University of Stuttgart, Germany
Daniel reflected on the differences between spreadsheet auditing tools and traditional code static analysis tools (TSATs), and on how useful they are to casual users of spreadsheet. TSATs are non-destructive: they provide the report in a view layer that does not change the underlying code, and can refresh if the code changes. Obviously that is easier to do for one-dimensional text than it is for the two-dimensional grid, especially as the object of interest (eg a formula block) can span large ranges. The students found the use of colour coding problematic as they inferred significance from the colours and it was not reversible. They found the tools less customisable than TSATs; and were disappointed that none were localised with a German user interface. They also wanted the software to prioritise the findings for them and point out definite errors rather than a list of possible problems with a high number of “false positives”. As the industry partner did not perceive faulty spreadsheets to be a major risk, they picked an auditing tool on the basis of price (five licenses for less than 1000 euro) rather than features. The German version of the paper gives more details:
ftp://ftp.informatik.uni-stuttgart.de/pub/library/medoc.ustuttgart_fi/FACH-0154/FACH-0154.pdf

Excel 2013 Spreadsheet Inquire; Patrick O’Beirne, Systems Modelling Ltd, Ireland
For my own paper, I reviewed the Inquire add-in for Excel 2013. This is very much a version 1.0 as there are quite a few shortcomings. In particular, it lists formulas individually rather than by distinct formulas (ie a block of one formula copied). I provide a helper addin to make that list more useful:
https://sysmod.wordpress.com/2013/03/06/excel-2013-inquire-addin-improved-iq/
It is important in that it shows that MS are now drawing attention to the relevance of spreadsheet audit. Other delegates pointed out that this addin is only available in the Professional Plus version of Excel, which excludes most small business licenses. If I was Microsoft I too would probably want to restrict its availability to professional users and get the feedback on bugs before making it more generally available. You can read the paper at
http://www.sysmod.com/excel-2013-inquire-add-in-review.pdf

Spreadsheet Auditing in Practice; David Colver, Operis Ltd
David gave a presentation (there is no formal paper available) on the real life world of Private Finance Initiative (PFI) project auditing. Operis offer an opinion backed by a £5M liability insurance so they need to be very sure of what they say. Their scope is far wider than the spreadsheet and includes the contracts and legal documents.

The misuse of spreadsheets in the nuclear fuel industry; Simon Thorne, Cardiff Met. University, UK.
Simon reported on the data falsification incident at BNFL in 1999, where operators copied and pasted spreadsheet data rather than performing the measurements on MOX pellet diameters. The compensation to customers who returned product was over £40M and the CEO was fired by the government. The full report from the HM Nuclear Installations Inspectorate is:
http://www.hse.gov.uk/nuclear/mox/mox3.htm

A Quality Checklist for Spreadsheets; Henk Vlootman, Felienne Hermans
Henk and Felienne presented a spreadsheet with a checklist where the user can score the spreadsheet of interest by attributes such as the presence of documentation, structuring, and the evidence of various skills.
http://www.vlootman.nl/index.php?view=weblink&catid=12%3Abestanden&id=5%3Aexcelchecklist&option=com_weblinks&Itemid=30&lang=en

Eliminating errors through tables, a new type of worksheet technology; Ted Hawkins, Morphit, UK, Andrew Lemon and Alec Gibson, The Edge Software Consultancy Ltd, UK
This was an eye opener for many – a product that looks like Excel or Access and is in fact à kind of database with numerical computational features at the level of the field, a group of records, or one cell. It comes from work in recording pre-clinical experimental records.

Spatial Modelling Techniques in Microsoft Excel; Stephen Allen, ACBA, UK.
Stephen discussed his strategy for using Excel to solve Sudoku, which led him to reflect on complexity in formulas.

The plenary discussion and debate was quite wide ranging and included contributions recommending the introduction of training in safe ways of using spreadsheets, at the earliest level: school or undergraduate courses in all numerate disciplines such as accounting or engineering.
To discuss these further, join the EuSpRIG forum: send a blank email to eusprig-subscribe at yahoogroups.com.

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Research, Risk and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s