I have been a self employed software developer specialising in Excel and VBA since 1996. Before that I did manage to hold down the odd proper job or two.
My best (and last) permanent job was for a Leeds based brewery. As a Yorkshire lad, Tetleys would have been ideal, unfortunately I ended up at Bass (You would have to be a Yorkshireman to understand!). But it was not all bad news, for a start I had a superb boss, and also Bass introduced Hooch, Starapramen and Carling Premier- all great ways to get drunk on expenses (officially called ‘sampling’ – damn hard, working so late on Friday and Saturday nights!). Overall it worked out brilliantly.
Anyway, back to the point, my great boss, and what made him so great.
He educated me in the art of checking my work.
Every time I brought him a new report, or a new version of a report,
“have you checked the numbers?” he would ask,
“Yes” I eventually learnt to say,
“what to?” he would ask, and I would explain where and how I checked it. I don’t remember him ever checking my work himself, although I’m sure he did. (trust, but check!)
His approach was, you’re a professional, its your work, here’s the consequences if its wrong, you take responsibility for it. Of course the odd blunder still slipped through, but I don’t think I have worked many places since with such a good quality record. Our customers had a high level of confidence in the information we gave them to manage their business.
Now I’m not a massive fan of testing, or at least not a fan of having a late testing phase, or a ‘this will get sorted in testing’ type of attitude. But one secondary effect of knowing I was going to have to test my work, was to make sure my work was easy to test. Now, work that is easy to test I am a huge fan of, I hate complex, I hate ‘clever’, I love clear and simple, step by step.
I still think certain types of testing are a bit like locking the stable door after the horse has long gone. In mainstream software those companies that follow a waterfall methodology (there still are some, someone even thought this was real: http://www.waterfall2006.com/), leave themselves wide open to quality issues that do not surface until very late in the project. What if you turn up on test Monday 3 weeks before delivery, only to find that what has been developed is almost untestable?
More modern approaches to software development favour early regular unit tests, and in fact this is how I tend to work. I don’t go the full test driven development, but I do decide the test, perform the operation then build and run the test.
In my experience spreadsheets that do not have quality and testability built in are very difficult and very expensive to check for reasonableness, never mind to test thoroughly.
I believe the target should be in-built quality. The vehicle to get us there may be testing or inspection, and I think testability is possibly the same as quality, its certainly a part of what I see as quality.
I used to think developer skill was critical, now I’m beginning to think as long as they have the ability to build simple, testable models, and confidence in line with their abilities then that’s enough. There are other benefits to using highly skilled developers but I’ll carry that forward to another post.
One recent client implemented peer reviews on all important spreadsheets. That is an excellent idea. I don’t think the test is the key part, I think building a model knowing someone else will test it is the critical factor here. Anything spotted during the review I would see as a bonus.
Do you agree?
What do you think are the key factors in reducing the risk of spreadsheet errors during development? (lets cover the rest of the lifecycle later).