These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 10, 2009

Reconcile G/L Accounts: A VBA Application

Executive Summary

Digital Gateway, Inc. (DGI) is a small software company located in Provo, Utah. DGI has developed an ERP system that is designed specifically for the digital imaging industry. This ERP system is called e-automate and DGI also has developed multiple add-on products. In addition to developing these software products, DGI offers customer support services. DGI’s Customer Support Department has multiple specialties, one of which is accounting support. Customers experiencing problems with the accounting aspect of the products are referred to a support technician with accounting expertise. On a frequent basis, the accounting issues experienced by customers involve reconciliation discrepancies with A/R, A/P, or inventory. The ERP system has built-in controls to mitigate the risk of reconciliation discrepancies, but problems often arise when users choose to ignore these controls.

When these controls are ignored, transactions causing the reconciliation discrepancy are identifiable either by a database field called the transaction type or by being used as a detail account. DGI’s current process for identifying these problem transactions is to first identify the appropriate A/R, A/P, or inventory general ledger account. Once the general ledger account is identified, the process is to obtain a list of all transactions that hit the targeted general ledger account(s). After the list is generated, the list is filtered and sorted by transaction type and transactions with inappropriate transaction type or that are used incorrectly as a detail account are identified. This process is time consuming considering the majority of databases contain many thousands of transactions.

The solution that I have developed provides a less time consuming process. The VBA application will automatically query the database and identify transactions with an inappropriate transaction type or that are inappropriately used as a detail account. The system will connect to the necessary database, and based on whether the discrepancy relates to A/R, A/P, or inventory, will identify the appropriate general ledger account(s) and run appropriate queries to identify problem transactions. The application reports any problem transactions in a list format and organizes the information in multiple useful summary formats.

To download this application, please go to the URL provided below:

To view detailed documentation regarding this application, go to this URL :

No comments:

Post a Comment

Blog Archive