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 Nametags.com. 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 Nametags.com vendor’s website and access the appropriate account (each department has a separate Nametags.com 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 Nametags.com 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.

Solution

I chose to automate steps 2-5 of the old process.  My solution requires the user to simply download all the Nametags.com 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.

The following file contains my writeup:

• http://files.gove.net/shares/files/14f/danjb/Final_Project_Writeup.pdf