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.