How some real-world spreadsheet errors could be avoided using Smia

For some years now I have been monitoring the postings of the EUSPRIG. It strikes me that many of the risks of using spreadsheets would be greatly reduced or even completely removed if spreadsheets would use built-in quality measures similar to those found in Smia.

Obviously, many spreadsheet models fail due to human mistakes. The pity is that spreadsheets allow users to make mistakes that modelling software could have - and should have - prevented automatically.

In this blog I will from time to time post examples of real-world spreadsheet mistakes, and explain how they could have been detected more easily, or even prevented, if Smia had been used as the modelling tool rather than a spreadsheet.

Smia unique features:

  • reduce the risk of making errors when building models,
  • increase the chance of spotting errors when inspecting models, and
  • reduce the risk of introducing errors when modifying models.

Comments

On Feb 4, 2010 John Murell posted the following example of an error that relates to a measurement unit mistake in a spreadsheet.

I have another example where a large engineering consultancy did some calculations on an angled braced girder that had 20 or so supports (it was actually the framework that holds an escalator up). As a first pass they analysed it in Excel using a 'pin jointed' approximation where you assume the joints have no bending strength.

Despite the applied loads being vertically downward it appeared the forces in some of the mounting points were at random angles and even going upwards!

A quick look at the spreadsheet showed that they had entered the angle of the beams in degrees and of course the Excel trig functions work in Radians!

There were some very red faces at the consulting engineers when we told them. Even a most basic check would have found the error.

Regards

John Murrell

The Smia solution to this problem is that the modeller can express angles either in radians or degrees, and the trigonometric functions behave correctly in both cases.

The equations below define sin 45 degrees using three different angular measurements; radians, degrees, and gon (grads):

Formulas Results
x = sin(45 deg)   x 0.71
y = sin(pi/4)   y 0.71
z = sin(50 gon)   x 0.71

 

Jan 31, 2010 John Murell posted the following example of an error that relates to risk of errors that go undetected due to the intransparency of spreadsheets.

A contractor was installing a number of horizontal steel beams near a railway track. The individual measurements for each beam were put into a spreadsheet and there were some calculations of the length to cut the beams allowing for the base and a 4 inch clearance to the train.

Due to an error in the spreadsheet the 4" clearance was not subtracted and the beams were installed on site. No one noticed the error during installation and as a result the first train hit one of the beams causing extensive body damage and frightening a few passengers. Repair costs were in the order of £100k

Lessons

No checks on spreadsheet calculations

No checks on site after installation

John Murrell

Smia cannot prevent people from omitting variables from their equations. But Smia highlight automatically the relationships that are included, making it much easier to spot such mistakes.

Equations Diagram (links are automatic)

beam length = height

clearance = 4inches

height = 5m

Three variables with one link

A visual inspection of the above diagram is likely to detect the missing depency link from clearance to beam length.

By correcting the equation for beam length, the link arrow will automatically be drawn, maintaining the consistency between the view and the equations.

Scenic view signNote that the unit of clearance is inch while the unit of height is metre. Smia permits that beam length is defined equal to height + clearance, and performs an automatic conversion of the different length units in order to compute the correct answer.

Along the lines of the previous mistake, which went undetected due to the hidden and quite unreadable structure of spreadsheet equations, here is another example, posted on Feb 4, 2010 by Patrick O'Beirne.

(AP) - The Water Board Veluwe sintering is 2.7 million euros in its reserves because it has a low tax imposed on owners of homes and business premises. This is a mistake, says the water board. In a spreadsheet accidentally switched two fields, so the attack about five percent lower.

Here is the link to the source article, which is in Deutch: Foutje in spreadsheet kost waterschap 2,7 miljoen.

Smia cannot prevent people from exchanging variables in their equations, but Smia's named variable concept makes it much easier to spot such errors.

Just compare the readability of the two examples below:

Spreadsheet equation Smia equation

$C$92 - $Q$121

net = expenses - revenue

In the example to the right, bells will probably start ringing, as the intuitive equation would be that expenses were subtracted from revenue in order to compute net income; and not vice versa.

Inspecting the spreadsheet counterparts does not make you any wiser, unless you investigate what is actually stored in the two cells that the expression refers to.

Another thing, is that the likelihood of introducing this kind of error is much lower in Smia (and other languages where data are named) than in spreadsheets (where data just have 2D co-ordinates).

Today Patric O'Beirne posted another example of a costly spreadsheet error. Below is some text copied from the article, which was published in the Flintshire Chronicle (UK).

A £1M council cash ‘cock-up’ which prompted calls for the resignation of a top Tory councillor was down to a spreadsheet error, it has been claimed.

County Hall officials have not published details of an internal investigation carried out in the wake of the blunder, which left Flintshire Council £1m out of pocket after they over-allocated money cash to the county’s 12 high schools last year.

But the Chronicle understands the mix-up occurred because someone ‘put money in the wrong column’ – and no one spotted it before the money was handed out.

Here is the link to the full article: Flintshire County Council school cash blunder ‘down to spreadsheet error’.

Putting numbers in the wrong place is obviously not possible to prevent. Errors like this may need manual inspection (quality assurance) in order to be avoided. However, Smia's varaible concept (typed, named variables as opposed to anonymous cells) makes it less likely that numbers go in the wrong place without being spotted.

Here is another story about spreadsheet mistakes leading to costs - this time to customers: spreadsheet error costs time and money, yet again

The full story is found under the link above. Here is part or it:

When she did call back, she leveled with me: accounting was wrong, there was an error in the spreadsheet, and after fixing the multiplier cell, my bill was reduced by a factor of 10. After the credit calculators, they determined that I had overpaid from the previous bill by about $100, and that I probably wouldn’t have a bill for the next two cycles. She apologized for how long it took to resolve the issue, but reassured me that it wouldn’t happen to me again.
Priest

Also churches suffer from spreasheet mistakes...

ALPHARETTA, Ga. (NAMB/BP) — The North American Mission Board has informed the Great Commission Task Force that data it provided last summer regarding the geographic distribution of missionaries in North America contained a significant error. The NAMB error resulted in incorrect data being published in the Feb. 22 "progress report" issued by the GCRTF.
...
The data spreadsheet used listed each of those state conventions with their individual subtotals and also an overall total from the state. Both the subtotals and the totals from those states were added when the overall missionary count was computed, leading to a double-count for the two states.

The link to the full story is here: NAMB corrects missionary count data to GCR Task Force.

Smia's handling of totals and sub totals ensures that subtotals are never counted together with totals. So this kind of error is simply not possible to do in Smia.

Here is another example of spreadsheet errors that cost money, this time to the county of Albermarle.

Officials have estimated that the mistake cost the county $66,000 in overpayments; had the error not been corrected, the county would have been out $360,000.

Questions about precisely how the stipends were miscalculated and who was to blame remain unanswered.

Kimberly Suyes, director of the county’s Human Resources Department, said there was a “miscalculation in the spreadsheet. There’s not much more.”

Let us know what you think

You have to log in before you can comment on this article.

Please enter your account information to log in.

Notifications

Subscribe You must log in before you can subscribe to email notifications on new articles.