Automation of order costing analysis by using Visual Basic for applications in Microsoft Excel

Abstract

In the modern world of technologies (sciences of arts), optimum operation time is the prime consideration. Technologies with greater operation time are needed to be modified or replaced as per the requirement of the current scenario. In small and medium enterprises of developing countries, almost all the reporting activities are conducted in Microsoft Excel. The manual operation in Microsoft Excel takes a long time (17.37 minutes, only if there was one order of only one article with one color) of the users; furthermore, there can also be a greater chance of error in the report. In the same way, the order costing report at the ABC footwear company of Lahore is prepared manually in Microsoft Excel which used to take a long time to be prepared along with the greater probability of errors in the report. Because of the above-mentioned reasons, the order costing report was decided to be automated with the help of visual basic for applications (VBA) in Microsoft Excel. The manual procedure including all the activities was collected from the planning and costing department of ABC Company. Moreover, all the activities were listed down and their time study was conducted. VBA macros were programmed with the objectives of automating all the manual tasks of the order costing report in Microsoft Excel and eliminating the chance of error (calculation error made by the employee). Macros, were programmed by the use of conditional statements, (i.e. if and else conditions) and various types of loops, (i.e. Do while loop and for loop). Automated decisions were made with the help of conditional statements during the operation and the conditional statements were circulated with the help of loops across the whole worksheet. Two userforms were designed for the execution of macros by the click of command buttons (having macros at their back). Total eight worksheets were incorporated into the automated template. All the programmed VBA macros were explained with the help of flowcharts made in the Microsoft Visio. After the report was automated, a time study of operations performed after each click was conducted with the help of stop watch. The data obtained by the time study was entered into Microsoft Excel so that the time of old and suggested methods could be compared. Comparison of both methods indicated that the suggested method took 85.92% less time as compared to the old method of preparing the order costing report. At the same time, the report made by the suggested method was free of errors and the workload of employees was also reduced.

Keywords

• footwear
• optimization
• order costing
• visual basic for applications