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, April 17, 2013

Entering Transaction Data and Generating a Report for a company sales division

Executive Summary

The company that this workbook is defined for specializes in selling metal cutting and inspection equipment made by other manufacturers. As a distributor, it has salespeople who demonstrate how the equipment will enhance the operations of the potential customer. An example of the inspection equipment is equipment that flawlessly measures whatever surfaces it touches. The measuring “arm” moves easily, conforming to different angles and surfaces, producing flawless measurement. This might seem simple or even useless but these measurements are recorded and can be automatically input into CAD or other design programs. One practical use of these measurements in CAD is reverse engineering other equipment if necessary to know how they can customize the equipment to meet the specific needs of their business.
This company also provides support services including ordering spare parts, providing engineering resources, as well as helping buyers learn to use the equipment and troubleshooting any problems. In order to track one division’s quotes, demonstrations, and the resulting purchase orders, one salesperson asked me to automate a workbook they use to measure progress toward the division’s goals. The workbook now has the needed functionality.

The first user form allows the user to easily add potential transactions with corresponding names, salesperson, dates, products, categories, etc. Another user form allows the user to find previously entered transactions and update the progress of the transaction clear to the point of sale. Another function of the workbook is generating a report that counts how many demonstrations, quotes, and purchase orders were done each month and the total sales per month, per quarter, and for the year. These totals are then compared to the company goals automatically and a chart previously built updates to show the company’s progress so far.

Both of these user forms are accessible by clicking a button on the custom ribbon bearing the company’s name. Another button generates the report mentioned previously. Finally, the last button automatically saves a copy of the workbook under a name specified by the user and automatically emails each salesperson an attachment of the saved workbook copy as well as any message the user desires to be sent.

Discussion about learning and difficulties involved in the completion of the project are also included in this report. The company’s name has been omitted from this project completely and the data has been scrubbed to protect the identity of the company and its customers. The products and companies listed in the workbook have nothing to do with this company.

PDF Write-up:
Excel File:

No comments:

Post a Comment

Blog Archive