Automation of material cost comparative analysis report using VBA Excel: a case of footwear company of Lahore

Abstract

Small and medium-sized firms in developing countries perform the majority of all reporting tasks in Microsoft excel. The material cost comparative analysis (MCCA) report took the user 58.51 minutes to complete and there was also a higher likelihood of calculation error. The report was created in Microsoft excel by the Lahore-based ABC footwear business. This used to take a lot of time to produce and there was a higher chance that the report would contain errors. This is because Microsoft excel visual basic for applications was used to automate the report. This report's manual preparation method was compiled by the company’s planning and costing division. Each activity was listed, followed by the completion of a time study. VBA macros were built in Excel to automate all manual report-related tasks and reduce the possibility of human error. Conditional statements were applied to help automated decision-making. To enable command buttons to run macros, one user form was created. There were various worksheets included in the MCA template. The automated template was found to consume only 16.26 minutes and it was quicker than the current technique.