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

Monday, April 13, 2015

Fast Track Automation (Excel Fast Track)

Executive Summary
For the past year, I have been running the accounting procedures for the Brigham Young University School of Music. As the student accountant for the School of Music I am given several tasks that are necessary for the department’s ongoing functions. A few of these duties include overseeing 100+ student employees, transferring funds through journal entries, filing expense reports, etc. But one of my largest functions includes payment to independent contractors through the BYU payment system called Fast Track. I quickly noticed that the process was very ineffective and was taking up a large amount of time. The School of Music, by nature, brings in a lot of outside musicians, lecturers, recording engineers, etc. Each of these independent contractors is unique and provides different obstacles to get by. BYU does not make this process easy. However, there are many steps to the process that can be understood/performed by anyone, which subsequently means these steps may be automated. My goal was to make this process easier and quicker so that time spent is reduced and others less experienced can figure it out without too much effort.

My first step was to organize all of the data in a spreadsheet in a way that would require the associate buyer (person making the payment on behalf of the department, usually myself) to collect all of the required information prior to attempting to process the payment through Fast Track (which is where time is often lost). The spreadsheet also has an area for additional information that makes the process move faster if collected beforehand. The problem I then found was that some of the cells were more complicated to fill out than others. For instance, when a professor wants to make a payment, they should fill out a payment request form (see attached form under ‘Additional Documentation’) with all the required information already on it. When I am handed this paper, I scan it into our system and input the information into the spreadsheet. Therefore, entering the dates to be worked should be an easy step. Categorizing the payment, however, requires a lot of background knowledge of BYU requirements and governmental restrictions on independent contractors, which most people don’t have. It then became my goal to implement a series of userforms that would prompt the user to input basic information and answer simple questions that would then complete the process automatically, without additional research.


Now, when a professor brings in a payment request form while I am not in the office, any of our capable receptionists can input the information and start or even do the entire process on their own. And when I am here to do it myself, the process is a lot quicker and requires less flipping through notes and more simple data entry.

Final Project WRITE UP:
Final Project CODE:

No comments:

Post a Comment

Blog Archive