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


  • Muhammad Ahmed Kalwar Shafi Private Limited, Lahore
  • Asif Nawaz Wassan Mehran UET
  • Zuhaib Phul Sindh Madressa-tul-Islam University
  • Muzamil Hussain Wadho BBSUTSD
  • Tanveer Sarwar Malik Starlet Innovations Limited
  • Muhammad Ali Khan Mehran UET



Material Cost, Footwear, Shoe Costing, Cost Comparison


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.


Download data is not yet available.

Author Biographies

Muhammad Ahmed Kalwar, Shafi Private Limited, Lahore


Asif Nawaz Wassan, Mehran UET

PhD. Research Scholar, Industrial Engineering & Management, Faculty of Engineering

Zuhaib Phul, Sindh Madressa-tul-Islam University

Research Scholar, Computer Science

Muzamil Hussain Wadho, BBSUTSD

Assistant Professor, Electrical Engineering, Faculty of Engineering

Tanveer Sarwar Malik, Starlet Innovations Limited

Executive Officer (Planning & Costing)

Muhammad Ali Khan, Mehran UET

Industrial Engineering & Management, Faculty of Engineering


Abidin, I.Z., Juahir, H., Azid, A., Mustafa, A.D., & Azaman, F. (2015). Application of Excel-VBA for computation of water quality index and air pollutant index. Malaysian Journal of Analytical Sciences, 19(5), 1056-1064.

Abraham, R., Burnett, M., & Erwig, M. (2008). Spreadsheet Programming. In Wiley Encyclopedia of Computer Science and Engineering.

Ahmadi, A., Robinson, P.H., Elizondo, F., & Chilibroste, P. (2018). Implementation of CTR dairy model using the visual basic for application language of Microsoft excel. International Journal of Agricultural and Environmental Information Systems, 9(3), 74-86.

Ajinkya, W.S., Sachin, R.S., Manoj, S.D., Rajesh, R.M., & Mangesh, J. B. (2017). Preparing excel sheet for estimation and costing. International Journal of Engineering Sciences & Management, 7(1), 310-317.

Balson, D. (2012). User Defined Spreadsheet Functions in Excel. Proceedings of EuSpRIG 2012 Conference "The Science of Spreadsheet Risk Management.

Bartoszewicz, G., & Wdowicz, M. (2019). Automation of the Process of Reporting the Compliance of the Production Plan with Its Execution Based on Integration of SAP ERP System In Connection With Excel Spreadsheet and VBA Application. In Digitalization of Supply Chains.

Belchior Junior, A., Bruel, R.N., Andrade, D.A., Sabundjian, G., Macedo, L.A., Angelo, G., … Conti, T.N. (2011).Development of a Vba Macro-Based Spreadsheet Application for Relap5 Data Post-Processing. International Nuclear Atlantic Conference, 978-985.

Blattner, D.E., & Valrico, F. (2007). Patent No. US 2007/0055688 A1. United States.

Blayney, P.J., & Sun, Z. (2019). Using Excel and Excel VBA for Preliminary Analysis in Big Data Research. In Managerial Perspectives on Intelligent Big Data Analytics. IGI Global (pp. 110-136).

Botchkarev, A. (2015). Assessing Excel VBA Suitability for Monte Carlo Simulation. 1-30. Retrieved from

Broman, K.W., Woo, K.H., & Woo, K.H. (2017). Data organization in spreadsheets Data organization in spreadsheets and (Vol. 1305).

Chatvichiencha, S. (2015). Enhancing Computational Thinking by Excel-VBA Based Problem Solving. The 2nd International Conference on Innovation in Education, 284-290.

Cirujano, J., & Zhu, Z. (2013). Automatic reporting for manpower resources. Proceedings, Annual Conference - Canadian Society for Civil Engineering, 1 (January), 710-719.

Ding, H.L., Qi, K.Y., Zhaq, X.L., & Xu, G.F. (2017). Tibetan Typographical Specifications and Technical Realization Based on Word VBA. 4th International Conference on Advanced Education and Management, 407-412.

Dunn, A. (2009). Automated Spreadsheet Development. In arXiv preprint arXiv (pp. 908-928).

Evensen, H.T. (2014). A versatile platform for programming and data acquisition: Excel and Visual Basic for Applications. ASEE Annual Conference and Exposition.

Fisher, M., Cao, M., Rothermel, G., Cook, C.R., Burnett, M.M. (2002). Automated Test Case Generation for Spreadsheets. 24th International Conference on Software Engineering, 141–153.

Harahap, M.I.P., & Azmi, M.H. (2017). Development of Excel VBA Program for Small Drainage Network. E-Academia Journal, 6(1), 216-227.

Hila, R. (2009). Water Quality Data Management Database.

Kalwar, M.A., & Khan, M.A. (2020). Optimization of Procurement & Purchase Order Process in Foot Wear Industry by Using VBA in Ms Excel. International Journal of Business Education and Management Studies (IJBEMS), 5(2), 80-100. Retrieved from

Karim, A.J. (2011). The Significance of Management Information Systems For Enhancing Strategic And Tactical Planning. Journal of Information Systems and Technology Management, 8(2), 459-470.

Kuka, S., & Karamani, B. (2011). Using Excel and VBA for Excel to Learn Numerical Methods. 1st International Sympsosium on Computing in Informatics and Mathematics, 365-376.

Lessa, J.D.R., Lessa, P.F.R., Almeida, P.A., Junior, M., & Guimarães, H. de V. (2016). Mathematical Model and Programming in VBA Excel for Package Calculation. International Journal of Engineering Research and Applications, 6(5), 55-61.

Minto, C.F. (2009). PKPD Tools for Excel.

Norton, T., & Tiwari, B. (2013). Aiding the understanding of novel freezing technology through numerical modelling with visual basic for applications (VBA). Computer Applications in Engineering Education, 21(3), 530-538.

Perry, K.M. (2012). The Call Center Scheduling Problem using Spreadsheet Optimization and VBA. Virginia Commonwealth University.

Porter, D., & Stretcher, R. (2012). Automating Markowitz Optimizations Using VBA. Journal of Instructional Techniques in Finance, 4(1), 9-16.

Raza, M., & Gulwani, S. (2017). Automated data extraction using predictive program synthesis. 31st AAAI Conference on Artificial Intelligence, AAAI 2017, 882-890.

Sato, K., & Yokoyama, R. (2001). Teaching Aid for Remote Sensing and Map Imagery Analysis Using Excel Spreadsheet and VBA. 22nd Asian Conference on Remote Sensing. Retrieved from

Wettlaufer, G.J. (2010). Merlin.Net Automation of External Reports Verification Process (California Polytechnic State University). California Polytechnic State University.

Yan, Q., & Wan, Y. (2017). Using the special font and VBA program to make bill of materials in the transmission line engineering. Revista de la Facultad de Ingenieria, 32(2), 335-341.