Spreadsheet lifecycle (long)

A few comments have touched on parts of this, so I thought I would post what I have seen, and what I think could be improved.

Many important commercial spreadsheets I see seem to go through the following lifecycle (at least once):

  • A requirement becomes apparent
  • Previous work is adapted if possible
  • If not a new model is built, rarely completely from scratch
  • The results are reviewed for reasonableness by the business person who developed the model
  • Its used live, usually with gradually reducing scepticism
  • Other users feedback corrections and enhancements
  • Eventually abandoned as business needs change

This is fairly close to the standard Systems Development Life Cycle but a few elements are missing to my mind.

There is often very limited effort around developing useful requirements, and rarely any initial design. There is also rarely any sort of conscious technology choice. It was always going to be in Excel. A bit of effort in initial design may often highlight the fact a spreadsheet may not be ideal. I remember one Excel based unit costing system that was never going to work. A simple migration to Access and the whole thing was almost trivial. And not a 3 line multi nested array formula in sight. I accept the whole skill set argument – but that is why short term flexible consultants and contractors exist (and training courses).

We are always encouraged to reuse previous work, and in many cases reuse is a Good Thing. But what if you didn’t do the necessary due diligence to check that what you are reusing is both correct and appropriate? What if by dragging in old stuff you bring in a couple of hundred broken names and a load of useless styles, or some crazy VBA (did I ever tell you about the guy who trapped ctrl +C to trigger some VBA to delete a load of stuff on the active sheet, when I did a copy on his machine it killed my spreadsheet).

Reviewing the results for reasonableness is unlikely to generate much valuable insight. If you ‘check its ok’, you’ll usually find it is, if you look for the errors or the weak point you’re far more likely to find something worth correcting. I prefer to test with meaningless data so I can focus on the logic. A final review does no harm, unless it is in place of proper testing. Proper testing involves people other than the developer.

Using your customers as beta testers is fine, as long as they know it. Doing so without warning them is likely to reduce your credibility. I often worry that consumers of spreadsheet systems are a bit gullible, I prefer to have a developer pick my work apart as they tend to be more thorough, a real tester might be even better. As long as its formatted nicely and not too blatantly wrong, customers seem to have little difficulty accepting spreadsheets they are given.

As ball park figures, the stuff I work on takes 2-3 months to develop, test and deploy. Its lifetime is 2-5 years roughly. There is a ton of info available that is useful for those early months, but almost none that covers how to manage a live spreadsheet based system (a phase that is probably 10 time longer than the development). I would suggest that most of the real world errors are caused by poor version control causing a correct but inappropriate version of a workbook or worksheet to be used. Abandonment (ie leaving on the network, but not using) rather than graceful retirement (archiving, maybe leaving a shortcut in the original location) means there are normally more wrong versions to choose from than correct ones. Anyone else ever run last years data set with this years balances to produce almost-believable nonsense? I think a decent Sharepoint implementation would go a long way to solving these version issues.

I tried to warn one client about having multiple copies of very similar spreadsheets, he cut me off with ‘Disc space is cheap, I don’t care about redundancy’. As I was explaining the reconciliation nightmare that would follow, and the unnecessary risk he was carrying, he was already out the door. (Maybe I should have gone on a Dale Carnegie Win Friends and Influence People course?)

[I keep wondering about productionising the tatty version-compare tools I use as a commercial venture. Would anyone buy such a tool (if it was any good of course!)? would you prefer an add-in or a standalone exe? Would you swap a relative for one, or just want to swap some (secondhand) chewing gum? Anyone using something similar already? I’ve seen a few on-line, but they don’t seem to do what I want.]

Anyone else had similar experience? Think I’ve missed anything? Think I’ve got it wrong? Anyone got a better approach than warnings that fall on deaf ears?

Cheers

simon

Advertisements

