Development of a Bill of Materials System for Production Order Management Using VBA in Microsoft Excel: A Case Study of Curtain and Venetian blinds Products

Abstract

This research aims to develop an automated material listing system for production orders using Visual Basic for Applications (VBA) within Microsoft Excel. The case study focuses on the production of curtain and venetian blinds. An analysis of the case study company revealed significant issues with production errors and delays in placing orders. The root causes of these problems were analyzed using the "Why-Why" analysis technique. A new production ordering process was then developed by applying a data flow diagram to create a more detailed production order form. This form clearly lists the materials required for each customer order and calculates the total quantity of materials used. When the reorder point is reached, the system automatically calculates the Economic Order Quantity (EOQ) to optimize subsequent purchasing plans. The development of this database and automated system, using Microsoft 365 and Visual Basic for Applications, significantly improved process efficiency. The new system resulted in a 63.67% reduction in waste and production errors. Furthermore, it improved the production time efficiency for roller blinds, wooden blinds, and aluminum blinds by 27.5%, 32.5%, and 22.85%, respectively. A post-implementation user satisfaction survey indicated a high level of satisfaction, with an average score of 4.2 out of 5.0.