How often do you spot errors in spread sheets in your company?

How often do you spot errors in spread sheets in your company?
 

2015/04: Away with methods!

Maarten van Leeuwen

Recently, I drafted an instruction manual and circulated it to a number of colleagues for comments and corrections. One of the topics of the manual was to explain how we arrive at gross margin. Gross margin being, of course, revenues minus direct costs. The following presentation caused objections:

Sales
-/- Direct Costs
= Gross Margin

A young colleague, still attending university, had never seen the -/- sign. “What is this?” I explained it means minus, and is written like this to remove all ambiguity. Indeed, a – could be interpreted as a bullet point. Similarly, I explained, a dividing sign is not represented as a : but as a ÷.

“Aha, now I know what you mean” my colleague said. “This is no longer used. Just as the zero, which used to be presented as a Ø and not as 0. That is old fashioned.”

Old fashioned indeed. But is something by definition a bad idea if it is an old idea? If you happen to be a Dutch citizen like me, and have a recent passport, look at the number. In my case, and I studied it with my young colleague, it is impossible to tell what the number is. It is alphanumeric, and presents certain letters in capitals, others in lower case. Numerals are sometimes large, sometimes small. So what now is the o in it? Is it a Ø or an O? In this case, it is trivial and it makes me smile to think that the Dutch government has now made it a little bit more difficult for big brother to keep track of me. (Probably there is a chip in the bloody thing anyhow, but now I am side tracking.)

It has become clear to me that universities no longer seriously treat the subjects of methodology, the science of measuring and the science of science. I get frequently presented with Excel sheets that comingle numbers with varying positions after the decimal point. Few people seem to realise that 100 and 100.00 are not the same thing. Nor does it dawn on them that one cannot, and should not, add 100 + 100.00, but that one first should bring all fractions to a common denominator. This is slightly surprising, as this topic is taught in primary school (or at least, it used to be!).

Why does it matter? Excel adds such numbers without problem.

It matters for a multitude of reasons. One of them is that sloppy work gives sloppy results. A column with numbers of varying positions after the decimal point is difficult to check at a glance:

I know, the result is (deliberately) wrong, which is immediately perceived in the right hand column and virtually impossible to detect without calculator in the left hand version. 

But Excel won’t make such a mistake, will it? No indeed, it will not. Your employees will make such mistakes WITH Excel, for instance by making sloppy spread sheets and combining operations where they should not. I believe that if somebody gave me a cup of coffee for every mistake in an Excel sheet in OUR organisation, I would never need to buy coffee for the rest of my life. And if you believe our organisation is the exception, and that yours is not, do a random check and you will probably think again.

Such mistakes, if they creep into things like customer offers – which they unfortunately tend to do – can be costly.

Another example why the presentation of numbers matters is human psychology. At American Express, one of the things I had to do - after a budget was completed - was to round off all numbers to end with a minimum of three (where possible) or two zeros. Why? Because anyone who observes a column of numbers all ending with two or three zeros immediately recognises that these numbers are not actual, but some plan or budget. This relieves the brain of hundreds of managers and directors just that little. It allows them to focus on their tasks: analysing the numbers, as opposed to trying to figure out what it is they’re looking at. 

When we make a BUDGET offer to a customer, we end the number with three or four zeros. With this, we clearly indicate (we hope) that it is a budget, and not a precise offer. If you go into a car dealer, and the sales person tells you a particular model will cost “around 50,000” you will not be surprised if the final offer is, say, 54,355.67. Should the sales person have told you the car will cost “around 51,567.88” I am not sure your reaction would be the same.

You are likely to say “it is a three hour drive from here to city XYZ” rather then “it will take 2 hours, 56 minutes and 33.22 seconds to drive from here to city XYZ” However, at the supermarket you have to pay 122.34 if that happens to be the bill – you cannot get away with paying 120. Yet again, you’re likely to answer “around 120” when your partner subsequently asks you how much you spent.

So yes, methods matter and no, we cannot do away with them. Doing away with methods can and will be costly, also in your organisation. Unfortunately, universities seem no longer to care about these topics. This is probably because it seems boring. As a result, we have to train our employees ourselves in these matters.

Maarten van Leeuwen
Group Managing Director

If you wish to react to this article, you can send us your feedback to club@alvey.be.

 

terug

How often do you spot errors in spread sheets in your company?

How often do you spot errors in spread sheets in your company?
 

Certificering

Contact

  • Sales +32 (0) 55 33 57 70
    Service +32 (0) 55 33 57 41