This is a simple idea with, perhaps, complex and, from a business perspective, important implications. Nowadays, with the near-universality of spreadsheet use in so many businesses, it has become almost too easy for every user to “develop” a spreadsheet, whether for use only by themselves, by a small team or across the whole business. In fact most organisations have a de facto spreadsheet culture.
But maybe first we need to have a little background and what better than the results from a survey of nearly 1,600 spreadsheet users?
Since I happen to have them handy, we’ll look at the results of the ‘Spreadsheet Engineering Research Project’ (SERP) survey, where some of the results appear a little contradictory, if not downright surprising. Those that most grabbed my attention are listed below:
93% of users are the creators of the spreadsheets they use in work to track and analyse data, determine trends, make projections and develop business recommendations;
76% of the users completing the survey were in supervisory, managerial or executive level positions within their organisation;
93% of users consider that they have extensive experience and some to high expertise;
90% of users never or rarely use more than the most basic functions provided in their spreadsheet software;
91% of users never use any development methodology;
76% of users rarely, if ever, test their spreadsheet models for accuracy;
68% rarely document their spreadsheets – oddly 88% say they spend 0% of their time writing documentation;
49% of users aren’t aware of whether their organisation has any standards and policies applicable to spreadsheet development;
51% are don’t know if there is any awareness of spreadsheet risk in their organisation;
41% don’t know if there is a risk prevention strategy;
48% don’t know who is responsible for risk management;
97% of users rate the results of their spreadsheet models of moderate to critical importance to their organisation.
I’ve deliberately re-arranged the order of these items from the original survey results, so that we can look at them in my preferred sequence. Bear in mind that I will use the words ‘spreadsheet’ and ‘model’ interchangeably for the purposes of this discussion.
The first two items are a good indicator of how spreadsheets are used within a business:
93% of users are the creators of models used to develop business recommendations;
76% of the respondents are supervisory level or higher.
These are the people running your business, and in the main they’re using spreadsheets to do it. All well and good and, hey, if the models work, are flexible, accurate, structured and transparent then both your business and its future are clearly in good hands.
But let’s continue on our short stroll through the results: the next two, taken as a pair, are almost contradicting each other. For the sake of simplicity let’s consider the first item:
93% of users consider they have at least “some to a high level of expertise”.
Now this clearly suggests that most of the respondents would consider themselves to be ‘advanced’ spreadsheet users. Yet, from the next item we discover that:
90% don’t use more than the most basic functions provided by their spreadsheet software.
Interestingly this is also borne out by data collected by Microsoft in their Customer Experience Improvement Program (CEIP). The top 5 most-used commands?
So although our users consider themselves ‘advanced’, the evidence should leave us a little wary of allowing this self-definition. Perhaps we need to look at how we should be defining these levels of expertise, so let’s consider this now, specifically Types of Spreadsheet Users (Jones, Blackwell, Burnett 2003):
Managing & printing lists of information;
Very simple formulae, e.g., =SUM(A1:A10).
Understand spreadsheet paradigm fairly thoroughly;
Mastered pre-requisites, tackle more ambitious and long-lived applications ;
e.g., mastered absolute and relative cell reference.
Understand Visual Basic and can write user-defined functions and procedures;
e.g., mastered array functions.
Righty-ho, it seems from this that our self-defined ‘advanced’ users are more than likely ‘basic’ users, possibly ‘moderate’ but, in the majority, are definitely not ‘advanced’.
Not necessarily a big problem in itself, but worth keeping under consideration for the time being.
And now onwards, to look at the next three items:
91% of users use no methodology;
76% don’t test that their spreadsheet works correctly or the results they get are right;
68% don’t document how it works.
In fact, on the basis that 88% stated that they spend little or none of their time on documentation, let’s amend that upwards and say instead:
88% of users don’t document their spreadsheet models.
It’s now starting to look as if we are on shaky ground here: no development methodology, no testing, no documentation. So Steve suddenly goes off work on sick leave/goes on holiday/leaves without handing over his work… is there anyone who knows what he was working on at the time, how his models are put together, or even how to check if they’re right?
I don’t know about you, but I’m starting to worry on your behalf about the risks being taken here.
And since we’ve got onto the subject of risk, let’s look at the questions that relate to this. I’m going to generalise a little here, but at a rough approximation:
50% of your users don’t know if there’s any risk awareness within your business, don’t know if there’s a risk prevention strategy and don’t know who’s in charge of it even if there is one.
That pretty much defines a 50/50 chance that there are unknown risks being taken with your business.
Still feeling entirely confident about the decisions being made on the basis of these spreadsheets?
97% of users rate their spreadsheets and models of moderate to critical importance to their organisation.
Or, if you’d rather, no matter what other software you are using for modelling and business decisions the chances are your decisions are being influenced by the data coming out of Steve’s spreadsheet – or at least your own organisation’s equivalent of Steve.
To summarise our findings: for every major decision taken in your business, you may as well just flip a coin.
Let me borrow a quote here: “Oh. Dear.”
A final word
You’re making decisions in your business today based on spreadsheets used throughout your organisation. Two choices: don’t bother, take the coin out of your pocket and flip it – else give me a ring or drop me an email.