Cell mate: does familiarity breed contempt?

Spreadsheets are used for probate, client databases, schedules and all sorts. They are prevalent and mostly understood but there are pitfalls to consider, says Mick Jones

To paraphrase Julius Caesar in Shakespeare's play of the same name: "Yond spreadsheet has a lean and hungry look. They think too much. Such things are dangerous." Spreadsheets can indeed be dangerous. But are they a disaster waiting to happen or the best management tool there is?

Spreadsheets used to be a great comfort. I am old enough to remember the first spreadsheet computer program, VisiCalc, in the late 70s. The major breakthrough was the ability to separate logic from data. For example, cash flow forecasts took a major leap forward by asking 'what if'. This was very powerful. What if sales fall by 10 per cent? What's the impact on cash flow? The ability to do this spawned a huge industry culminating in Excel, which now dominates the market via Lotus 123 and others.

So what are the issues with spreadsheets? In some ways, their familiarity may be part of the problem. We can all do something with a spreadsheet and probably use more of its functionality than other Microsoft Office elements, such as Word or Outlook. Before we send the jury out to find the spreadsheet guilty, there are three key points to consider: the risk of divergence, the myth of speed and maintenance cost. In essence, they are hard to control, cost a lot in time to maintain and, therefore, are not the slick answer to easy-to-access functionality.

• Divergence risk
  Where spreadsheets are used by more than one person or shared, version control is important. The logic must be maintained and if it is not derived from a single source, problems will occur. For example, imagine you have built a spreadsheet to deal with probate. It will enable data to be captured and as it is entered, the logic will fill in key schedules such as reports. You disseminate the spreadsheet to a number of fee earners who will all enter the data relating to their own cases. Unless each time the sheet is used you force fee earners to copy from a master format, you will end up with a number of different spreadsheets where the logic may have been changed.

• Speed myth
  We know how to set up a spreadsheet and, to a degree, how to build a model. However, it's the appeal of the simplistic that can lead to the spreadsheet becoming a time-consuming tool. Surveys show that users can spend hours each month of non-chargeable time "consolidating, modifying and correcting the spreadsheets they collaborate on with others and reuse frequently".

• Maintenance cost
  Error correction, even on a single spreadsheet, can be very time-consuming. Mistakes are more common than we may think. According to a survey by Ventana Research, 35 per cent of respondents said they were aware of data errors in the spreadsheets used for the organisation's most important processes.

When will spreadsheets work for me, I hear you ask? If you have a stable but complex model that is only updated by data on a regular basis, it's good - very good. That's cash flow models, monthly management accounts, indeed anything where the model (logic) is stable and is maintained from a single source.

Where you will experience problems is for a flexible model. For probate, every estate is different and you need different outputs in different circumstances. If you achieve this by changing the logic in your spreadsheet, you will potentially have the problems referred to above. You may start with one well-constructed probate spreadsheet but you will soon have as many versions as you have fee earners using it.

Finally, if you are going to use the spreadsheet in such circumstances, make sure your firm has ownership. It's far too risky to leave maintenance to the originator of the spreadsheet - especially if they retired a number of years ago.

Mick Jones is managing director of thewealthworks

He writes a regular blog about technology for Private Client Adviser