Automation of material cost comparative analysis report using VBA Excel: a case of footwear company of Lahore
Keywords: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.
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. https://doi.org/10.1002/9780470050118.ecse415
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. https://doi.org/10.4018/IJAEIS.2018070105
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. https://doi.org/10.17270/b.m.978-83-66017-86-3
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). https://doi.org/10.4018/978-1-5225-7277-0.ch007
Botchkarev, A. (2015). Assessing Excel VBA Suitability for Monte Carlo Simulation. 1-30. Retrieved from http://arxiv.org/abs/1503.08376
Broman, K.W., Woo, K.H., & Woo, K.H. (2017). Data organization in spreadsheets Data organization in spreadsheets and (Vol. 1305). https://doi.org/10.1080/00031305.2017.1375989
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. https://doi.org/10.12783/dtssehs/icaem2017/19117
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. https://doi.org/10.18260/1-2--20017
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. https://doi.org/10.1145/581339.581359
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 https://www.ijbems.com/doc/IJBEMS-124.pdf
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. https://doi.org/10.4301/S1807-17752011000200011
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. https://doi.org/10.1002/cae.20498
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. https://doi.org/10.1609/aaai.v31i1.10668
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 http://www.crisp.nus.edu.sg/~acrs2001/pdf/015venka.pdf
Wettlaufer, G.J. (2010). Merlin.Net Automation of External Reports Verification Process (California Polytechnic State University). California Polytechnic State University. https://doi.org/10.15368/theses.2010.3
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.
Copyright (c) 2023 Muhammad Ahmed Kalwar, Asif Nawaz Wassan, Zuhaib Phul, Muzamil Hussain Wadho, Tanveer Sarwar Malik, Muhammad Ali Khan
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
This journal is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International Licencse