PRODUCTIVITY is never an accident. It is always the result of a commitment to excellence, intelligent planning, and focused effort — Paul Meyer

CAUTION! As you realise by now, I am passionate about productivity software tools as potent weapons in the quest to improve productivity. Last week I referred to MS Excel as one of those tools that is really very, very powerful, but alas! — vastly underutilised. As with any powerful tool, though, it can equally wreak untold havoc if not used properly and with great discipline.

According to Tim Worstall in a 2013 Forbes article titled MS Excel Might be the Most Dangerous Software on the Planet, financial practitioners throw trillions of dollars around the markets based on incomplete spreadsheets and their ignorance of how they’re doing it wrong. Wow! Can you imagine — the pillars of the global financial system may very well be resting on the state of Excel use and misuse!

Since we have gone down that road, let us take a sampling of some of the crimes that MS Excel has committed. As I indicated last week, there are several other spreadsheet options available, including the free to use Google Docs. However, MS Excel is by far the most used worldwide. You can Google an interesting article on CIO.com titled Eight of the Worst Spreadsheet Blunders, from which I have referenced and/or quote, in the following examples.

From as far back as 1995, a Fidelity accountant omitted to transfer the minus sign on a US$1.3-billion loss. The result was that the results were overstated by US$2.6 billion. Another financial institution, Fannie Mae, which finances home mortgages, in 2003 discovered an error involving more than US$1 billion in shareholder equity. Here is how a senior vice-president explained the error, “There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard!” Really?!!

Not to be outdone, higher education institutions are also involved. The University of Toledo made an error in over projecting their revenues in 2004 by US$2.4 million. The university had projected that student enrollment would have actually declined by 10 per cent. However, a simple typo in the spreadsheet formula caused the enrollment, and hence the revenue to be overestimated.

There you have it — Excel is dangerous! So, as the accountant, the financial guru, the vice president of finance, do you know who, where and when in your organisation Excel is being used? Do you make strategic decisions based on the output from these spreadsheets? Then be very scared, and ensure that there are adequate controls in place to ensure that your spreadsheets are not loose cannons in your organisations!

Since the point is so important — let me persist. The cleaver is a very useful kitchen knife. It is solid, effective, and can cut through the toughest of meat, and bone, in a single chop. Similarly, it is very dangerous — and so kids should be prohibited from touching it. Adults should also use e-x-t-r-e-m-e care before, while and after using a cleaver. I think I have now made the point — Excel is powerful but dangerous!

GIGO — Garbage In, Garbage Out

The main reason for faulty spreadsheets is data input error — the classic and well known “Garbage In, Garbage Out”. Computers are good at that. When we were discussing process improvements I indicated that many people rush to automate when they are having process problems. What they fail to realise is that if they don’t fix the process first, what they get is that the system would just produce the errors faster! Again, computers are good at that!

Anyway — back to Excel. Many persons create spreadsheets so that they and/or others may later input data for subsequent analysis. Underline “subsequent analysis.” Wouldn’t it be good if we could restrict what gets entered? Say for example you have a financing mart with branches in Half Way Tree, Montego Bay and Mandeville. You have designed a spreadsheet and you want the three members of your department to use it and enter certain transaction records, each to contain data on branch, amount and date. Most persons would create the column headings Branch, Amount, and Date, and the spreadsheet is ready for data entry.

Data validation

What you will get is that for inputs for say the branch Half Way Tree, you will get some entries with “Half Way Tree”, some with “Half-Way-Tree”, some with “HalfWayTree”, plus a plethora of versions with different misspellings. That is OK if all you will do is read the data. However, if you are going to use a powerful tool like the one we mentioned last week — Pivot Tables, then all the entries for Half Way Tree need to be the EXACT same thing. How to do that? Data Validation — found by clicking the data tab on the ribbon and then selecting data validation near to the middle of the ribbon, then clicking the data validation selection.

What most Excel users do not realise is that most settings in Excel are defaults and can be changed. So the cells on the worksheet are by default all set to be able to accept any type of data inputted — that is any number, letter, or special character. But with Data Validation, you can adjust the cell to accept only what you want to be entered. In the dialogue box, you will notice that you can restrict the cell to allow only whole numbers, or only decimals, or only text of a certain length, or only dates, etc. So for the date data, you can restrict the date cells to only accept dates — anything else would be prohibited.

For those who are still reading it means you are interested, so please note still another option — “List”. This option allows you to create a list of items that will be shown as a drop-down selection list within the cell. You read right, you can do this in Excel! When you select “List”, in the “Source” box displayed, just enter the items to be listed separated by commas. So for our example it would be “Half Way Tree, Mandeville, Montego Bay”, without the quotes, then click OK to close. When you click on the cell you will see the drop-down list with these three items — then the user would just select one. In this way all branch data entry would be accurate!

Kenroy Wedderburn is an MBA part-time lecturer. Send your e-mails to drkwedderburn@gmail.com.