Agile Spreadsheet Development (ASD)
Because of the pressures of compliance with Sarbanes-Oxley Act, managers are now looking for good advice on how to reduce information risk and manage the end-user development hydra. This article suggests how IT managers can help users to improve their process so that they save time by making fewer mistakes and endure less of the pain and cost of bad practice.
Spreadsheet development (also called ‘modelling’ or even ‘spreadsheeting') is often an ad hoc solution to an immediate end-user need that the IT department are not able or willing to satisfy in the time frame required. For such reasons, it is the original agile development environment, the tool of choice for end-user computing.
Be aware of the risks
There have been four annual conferences of the European Spreadsheet Risk Interest Group [1] and each has been bigger and seen better researched papers than before as the concern about business exposure from end-user development grows. Spreadsheets are too easy to use, permitting an unplanned approach that often results in unstructured models with unvalidated data. Being easy to change, they lack version and audit controls (AllFirst fraud[2]). Poorly documented, they permit easy operator error (Transalta $24M loss [3])
Two heads are better than one
Having two people working together at the keyboard has the effect of continuous peer review. Ray Panko [4] in his study of spreadsheet error rates finds that “[pairs] reduced errors by about a third”. XP advocates [5] say “It is counter intuitive, but 2 people working at a single computer will add as much functionality as two working separately except that it will be much higher in quality.”
A good practice is to deploy spreadsheet champions or “gurus” whose experience can be passed on to users in a master-journeyman-apprentice manner. Experienced spreadsheet builders know that time spent planning the spreadsheet up front saves time re-working a bad structure. They save often and keep backup copies so they can revert to previous known working positions quickly.
Master modellers have rules of thumb such as “Put constants in their own cells”, “Create a structure that is easily auditable”, “Create formulas with relative and absolute addresses to make block copying easier and safer”. They know the built-in functions so they can avoid re-inventing the wheel and producing wobbly wheels.
A “moving pairs” team structure helps such techniques get passed around quickly, spreads knowledge across teams, keeps thinking fresh, and avoids bottlenecks. When a new person joins a task, the questions that they ask to get up to speed show what needs to be clarified or simplified in the task, which ultimately makes the system easier to maintain.
In some financial industry environments, where there are performance bonuses or competitive pressures, pair working may be unacceptable to the users even though it is done in risky conditions of intense time pressure, for example in merger and acquisition analysis. In that case, the managers need to decide to what extent the spreadsheets are personal assistance tools or corporate assets. Look at whether they always disappear with each project or person, or whether they are handed on and re-used.
Test early and often
End-users rarely have any concept of testing except the rough “does it look right” question. In that case, the use of a spreadsheet to obtain the answer they have already decided upon might be regarded as mere political show to obtain apparent support.
An answer that is obviously different from expectations might either give them some business insight (which is often the purpose of a projection from present knowledge) or they might decide that their pre-judged answer has to be right and will bend the model to fit.
A slightly different answer, unfortunately, may be accepted as verification of their intuition – even though both the model and their intuition may be wrong and a nasty surprise lies in wait for a business decision based on such an output.
When users say that they do not know what the expected answer is – that that is why they are building the spreadsheet – then there is still a need to perform some parallel calculation in order to provide some kind of independent check.
Good practice, then, is to build in tests from an early stage. The simplest and most common test used in accounting models is to check that totals across and down agree. More sophisticated tests use IF functions to display a message beside an output cell if some result is not as expected.
Input cell validation can be specified in Excel to check for typed-in values being whole numbers or being within a given range. But it is easily defeated by copy-and-paste operations, so it cannot be relied upon.
VBA can be used to program in test suites that perform basic batch tests such as clearing all input cells, and setting them all to known values and checking a final result cell.
Debugging and Maintenance
Tools are available to audit spreadsheets for unusual patterns, such as numbers or inconsistent formulas appearing in the middle of blocks of formulas. They can be used to compare workbooks and report changes other than simple expected data changes. Excel has built-in auditing tools which now include a multi-level formula evaluator that permits tracing the precedents of a cell to see where the data comes from; and the dependents to see where a given cell is used.
Refactor
Conventional methods associate testing with each phase of the process – the so-called W-model where the V-shaped model from development to delivery is shadowed by a parallel V of testing.
Agile methods advocate test-first design at the coding stage. That is, a test is written first; and is immediately run in order to verify whether it fails as it should because the code is not yet written. Obviously, if it does not fail, then the developer knows immediately that their test is flawed, or that what is being tested has more functionality than they knew about!
As spreadsheets grow, users become afraid to touch certain parts of them, and they have to add notes to say things like “if you change this, be sure to change that other cell as well”. Such sprawling monsters are going out of control, so good practice here is to spend some time to simplify the model. Users are reluctant to spend time on clean-up because there is no immediate reward - the model simply (or perhaps I should say ‘at best’) works as before. They need the longer-term view to understand the future benefits. There is a curious habit of treating models as being ‘once-off’ when in fact they persist undated through skeins of time.
Spreadsheets usually “just grow” by incremental development. They start off as simple tables used to summarise data for reporting, and users then extend them as their knowledge of the business develops.
A large system developed or evolved from multiple spreadsheets is much more like a conventional systems development project, so there is a need for IT to offer support with their experience of integration testing.
Cleanup
Examples of clean up work include extracting constants into their own cells; adding cell comments; documentation worksheets; worksheet protection with a password; range names to make formulas self-documenting and readable; colour mapping, currency and quantity formatting styles and standards to aid comprehension and alert users to the meaning and usage of cells and areas; breaking long and complex formulas into separate cells; naming conventions and modular structures in VBA code; ready-made cross-tabulations, charts, and scenarios; restructuring into a logical flow of calculations that read top-left to bottom-right, front-to-back; provide batch testing macros; save commonly used models as templates to help good structures persist.
Avoid gold-plating
Agile developers avoid the “I’ll just do this too as I’m at it” syndrome, adding functionality before it is scheduled. They believe that these guesses at what might be needed in the future are not justified, so they should just add what is needed for today. Those developing for themselves are probably better placed to judge what they will need in future, but the advice of starting with a simple sound structure is still good. The aim is to always have a working model, even if it is initially at a low level of resolution. That way, if the schedule is suddenly curtailed (surprise!) they at least have a usable snack rather than a half-baked heap of spaghetti.
This drive towards simplicity starts with the imperative “Do the simplest thing that could possibly work”. It's always faster and cheaper to replace complex logic now, before a lot of time is wasted on it.
Simplify Communications
Much of the delay in conventional project delivery comes from the need to co-ordinate people who are working with fluid and shifting requirements from business needs with those who attempt to encapsulate the last specified design in working code. Where there is not constant interaction to set and reset expectations, acceptance criteria, scope, priorities, and schedules, then customers and developers drift out of alignment.
In agile methods, this interaction is brought to weekly or even daily interaction. In end-user development, there is no delay but therefore there is often little reflection and pause to think how things might be done better.
Our focus is on users who develop spreadsheets for themselves. Those who develop for others are really getting into professional services and need to adopt a much more disciplined approach to producing robust and secure products. Where this is done by IT professionals, well-known standards can be applied such as requirements analysis, design before development, testing, and reviews. They also apply specific techniques such as password worksheet protection to reduce errors from accidental changes in data or structure.
What to do with exploratory solutions
A spreadsheet is often used to incrementally explore tough technical or design problems to reduce risk or get a better estimate of how much work is going to be needed. End-users normally hate to throw away work; but it is often cheaper in the long run to throw away the prototype and build a more robust and scaleable solution in a structured modelling language or around a database.
Conclusion
It is possible to tame the end-user development monster by careful feeding with good advice and making use of training and experience to assist people to do their work with fewer delays and problems.
Copyright © 2003 Patrick O’Beirne, Systems Modelling Ltd.
http://www.sysmod.com/spreads.htm
Keywords: End User Computing (EUC), End User Development (EUD), eXtreme Spreadsheet Engineering (XSD), eXtreme Programming (XP), Spreadsheet Modeling.
References:
[1] http://www.eusprig.org European Spreadsheet Risks Interest Group fourth annual conference, 2003.
[2] http://www.gre.ac.uk/~cd02/eusprig/2001/AIB_Spreadsheets.htm “The role of spreadsheets in the AIB/Allfirst currency trading fraud” Ray Butler.
[3] http://www.theregister.co.uk/content/67/31298.html TransAlta’s $24-million spreadsheet "clerical error" article in The Register (UK)
[4] http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm “What We Know About Spreadsheet Errors” Raymond R. Panko University of Hawai’i
[5] http://www.extremeprogramming.org/rules/pair.html “What is Extreme Programming?”
Related pages: Home Spreadsheet Consulting
provided subject to the terms and conditions below.