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 11, 2014

Missionary Training Center 
Nametags Payment Process - Executive Summary

Business Description
The Missionary Training Center (MTC) facilitates the training of thousands of LDS missionaries each year.  Each missionary is issued nametags that are worn on their shirts.  Similarly, the employees all wear nametags that identify them.  These nametags are purchased through a vendor called Several departments at the MTC have accounts with this vendor and place orders independently.  Once an order is received the packing slip is forwarded to the accounting office.
Business Issue
These packing slips are delivered to the accounting department sporadically and in fairly large quantities.  Because of this, these nametags invoices were normally processed as a batch every Friday.  This process resulted in many user errors and approximately 2 hours of time.  Previously, the process to pay these nametags went as follows:
1)      Organize them according to the department from which they originated. 
2)      Log onto the vendor’s website and access the appropriate account (each department has a separate account).
3)      Use “Control-F” to search for the invoice that corresponds to the packing slip number.
4)      Print the correct invoice
5)      From the invoice, copy several key pieces of information and manually type it into a spreadsheet that tracks every invoice paid.  Key information includes: the packing slip number, invoice number, date, amount, account to be charged, etc.
6)      Mail the invoices to BYU for final payment.
I chose to automate steps 2-5 of the old process.  My solution requires the user to simply download all the invoices and by clicking a button the macro will:
1)      Format the invoice so that the Excel invoice is in an appropriate format for payment
2)      Prompt the user to input merely the packing slip number
3)      Find and print the corresponding invoice
4)      Input all relevant data from the invoice into the spreadsheet that tracks nametags invoices

Since creating this macro the time spent on this activity has been reduced from approximately 2 hours to less than 20 minutes and user error has been drastically reduced.

No comments:

Post a Comment

Blog Archive