Exit Excel Hell - Part 1

Did you know, and this is based on studies by PWC, KPMG and others, that over 90 per cent of spreadsheets have errors? The larger the spreadsheet, the more errors found.

Many organizations I’ve worked for in the past relied on Excel too much. Recognizing the problems with Excel, I tried to promote a strategy that I called ‘Exit Excel Hell’.

Too many senior managers rely on the dubious quality of data in a spreadsheet.Taking this to the extreme, look at what happened to Worldcom or Nortel. At some of these companies, senior managers manipulated or unknowingly introduced errors into the spreadsheet financial reporting. Those looking to misrepresent the business can easily manipulate spreadsheets.

Think about it, anyone can change a cell, manual errors happen so easily, even accidentally keying in the wrong number e.g. 200 instead of 100. How do you know if you have keyed in the wrong number? If you're lucky, you visually catch it. Other things like simply cutting and pasting the wrong cells can easily happen. Even formula errors are so easy to make —  a row was omitted in the sum for a financial report is easy to do. Sometimes when adding rows, Excel will adjust the formula, but not in all cases. It’s so easy to make a mistake. Even knowing the pitfalls doesn’t prevent mistakes; many times people have found errors in my spreadsheets I’ve used in making business cases.

There are some simple controls you can do such as build in control totals. For example, if you had a spreadsheet that reported staff absences and the number this month was ten times what it was last month that might be a warning flag to check the numbers. The spreadsheet designer should devise tests to verify the results generated. Another control is to lockdown cells. By lockdown, I mean set a cell so it can’t be changed by a user (also known as protecting a cell). This applies to cells where data is not being changed, such as a formula cell. More sophisticated spreadsheets may actually extract data from a corporate database, such as the financial system. Getting the data directly from the source provides a better control than someone manually re-entering the data and you are more assured of the data integrity.

Since each spreadsheet is largely manual, reconciling numbers and the source can involve, and waste, a lot of company resources. That’s why I like to promote using a single source for certain data. For example, revenue recognition should be in the financial system, not in someone’s spreadsheet or spreadsheets. All departments should agree on the process entering data for sales and rules need to be clear. If Sales uses one system and Finance uses another, you are caught with two different sets of numbers — which is correct? Who do you believe?”

Sometimes staff try to create their own systems using Excel rather than relying on a corporate application system. As an example, suppose rather than using the purchasing module within the financial application, the purchasing department used a system of Excel spreadsheets to do their work. This creates a disparate system, often without appropriate controls, that does not tie into the existing systems. These Excel systems were never designed to be enterprise-level applications.

Perhaps the most popular Excel system is for budgeting. Many companies claim their budget process is unique so they have to use Excel because it is so flexible and accommodating. Ideally though, it would be much better to use either the budgeting solution in the finance system or have a system that is specifically designed for budgeting. The main problem that occurs with budgeting is that you often have many spreadsheets, hundreds or thousands for some companies, that your managers use to fill out their budget, with finance probably going through hoops trying to link or consolidate all the budget spreadsheets. In addition, in both the purchasing and budgeting spreadsheet examples, you end up creating a duplication of data that must be re-entered into the financial system at some point. In a corporate application system, you often have data entry validation. For example, in Purchasing you might enter a supplier number. The computer would look up the supplier name and address at the time of entry. Or, when entering the GL account number the application would validate the GL account number and it could look up to see if funds were available in that account for this purchase. Staff using Excel often do not build in data validation for cells that require data entry.

You know, Excel spreadsheets can proliferate like rabbits, and once that happens, version control becomes a real problem. As people create, modify and share spreadsheets, how do you keep control of the latest version? Is the latest version on a shared drive, on someone’s laptop, in someone’s email, where? During a budget process, sending hundreds of emails with budget attachments can choke your email system.

More in my next blog - 'Exit Excel Hell - Part 2'.
 Dilbert Copyright Scott Adams