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 6, 2012

Hobble Creek Bank Reconciliation Tool

Executive Summary
Hobble Creek Learning & Enrichment Center, a small preschool and tutoring school located in Springville, is run by a retired high school teacher. I was introduced to Stephanie through my roommate, Steve. During the time Stephanie was experiencing difficulty with her accounting and book keeping. Hobble Creek barely receives enough cash to pay its employees, leaving Stephanie with most of the administrative burden. The last few months my friend and I have dug up the current years books in an effort to reconcile the books with the bank account. We are nearly finished cleaning up the books and would like to improve the processes in place to make it simple for any of Stephanie’s employees, who have no or little accounting background, to maintain well kept books.
If you have ever balanced a checking account you would know that it’s not rocket science—another reason why I became an accountant and not an engineer!  Hobble Creek is a small business and should not require too much time to keep up on book keeping and monthly bank reconciliations. One difficulty arises when customers pay with credit cards. Hobble Creek has a vendor (Global Pay) process the credit card payments. Global Pay processes the payments and deposits the lump sum payment of many customers minus a transfer fee into Hobble Creek’s bank account. When a customer pays, Hobble Creek records the transaction on its books. You can imagine the difficulty that arises when trying to reconcile the lump sum payments with individual customer payments, especially when the two totals won’t match because of the transfer fee.
Impact of a Better System
The individuals that were handling the books earlier on were not accustomed to the complexity and gave up on balancing the books. Without this complexity not only would it be easier to train employees to handle the books, it would also cut down more than an hour of time off of labor. These are hours saved that employees can instead use for teaching or other administrative matters.
In order to alleviate some of the complexity in the bank reconciliation process, I decided to build a excel program that matches Global Pay’s lump sum payments received in the bank with individual customer payments that are recorded in Global Pay and should match Hobble Creek’s books. The program will navigate to the Global Pay webpage and select the correct report for the current month of individual customer payments. The user will then insert the lump sum payments from the bank statement which will generate a clean report that ties customer payments to lump sum payments and also displays the amount of transfer fee that needs to be recorded in the books. In order for the system to be successful it would need to be very simple and easy to use. I also would like for it to take an average user less than 5 minutes to use.

No comments:

Post a Comment

Blog Archive