Spreadsheets for Risk Management?
A disaster in waiting!

The following is a summary of a full article. Download Full Article here.

It would appear to be a no-brainer – we need a risk register, so let’s use a spreadsheet. After all, it’s just a list of risks and associated information, so it should be easy, shouldn’t it?

I have lost count of the number of times that I have had to pick up the pieces of this approach, when everyone involved has found that the resultant spreadsheet doesn’t meet the needs of the project/programme/operation/strategic vision.

What has happened?

Problem 1 – The Quicksand

It is remarkably easy to put a spreadsheet together that looks reasonable. That initial ease of development seems to act like a quicksand as the spreadsheet expands, requiring more and more development to produce displays and reports that a spreadsheet is not designed to do. Some large organisations have whole teams engaged in this, rather than on risk management.

Problem 2 – Spreadsheets are NOT free

Unfortunately spreadsheets are not free. Even if we overlook the small licence fee paid for Excel as part of Microsoft Office, someone will spend many hours planning, programming, testing and then – hopefully - documenting and training. Their time will still cost the organisation many thousands of pounds. Maintaining the spreadsheet when things break, requirements change or a new report is required will be an ongoing cost. Just one person doing this will cost at least £7000 a year. Even if they are an expert on risk, spreadsheet programming and maths the development cost simply can’t compete with the level of quality, accuracy and functionality that a commercial tool can provide.

Problem 3 – Bad Maths

Sadly, I have yet to see a risk management spreadsheet that gets the maths right! I have seen hundreds, almost all with very simplistic calculations. Risk management calculations are more complex that most people realise, especially when one considers the differing effects of actions (controls) that a) reduce the chance of a risk occurring, or b) limit the damage if it occurs.

Problem 4 – Spreadsheets don’t share

A spreadsheet is just a file, so shouldn’t it be easy to just let everyone get at it?  The problem is that multi-user access to spreadsheets depends very much on type of spreadsheet, and how and where it is stored - something the user is frequently unaware of. Even then, true multiuser integrity in complex spreadsheets may not be guaranteed. So it is necessary to either have a procedure for managing sequential access to the sheet, or constraining access to specific areas, or running the risk that it may not be reliably accurate.

Problem 5 – What differences?

Understanding what has changed is important at each review. Spotting changes between spreadsheets is tedious, time-consuming and difficult to automate. So inevitably, changes get missed, that may be indicate a problem.

Problem 6 - Spreadsheet variations

Changing a spreadsheet is easy – in fact too easy! I know of one large UK organisation that started with two risk management spreadsheets: One for each project/programme/operational centre, and one to aggregate the results of the first type. Within two years there were 40 different versions of the first spreadsheet type, and 6 versions of the second type – all incompatible! Every spreadsheet had been “tweaked” as various users thought that they could do better. That risk manager was spending half his time manually collating the resultant sheets– wasting his expertise and time.

Problem 7 – Presenting the information

Spreadsheets are great for showing columns of figures and graphs. They are not so good at showing information in ways that we can easily assimilate – priority matrices (heat maps), inter-relationships, summaries – without extensive programming.

Strangely, some large organisations export information from some risk management tools to spreadsheets, to produce complex dashboards in Excel. The effort needed is immense. Why not use risk management tools that have these facilities built in?

Problem 8 – Audit trail?

Historical and audit information is very useful for:

  1. Spotting trends
  2. Seeing who has changed what, when and why
  3. Having a historical record of decision-making - vital in the event of disputes.

Achieving this reliably with spreadsheets is virtually impossible.

Problem 9 – Spreadsheet Security

Spreadsheets are files and files can be copied, leaving no trace. Risks are commercially sensitive and so can be of value to others. So storing risks in spreadsheets is creating a potential security problem.

The Solution – Use the right tools for the job

The solution by this stage should be obvious. Select a risk management tool that supports your projects/programmes/operations/strategic vision. Things to look for should be:

  • Multi-user, encouraging collaboration and capable of supporting any number of users simultaneously, without them having to wait for each other
  • Availability – accessible wherever your users are, preferably on any device that they might be using i.e. PC, Mac, Chromebook, tablet or even phone
  • Security – secure storage, secure transmission links, user security, security of each assessment and update security should all be catered for by default
  • Ease of Use – most users will be using the RM tool occasionally, so it must be easy and intuitive to use
  • Instant reports and displays – no waiting for calculations and a full range of instant reports that ensure that each user gets the relevant information for him/her, and is not swamped with irrelevant information.
  • Consistent calculation – all calculations handled consistently by the RM tool.
  • Configurability – allowing you to use the risk classifications, assessment criteria, impact ranges and chance ranges that are used in your organisation or by your client.
  • Dashboard facilities, if required. However you may find that the tool’s standard displays are sufficient
  • Excellent support, including seamless upgrades (meaning that you don’t have to change any data for an upgrade), prompt help-desk support and customisation services if needed.

And finally:

  • Ability to make sense of your spreadsheets, import what you have done so far, and knock your risk assessments into shape!

After all, you wouldn’t plan a project with a spreadsheet, would you?

Download Full Article 

Take the risk out of choice Book Your Demo Now