Spring 2014 issue of Horizons

A Google search of “big data” will return about 12.7 million results. You will be quickly inundated with an endless list of vendors and software claiming to provide the solution to all of your big data needs. However, the marketing blitz may be slightly misleading as more pressing data challenges often fall into the camp of “small data.” That is to say, many of us may not be effective creators and consumers of information that can fit neatly within the realm of common spreadsheet software.

We are most often held back by a lack of a methodological approach to the way we capture, store, analyze and access this small data.

Currently, Excel is the data tool of choice. Approximately 95% of U.S. companies use Excel in their operating environments. Further, 50% of Excel files are relied upon for critical business decisions. The fact that this software is so prolific throughout the U.S. economy is not, on its face, a problem. The issue is that there is no method to the madness. The average Excel file lives on for 5 years and is edited by an average of 12 professionals. Such a long-lived, dynamic instrument warrants a rigorous review and control environment to mitigate the impact of errors.

For some additional reading on the potential impact of such errors, you can peruse the “Horror Stories” compiled by the European Spreadsheet Risks Interest Group at www.EuSpRIG.org .

The appropriate Excel environment can be created if we first think of it as a data manipulation tool. Think of Excel users as ‘coders’ or software engineers.

If you open and create Excel documents, you can technically be referred to as an Excel coder. This is a subtle distinction, but an important one. It is made more obvious when we juxtapose the control environment of a typical software engineer and the control environment of an Excel user. Software engineers often develop centralized controls and establish strict segregations of duties, monitoring the software’s development at every stage of its life cycle. Technically, we can leverage the methods and controls implemented by developers to mitigate the potential errors we make in Excel. A good place to start with error mitigation and file control would be to have the Excel coders provide some metadata about their files. Have them create a road map and document their work in Excel, including any changes made after its initial creation.

Further steps can be taken to clean up the actual work in Excel to increase the computational efficiency of the file and make it easier to review and understand.

Excel coders can reduce the amount of embedded cross worksheet linking . For example:

∙ DO NOT sum items from other worksheets using a formula such as: “=Sheet1!A1+Sheet2!A2+Sheet3!A3”

∙ DO pull the information into individual cells in the current worksheet so that you can use the following ‘code’, “=A1+A2+A3”

www.RubinBrown.com | page 15