How to Avoid Becoming a Spreadsheet Horror Story

Spreadsheets offer a quick, flexible, intuitive, and cost-effective platform for rapidly analysing data and presenting information to support business decision making. Spreadsheets are developed by end-users and are part of, and are the most pervasive example of, the End User Computing (EUC) group of applications that form a bridge between the non-IT business requirements and the stringently managed IT systems. However, the fact that End-Users are not subject to the same controls prevalent in IT systems have led to increased risks for many of the businesses that rely on them.

Spreadsheets are rarely a cause for concern or suspicion. Management may believe there is little reason for concern because they have used the same spreadsheet software for many years, even though there should be. Spreadsheets can easily be changed, may lack certain controls, and are vulnerable to human error.

Media and devoted pundits have published an abundance of the results of spreadsheet errors over the years, many of which caused Commercial, financial and reputational loss.

These are characterised as “Spreadsheet Horror Stories” but probably only the tip of the iceberg ever reach the public domain. This is because, unless the risks prove to be a breach of regulations in financial services firms or catastrophic, as in the case of Conviviality who went into administration due to a spreadsheet arithmetic error, nobody wants to publicise their failings.

An independent consultant recently summed up one of the problems by stating “the majority of his Country’s businesses that operate spreadsheets in mission critical working environments fail to automate their analysis and auditing of spreadsheets, relying on eyeballs rather than proven technology”. Research has found that over 95% of spreadsheets contain errors.

ExcelAnalyzer was created to identify these errors and inconsistencies in Excel spreadsheets and, thereby, eliminating the many risks they can cause as well as reducing the huge time and cost involved in manually trying to check for errors.

The use of very hidden sheets that do not appear in the sheet ribbon has caused a significant breach of confidentiality in one case, where a spreadsheet containing very hidden sheets was emailed to a third-party and found to contain hundreds of people’s personal information. ExcelAnalyzer makes visible all hidden and very hidden sheets.

Inter-sheet links can be a common cause of error because of the difficulty in tracing the links and the problem has coined the term “spaghetti spreadsheets”. ExcelAnalyzer’s Model-Flow report clearly shows all of the links between sheets and includes inter-sheet links and external links. This report is particularly useful if you have a multi-sheet workbook with many links, especially if you need to delete any sheets without causing #REF errors.

The report is colour-coded and shows instantly how the sheets are connected with each other. It shows which sheets contain External Links and clicking on any of the green or orange numbered box links will navigate to that sheet and highlight those inter-sheet links and provide a link back to this report.

This report also shows Additional Inter-sheet links and External formula links as you can see below. This report will also identify errors caused by the target of an external link being accessed from two different locations which would probably be different versions of the same link which is clearly an error.

So, by these means, spreadsheet errors can be identified and eliminated either directly by the spreadsheet builder or, if this was a colleague’s or client’s spreadsheet that was provided to a Reviewer, then through using ExcelAnalyzer’s Comment feature.

The optional Comment feature is very useful for anyone who needs to review a colleague’s or a Client’s spreadsheet. Instead of the reviewer editing somebody else’s spreadsheet they can query/comment the validity of any issues in the spreadsheet and generate a comment report and send this to the model builder who can respond to the reviewer’s questions. Afterwards the reviewer or the model builder can correct the spreadsheet based on the responses of the spreadsheet owner. This option tool is accessed from section 2 of the ExcelAnalyzer ribbon.

Thus, the Comment feature is a very powerful tool, not only to Review, Edit and correct the spreadsheet but also to Add Comments and therefore the comment report provides an audit trail of all issues raised in the spreadsheet by the reviewer as well as the responses provided by the model builder, thereby documenting who changed them and how they were changed.

Once generated, the Comment Report as an extra tab next to the Formula Report in the sheet ribbon as shown below which shows all of the Reviewer’s comments including the sheet names and cell addresses of the formulas in question.

The following on-line help panels explain the different areas of the Comment Feature report:

ExcelAnalyzer – An Essential Tool for Forensic Accounting

Many Accounting firms provide Forensic Accounting services, the origin of which is thought to have originated from the US IRS income tax investigations into Al Capone’s fraudulent transactions in the 1930s.

Forensic Accounting is the process of conducting a forensic investigation into financial discrepancies and inaccuracies that can result in an expert’s report or witness statement being prepared from spreadsheet data and submitted in court-based litigation as part of a commercial dispute.

Excel spreadsheets are widely used throughout business and are the most pervasively used of the various End-User Computing (EUC) applications. Spreadsheets are widely made use of in Forensic Accounting.

Spreadsheetsoftware provides a critically essential Excel add-in called ExcelAnalyzer to review, audit, and correct spreadsheets to eliminate hidden errors and inconsistencies that could cause commercial, financial, and reputational risk.

ExcelAnalyzer discovers the DNA of spreadsheets, not only identifying errors and inconsistencies and also displaying external and inter sheet links, revealing hidden columns, rows, and sheets, and very hidden sheets, and much more besides. All of which is displayed but can be printed and saved as a PDF to provide documentation for spreadsheets and support various regulatory requirements.

The Forensic Services department of one of our ExcelAnalyzer Customers uses Microsoft Excel to build data-heavy spreadsheets and models to prepare for court proceedings. Because of the data’s confidential nature, this process cannot be outsourced, requiring the firm to use their team of talented people to build the models themselves.

Before they go to court, an expert report is prepared which will be verified using Excel spreadsheets. Before the use of ExcelAnalyzer, this was “very time-consuming and not always 100% fool-proof”. There would be a substantial risk of being exposed in court if somebody discovered any errors in the Excel spreadsheets resulting in the wrong conclusion being reached in the expert report. Both sides exchange expert reports prior to the court case which allowed them, prior to using ExcelAnalyzer, to be manually checked, which was both time-consuming and still open to missing errors. Both sides checking for errors in each other’s expert reports undermining the opposing side’s expert report.

It would be disastrous having to admit to errors in the expert report and the conclusions or the other side spotting errors in what is being brought to the judge’s attention.

If an expert goes to court to give evidence and is asked if you can prove this spreadsheet is error free, without ExcelAnalyzer you cannot guarantee that your spreadsheets are error free.

So with ExcelAnalyzer you have a double-edged sword to prove that your spreadsheets are correct and, since the other side provide their spreadsheets in advance of the court session, you can run their spreadsheets through ExcelAnalyzer and reveal any errors found at the court hearing.

ExcelAnalyzer is also used to satisfy many other financial aspects, including audits, forecasts, budgets, analysis, etc.