Spreadsheet errors can be a major cost to business

Canadian company TransAlta took a $24 million charge last year after a bidding mistake caused by a cut and paste error in an Excel spreadsheet, according to a report on Computerworld. This is just one of many documented cases that have led to fraud charges, loss of profits and projects going over budget, all caused by simple errors in Excel.

Research by Raymond Panko, a professor at the University of Hawaii, shows that over 90% of spreadsheets contain errors of some kind. David Finch, head of internal audit at Superdrug in the UK, says: "The use of spreadsheets in business is a little like Christmas for children. They are too excited to get on with the game to read or think about the `rules`, which are generally boring. There is often little control over end-user developments in spreadsheets, with little if any standardisation in development processes by users in different departments, little risk analysis and a general assumption that models, on which important decisions are made, are accurate."

Adrian Miric, MD of Miricle Solutions, which supplies Spreadsheet Professional, an Excel auditing tool, says there are a host of human errors that are made while using the spreadsheet program.

One of the most common sources of errors is the copy and paste function, he says. This, along with the use of absolute and relative referencing, was possibly the reason behind TransAlta disclosing a loss of $24 million. TransAlta`s computer spreadsheet contained mismatched bids for transmission congestion contracts (TCCs). As a result, it purchased more contracts at higher prices than intended. The actual value of TCCs was determined daily, based on changing supply, load and transmission conditions. TransAlta worked throughout the month to mitigate the financial impact from the error.

Says Miric: "The ability to copy and paste in Excel is one of its main and strong features. However, absolute and relative referencing can be extremely dangerous and a number of errors result due to this." He cites as an example a typical spreadsheet where revenues and margins are calculated. "Because of the copy and paste function, once you`ve built a model, you can highlight a section, and copy and paste it - and you believe it will be correct.

"However, what you have to be careful of is, if you change that margin, that change may not be reflected in the total. Often the reason is the user used absolute referencing on a particular cell. When you copy and paste it follows your instructions and as a result your cells look at the incorrect calculation.

"A way to identify that problem is to use the in-built spreadsheet auditor and click on the margin to see what else is looking at it. If no other formula is connected to it, there is cause for concern."

Inserting rows and cells is another spreadsheet nightmare, he says, especially when it comes to tenders. "The reason is, if you make an error in a tender and it is to your company`s advantage, you probably won`t win the tender as the price will be too high. Alternatively, if the price is too low, the chance is you will win the tender, but will have to deliver the product and service at a fixed lower price than you anticipated."

The reason, says Miric, is - depending on where the row is inserted - it may or may not reflect on the total price, as the user may have selected only a certain set of cells to make up the total, which is out of the parameter of the inserted row. "It`s very important to realise that, depending where you make the insertion, any formula that refers to the cells close by might not do what you expect it to do," he says.

"Just as you can insert rows, so you can insert individual cells. Again, this has its risks. For example, in a simple spreadsheet covering a couple of products, you may accidentally type in product D`s cost of sales into product C`s.

"The solution is simple: you simply insert a cell and shift the others to the right. However, what you need to realise is Excel will adapt the formulas. The problem is product C will now subtract from product D, so it is very important to think seriously about when you insert individual cells. I recommend you don`t do it at all. If you`re going to do it, be sure to look at the cells around it."

By the same token, says Miric, deleting individual cells and moving their relative position can also have a serious effect. In the case of a simple spreadsheet where the gross profit is being calculated by subtracting the cost of sales from sales, the user may put the cost of product B`s sales into product C`s and type on, so there is one too many cells at the end.

"What you can do is delete that cell and shift the others to the left. Immediately you will pick up a problem because Excel will think the formula no longer exists. What people forget is not only does this affect the deleted cell, but it affects all subsequent ones as well. You need to be very careful when deleting individual cells and moving the relative position of them."

A further common problem in spreadsheets, says Miric, relates to hidden rows or columns. "These can cause their own problems, especially if you are working on someone else`s spreadsheet or you have worked on a spreadsheet a while back and this is the first time you are coming back to it."

He says it is easy to overwrite hidden columns by copying over them, causing errors.

"It`s better to avoid hidden rows, especially while building a spreadsheet. If you receive a spreadsheet from someone else, open up all hidden rows and columns just to make sure you know what`s actually being looked at."

Linked workbooks are a norm in the business world, Miric says. "Linking cells in different spreadsheets is one of Excel`s best features and is used extensively. However, what people don`t realise is how easy it is to make an error if all linked spreadsheets are not open at the same time.

"If you keep them all open while making structural changes, Excel will take all changes into account. If you insert a row in one linked spreadsheet, Excel will adapt the others accordingly. However, if you close one spreadsheet and insert a row in a linked one, when you open the other you will notice zeros coming through. The reason for this is it is still looking at the old cell.

"This is a very common error to make. If the spreadsheets get big enough, this problem is very difficult to spot. What is really risky is if the linked spreadsheet doesn`t look at a blank but at another number. If your workbooks are too big it is vital that everyone knows that structural changes, such as inserting rows and columns, cannot happen or must be strictly controlled."

Miric says these are just a few of the common problems associated with spreadsheet usage - which can clearly cause a business lasting and perhaps irreparable damage. The solution, he says, is to use an auditing tool to address the risks associated with spreadsheets.

More information and audiovisual examples of errors and how they can be fixed can be found at www.auditexcel.co.za.