The Development of A Program to Create A Report Showing Inventory Data Case Study - Raw Material Department, XYZ CO., Ltd.

Abstract

The purposes of this research were 1) to improve an inventory report by Microsoft Excel and Excel VBA; 2) to reduce the process of preparing weekly inventory reports; 3) to reduce the time of preparing the weekly inventory report; and 4) to report the inventory for management decisions. The area of study for this research is the Raw Material Department of XYZ CO., Ltd. The sample group was 4 weeks’ inventory report before the adjustment and 4 weeks after the adjustment. The tool used in this study is Cause and Effect Diagram to find the cause of the problem. The problem found is the time to prepare inventory reports and then the Flow Process Chart was used to analyze the process of preparation of the inventory report. It was found that the preparation of the inventory report had many unnecessary procedures with unnecessary repetitions. For this reason, Microsoft Excel has been developed with Excel VBA to improve and eliminate unnecessary work procedures. The results were found to be able to reduce the process of preparing inventory reports. Before the update were 295 steps down to 230 steps after the update process; and reduced the working time by 64.34%. The developed system has been evaluated by those who used the information from the report (or the administrators). The evaluation results from the questionnaire showed that the assessors were highly satisfied.  This indicates that the developed program can help support the decision making and improve work efficiency as well.