Tuesday, November 18, 2014

Enron's spreadsheets

Remember Enron, the American oil company which cooked its books for several years, showing profits which didn't exist in reality, etc? Well, it transpires that as part of the court case against them, all the company's emails were sub poena'd. This material has formed the basis of a study of their spreadsheets.

I wrote seven months ago (it seems like a lifetime ago) about discovering the European Spreadsheets Risks Interest Group and being in contact with one of their number (Dr Felienne Hermans). Yesterday I was looking at her blog and read her account of discovering the 'Enron Corpus'. Apart from the blog itself, there is also a link to an academic paper which I found fascinating.

Following is data extracted from Dr Hermans' analysis with regard to formulas:

Description number
Number of spreadsheets analyzed 15,770
Number of worksheets 79,983
Number of non-empty cells 97,636,511
Average number of non-empty cells per spreadsheet 6,191
Number of formulas 20,277,835
Average of formulas per spreadsheet with formulas 2,223
Number of unique formulas 913,472
Number of unique formulas per spreadsheet with formulas 100

and with regard to functions:

Rank Function # Spreadsheets Percentage
1 SUM 6493 72.0
2 + 5571 61.8
3 - 4866 54.0
4 * 3527 39.1
5 / 3112 34.5
6 IF 1827 20.3
7 NOW 1501 16.7
8 AVERAGE 879 9.8
9 VLOOKUP 763 8.5
10 ROUND 606 6.7
11 TODAY 537 6.0
12 SUBTOTAL 385 4.3
13 MONTH 325 3.6
14 CELL 321 3.6
12 YEAR 287 3.2

In case this table isn't clear, it means that 72.0% of the spreadsheets which used functions contained the 'SUM' function. The insight that I derive from this information is that the part of my research questionnaire which deals with spreadsheet competence needs to ask about the 'SUM', 'IF', 'AVERAGE' and 'VLOOKUP' functions in order to cover the most highly used functions. I looked at the questionnaire over the weekend and there are questions about the first three; I should add a question about VLOOKUP to cover myself.

The second insight from the analysis is that employees of Enron were sending spreadsheets back and forth, or as Dr Hermans puts it, about 100 emails with spreadsheets attached were sent each day, so we can safely conclude that emailing spreadsheets was common practice at Enron. This practice is known to result in serious problems in terms of accountability and errors, as people do not have access to the latest version of a spreadsheet, but need to be updated of changes via email. Presumably I will have to ask a question such as "How frequently do you work on spreadsheets which are passed back and forth between your colleagues?" in order to quantify this behaviour.

I also found yesterday an interesting paper on measuring cognitive style in students of accountancy; the authors note that cognitive style influences how information is acquired and utilised during problem solving whereas cognitive ability determines how well an individual performs. The authors determine on a sample of 138 accounting students that students’ cognitive ability had a higher impact on their performance than cognitive style. There is a significant interaction between students’ cognitive styles and the cognitive strategy demanded by the accounting task: students with both global and sequential styles performed better when the cognitive demands of the task matched their cognitive style. It is not clear how these findings can be extrapolated to ERP users.

[SO: 3683; 2,15,36
MPP: 574; 1,1,6] 

No comments: