Reflections of an Ancient Boater—The curse of the spreadsheet
IN DAYS gone by I used to turn up at engineering meetings with an A4 pad with a load of figures scribbled on the top page. This was known as a "back of a fag packet" type of document, writes Ralph Freeman.
Contained on the sheet of paper were maybe rough calculations, estimates of times and materials to complete a project or some figures estimating the performance of a particular device. Because of this informal method of presentation, all those present understood that the figures mentioned were at best rough calculations, at worst just a guess. It's always easy to spot a guess by the way—they tend to have lots of zeros in them!
Highly suspicious
A cost of say £100,000 is highly suspicious as a looks like a guess. Closer inspection may find the error in that figure could be as much as plus or minus £50,000! A figure of £92, 764 looks to have more credibility in my book. Someone may be quoting the cost of a similar job in the past or may have done a detailed analysis taking several weeks? Who knows, but a guess is less time consuming I know that much and now that seems adequate in most cases.
Nowadays a similar meeting would involve numbers on spreadsheets and probably a PowerPoint presentation too. The problem as I see it is this:
Regarded as fact
'Back of a fag packet' data was rarely circulated outside a small number of people who would understand how that data had been compiled and critically, how much confidence to place on those figures. Not so with a spreadsheet! Once a number (maybe a finger in the air guess) appears on a spreadsheet, it is highly likely to be regarded as a fact by people reading that spreadsheet and not be questioned.
This is especially true when the said document is stored on a company computer server somewhere. This is what I call the 'Curse of the spreadsheet'. In an instant an off the cuff guess becomes a solid, accurate figure!
Canal project costing
Just imagine for a moment an engineer/project manager at CaRT is asked to produce a estimate of the critical assets that need maintenance in the next 12 months in his/her region, for a meeting in a weeks time. Now the items concerned are done by outside contractors, it is unlikely there will be any direct, hands on, costing experience inside CaRT. Why should there be? The procedure is to ask the contractor involved to prepare a quote to do the work. However, that is not often available in the time-frame allocated, besides quotes are costly to produce so contractors are loath to produce them willy nilly. How the contractor comes up with a final figure is of course a commercial secret! So the CaRT project manager will (maybe) look at similar jobs in the past or guess a figure. To be on the safe side they will probably add in a margin of say 20% to cover unforeseen eventualities?
So what is presented at the meeting is a spreadsheet, with many rows of figures, usually ending in many zero's, with a total at the bottom. It is almost certain some of those numbers will be added to the boss's spreadsheet, and so it goes on. At the end someone announces it will cost £150,000,000 next year to maintain the canals next year and they have the figures to prove it!
What is missing?
Facts! I suggest nearly all the data presented on that final spreadsheet produced by the engineering director and presented to the board will be, almost certainly, rough estimates or outright guesses. Who, reading that spreadsheet will know that the figure of £200,000 to repair one side of say accommodation bridge 28A is ridiculous as it cost a recently constructed marina only £80,000 to build a new one to do a similar job.
Note the figure of £80,000 was a 'fag packet' figure but produced by the manager who oversaw the construction of the said marina. However, at least he saw the invoices for the labour and materials used and asked if I wanted a more accurate figure. See the difference?
Coronavirus proves my point
As final proof of the dangers presented by the 'authority' (sic) of a spreadsheet, one of the major failures of the Track & Trace system is that it is based on a spreadsheet. (Yes really, Dell Boy lives!). Evidently the expensive consultants employed didn't know the proper software tool to use is a database, not a spreadsheet. Even worse the originators of the spreadsheet put the individuals data in 'columns', not a 'rows'. An Excel spreadsheet can contain 1,048,576 rows, but only 16,384 columns. (Note no zeros in these numbers quoted!)
On bad months if more people than 16,384 people contracted coronavirus, the later ones 'fell off' the spreadsheet and became invisible! It took several months before this was noted.
Curse of the spreadsheet?—I rest my case.
[Today's announcement that the borrowing this year relating to coronavirus will be 395,000,000,000, certainly gives credence to Ralphs' point!—Editor.]