Automation of production plan generating workbook at leather footwear company of Lahore Pakistan by using VBA in Microsoft Excel

Abstract

In Small and Medium Enterprises (SMEs), all the reporting tasks are carried out in Microsoft Excel. The employees spend all of their time working on the reports and in the case of an error in the report; a tremendous amount of their time is incurred on the detection of that error. At one of the leather footwear companies in Lahore, Pakistan, report automation was carried out using visual basic for Application (VBA) in Microsoft Excel. The purpose of automation was to increase the reporting efficiency and minimize the chance of error. The authors automated the generation of production plan papers, which used to take 3.11 minutes to be made per plan paper. 3.11 minutes were required just for a single order of only one footwear article). This research provides the framework for the automation of manual reporting in Microsoft Excel. This automation was conducted by using VBA in Microsoft Excel. In the VBA code, the loops and conditional statements were used to program the manual activities to be performed in the report. Initially, the manual method was demonstrated in detail then way of report automation was the focus of discussion. The comparison of both methods was conducted in terms of time utilization. The manual method encompassed a series of activities whereas; the automated template included the buttons with few clicks. A time study of report-making by manual and automated method was conducted which indicated that the automated method was 1.36 minutes faster than the manual method. This research contributes to the provision of a detailed framework, with the help of which any manual work in Microsoft Excel can be automated. It was also indicated by this research that SMEs who cannot afford the implementation of Enterprise Resource Planning (ERP) software, have the option of VBA in Microsoft Excel by which they can enhance their reporting efficiency and office employees` productivity.

Keywords

• production
• plan papers
• visual basic for applications
• macros