Are Your Excel Spreadsheets Accurate? Are You Sure?

Introduction
Microsoft Office Excel is a cornerstone tool for most business professionals. We use it for everything from financial reporting and analysis to budgeting and forecasting to tracking project statuses and all things in between, even including our Holiday shopping lists. Given our personal and professional dependence on this tool, maybe we should take a few moments and address a simple question: Are my spreadsheets accurate and how can I be sure?

We should not underestimate the significance of this question and our response to it as some studies report that approximately nine out of ten spreadsheets in use by financial professionals contain errors.  Given the ubiquitous presence of spreadsheets and our seemingly increasing dependence on them, can we really continue to accept the risk that our spreadsheets contain errors? Probably not. So the next question that we need to ask is: How can I prevent and detect spreadsheet errors? The remainder of this article outlines several tools and techniques that you can implement right away to help reduce the likelihood of spreadsheet errors and their adverse impact on your organization.

An Ounce of Prevention…
The logical starting place for reducing the volume and impact of spreadsheet errors is to prevent them from occurring in the first place. To do so, let us consider several strategies. First, reduce the number of spreadsheets in the organization. This may sound overly simplistic, but it is only logical that if we reduce the number of spreadsheets in the organization, the opportunity for spreadsheet errors should decrease. To reduce the number of spreadsheets in use, consider first for what purpose are you creating a spreadsheet. For example, many organizations use spreadsheets extensively in budgeting and forecasting applications, completely oblivious to the fact that dedicated budgeting and forecasting applications are available for this purpose. Tools available from companies such as Prophix, Centage, and many others can help you to automate budgeting and forecasting functions, while simultaneously reducing or even eliminating your dependency on spreadsheets for this function. We can say the same for many financial reporting and analysis functions; that is, dedicated financial reporting and analysis software can be used to replace spreadsheets otherwise used for these processes.

To the extent that we cannot replace spreadsheets with specific software applications, then our focus must turn to how we construct our spreadsheets. Some of today’s “best practices” for constructing spreadsheets to help minimize the risk of errors include the following.

  • Never embed assumption in formulas. We should never embed assumptions directly in formulas; rather, we should place our assumptions in cells external to formulas and then make cell references to the assumptions inside the formulas. The following formula complies with this methodology.

    =PMT(B4,B5,-B6)