15 Responses to “Spreadsheet lifecycle (long)”

  1. Marcus Says:

    Hi Simon,

    I’ve often seen spreadsheets start life like this…

    Fred in accounts got fed up waiting for the monthly whatever (or asked for a system change which was denied) from IT so to replicate the process/data/solution he created a spreadsheet.

    Over time this grew to become a complex beast with nifty macros, many of which replicated innate Excel functionality. Understandably it was undocumented – hey, Fred knew how it worked, and was safely saved on Fred’s C: drive so no-one could accidentally delete it.

    The spreadsheet evolved to the stage where the business or department depended on it (mission critical). Eventually some one in an authoritive position caught wind of what was happening and realised the true nature of the situation and did something about it. Enter the contractor. Standard SLDC follows from here.

    As to life span, I know I’ve got some spreadsheet solutions I developed in 2000 which are still in daily use. Another spreadsheet model I developed in 1999 incorporated an audit trail written to a worksheet dedicated to this function (tsk, tsk Marcus – what were you thinking?). I received a support call about 3 years ago that the audit trail failed to work. The reason? No one had ever used the archive functionality which appended the audit trail to a CSV file and cleared the slate – they had run out of rows on the worksheet.

    “A simple migration to Access and the whole thing was almost trivial”
    This I’ve seen time and time again. Relevant to your linked workbooks post I’ve regularly seen a pyramid of spreadsheets which aggregate values to the next level, with a single summary spreadsheet at the top of the hierarchy. The business simply wanted to present the same reports, with varying levels of aggregation, to different audiences in the business. A database (relational or OLAP) using Excel as the reporting layer would have solved this complexity easily.

    Cheers – Marcus

  2. Simon Says:

    Marcus
    Good point about the IT refusal often being the trigger! or what I have seen recently – quoting an outrageous transfer price.
    as you say – enter the contractor…
    cheers
    Simon

  3. Paul C Says:

    Hi Simon,

    Good article and very relavant in my day to day affairs!
    The example you give of the guy trapping ctrl+C to trigger some VBA scares me and is an excellent example of the pitfalls of Reuse.

    On the subject of useful requirements – I am reminded of a project I was involved in. Implementation of a new intercompany solution brought up the requirement for a tool to split upload files into separate components. This was as far as defining requirements got – the managers raced off to get someone to “knock something up in half a day”.
    Inevitably it was in Excel and inevitably it was far from perfect. Our Canadian users on 2000 were unable to access one of the libraries used. For everyone else the tool did not even do what it said on the tin. 3 versions (and a trip to Montreal) later and somebody suggested maybe we should define the requirements properly….

    Cheers – Paul

  4. Simon Says:

    Hi Paul
    So you’re saying the dev managed to get a weeks ‘work’ fully paid in Montreal – whats bad about that??
    Seriously though – good example on requirements, must get that one on a powerpoint somewhere!
    cheers
    Simon

  5. Charlie Hall Says:

    To answer your last question, I am interested in a version control tool – although I am not far along in defining my requirements. I have used Excel’s track changes, but the limitations on vba seemed to reduce it’s attractiveness. I like that a tool would not add to the size of the file (which would mean external might be better) but would need to be integrated well so the changes could be accepted or rejected – although that may be two tools. I currently use a combination of compare tools to find the differences.

    –Charlie

  6. Marcus Says:

    Hi Simon,

    I just wanted to highlight that the ‘IT refusal’ usually occurs to constraints that have been placed on the IT department. Often they haven’t been given the budget or resources (human or otherwise) or they lack the skill set to attend to the request. Or also commonly, they have such a long list of ‘high priority’ tasks and projects, the business would have to wait too long for their request.

    I’ve yet to see an IT department that preempts the business. The business is always moving faster than IT. As a result, the IT dept is always playing catch-up. Which is great as it means that there will be a place for me in the foreseeable future as I typically work on projects for the business not IT.

    What I’m experiencing more though is that the business is becoming more aware of IT compliance requirements such as development standards, documentation (technical & user) and the like which is a step in the right direction to help manage the entire spreadsheet SLDC.

    Cheers – Marcus

  7. Bob Phillips Says:

    … or also, because the business have gone away and built something themselves, or contracted a build, without involving the IT departement, and then expect IT to come and fix it. Too often the IT department are on a hiding to nothing, thye get slated as being obdurate when they point out the fallacies of a particular apporach, and then slated because they don’t want to pick up the pieces.

  8. Simon Says:

    Marcus and Bob
    You’re right, its too easy to kick the IT department.
    In many orgs the head of IT reports to the CFO (or FD in the UK). This almost inevitably leads to IT being seen almost solely as a cost. Those orgs that have a CIO that reports to the board directly, not via finance, usually have a more strategic view of IT. These orgs are more prepared to invest, and not so quick to cut headcount to the bone.
    I see spreadsheets as a superb prototyping tool, and the most useful ones should get migrated in due course to something more robust. Barry Pettifor of PWC gave a great presentation at a Eusprig about this a while ago. His view was that a leading edge of business developed spreadsheets was a healthy sign, and the most useful get migrated over time. here is a brief link http://www.eusprig.org/2003conf.htm#Pettifor
    for the full info you would need a copy of the combined proceedings.
    cheers
    Simon

  9. Marcus Says:

    Simon:
    I did a quick search but couldn’t locate a document ‘Getting Spreadsheets Under Control’. Could you advise if and where the combined proceedings would be available. Thank you.

    Bob:
    obdurate – excellent word (admittedly I had to look it up).

  10. Simon Says:

    Marcus
    The combined proceedings (2000 – 2004) are available from Eusprig direct. its about 100 quid.

    Doh! I’ve looked properly and the slide is from his 2005 talk not 2003 – sorry for that.
    very brief summary here:
    http://www.eusprig.org/gre2005.htm
    I’m not sure if its published anywhere you can get hold of?
    In summary:
    spreadsheets fill the requirements gap, production systems are obsolete by the time they go live (because requirements have moved on since they were first specified) users will build spreadsheets to fill this gap.
    I had a quick google around but couldn’t find any more detail. You may be able to buy that years proceedings separately from Eusprig.
    I agree on obdurate, nice one Bob (I guessed first time, but just went and looked it up)
    cheers
    Simon

  11. Ross Says:

    Just gonna drop a few lines (does this make me sound like a rapper?*) on version control / compare.

    I don’t use it, but if I did I would like a solution that I could use across all my documents, something that ideally was built into explore, or was a complete document management system. Maybe there is merit in a tool that handles versioning of SS alone, I’m not sure.

    I think Joel on SW motioned that he used Perforce quite a bit and I very vaguely remember a tool, and God Help me on this one, that was developed by Lancaster university to compare/control SS versions – but I don’t think I will ever dig that up again. There is a addin somewhere that compares one sheet against another, which I have used and thought was quite good – can’t remember the level of details it delved into though.

    But yes I think there would be merit in some tool to do this, I think the hard part would be not repeating what something else already does, and perhaps the key would be reporting the results in a useful way.

    *No, a clumsy cocaine dealer perhaps!

  12. Ross Says:

    A quick google returns about 1000000000000 ss audit and compartions tools! – not too may version controls one though, i think this is what i was talking about

    http://sourceforge.net/project/screenshots.php?group_id=109669

    I must add this to my list of things to do a decent free set auditing tools.

  13. Simon Says:

    Ross
    thx for the link
    lets discuss audting tools, I have a few thoughts in that area too.
    cheers
    simon

  14. Ross Says:

    Yeah, I was think about somthing…

  15. Ross Says:

    (something about audting tools that is, sorry!)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: