Spreadsheet Over Reliance

(Based on an article published in the Bottom Line in May 2005)

It's hard to understand how we survived Before Spreadsheets. We all know the advantages of spreadsheets. Spreadsheets are great in the beginning to analyze a problem or prepare a report in exactly the right format. The problem creeps in when the spreadsheet becomes of a victim of its own success. They proliferate like rabbits until a company depends on them for decision making and operations. Another reason for spreadsheet pervasiveness is related to control. With a spreadsheet, you have the ultimate control over what is presented. Many people don't want to give away their power.

However, spreadsheets are inefficient and not completely reliable: errors can slip in through re-keying or calculation mistakes. There is no audit trail on changes and mistakes may not be detected. To make matters worse, spreadsheets are typically not shared across an organization and they are not updated as things change. So decisions are made with old data. A common complaint is the lack of one version of the truth, as individuals take the same underlying data and work their magic in the spreadsheet. Another common problem is that each department in an organization gets very creative with spreadsheets in resolving issues within their own department. The spreadsheet may be ok for an individual department, but a disaster outside the walls of the department. Spreadsheets contribute to the silos found in many companies.

How do you know you have a problem?

1. Someone in your organization spends most of his/her time managing spreadsheets that no-one else can understand. First you have over reliance on one person. Second, it's obvious that the spreadsheet is too complex.
2. You are afraid to change a spreadsheet. You are also exposed to big problems in supporting the system and making changes when necessary.
3. Anyone is re-keying information from or to a spreadsheet.
4. You need to wait too long for the analysis.
5. You are the victim of bad information.
6. You have been told to comply with Sarbanes-Oxley or the equivalent.

What can you do about it?

You don't have to eliminate spreadsheets. But you can cut down on their proliferation and complexity. You should consider the use of pivot tables, which is a way to use the same spreadsheet for multiple views of the data. As well, you might want try using a database (Acess, SQL Server…) as an intermediate step. A database is more appropriate for data storage. You can easily select the data you want from the database and pass it on to Excel. But sometimes there is no choice but to move beyond spreadsheets.

The solution depends on the problem. If the problem is related to reporting, you have many choices including On Line Analytical Processing (OLAP). OLAP allows you to slice and dice information across multiple dimensions, drill down and graph the results. One OLAP cube (a cube is way of storing the data for easy viewing) could be the equivalent of 100 traditional reports. In the past, OLAP was used only by the largest of companies, but that is now changing. You will find OLAP available directly from accounting and ERP vendors. Many vendors pre-configure OLAP cubes for analysis of sales and other information. Microsoft includes the ability to build an OLAP cube with SQL Server Analysis Services, which is included at no extra charge with the purchase of Microsoft SQL Server.

Another problem could be related to scheduling, which can be a huge problem for many manufacturers. Production scheduling systems are available either as part of an ERP system or as a best of breed solution. Best of breed means that you choose the best solution for a specific part of the business and integrate it with the other systems as opposed to getting it all from one vendor.

Perhaps the problem is related to complex order processing. There are lots of solutions here too including a configurator that allows you to build a complex order on the fly. You might want to investigate a product called InfoPath from Microsoft that can create a form that looks exactly like your existing form, but which could update your accounting and ERP system with the data entered into the form. Today, many companies avoid doing customizations of their system and instead rely on a spreadsheet to do the job. However, you may find that there is a compelling business case to have your existing vendor, implementer, value adder reseller… customize the system to meet your needs.

It doesn't have to be spreadsheet hell.