Background
This project comes from an old work project I was working on at my previous place of employment. I used to work for MyComfort, a Utah-based mattress company. I worked in the warehouse organizing paperwork part time and I did IT support part time. The VP of operations decided that he wanted to keep a tracking system of all the mattresses that were manufactured with a serial number system. Since we didn’t have money for an advanced serial number system, I created a simple Excel spreadsheet and a small VBA module in which the warehouse worker could select the type of mattress, the size, and it would generate a unique SN. I wanted to make a more sophisticated system but my lack of VBA knowledge and limited time prevented me from doing so before I left the company for another job. Now, with greater knowledge of VBA, I will complete what I intended.
Business Problem
The reason why this serial number system was recommended by the VP of Operations was for tracking inventory and warranty issues with the beds. First of all, there were numerous problems with MyComfort being over and under billed for inventory manufactured. The paper work and POs for orders were a mess and not organized. Many times an order would not be 100% finished and/or delivered and MyComfort would still be billed. Using serial numbers and a tracking system as to when things got shipped out would make sure this problem wouldn’t happen.
Second, there had been a lot of poor quality mattresses manufactured and this tracking system would be able to identify beds that were manufactured around the same time that had similar problems to a product that was reported to have issues and be inspected by a customer service representative.
Also, to help with these problems, the company also decided lease out an office in the manufacturer’s warehouse to keep track of production and do QA onsite. This way a MyComfort worker could attach serial numbers to the beds even before they left the manufacturer’s site.
Program Solution
My solution to this problem includes an Excel workbook with a two forms and two sheets. The procedures and program would facilitate the following:
1. As POs are created, an employee would enter in (using the 1st VBA form) each product with the quantity.
2. The worker would submit the PO into the system and a Word document would be generated with the serial numbers for easy printing (perhaps Avery label sticker templates would be used for an easy ‘stick-on’ serial number). The serial number would be unique and is based on the mattress type, size, and month in which it was ordered. Two stickers would be created for every serial number. This is because one needs to go on the mattress itself and the corresponding PO.
3. As each serial number is produced, a worksheet is populated. Each item produced is listed along side of its serial number, the PO number, and the date of order.
4. As each bed is produced, the worker would have to stick the serial number on each mattress (on tag). Also, the worker would place the stickers on the POs (actual document) in which they were found
5. When a shipment is about to go out, the worker would return to the program and enter in exactly what product is going out by selecting the serial numbers/product. This would be done using a multi-select list box.
6. The worksheet would be populated with the date the product was actually shipped. These worksheets would be organized by month and would be able to be sorted by each column for reporting purposes.
No comments:
Post a Comment