eXtreme Spreadsheet Engineering

What spreadsheet modelling can learn from XP
Member Submitted

Extreme Programming (XP) was created in response to problem domains whose requirements change. Spreadsheet development is often done in conditions of intense time pressure, for example in merger and acquisition analysis. The spreadsheet development itself is often used to develop an understanding of what is being modelled, and therefore change is constant.

Extreme Programming (XP) was created in response to problem domains whose requirements change. Spreadsheet development is often done in conditions of intense time pressure, for example in merger and acquisition analysis. The spreadsheet development itself is often used to develop an understanding of what is being modelled, and therefore change is constant.

Key features of XP

  1. Customer satisfaction through frequent visibility of increments of functionality;
  2. Constant communication with customers to set and reset expectations, acceptance criteria, scope, priorities, and schedules;
  3. Risk reduction from test-first development;
  4. Defect reduction from continuous review with fellow spreadsheet developers;
  5. Maintenance reduction through simplification;
  6. Increased productivity through focus on delivery of real needs as they are needed.

It is said that a lightweight methodology has only a few rules and practices or ones which are easy to follow. In fact XP is actually a deliberate and disciplined approach to software development. In a traditional software development environment, users may be uncomfortable with the amount of availability that XP demands of them; they would rather hand everything over to the programmers and complain later. With spreadsheets, the developers are often also the users, which is a big advantage to begin with.

User-defined tasks
The customers write down what XP calls "user stories", a few sentences in their own words saying what the system needs to do for them. These high-level (that is, not at the level of detailed screen & report design) tasks are used to create the acceptance tests–that is, does the system do each of these things? The developer guesses (frankly, a more honest term than "estimating") how long each of these tasks will take to implement. If any one is more than three weeks effort, they should be decomposed into smaller tasks. The actual time chunk, of course, depends on the environment.

Release and Iteration planning
In XP, a release planning meeting is used to creating a release plan, which lays out the overall project in terms of iteration plans. The customer decides what story is the most important or has the highest priority to be completed. In spreadsheet development, this is likely to be a major shift in thought–away from building up pieces to an overall model that answers the big questions first, however crudely. Each task includes its acceptance test, without which it cannot proceed. Therefore, you are always working with a correct model; initially oversimplified, but it gets more detailed as time permits.

The other meeting style that is used is a daily "stand-up" meeting; this avoids wasting time on stultifying meetings where only a few contribute.

Project velocity
XP’s measure of progress is "project velocity"–the number of tasks that were finished during an iteration compared to the total of the estimates that these stories or tasks received. This data is then used in a release planning meeting to re-estimate and re-negotiate the release plan if project velocity changes dramatically for more than one iteration. Don't fudge estimates; that is just a flight from reality.

Another discipline is to avoid the "I'll just do this too as I'm at it" syndrome, adding any functionality before it is scheduled. In practice, these guesses at what might be needed in the future are often not justified. Just add what you need for today, anything extra will slow you down.

Moving pairs
One of the most distinguishing features of XP is "Pair programming". This has the effect of continuous peer review. Advocates [1] 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." Ray Panko [2] in his study of spreadsheet error rates also finds that "[pairs] reduced errors by about a third".

This practice is linked to "moving people around", where changing one pf a pair ensures continuity of thought. People are moved around to spread knowledge across the team, keep thinking fresh, and avoid bottle necks. When a new person joins a task, the questions that they ask to get up to speed show up 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. 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.

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 you waste a lot more time on it. There is a formal process called "refactoring" where you remove redundancy, eliminate unused functionality, and rejuvenate obsolete designs. The process is written up in scenarios where experienced people describe how they make complex logic and structures more simple so that the spreadsheet is easier to understand, modify, and extend. For example, "make sure everything is expressed once and only once", "put constants in their own cells". Such techniques get passed around quickly in the “moving pairs” team structures, lifting everybody's skill level.

Exploratory solutions
XP's approach to tough technical or design problems is to create "spike solutions" reduce the risk of a technical problem or increase the reliability of an estimate. Again a difference from common spreadsheet practice is that people normally hate to throw away work; but XP recognises that spikes are usually not good enough to keep, so it is expected that these experiments are thrown away and a clean solution written in.

Test-first development
XP creates the test before the code. In spreadsheets, there is no code/compile/integrate cycle; there is immediate feedback of any change, so it should be actually easier in that environment. For example, you could add cross-check calculations to verify that the answers are as expected. The users are required to have a set of test data and an expected answer first. Even if the answer is not known (which is the reason for building the spreadsheet), there must be some existing data and some expectation of what the result might be. If the expectation is wrong, that in itself is a useful outcome.

A large system developed from multiple spreadsheets does have a need for integration tests. In this environment, you are nearer a conventional systems development project. Standards.

There is an equivalent to XP’s coding standards: spreadsheet design standards. These are common conventions and practices that make it easier to pick up a worksheet and know where everything is, to navigate easily, and to follow its logic clearly.

Finally, the XP practice is 40 hour weeks–no overtime, no fuzzy eyes, fogged thinking, and wasted time from going down the wrong road from tiredness. Think about it!

[1] What is Extreme Programming?

About the author

StickyMinds is a TechWell community.

Through conferences, training, consulting, and online resources, TechWell helps you develop and deliver great software every day.