AuditNet Resource List
Audit Programs
AuditNet Virtual Library
AuditNet Newsletter
Ask the Auditor
AuditNet Mailing Lists
Audit Jobs
Travel
Career Links
Partner Discounts
Search
Sign Guestbook
AuditNet Sponsors
Advertising Opportunities
About AuditNet
About Jim Kaplan
AuditNet Seminars

Proving the Numbers

By Cynthia Thompson, 
Business Development Manager, SGP International, Inc.

With the proliferation of database systems and the variety of source data contained in them, there is an increasing need to validate data to assure consistent, accurate information.  However, there are a number of challenges that arise in pursuit of this effort.  Inevitably, during a meeting, it seems as if each analyst has a different number, and these values are sometimes used for different purposes.   The battle of the spreadsheet, where a team meeting is spent arguing about the source of the numbers rather than what they represent, is not productive or conducive to efficiency. 

The Reconciliation Problem

Have you ever attended a meeting with your reports in hand, only to find that others have the same reports for the same timeframe, but with different numbers?  When consistently generated reports contain different values, many issues need to be considered to ensure accurate information.   This article will explore these issues as well as explain how the two most prevalent reconciliation methods, the Source Transaction Method and the Line Grouping Method, can be used to locate inaccuracies.  Typically the Line Grouping Method is used for gaining consistency among groups of people or definitions, while the Source Transaction Method is used to verify accuracy of system data and the placement of data into categories.  While it can seem tedious at times, working through the process, slowly and steadily, is essential to reconciliation success.

The Source Transaction Method

In the Source Transaction Method, two source files are compared to each other to investigate variances.  When working with two source files that should be the same, it is important to work with the original source data.  Even if modifications are required, original source data will always be available to be used for verification purposes if necessary.  With two source files at hand, translations or conversions might be necessary to allow for comparative processing.  Within transaction processing systems, an example of this would be two systems that use different date formats.  For comparison purposes, a translation must occur to facilitate comparisons.

Once each file is comparable, records must be separated into records that are in common and records that are in one file but not the other.  To separate these records, a join key must be determined.  A join key is a field or group of fields that designate a one-to-one relationship between the two recordsets.  Finding the join key is essential to continued reconciliation accuracy.  For example, if there were 45 records in one file and 42 records in another file, with 40 records in common between the two files, after joining them, there would be a total of 47 records.    Separating them into 5 records, 40 records, and 2 records will allow for reconciliation.  Without the correct join key, there would be more records, invalidating any results derived from this source. 

After separating out the files, for comparative purposes, investigation into the justification for the lack of match for some records occurs.  Additionally, further investigation needs to occur with the records the two files have in common..  Reconciliation requires that these two files match, so analysis that generates reports where values are equal within a given range will generate valid records.  Records that are not equal will again require further scrutiny.

When using the Source Transaction Method, it is sometimes helpful to view data using an absolute value function to supplement a standard mathematical equation.  This is particularly valuable when dealing with General Ledger accounting balances.  Since debits should always equal credits, transactions could be placed in the wrong account, and would not be easily evident unless the absolute value function is applied to determine the use of an incorrect account. 

The Line Grouping Method

In some circumstances, access to the original source data is limited or inappropriate because of volume.  This will automatically eliminate the Source Transaction Method, leaving the Line Grouping Method as the way to proceed. 

Typically, the Line Grouping Method is performed specifically to solve a point in time issue.  Definitions play an important part of the Line Grouping Method.  For example, currency exchange and translation usually causes variances, depending on which one of the multiple methods are used to apply a currency rate.  Decisions made based on these different numbers could yield different conclusions because they applied different rates. 

Since reports by their very nature are snapshots in time, every report has a starting point.  For good communication and substantive discussion validity, each individual involved has to agree on the same starting point.  There is no right or wrong answer, yet if one person is viewing data from last year, and a second person is viewing data from the current year, data variances will inevitable occur.

After agreeing on a consistent starting point, additions and subtractions need to occur to reconcile two different values.  These could be issues related to source timing, system timing or categories of information that are not relevant to analysis.  Source timing requires adjustments, usually subtraction, to the numbers primarily due to the age of the transactions and their relevancy to the topic under discussion.  In these days of 24 hour processing, modifications could occur because of system timing issues where transactions are still being processed by one system and are not yet sent to another system.  Identifying categories of information not relevant to analysis leads to more fine-tuning, to cull the most value from data.   

The Line Grouping Method is more often used internally by a department within an organization, and perfected over time.  A department manager learns which numbers provide the most relevance for him or her, and working from basic corporate numbers, changes those numbers to generate the most significance from the numbers provided. 

With the proliferation of technology, one would assume that software is commonly applied to automate these methods.  In reality, many organizations are still using spreadsheets or rudimentary manual paper to perform these reconciliation processes.  While the Source Transaction Method can be easily computerized, the Line Grouping Method, which often depends on the unique requirements and processes of the unit being evaluated, is more complicated to automate.  The best reconciliation software will be an easy to use tool designed to mirror the manual processes already in place.  Additionally, like the manual reconciliation processes, an automated process should be fluid and flexible.

While reconciliation can be a tedious process, data accuracy is too important to ignore.  Decisions are made based on data accuracy, so all efforts must be put forth to validate the data.  Once data has been validated, the relief and confidence in the numbers are almost tangible, making data reconciliation well worth the time and effort required.

Cynthia Thompson is Business Development Manager of SGP International, Inc., developers of DataCheck, Data Integrity Assurance System.  For more information, contact Cynthia at 212-685-0819 X11.  Or visit their web site at www.sgpnyc.com.