Recently we held an session at our office. The Guest of honour was Felienne Hermans of the Technical University of Delft. She has an PHD in testing spreadsheets. Quite a notable issue because traditionally we pay much attention to the testing of large and complex administrative systems, embedded software, or if we’re talking about smaller applications, mobile applications. Spreadsheets are a different cup of Tea, so why do an PHD on that?
Feline explained to us that spreadsheets are the most successful applications of all time. Research indicates that 90 percent of computers worldwide have a spreadsheet program installed. 95 percent of all U.S. businesses spreadsheets are used in the financial process. She explained that the European situation is not that much different. The infiltration of spreadsheets is enormous. Consider for yourself, what drives the major decisions of the CEO in your organisation? Ten to one, that his decision is based upon data originating from or generated by a spreadsheet program like excel. Felienne shared a story about an organization that lost three million euros, when two data fields were switched in one of the calculation sheets.
Spreadsheets are not real programs. Little programming experience is required to start using them. Therefore the creation of spreadsheets application often starts in operations, beyond the knowledge of the IT department. In initial set-up, these applications are small and simple, but they are expanded over the years. A spreadsheet has an average life of five years and in that time, about thirteen different users. Each making their adjustments to tune the spreadsheet to their needs. Undetected errors and vulnerable structures sneak into the spreadsheet.
Within the IT it is common practise to test important applications thoroughly. But who tests the spreadsheets? If they would be part of the IT landscape, then they probably would not escape the attention of professional testers. Personally, I do not know any organization that records them in their configuration-items Database. In fact, my experience is that IT departments are not aware of their existence at all.
During one of my assignments in a healthcare organization the network drive structure was reorganized. The migration was prepared to perfection: an inventory was made of all applications and the folders they used, configuration changes were included in the migration script and all users were informed about the operation and its impact. Confident they addressed all issues the migration was started, until a sudden panic broke out. The finance department was completely paralyzed.
The excel-sheets they were using were not known to the migration team, and proved to contain a large number of hardcoded file paths that failed in the new network drive structure.
What can we learn from this story? The first lesson is that there are different ways to build a spreadsheet. TU Delft has collected and analysed hundreds of spreadsheets. On the basis of this scientific research Felienne and her colleagues created thorough insight in the mistakes that are commonly made. Hard-coded paths is one of them. An automated scan indicates the vulnerabilities in the file. Obviously, this scan does detect problems calculation errors, but by rearing the vulnerabilities we reduce the chance of errors sneaking in when the sheet is adapted and extended.
The most important lesson is however, that spreadsheets should be an integral part of the IT landscape. They need to be included in the application lifecycle management process, since they are often critical for business. We need to properly develop, manage and test them. A lot of mission work needs to be done in this field, since only a small group of professionals seems to be aware of the huge business risk that is associated with failing spreadsheets. Time for a call to arms, let’s meet on the barricades. Will we test the spreadsheets? We will!
Biography:
Derk-Jan de Grood works for Valori as senior test manager and product manager. His drive is to improve the visibility of testing in both agile and traditional organizations, by sharing his knowledge and experience by means of training, presentations, workshops and publications. He is a regular speaker at conferences like EuroSTAR, wrote several successful books on software testing and publishes articles for the major testing magazines.
