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

Wednesday, December 5, 2012

Global Trade Logistics Sales Report Program

Executive Summary
Global Trade Logistics (GTL) is a small shipping company located in Provo, Utah. My wife works as a combination of a variety of positions – she answers the phones, handles the invoicing and bills of lading, and retrieves quotes from various shipping carriers. Her boss (Justyn) is the owner of the company and usually stops by the office two to three times a week. Justyn actually owns a few small businesses here in the Utah Valley. Due to the amount of work and capital required to own and operate multiple small businesses, GTL does not have up-to-date accounting information systems. GTL currently uses a very basic online database that can only store transaction data and export it to an Excel file.

Once a month, Justyn logs into the online database, queries it, and downloads an Excel workbook containing that month’s transaction data. He then sends that Excel workbook to my wife and asks her to create a summary report that displays the following for each customer included in the month’s transaction list: total cost, total revenue, and total number of shipments. Creating this summary report by hand can take my wife quite a long time as a typical month’s transaction listing has between 500 and 1,000+ lines of information.

The purpose of my program was two-fold: 1) Improve the efficiency of GTL’s internal reporting process and 2) make my wife’s job easier. As was stated earlier, Justyn logs into the online database, runs the query, attaches the exported workbook to an email, and sends it to my wife who then downloads it herself, creates a summary report by hand, and sends it back to Justyn. This process can take anywhere from 3 to 5 hours to possibly even a day depending on when my wife actually gets the email and begins working on making a summary report by hand. This is an extremely slow and painful (for my wife) process that can be improved through the use of Excel VBA.

My program implements a user form that gathers a username, password, starting date, and ending date and then uses those inputs to log into GTL’s online database, run a query, and export sales transaction data to the user’s workbook. With the data in the workbook, the program then uses arrays to gather and group the transaction data into a clean professional-looking summary report. With this program, my wife can now create the sales summary report herself which saves Justyn’s time as well as her own and improves the overall internal reporting process of GTL.

No comments:

Post a Comment

Blog Archive