PERFORMING THE AUDIT
Obtain Test Files
Where there might be a large volume of data that would be time consuming for the client to provide, you may wish to agree to obtain test files first with a limited number of records.
Obtaining sample files ensures that the fields and layout are as desired. This also ensures that the output format is readable and the data transfer method is tested. Any errors or integrity issues can be dealt with on the smaller scale.
If successful, the client should use the same query but expand the date range to cover all the records for the period for which data is requested.
Once obtained, the data can be imported into IDEA.
Data quality can be assessed by its accuracy, integrity, completeness, validity, consistency, and uniformity.
Completeness and uniformity are the result of a well-designed and implemented data request, since this is based on the original computer data. Completeness checks for unwarranted inclusions or required data that is omitted.
Data irregularities such as incomplete data can cause nonuniformity. Nonuniform data may cause issues such as skewed columns or make it difficult to distinguish the data. Nonuniformity between files may create issues for procedures that require a common key between the files.
To evaluate data quality received, steps that can be taken include:
- Reconciliation: Reconciling the data to an independent source is a good indicator that the data is complete. The data may include unintentional nonfinancial, budgetary, or statistical information, which will be detected by reconciliation. However, this process cannot tell you whether some of the records are at a summary level rather than the desired detailed level.
- Count: A total count of the number of records imported into IDEA matching the total number of records from the output can contribute to the assurance of complete data.
- Control total: Similar to the count, summing the amounts in a numeric field and matching it to the expected total is assuring. Debit, credit, and other fields may be summed. For some files, such as a trial balance, the control total in a net amount field is expected to be zero.
- Field statistics: IDEA's Field Statistics easily displays information such as net value, number of zero items, minimum value, maximum and number of data errors, which can assist in the determination of the data integrity. Reviewing the Earliest Date and Latest Date statistic tests the boundary of the data for the beginning and ending period range.
- Browse data: A quick browse while scrolling through the data may reveal data integrity issues, such as the actual data layout is not the same as in the file layout provided to the auditor. Browsing allows for obvious omissions or inclusion errors possibly due to query issues.
- Data dump printout: Matching the IDEA sample records contents to the same sample records printed from the system can confirm data content and confirm whether fields are populated with proper data.
Cleaning Up the Data
Data received may not always be as anticipated. At times, some adjustments may be required. For example, both the first and last names of people are in a single field rather than appearing in separate fields. In that case, functions such as @split and @simplesplit may be applied.
For files from different systems that need to be joined, the common character field may have different lengths; for instance, one file may have a leading zero and the other does not.
Typically the common field in two files will be of different field types. This can be resolved by simply changing the field type in one file to match.
Sometimes it is more efficient to make IT aware of the problem and have them rerun the job rather than try to fix all the issues yourself. This also establishes the correct routine for future data requests.
Documenting the Results
Results of the download should be documented so that the process can be simplified for the next time the files are needed. Procedures for cleaning up the data for eficient usage should also be noted. If the download was successful the first time with no data adjustments required, this should also be documented. Obtaining a copy of the query for your own file is a good practice.