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

Background
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.

Issues
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.

Overview
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. http://files.gove.net/shares/files/12f/jacoblh/Jake_Heywood_-_Final_Project.xlsmhttp://files.gove.net/shares/files/12f/jacoblh/Jake_Heywood_-_Final_Project.pdf