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