Executive Summary
a. Description of the business
Chao’s Asian Market is a very well known Asian grocery store in Provo and the surrounding area. They have a large base of loyal customers. Two months ago, Chao’s moved to Orem and the owner sold the store to Ernie Silva. The new location that they currently moved too also belongs to Ernie; this location is 4 times larger than the old store was in Provo. However, the old owner didn’t have any systems to calculate inventory in stock, keep track of expired items, and other supply chain knowledge.
One day as I shopping in the store after they moved to Orem, I noticed that the store’s inventory was very bad. The old owner left a great amount of expired items and products that are not well displayed well at all in the store as they were just randomly placed on shelves.
Therefore, they hired me to control the inventory and make sure all the expired items are identified soon enough to apply discount as to avoid lost revenue due to spoilage. I have to remember and know where the products are in the store. However, the store is not fully expanded and I have brought in over 100 new products to meet customer demand. Remembering the prices and where they are all located is almost impossible. I have been thinking about these problems, looking for a more efficient way to manage the inventory since the first day I started working. I have made this VBA program as an answer to the problems of the store.
b. Overview of the program I built
I have showed the program to Ernie and explained him in details of how it works. He told me, “Lily, I’m impressed! Great job!” He is very happy that the cashiers and any employees won’t have to run and look for an item each time they can’t find out how much it costs when the customers check out. It will save time for both the customers and cashiers. One of the functions of the program is to find the products; it will also indicate the mark up price, the aisle number, and the arrival date. The arrival date is very important since Chao’s carries some unique Asian products, which we can only order once every two weeks. Therefore, the customers will ask for the scheduled arrival date.
As Ernie said, “It’s very good that now we can control the inventory and see what will expire soon so we can apply the proper discount.” I have built another function in the program, as the users click on expired in “<= 1 month” or “2 month”, etc. It will let us see the products which about to expire at those times, as it loops through the worksheet, comparing the current date and the expiration date. It uses the expiration date minus current date to get the days before expiration to track the inventory. The users also can look at the aisle number to take the products out as they are expired. After that, user can press the “Create Worksheet” button to create a new worksheet which only has the items they need to find (expired, <= 1 month, 2 month, 3 month, 4 month, 5 month). It is very helpful for the employees at Chao’s so they can manage the inventory totaling than 1500 products.
Chao’s Asian Market uses Outlook for emailing and they can also control their employee’s emails. I searched online for a way to automatically open Outlook and send the new expired items worksheet to whoever is in charge of recording and taking care of the expired items. Outlook will put the new worksheet into an email and send it automatically to notify that person.
No comments:
Post a Comment