Accounting Audit Utility

A national computer retail chain had applications gather data from multiple sources with no facilities to determine matching and unresolved information between the disparate systems. Since the information was stored on mainframes and all the mainframe programmers had left the company, the customer had no way to analyze the information stored on the mainframe.

A prototype was demonstrated by the customer which would download the mainframe information into Excel Data Interchange Format (DIF) files. Macros constructed within Excel were subsequently used to analyze which records were matching and unresolved. The matching required examination of several fields. For all records that were unresolved, an appropriate error status had to be associated with the pertinent fields from the specific source of data.

The prototype presented by the user had major problems. First, no one in the organization was maintaining the application. Second, when the amount of data exceeded 16,000 rows of information within either the matched or unresolved data, Excel would fail. The customer stated that they expected to reach the 16,000 row limit by May of the current year. Finally, there was little status information depicted during the comparison process. Since the procedure took over an hour, it was unclear if the application was still running or if it had hung the machine. Each day the size of the Excel files would grow to reach in excess of 10 megabytes.

A new application was designed and developed based upon the principles shown in the prototype. Improvements were made to provide the user a running status of the comparison process. The overall time to run all procedures was reduced from over an hour to less than 15 minutes. Since the application was written to use a database rather than Excel, the 16,000 row problem was no longer an issue.

A report template database was built to allow further customization in a single location to provide on-going modifications to report formats and criteria. Since the report information was exported using the template to an archive each day, the data storage each data was considerably less than what the prototype was producing.

Convenience features were added to allow the client to query for information automatically. Most of these features where predefined queries for which the user could specified dates or date ranges. This improved comprehension and focus of the users to relevant information. Reports are produced daily and transferred to a daily report archive. Any report (with filtering applied) can be exported to Excel for further editing and manipulation.

The users were thrilled with the speed, ease of use and conveniences built into the application. The user interface for the matching and reporting databases were built with ACCESS/97. The application runs on Windows 95/98/NT.