APX Licensing Reporting Tool

Tracy Farr and Trevor McKendrick

Executive Summary

APX Alarms must ensure all its sales reps and technicians are licensed in the states where they knock door-to-door. This process must be completed every year as most reps do not sell in the same state in simultaneous years. Also adding complexity is the large turnover rate that APX experiences, with some licensees only selling for APX one summer and then moving to work for other companies in subsequent years. Sales representatives are also commonly licensed in more than on state or city, which adds to the complexity of reporting requirements.

The company has a state license department that tracks all licenses and the status of reps whose licenses have expired. When we first met with the department their inspection and reporting process was manual and extremely repetitive. For instance, the manager-level expiration reports are sent out on a very regular and frequent basis. These reports inform each manager about the status of the employees’ licenses that they supervise. In order to run the reports, the department must import the latest license data from their Contract Management Status database (CMS). They then filter by whatever field they’re interested in to view the status of all licensees, usually by state or manager. They then save the filtered data to a new spreadsheet or tab and notify via email whomever the report may concern (most often managers and regional managers). They must do this for every group of licenses they wish to track and for each individual to receive a report. With hundreds of managers, this quickly becomes a tedious and difficult task with each manager having a different email address and being sent a different report.

In order to improve this process, we developed the Licensing Reporting Tool. This Excel-based application helps to streamline license reporting processes, not only decreasing the amount of time spent on reporting needs, but also increasing the accuracy of such reports. Its main functionalities include the Query Wizard, Session Archiving, and the Email Wizard.

This report will attempt to outline these general functionalities; helping to explain the logic, Visual Basic code, and user-interface processes. It is important to note that due to the brevity of this report, not all modules, sub-procedures, functions, or objects will be discussed in detail. This report will walk through the main user forms, functions, and procedures that help to make the project work.

Attachments:

Full Report

Project File

Data File