Spreadsheet testing

I have been asked by a couple of people to post about testing/reviewing spreadsheets, so we can discuss in more detail. So here it is:

One of the services codematic (ie me!) offers is to fix and maintain anyones spreadsheets no matter what state they are in. I charge a day rate, if its a total ‘frankensheet’ (credit to Rob Bruce for the perfect (polite) description), it takes me longer to sort it out so the client pays more. Nice spreadsheets are cheaper to look after, and sadly much rarer!

I presented at Eusprig last year about the approach and the tools I use to get to an estimate of how many days (or years!) it will take to fix/change the spreadsheet. This is just a review, and its pre-sales, so its not any kind of audit, and I take a fair chunk of commercial risk that my estimate is miles out and I’m working for peanuts. But hey, thats being in business, right?

The paper is here, the slides are here.

There is a page all about testing spreadsheets on codematic here. I’m not going to pretend I do all that stuff every time, but I have done pretty much all of it somewhere and I’ll do a fair few of those tests on any spreadsheet I build, or review, or take over from someone else.

What testing do you do? Any additions to (or deletions from)  that list?

Also, under what circumstances do you test? have you ever tested someone elses work and found errors and then had hassle sorting out whos going to fix it and whos going to pay? Do you protect yourself from this? and how?

Personally I work on an enagement letter basis, that is less than 2 sides of A4. This basically explains that after the product has passed user acceptance testing its their baby. I tend to fix any problems in my own stuff, but if they are using it in some wacky unexpected way that really represents an undisclosed requirement, then I’ll charge. All my work is based on relationships so its fairly flexible. Clients can do what they want with stuff I have worked on once delivered, I’ll keep repairing it and charging if they keep breaking it. If they were really bad I would move it into a compiled component. How do you set things up?

I dont do legal, and I dont issue copies of my insurances, these things do not represent the sort of working relationship I look for with clients. I walked away from a decent sized deal last year because this crap turned up at the last minute.

How do you operate? I suspect its quite different in different countries. How do you manage responsibility for potential errors as you transfer systems to and from clients and other consultants?

If I’ve missed any questions you wanted to ask around this subject please add them as comments.

As usual any other thoughts in this area very welcome.