In the formula above, the variable or assumption for interest would be stored in cell B4, term would be stored in B5, and principal would be stored in B6. The advantage of this technique is that if the assumptions require modification, it is much easier to change the values in cells B4, B5, and/or B6, respectively, than it is to modify the formula. Perhaps more importantly, modifying the formula is a more risky maneuver as end-users may inadvertently change it so that it contains errors.

  • Once built, apply worksheet and workbook protection as appropriate to prevent unauthorized changes. Excel offers numerous options for securing your spreadsheets to ensure that once they are properly constructed, unauthorized changes do not creep into the spreadsheet. Enable simple password protection, worksheet protection, and workbook protection to ensure the continued accuracy of the spreadsheet.
  • Use Tables as data sources. Tables are a feature that Microsoft added to the 2007 Excel release of the application. Numerous advantages exist to working with Tables, including the rather obvious advantage of quick and persistent formatting of a data range that you convert to a Table. However, from a spreadsheet accuracy perspective, the advantages of working with Tables are not found in formatting; rather, three specific additional advantages generally mean that converting your data ranges to Tables and then building formulas and calculations based on Tables instead of traditional row-and-column addresses is usually the right move. These three advantages are:
  1. Tables serve as automatically expanding and contracting data ranges. As you enter (delete) data to the row(s) immediately below the Table or the column(s) immediately to the right, the definition of the Table automatically expands (contracts) to include (delete) the additional data. More importantly, any formulas or object that utilize the Table as their data source also now automatically include the newly appended (deleted) data, meaning you do not need to manually edit these formulas or objects to reflect the updated range of data.
  2. Tables offer a new way of building formulas known as structured referencing and most users will find structured reference formulas easier to build and interpret. Structured reference formulas instruct Excel to operate with specific elements of a Table such as specific rows and columns, instead of specific cells. For example, the following structured reference formula
    =SUM(Sales[May])
    instructs Excel to sum the entire column of “May” from the Table known as “Sales.” Note the obvious difference between the structured reference formula syntax and that of a more traditional formula such as =SUM(B32:B3058). The structured reference formula is not only easier to construct, but its self-documenting nature makes it much easier for an end-user to interpret.
  3. Tables provide the added advantage of self-replicating formulas. When entering a formula in a Table, the formula copies down all rows in the Table without any effort required by the end-user. Further, as additional rows of data are added to the Table, the formula continues to copy down the range automatically, ensuring that all rows of the table have the same calculation.
  •   Take advantage of PivotTables to automate calculations. PivotTables are, in the minds of many, Excel’s most powerful feature, capable of performing calculations on potentially millions of records of data. One of the primary advantages of working with PivotTables is the large number of calculations that a PivotTable can create on your behalf, in the absence of formulas. That is to say, a PivotTable can summarize and report on large volume of data without user input of formulas. To the extent we can automate these types of calculations – and at the same time, reduce the number of formulas in our spreadsheets – then spreadsheet accuracy is likely to improve.
  • Work with Defined Names when linking data across multiple workbooks. Defined names are a long-standing feature of spreadsheets, dating back to the early days of DOS-based spreadsheets when defined names were known as “range names.” Defined names are nothing more than nicknames for cells or ranges of cells and we can use them in formulas wherever we would otherwise use traditional cell references. For example, we can create a defined name known as “Revenue” that is a nickname for cells A2:A502. Once created, we can then use that defined name in any formula where we would otherwise use the cell references of A2:A502. As such, =SUM(Revenue) is a valid formula that instructs Excel to sum cells A2:A502. A real advantage of working with defined names lays in formulas that link data from other workbooks. By establishing defined names in the “source” workbooks and using them – instead of traditional row and column references – in the formulas in our “destination” workbooks, we can reduce greatly the risk that the formulas end up referring to incorrect cells when the position of the data in the source workbooks changes.
  • Use third party tools to build your spreadsheets. Another option for preventing errors in your Excel spreadsheets is to use third-party tools to automate generating the spreadsheets in the first place. For example, BizNet Software offers a number of tools that automatically extract information from various accounting applications and summarize said information as financial statements in an Excel spreadsheet.

Tools for Detecting Errors
Despite our best efforts, errors are likely to creep into our spreadsheets; accordingly, we need tools to identify these errors before they have a negative impact on the organization. Of course, Excel provides a number of formula auditing tools to facilitate this, including Trace Precedents, Trace Dependents, Show Formulas, and Evaluate Formulas. Although these tools are easy to use and are effective at what they do, a relatively significant amount of manual effort may be required to analyze a spreadsheet for errors.

As alternative, consider the automated approaches offered by Cluster Seven. One of these approaches is a web-based analytic tool that allows you to upload your spreadsheet and have Cluster Seven’s cloud-based service analyze the document to determine if it violates any of the pre-determined best practice rules. Not only does this provide for a thorough analysis of the document, only a minimal amount of time and effort is required to complete the analysis. You can learn more about this service by viewing the video at http://www.clusterseven.com/storage/videos/cloudbasedriskassessment.mp4.

Summary
Spreadsheet errors are occurring at epidemic levels but that need not be the case. Following a few simple best practices and techniques can help you to reduce the errors that might appear in your spreadsheets. Further, using error detection tools such as those embedded in Excel and provided by third parties such as Cluster Seven, can help you to identify potentially damaging spreadsheet errors before their impact manifests within your organization. In other words, with just a little guidance and effort, we can have tremendous confidence that our spreadsheets are computationally accurate and are providing use with meaningful information on which we can make better and more-informed decisions.

Author Bio: Tommy Stephens is a shareholder in K2 Enterprises, where he develops and presents continuing professional education programs to accounting, financial, and other business professionals across North America. You may contact Tommy at tommy@k2e.com.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: