Executive Summary

As a Mary Kay Consultant, I essentially run my own business. This business is very heavily centered around inventory – ordering new inventory from a supplier, selling inventory to clients, etc. Up to this point, my inventory management system has consisted of the process shown in Figure 1 at the end of this section. There are two cumbersome and time-consuming parts of this process: checking my inventory (especially since I have multiple locations), and getting the product from Mary Kay if I don’t have it on hand. For my final project, I decided to use VBA to automate my inventory management to help alleviate these problems.

Project Overview

The first problem I dealt with in this project was having to manually check my inventory each time a client requests a product. I used a local database to keep track of my inventory, and created userforms to make it easy to communicate with the database. The userforms fall into three different categories (as shown in Figure 2): updating basic information (adding a new product, product line, or category); managing inventory quantities (recording a shipment or sale); and generating reports (finding the current quantity for a specific product). Having this information at my fingertips will save a significant amount of time in this process.

The second problem I worked to fix with this project was having to wait on shipments from company, which can often take a week or more. To decrease the frequency of having to wait on shipments before I can provide the requested to my client, I added a “preferred quantity” field to each product, which will be filled out when the product is initially added to the database. Each time I open the workbook, the dashboard displays a list of all the products for which the current quantity on hand is less than the preferred quantity for the product. (See Figure 3) This list will allow me to see what products I need to order from Mary Kay so that I will have them on hand when a client requests them, and they will not have to wait to get their product.

This project will allow me to provide better customer service to my clients and to not miss out on sales because I don’t have the desired product on hand. I plan to continue to develop this system to record more information about clients and sales, and provide more detailed reports that will give me valuable insights as I grow my business.

Figure 1 – Current Order Process