10 Responses to “Spreadsheet testing”

  1. Dennis Wallentin Says:


    Clarity and communication are the keywords for me and it includes terms of business, liabilites, pricing and what the outcome of this kind of projects will/can be. In my experience we should never be afraid of saying a clear ‘no’ to clients, especially when it comes to this kind of projects.

    When I first meet the client I got following questions with me:
    # Why does the present solution exist at all?
    # Why does they want a professional help with it?
    # If the client is prepared to give time to the project themselves?

    If the client can’t give straight answers on these questions then the ‘black box’-syndrome probably exist and it may difficult to deliver what they want (or believe what they want).

    Another syndrome that may or may not exist is the ‘don’t touch my baby’ which also has an impact on my decision to accept a job or not of this kind. This is common when some manager has decided to hire an external consultant where the solution has originally been developed by a co-worker who is then instructed by the manager to participate in one or another way in the project.

    I use one A4-page for agreements / suggested agreements of this kind of projects.

    I prefer to document present solutions in a Word document that I can easily convert to a PDF document. It enables me to send it to the client if wanted/required.

    I usually work with corporates in the manufacturing industry which also is reflected in the ‘toolbox’ I use:

    – A command based tool to document the structure of the workbook(s) where the output is presented in a diagram in Word (written in Delphi by a local friend)

    – A VB 6.0 tool which provides the technical aggregated data (formulas, links, connections, code) also presented in Word.

    Two commercial tools:
    – FastExcel
    – vbaCodePrint

    I must admit that I haven’t noticed until now that You got a commercial tool available! It seems to have some nice outputs that can be useful.

    Kind regards,

  2. Simon Says:

    Excellent point about if the client has time to be involved. I know this, I know I know this, yet time after time I get caught out with lack of access to client.
    I agree with everything else too, and it sounds like we have similar tools, but I prefer my outputs in Excel so I can do further analysis.
    I often find when taking over from an internal person they are secretly glad to see the back of it, so they can get on with the rest of their life. I have certainly seen the ‘dont touch my baby’ stuff too though.

  3. Dennis Wallentin Says:


    I’ve done and been there myself too many times in the past…

    In my experience there is a positive correlation between client’s engagement and the outcome of the projects.

    I use a factor of 30, i e if a project needs 100 hours from me then 30 hours are required from the client. I try to add it to the written agreement which may later on be used to get the client ‘onboard’.

    There is also another benefit of it, we can get an indicator of the TOC (total owner cost).
    + Estimate the cost / hour including the overhead for the employee(s)
    involved x the number of hours.
    + My price / hour x the number of hours.

    The TOC can also be for evaluation and discussion of the project itself.
    Another benefit of the above is that the cost for me can be reviewed of of the internal costs.

    Kind regards,

  4. Rob Bruce Says:

    Just a note to say that ‘Frankensheet’ does (in my terminology, anyway) have a specific meaning. That is to say, a Frankensheet is one thrown together with little design, consistency or accuracy considerations from bits of other spreadsheets.

    A classic example from around a decade ago was from my time in mail order marketing. At budget planning time, the accounts dept. would distribute a standard template to all departments for them to fill in with their budget expectations for the following year. These would be returned often with logic and structure altered, to reflect what was always claimed to be the ‘special features’ of each department. The whole lot would then be manually merged into a Frankensheet by copy/pasting, along with other stuff such as standard lookup tables from central accounting models (also copy/pasted – formatting and all! – where some poor junior accountant with the help of the corporate modelling team would have to make sense of it all (and subsequent changes in scope and focus) by creating the spreadsheet equivalent of a bowlful of programming spaghetti.

    This was all so easily avoidable, obviously. But, here’s the real lesson for us, I think, the corporate reaction to this annual fiasco was not to decide to build better, more disciplined, more professional spreadsheets, but to bypass them entirely and attempt to construct a bespoke corporate accounting system using Oracle and PowerBuilder.

    I’ve seen this time and again where the reputation of spreadsheets as untrustworthy has lead to them being abandoned as a development platform, which is really not good news for people like us.

    BTW, the super accounting system failed, as it was bound to do, because the problem was with corporate culture, not with the tools.

  5. MikeC Says:

    Rob – you have a real knack for coining terminology that fits perfectly. “A bowlful of programming spaghetti” is yet another beaut that is sure to become a colloquialism pretty quickly!

    As I don’t work on a consulting basis, but am employed directly within a BI area, I rarely have the option to say “no”, or even “this is what I need from you”, regardless of the task involved. Trying to sort out Frankensheets that have grown organically with the input of three or four people over a period of years, whose total formal experience and training consists of once having picked up “Excel Bible 2000” in WH Smiths – and then putting it down again – is something that I generally end up doing by the easiest method available….

    I find out what’s actually wanted from it and start again from the ground up, using the original version as a guide only.

    Despite the final product working more effectively than the original that I’m handed, you can imagine this doesn’t make me many friends, due to similar situations to those described above by Dennis. I also frequently find that when I approach the original builder to find out “why this bit does that” – they have no idea that I’m working on a replacement…

    So, the next time you guys turn down work because it doesn’t meet your exacting standards, spare a thought for the poor saps like me who deal with nothing but!!!! =;-)

  6. Harlan Grove Says:

    MikeC raises an interesting point: qualifications. Myself, I had no formal college programming coursework, though I did attend a few evening sessions on programming basics for science and engineering students. After college, I’ve taken three evening courses on programming, but nothing directly related to spreadsheets. I did read the How-to manual that came with Lotus Symphony, read most of the VP-Planner manuals, read Lotus magazine while it was still published, and subscribed for a brief time to the Cobb Group’s Excel newsletter. But that’s it. No formal certification.

    How many spreadsheet developers have MOUS, MCP, MCSD etc certification?

  7. MikeC Says:

    An ACITP in VB6…. but I’m about to start studying for an MCDBA because I’m bored with what I’m doing – though the £4,500 (near as dammit US $9K) price tag is a little daunting. But for ROI purposes, looking at what’s out there at the moment, I think (read: “HOPE??“) it’ll prove worthwhile.

    On that note: can anyone recomend UK-based suppliers for that sort of thing? I’m currently leaning towards Advent Computer Training, but I have a few more companies to see.

    No Excel qualifications or formal training at all. Not one bit. Pretty much only started using it beyond tracking my banking etc about 4 years ago, and then mainly as a platform for VBA, and invested in a couple of reference books to get me started, as well as spending a LOT of time on Excel-based forums etc…

  8. Spreadsheet training and qualifications « Smurf on Spreadsheets Says:

    […] https://smurfonspreadsheets.wordpress.com/2007/04/04/spreadsheet-testing/#comments […]

  9. Simon Says:

    I’ve just posted about training. Great discussion.
    I’ve used trainingcamp.co.uk because they offer intensive, email me direct to discuss if you like. I’d be tempted to look at one of the offshore intensive ones. I’ve also used QA-IQ and I’m happy with the courses I’ve done with them (I’m on one next week).
    Let us know how the MCDBA goes, I have been tempted by that

  10. PowerBuilder Says:

    Hi dude,
    I read your blog,This is a wonderful blog.I was able to get the
    information that I had been looking for. Thanks once again.
    PowerBuilder Application development

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: