Management Business Process Application

Executive Summary

Problem

Cuong Long Pharmaceutical Company is a small enterprise in Ho Chi Minh city, Vietnam with less than ten employees to operate the whole business which generates approximately one million dollar revenue annually. The main customers of the firm are the hospitals in Vietnam. The firm imports medicines from Korea, Japan, India and China and distributes those products to the customers. They get the customers through bidding and relationship so that their medicine will be used for the patients in these hospitals. Although the firm has a quite good amount of annual revenue, the management and accounting processes do not operate efficiently.

The accountants of the firm must keep track all the sales transaction manually. They do use excel to record each sale transactions; however, they need to process all of those records manually at the end of the month to calculate commission, total sale of each customer and generates inventory report. This process takes a lot of time and causes mistakes due to human involvement.

Purpose

With the request from director of Cuong Long Pharmaceutical, who is my father, I decided to apply my skills and knowledge in VBA class to build an application that can meets the needs of the firm. Basically, the firm need to keep track of employees, inventories and customers. In addition, the firm also needs reports for current inventory, sales of the month and commissions of the month. The firm only needs the data for those report so they can perform further analysis with Pivot Table. Therefore, for this project, I do not support formatting for the reports.

• In general, my project delivers the following functions:
• Support to manage (add and edit) employee
• Support to manage (add and edit) product
• Support to manage (add and edit) customers
• Create and record sale transactions
• Generate current inventory report, sale of the month for each customer, commission of the month for each employees
• Automatically send email to employees regarding commission of the month

 Conclusion

This project has taken more than 40 hours in about two months (including interviews for requirements, analysis, design, programming and testing) with most of the features function correctly. The scope of the project can be easily expanded which can support other functions such as Pivot Table, generate reports based on different categories (employee, customer, date, etc.), etc. However, this application has met most of the needs to support the firm. 

Download:

VBA file: http://files.gove.net/shares/files/13w/lphong/FinalProject.xlsm
Project report: http://files.gove.net/shares/files/13w/lphong/FinalProject.pdf
CreateDB script: http://files.gove.net/shares/files/13w/lphong/CreateDB.sql