How to keep your Excel files as clean as possible.

Excel is one of the most popular pieces of software for business. Nearly 95 percent of U.S. companies use the spreadsheet program to manage their data. About half of Excel files are used for critical business decisions.

Unfortunately, those files might not be as accurate as they should be. The typical Excel file lives for five years, and during that time, it will be edited by an average of 12 different people. It’s all too easy for mistakes to crop up.

Sunti Wathanacharoen, a partner in RubinBrown’s Business Advisory Services Group, shared advice about the best ways for reducing spreadsheet errors and keeping your Excel spreadsheets as clean as possible.

What are the most common mistakes that companies, especially smaller businesses, make when using Excel?

The most common mistake companies make is failing to implement effective controls around the creation, use and storage of Excel files. Test this theory with a simple experiment within your own organization. Just ask: “How many Excel files do we use (to some extent) when making decisions about our business?” There is a good chance that this question will be met with silence, when it shouldn’t be.

Establishing a “control and review” environment around Excel is a great first step toward getting this question answered, and eliminating costly errors. As a starting point, you should view Excel users as Excel “coders” or software engineers. It’s standard procedure for software engineers to develop centralized controls, establish strict segregations and monitor the development of software throughout its life cycle.

Do you have any examples of what can go wrong if a company doesn’t have good policies regulating its Excel use?

AstraZeneca, Britain’s second largest drugmaker, was forced to reiterate its 2011 and mid-term financial forecasts after accidentally releasing confidential company information to analysts. AstraZeneca issued the following statement: “Confidential company information was inadvertently embedded in a spreadsheet template sent to the sell-side analyst community that follows the company.”

In 2010, chemical company Georgia Gulf Corporation determined that a manual input error had been made to a tax calculation spreadsheet, affecting tax impact of its 2009 financial restructuring activities. The company issued the following statement: “This error caused our provision for income taxes to be understated by $36.4 million and our net income to be overstated by $36.4 million, each for the year ended December 31, 2009.”

What kinds of best practices should companies adopt when using Excel?

The single best practice may be to remind yourself that others will use your files or the conclusions reached therein. Many Excel analyses begin as a slightly more advanced version of a back-of-the-envelope calculation, which does not provide enough information for the next user to understand, or much less validate, the analysis.

Smarten both yourself and your business with the following best practices:

• Provide information about the file you are creating or editing. Start simple and add a note about the purpose of the file and the date the file was created or edited. Even better, add a note about how the file is intended to be used.
• Make your analysis easy to follow, even if you think no one else would ever possibly use the file. Add “bread crumbs” for other users, such as notes or linked formulas (avoid “pasting” values). More pointedly, the only hard-coded figures in the file should be source data or assumptions. If your file does contain assumptions, clearly identify them as such, and centralize them so they are all in one identifiable spot in the file. Break up complex formulas into discrete steps with notes or descriptive headings.
• Add “sanity checks” to your work. Create your Excel document as though you will use it to make a business decision before anyone else will have a chance to double check your work. For example, if you know that a variable should be between 0 and 1 (i.e. between 0 and 100 percent), check to make sure there aren’t values outside of this range.
• Visualize your analysis. Visual inspections of your analyses and data can make anomalies and errors more readily identifiable.
• Create a peer review process. Now that you have created your Excel file with your best effort to notate and document its creation and purpose, have an equally (if not more) Excel-savvy user check your work. Further, create a forum to identify common issues and items to leverage on future reviews.
• Control your files. Use passwords and logs to monitor and perhaps even limit access and changes. Back up data frequently in order to retrieve information easier and not have to repeat hard work.

Sunti Wathanacharoen can be reached at sunti.wathanacharoen@rubinbrown.com.