Everyone is probably familiar with the 600,000 emails released by Enron after their scandal right at the turn of the century. A lot of different analyses was done on those emails, but there's one interesting analysis that I didn't see until recently: the emails with spreadsheets as attachments. Felienne Hermans, a computer scientist at Delft University of Technology in the Netherlands, scoured all the emails that had Excel spreadsheets attached to them and analyzed 15,000 internal Enron spreadsheets to see what patterns existed in the models, formulas, and yes, the errors. After reading her paper, my opinion is that Enron's spreadsheet errors were not unique to Enron, but could happen at any large company.

24% of Enron's spreadsheets contain errors

One of the key takeaways from Hermans' paper that has been cited elsewhere is that 24% of the spreadsheets she analyzed contains an error. These are spreadsheets where there was a "runtime" error, so the errors you typically see when you divide by 0 or make an error in your formula. Here's a table from the report showing how many of these errors appears in these spreadsheets:

The sheer number of errors is startling:

In total, we have found 2,205 spreadsheets that contained at least one Excel error, which amounts to 24% of all spreadsheets with formulas (14% of all spreadsheets). They together contain 1,662,340 erroneous formulas (49,796 unique ones), which is 585.5 (17.5 unique ones) on average. There were 755 files with over a hundred errors, with the maximum number of errors in one file being 83,273.

755 files with over one hundred errors. It's easy to say the people creating these spreadsheets weren't skilled Excel users, or they were deliberately trying to make these errors. Putting the ethical argument aside, I'd say these Excel errors are only one side of the story.

A lot of times you might not have a finished model or haven't collected all the data, so there may be formula errors until the final model or report is finished. I don't believe Hermans scanned for the version of these files, so it's likely that many of these files were simply incomplete. I'd argue that this is the predicament at many companies. You have files at various stages of completeness or readiness, so looking at the files with only these "runtime" errors can only tell you so much about the intention of the person who worked on the spreadsheet.

Auditing Enron's files for accuracy beyond formulas with runtime errors

One type of formula error that wouldn't show up in Hermans research is the formula that references incorrect cells. This can be due to human error or lack of understanding of how the model should reflect the business.

In a separate study by Thomas Schmitz and Dietmar Jannach done in 2016, Shmitz and Jannach looked closely at formulas that didn't have normal runtime errors to see if they referenced incorrect cells. This is a much more difficult analysis because you have to know a bit about the business situation the model is based on, but most of the files in their analysis are quite straightforward.

Take, for instance, this Southpoint Gas Model which appears to show how much gas Transwestern (a pipeline subsidiary of Enron) is using on an hourly basis:

The Total Gas Usage column is a straightforward formula ...