A look back to Eusprig 2019

The 20^th conference of the European Spreadsheet Risk Interest Group was held in  Browns of Covent Garden, London on 11-12 July 2019,

Thursday 11 July 2019

Developing Excel Thought Leadership

David Lyford-Smith, ICAEW, Chartered Accountants’ Hall

This described the development of their published booklets on Twenty Principles, the Spreadsheet Competency Framework, and Financial Modelling Good Practices.  David summarised their approach as:

• We have a model for developing spreadsheet thought leadership:

1. Identify an area where a high-level guidance publication could be of use by consulting with experts and stakeholders
2. Convene a suitable and diverse group of experts and synthesise a consensus view from their disparate opinions
3. Invite commentary and critique via an exposure draft process
4. Complete the project based on the feedback received

• With such variation in opinions and practice, building in room for individual preference and differences in approach must be an explicit consideration
• Many people are willing to follow good practice when exposed to it, but few will seek it out for themselves

Practical aspects of applying an End User Computing policy

Roger Turner, Wesleyan Assurance Society, Birmingham

This was an update on his 2018 paper where he described the work Wesleyan did to set up its EUC policy. There was a continual focus on meeting and working with managers to be sure that actions were completed. His slides give a very frank account of their journey which will be very valuable to those present working on similar projects. Their fine-grained approach to classifying risks made it easier to score some quick wins, on things like Controls, Validation, and Documentation. Some slides showed their KPIs to measure progress on moving spreadsheets from red to green status. His learning points are:

1. Plan what you’re going to do
   1. If you fail to plan you plan to fail
   2. Work out risk metrics, use best practice to make an EUC policy, do pilot runs to iron out problems.
2. Get buy-in right from the top
   1. Identify the risks and benefits – suggest a risk based approach tackling where the biggest risks are first.
   2. Success with these will give your project momentum which you will need for harder areas
3. Use of expensive software can usually be avoided
   1. Look to re-use existing infrastructure and systems
   2. A top down approach can get some results and is cheaper, less intrusive than a bottom-up approach
4. Publicise the policy with its benefits (intranet, meetings etc to make the approach clear)
5. Be sympathetic with peoples’ other business priorities
   1. Most will try to help and this will give you momentum
6. Use KPI reporting – this will show how your EUC project is reaping benefits over time.

A Case Study of Spreadsheet Use within the Finance and Academic Registry units within a Higher Education Institution

Simon Thorne and Jamie Hancock

This was a survey of the Academic Registry and Finance departments of a university. The paper has many tables giving an analysis of the responses to the survey, although bear in mind that this is self-reporting. They identified the common risks:

1. Lack of training, standards, development methods, testing and documentation in spreadsheet development
2. Mistakes in communicating with third parties
3. Potential for fraud and falsification
4. GDPR risks

The mitigating actions that follow are:

1. Generate an inventory of active critical spreadsheet applications and take appropriate mitigating actions with the riskiest spreadsheets.
2. Development of a robust spreadsheet development policy governing approaches to development, deployment, documentation, testing and accountability
3. As part of the organisational policy, decide on development methodologies, documentation standards and testing policies for spreadsheets
4. Identify and deliver appropriate training in planning, development and testing of spreadsheet models
5. Establish peer working, user groups and risk awareness sessions

Abstracting spreadsheet data flow through hypergraph redrawing

David Birch, Nicolai Stawinoga, Jack Binks, Bruno Nicoletti, Paul Kelly

Imperial College London, Filigree Technologies, London

This represents the spreadsheet as a network diagram of its dependencies. They make the link structure explicit by transforming the spreadsheet into a graph structure. They find groups of cells performing near-identical operations by detecting vector operations using isomorphic subtree detection. They illustrate the effectiveness of raising the abstraction level by translating a series of spreadsheets to a high-level data-flow language called ModL. By applying a force-directed algorithm they can rotate the graph in a virtual 3D space to better see the relationships.

More established technologies such as Quantrix are specifically aimed at the financial modelling market and start with such structures as their design surface.

Implementation Strategies for Multidimensional Spreadsheets

Paul Mireault, Founder, SSMI International, Honorary Professor, HEC Montréal

Last year, the author issued a challenge to spreadsheet users to create a spreadsheet model with some initial data and simple formulas to allocate quantities and money over four dimensions. There was no challenge in the formulas, they were given them all, it was simply a request to implement a model. Even with that, of the 109 who responded, only 17 completed the challenge. Some implemented it as tables which spread the four dimensions over multiple blocks of formulas in multiple worksheets. Others collated the data in a flat database structure and created the reports as simple summaries of the data. His conclusions are:

1. Participants using the Database approach usually have the simplest formulas for aggregated variables. We expect that they will be the easiest to maintain.
2. The analysis of the Multi-dimensional Spreadsheet Challenge submissions has given us insight on the different implementation strategies used by seasoned spreadsheet developers. Given the same problem and its solution, developers had very different results. This lack of standardization may explain the why it is hard to understand and maintain somebody else’s spreadsheet.

EQUS – helping to see formulae

Chris R. Roast, Sheffield Hallam University

EQUS is a C# Excel addin that presents a graphical breakdown of cell formulas annotated with an evaluation of each subexpression and the calculation steps. It is like a more colourful, simultaneous, presentation of what the Formula Evaluation tool in Excel does; or other formula reference tree viewers in the Excel addins market. This is intended for educational use and is very suited to explaining to students how the formulas work.

From webtables to datatables

Mária Csernoch, University of Debrecen, Hungary

The motivation for this work is to create realistic and interesting tables of data for school students to analyse. Web sites are an expected source, but unfortunately many frustrate the ability to conveniently collect data from their pages. Standard web queries depend on data in HTML tables, but many use <div> or other tags which can not be read by Get Data from Web, or Powerquery. She presented the steps they must use for copy/paste or saving pages as HTML or plain text and opening in Excel or Word; then reformatting, editing, cleansing, parsing, and reshaping to get a usable table out of the result. It can be a lot of tedious work. While professional developers may use VBA and APIs, this aims to create reproducible manual steps which schoolchildren can use. Be aware that these are Hungarian schoolchildren who have already been taught Excel array formulas.

Will Dynamic Arrays finally change the way Models are built?

Peter Bartholomew , MDAO Technologies Ltd

The author is an engineer, not a financial modeller. His kind of task for the MoD was a low-fidelity Excel model of the likely interaction of warship and helicopter in the North Atlantic, with a graphical readout of the behaviour. His models are largely constructed with names rather than cell references, so have always been innately array-oriented. He points out that Excel 365 dynamic arrays now provide the most direct route to a solution

instead of requiring effort to set up Control-Shift-Enter (CSE) objects; any formula can be an array calculation, any cell can hold an entire result array.

The surprise for anyone versed in traditional  methods may well be how few cells contain formulas, so consistency errors are simply not possible and having fewer formulas reduces the opportunity for error.

Cubes an alternative to spreadsheets to mitigate risk and improve performance?

Marc BRAUN, Xcubes

This was a demonstration of XCubes, a Multidimensional Spreadsheet System software package. He used the model specified in Paul Mireault’s talk as an illustration. An Excel solution required 1,427 formulas; an XCubes solution just 13. Cubes are the most suitable solution when the model can be broken down in blocks to store well-structured data. They make analysis, calculation and visualization even on large data sets much easier. The solution can be downloaded from xcubes.net.

Does the teaching of spreadsheeting skills have the potential to be a significant global education policy intervention?

Morten Siersted, Visiting Research Fellow, Centre for Development Studies, University of Bath

Spreadsheeting skills do not currently feature within global education policy in any significant way.  However within the UN Strategic Development Goals (SDGs), the proxy of ‘ICT skills’ is of direct relevance to Target 4.4 ‘Skills for work’ and has led UNESCO to propose ‘using basic arithmetic formulas in a spreadsheet’ as a component of an ‘ICT skills acquisition’ element of measuring progress on SDG4.4.

Although there is a shift from superficial surface level ICT skills to deeper cognitive skills and computational thinking, he sees a risk of policy makers jumping straight from ICT skills to traditional computer programming education, thereby missing out the ‘spreadsheeting as entry-level programming’ stepping stone. ICT for Development is now mainstreamed within key UN agencies (ITU, UNCTAD, UNDP, and UNESCO). He concludes that an initiative should be undertaken to disseminate existing spreadsheeting research to education policy makers working with low-income countries.

 

Friday 12: Training Sessions

Working through Modeloff example problems

David Lyford-Smith, ICAEW

David worked through two games from the Modeloff questions resources as an example of problems that require no domain knowledge but are capable of solution by Excel. Indeed, the drive for more efficient solutions often captures the attention of Excel nerds beyond the point of return on time spent.

Dynamic Arrays in Excel

Charles Williams, Decision Models Ltd

Charles worked through a presentation created by Joe McDaid of Microsoft which explained how the new dynamic arrays work and also, illuminatingly, how the legacy array formulas and their implicit intersection rules really worked! This is the video from Microsoft:

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Excel-Advanced-spreadsheet-modeling-using-Dynamic/td-p/726918

Powerpoint slides:

https://medius.studios.ms/video/asset/PPT/MBAS19-BRK4001

Slide 32 (Array Eval) has a typo, the word “didn’t” should not be there. Slide 42 (Calculation Modes) table is blank in edit mode but fills in by animation in presentation mode. The content can be seen in this tweet from Charles’ presentation: https://twitter.com/ExcelAnalytics/status/1149633726592114688

Patrick O’Beirne  16 July 2019