801.254.4503   Request Demo

Spreadsheet Problem

75% of organizations are using spreadsheets for financial reporting, but research shows that serious errors are found in an alarmingly high percentage of them. According to one important study by the accounting firm Coopers and Lybrand, a staggering 90% of the spreadsheets audited contained errors. And the dilemma isn't confined to one study, as unfortunately every study tells the same story. As the experts citing the Coopers and Lybrand study rightly insist, “the occurrence of spreadsheet errors is a major problem for businesses and needs to be addressed urgently” [Rajalingham et al].


Analytics and BI products have promised relief, but most organizations aren't adopting them because of their high IT costs and they don't have the spreadsheet format and ease of use to which companies are accustomed. They trade one pain for another. So although a host of Analytics and BI tools exist, they’re failing to satisfy customer desires.


Synoptix however uniquely solves these deficiencies by eliminating 75% of the most serious spreadsheet errors. It does so by retaining the spreadsheet model companies embrace while providing database capabilities usually attributed exclusively to Analytics and BI products.

Spreadsheet Traps

Studies show that being able to analyze data in spreadsheets is precarious because of the overhead required to keep them current. CFO Warren Green of One Call Medical Inc. sums up the spreadsheet problem concisely:

“I spent more time building and managing the [spreadsheet] model, and making sure none of the links were broken, than I did managing the data and analyzing it to ensure it fit the strategic plan. Aligning the spreadsheets was a nightmare. A simple change like someone adding an account threw the whole template [of operating expenses] out of whack. But the real drawback was my inability to do an analysis of data to make better decisions, to re-forecast or otherwise plan accordingly” [Banham & Knox].

Even more more debilitating are the serious errors spreadsheets create by nature.  The chart below illustrates 16 areas that experts Rajalingham, Chadwick, Knight, and Edwards summarize as error-producing in spreadsheets:


Divided into two main headings, the most serious spreadsheet mistakes fall under Quantitative Errors.  Mechanical Errors are mistakes such as pointing to the wrong cell, mistyping a number, or accidentally overwriting data.  Omission Errors occur when something is accidentally left out of the report, while Logic Errors are mistakes in writing formulas.  As Rajalingham et al point out, creating spreadsheets is very similar to traditional programming because of the technical nature of spreadsheet formulas.  And in fact, because most who create spreadsheets aren't programmers, the majority of spreadsheet errors are formula based.  What makes these errors especially dangerous is that they're very difficult to detect and correct, costing companies countless wasted hours and tens of thousands of dollars.

 

The reality is sobering. While organizations are intrigued by Analytics and BI tools, they're not willing to give up that intuitive spreadsheet look and feel. Since most say they'll continue to use spreadsheets for reporting, they're facing a major dilemma: spreadsheets present an inherent barrier to being able to analyze key performance indicators in a timely manner and present themselves with costly errors.

Solving The Reporting Dilemma

Rather than miring you in spreadsheet nightmares, Synoptix allows you to focus on what’s important: account numbers, ledgers, and time periods.

Instead of manually entering/re-entering data and constructing complex spreadsheet formulas, Synoptix automatically populates cells - directly from your accounting database - along with the right accounts numbers, ledgers, and time periods. Synoptix cells track your data automatically, no matter what changes occur in the report. And all the while you’ll have that spreadsheet look, feel, and functionality that you’ve come to love.

The bottom line is that Synoptix eliminates the most serious spreadsheet errors. As noted, the bulk of potentially serious errors occur when using formulas to populate cells, or as they're called in our chart above, Logic Errors. Because Synoptix automatically gets data directly from your accounting system, the vast majority of the formulas users write are eliminated from the start. How? Here’s an example.

Formulas:  Referencing Other Sheets

When developing financial statements accountants use formulas to reference numbers on another sheet.  For instance, Sheet 1 may contain a Trial Balance used as a reference cell for B5 of an Income Statement in Sheet 3.  A simple enough formula, they simply enter =Sheet1.B5.  However as commonly happens, the cells in Sheet 1 change because account numbers have been added, thereby rendering the numbers in Sheet 3 - and any other sheets referencing that cell - invalid.  

The problem here isn't just that the numbers have changed (which is bad enough), but that the error is difficult to detect because the formula is still reporting a number, but now an incorrect one.  Because spreadsheets don't track actual account numbers, there are no built-in mechanisms for flagging these kinds of changes.  The results are disastrous.  Multiply this instance by fifty or hundreds across multiple sheets and you have a volatile situation considering accurate reporting of financial numbers is so important.

With Synoptix accountants have direct access to their chart of accounts in each cell and can simply click on the appropriate account number(s), which then automatically populates the cell no matter what happens to the rest of the report.  In fact, by entering a range of accounts, they can add account numbers as often as needed confident that the numbers are being accurately pulled in automatically.  Synoptix does all the heavy calculations in the background without the need for any formulas.  Thus the age-old problem of invalidating reports by adding or deleting account numbers is obsolete. 

Re-Keying Data

Mechanical errors are also virtually eliminated with Synoptix.  Accountants often re-key data from their ERP/accounting system back into a spreadsheet - overwriting or omitting data in many instances - in order to have them in a spreadsheet.  The problem is that re-keying data opens the door for all sorts of input and omission errors, and research shows that’s exactly what happens in most audited spreadsheets.  And consider this sobering fact.  These are the same types of errors found in ancient manuscripts that were physically copied, by hand, from one scroll to another.  Seems we’ve come a long way only to return to where we began.

But we can finally leave past technology where it belongs because Synoptix gets data directly from your database, making errors of this sort a distant memory.  There's no need to re-key information or overwrite data as Synoptix will automatically re-calculates balances based on up-to-date account numbers.  Omission of important data is eliminated as a matter of course, and, for the same reasons, so are Update Errors under the heading of Qualitative Errors.

To visualize how dramatically Synoptix reduces potential spreadsheet errors, we can reconstruct the above chart as follows:

As you can see, Synoptix eliminates Mechanical, Logic, and Omission Errors, along with their respective subcategories.  It also eliminates Update Errors, but the same possibility of Formatting, Hard-coding, and Semantic Errors remain because Synoptix retains the flexibility of spreadsheets.  However, while it doesn't remove the possibility of these particular errors, it takes great strides in purging the more serious errors from the spreadsheet model affecting bottom lines – at least 75% percent of them, or from Rajalingham's 16 to only 4.

Conclusion

In the end, Synoptix users have the best of Analytics and BI tools with the ability to track and analyze data, as well as the best of spreadsheets with their intuitive format - without the traditional drawbacks of either.  The unique technology of Synoptix has successfully bred the power of a database with the spreadsheet format, enabling users to create and maintain reports with the ease of spreadsheets, but also with database functionality at the heart of each and every cell.  Companies are able to maintain the flexibility with formatting they seem to appreciate, but are relieved of 75% of the serious errors afflicting the majority of spreadsheets.