EndUserComputing.org

I’ve decided to start a new series of posts on EUC Control Best Practices. Our customers and prospects are always asking us how other companies are managing their spreadsheet and EUC control projects, and what the Best Practices are, so here we go!

Auditors Recommend Baselining Spreadsheets

Many of the leading audit firms and consultancies recommend starting an EUC control project by first creating an inventory and performing a risk assessment. The purpose of these exercises are to identify risky or critical spreadsheets within your business (i.e. separate the cooking recipes and fantasy football stats from the journal entries and financial statements). Critical spreadsheets will be the ones directly linked to financial, regulatory or P&L reporting processes where errors or issues with fraud, data integrity and non-compliance can have an adverse, material impact on your business.

Once you have identified the critical spreadsheet population, auditors recommend Baselining spreadsheets. The goal of this exercise is to provide a clean slate – to ensure that your mission critical spreadsheets are doing what they are supposed to be doing. To baseline a spreadsheet, you need to do several things, including (but not limited to):

• Analyze the spreadsheet structure and validate that it follows spreadsheet development best practices
• Test the spreadsheet against known input/output data (e.g. test harness) to verify it is mathematically correct
• Re-mediate any errors (e.g. broken links, formula errors, plugged cells, etc.)
• Document the spreadsheet

Manual Diagnostic Efforts Do Not Scale

Performing these tasks manually for a typical consolidation spreadsheet model (i.e. complex spreadsheet) is very time consuming and error prone, and the challenges are numerous. How will you easily understand and document the structure of the spreadsheet? What kind of skills and training are needed? How can you quickly find any and all errors? What will you use to highlight any fraudulent practices? How will you identify and verify the external links? I could go on here…

Enter, Automated Diagnostics

The point is, baselining spreadsheets manually just isn’t very efficient. Enter the Automated Spreadsheet Diagnotic Tool. With automated speadsheet diagnostics, an auditor or spreadsheet developer can quickly analyze any and all external links (including dependent spreadsheets, Access databases, text files, web feeds, SQL queries, etc.) and create graphical diagrams to help document and verify the data sources are correct. Here is an example created with Prodiace Spreadsheet IQ:

The application of color coding schemes can be used to easily understand and identify the spreadsheet structure, including input cells, formulas, external links, blank referenced cells, errors, and more. Here is an example color scheme applied to a workbook using the Color Scheme tool in Prodiance Spreadsheet IQ.

With the right tool, an extensive set of diagnostic checks can be run on any spreadsheet to document and analyze the spreadsheet structure. This process also highlights any red flags or potential errors that will require remediation. Potential red flags may include formula errors, blank referenced cells, hidden rows and columns, invisible cells, numerics stored as text, names with errors, very hidden worksheets, etc. Here is an example report from Prodiance Spreadsheet IQ.

There are several other powerful features included with such tools, including the ability to clean excess cell formatting to optimize file size and performance, ad-hoc error checking tools, cell and worksheet dependency diagrams, and more. You can learn more about Prodiance Spreadsheet IQ or download a trial version on our web site.

The “So What?”

The bottom line is that using Automated Spreadsheet Diagnostic Tools can really accelerate and ease your EUC baselining efforts and help spreadsheet developers to create error free spreadsheet models. The ROI is simple:

• Before Automated Diagnostics: 8+ man hours to validate each complex, critical spreadsheet model
• Typical # critical spreadsheets: 500 (you may have more or less)
• Typical hourly rate for a spreadsheet domain expert: $225
• Cost Before: $900,000 (ouch, who has the budget for that??!!)

Based on our experience, the use of Automated Spreadsheet Diagnostic Tools can reduce these efforts by more than 50%, but we’ll assume 30% savings to be conservative here.

• Typical cost Savings/Cost Avoidance:  $270,000 (per typical project)

This is real cost savings if you have a heavy reliance on critical spreadsheets because its work you need to do anyway. Additional cost savings and productivity gains can be realized by using technology to automate inventory, risk assessment, and change controls.

Tune in next week for more Best Practices!