Home   >  Business Tools > 

Agile Spreadsheet Development

Many programmers use Agile Software Development, a collection
of techniques that reduce errors and speed software development.
Here's how Excel users can use similar techniques to gain similar
benefits.

Spreadsheets are too easy to use...at least from the perspective of information risk.

Easy spreadsheet development permits an unplanned approach that often results in unstructured models with non-validated data. Easy development encourages too many errors.

The European Spreadsheet Risk Interest Group [1] has held five annual conferences about this subject. As the concern about business exposure from end-user development has grown, each conference has been bigger and seen better-researched papers than before.

The danger is real. Because spreadsheets are easy to change, they lack version and audit controls. (AllFirst fraud [2].) And poorly documented spreadsheets permit easy operator error. (Transalta $24M loss [3].)

This article suggests how certain methods, many of which are attracting more attention by programmers, can help Excel users improve the Excel-development process, save time by making fewer mistakes, and endure less of the cost and pain of bad spreadsheet practice.

Two Heads Are Better Than One

Having two people working together at the keyboard has the effect of continuous peer review.

Ray Panko, in his study of spreadsheet error rates [4] finds that “[pairs] reduced errors by about a third”. XP advocates [5] say “It is counter-intuitive, but two 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 others 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 their work often and keep backup copies so they can revert to previous known working positions quickly.

Master modelers 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 how to use Excel's built-in functions so they can avoid re-inventing the wheel or 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 new people join a task, the questions 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.

Some financial industry environments have performance bonuses or competitive pressures. There, working as pairs may be unacceptable to the Excel jockeys, even though there are risky conditions and intense time pressures -- in merger and acquisition analysis, for example.

In these conditions, the managers need to decide the extent to which the spreadsheets are personal scratchpad tools that always disappear after a project, or corporate assets that are re-used. Bear in mind that many "one-off" creations are handed on and become embedded in the decision making process, precisely because they proved so useful. In such cases, they should really be treated with the same care as other Information System assets.

Test Early And Often

Naïve spreadsheet creators often lack a concept of testing, except to ask, "Does it look right?" However, the mere appearance of correctness may deceive in a couple of ways.

An answer that is expected, 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.

An answer that is obviously different from expectations might either give some business insight (which is often the purpose of a projection from present knowledge) or the spreadsheet author might decide that their pre-judged answer has to be right and will bend the model to fit. That might be regarded as mere political show to obtain apparent "objective" support, and risks being found out in the most embarrassing possible manner.

When you do not know what the expected answer is – that is why you are building the spreadsheet – then there still is a need to perform some parallel calculation 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 when a result is not as expected.

Input cell validation can be specified in Excel to check for typed-in values being whole numbers or 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 automate 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.

Simplify Continually

As spreadsheets grow, people become afraid to touch certain parts of them. You can tell that this is happening when you see notes added to say things like, “If you change this, be sure to change that other cell as well.” Such sprawling monsters are growing out of control, so good practice here is to simplify the model continually.

There is a natural reluctance 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. Take a longer-term view to understand the future benefits. How often have you found supposedly ‘once-off’ models that are actually too useful to throw away?

A spreadsheet often is 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. We normally hate to throw away such work. But it is often cheaper in the long run to treat the prototype as a "mock-up" and build a more robust and scaleable solution in a structured modeling language or around a database.

Spreadsheets usually “just grow” by incremental development. They start off as simple tables used to summarize data for reporting, and are extended as the 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. This article is addressed to those who develop spreadsheets for their own use. 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. This means requirements analysis, design before development, testing, and reviews. Professionals also apply specific techniques such as password worksheet protection and menu reduction to reduce errors from accidental changes in data or structure.

Cleanup

Here are some good ways to clean up worksheets.

• Extract constants into their own cells.
• Add cell comments.
• Document worksheets.
• Add worksheet protection with a password, while making sure that some other responsible person has a written copy of the password.
• Add range names to make formulas self-documenting and readable.
• Set up styles for color mapping and for currency and quantity formatting
• Establish other standards to aid comprehension and alert data entry users to the meaning and usage of cells and areas.
• Break long and complex formulas into separate cells.
• Apply naming conventions and modular structures in VBA code.
• Restructure calculations into a logical flow that reads top-left to bottom-right, and the front spreadsheet to the back one.
• 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.

If you are developing for yourself, you are probably better placed to judge what you will need in future, but the advice of starting with a simple, sound structure is still good. The aim is always to have a working model, even if it is initially at a low level of resolution. That way, if the schedule is suddenly curtailed (surprise!) you 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 a lack of coordination among the developers. Where there is not constant interaction to set and reset: expectations, acceptance criteria, scope, priorities, and schedules, then customers and developers can turn a simple project into a complex one.

In agile methods, this interaction is brought to weekly or even daily interaction. In end-user development, there is no delay but also, there often is little reflection and pause to think how things might be done better.

Conclusion

It is possible to escape the end-user development nightmare by adopting good advice and making use of training and experience to assist people to do their work with fewer delays and problems.

Copyright © 2004 Patrick O’Beirne, Systems Modelling Ltd., Ireland.
http://www.sysmod.com/spreads.htm


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?”