Optimization of material delivery time analysis by using Visual Basic for applications in Excel

[EN] In small and medium enterprises, huge numbers of reports are prepared regarding the procurement, planning, production etc on daily, weekly, monthly, quarterly and yearly basis. If the interval between preparation of same report is longer then there is the greater probability that employee can forget the procedure of preparing the report; thus it is beneficial to automate these types of reports especially when there is no cost required for automation. In the same way, material delivery analysis is a kind of report which is prepared on monthly basis and as per the employees` feedback they usually forget some of the steps of report formation; therefore, there was the chance of mistake at the end of employee. Therefore, this report was automated for to minimize the chance of error and report preparation time. All the manual tasks were enlisted and were programmed for automation by the help of VBA macros. For the execution of macros, userform was designed in visual basic editor (in MS excel) consisted on four command buttons and macros were called on command buttons` click. On pressing ‘ctrl + q’ userform used to appear on the screen. Furthermore, the comparison of old and automated methods was conducted to reflect the best suitable method. The report used to take 1.55 minutes to be made manually. Comparison of time of both methods of report formation indicated that suggested method took 70.86% less time as compared to the old method of preparing material delivery time analysis report.Authors of present research paper (representing the automation of one the routine reports) want to thank the concerned employee of ABC Company at its purchase department for his generous cooperation in the automation period of the report.Khan, MA.; Kalwar, MA.; Chaudhry, AK. (2021). Optimization of material delivery time analysis by using Visual Basic for applications in Excel. Journal of Applied Research in Technology & Engineering. 2(2):89-100. https://doi.org/10.4995/jarte.2021.147868910022Abidin, 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. (2009). Spreadsheet Programming. In Wiley Encyclopedia of Computer Science and Engineering, B.W. Wah (Ed.). https://doi.org/10.1002/9780470050118.ecse415Ahmadi, 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.2018070105Bartoszewicz, 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. Digitalization of Supply Chains. https://doi.org/10.17270/b.m.978-83-66017-86-3Belchior 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. In International Nuclear Atlantic Conference (pp. 978-85).Bernard, M., Dwi Minarti, E., & Hutajulu, M. (2018). Constructing Student's Mathematical Understanding Skills and Self Confidence: Math Game with Visual Basic Application for Microsoft Excel in Learning Phytagoras at Junior High School. International Journal of Engineering & Technology, 7(3.2), 732-736. https://doi.org/10.14419/ijet.v7i3.2.18738Blattner, D.E., & Valrico, F. (2007). Automatic Report Generation. 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.ch007Chatvichiencha, S. (2015). Enhancing Computational Thinking by Excel-VBA Based Problem Solving. In The 2nd International Conference on Innovation in Education (pp. 284-290).Chaudhry, A.K., Kalwar, M.A., Khan, M.A., & Shaikh, S.A. (2021). Improving the Efficiency of Small Management Information System by Using VBA. International Journal of Science and Engineering Investigations, 10(111), 7-13.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., Zhao, X.L., & Xu, G.F. (2017). Tibetan Typographical Specifications and Technical Realization Based on Word VBA. In 4th International Conference on Advanced Education and Management (pp. 407-412). https://doi.org/10.12783/dtssehs/icaem2017/19117Evensen, H.T. (2014). A versatile platform for programming and data acquisition: Excel and Visual Basic for Applications. In ASEE Annual Conference and Exposition. https://doi.org/10.18260/1-2--20017Harahap, M.I. P., & Azmi, M.H. (2017). Development of Excel Vba Program for Small Drainage Network. E-Academia Journal, 6(1), 216-227.Hart-Davis, G. (2005). Making Decisions in Your Code. In Mastering MIcrosoft VBA (2nd ed., p. 202).Hila, R. (2009). Water Quality Data Management Database. Society.Kalwar, M.A., & Khan, M.A. (2020a). Increasing performance of footwear stitching line by installation of auto-trim stitching machines. Journal of Applied Research in Technology & Engineering, 1(1), 31-36. https://doi.org/10.4995/jarte.2020.13788Kalwar, M.A., & Khan, M.A. (2020b). Optimization of Procurement & Purchase Order Process in Foot Wear Industry by Using VBA in Ms Excel. International Journal of Business Education and Management Studies, 5(2), 80-100. Retrieved from https://www.ijbems.com/doc/IJBEMS-124.pdf [Accessed December 2020].Kalwar, M.A., Khan, M.A., & Malik, A.J. (2020). Formulation of Mathematical Model for Maximization of Profit: Case of Leather Fotowear Company. International Research Journal of Computer Science and Technology, 1(1), 54-70.Kalwar, M.A., Khan, M.A., & Shaikh, S.A. (2020). Analysis of Driving Anger: Case Study of Mehran UET, Jamshoro. International Research Journal of Computer Science and Technology, 1(1), 13-37.Kalwar, M.A., Khan, M.A., Shaikh, S.A., Salam, A., Memon, M.S., & Khaskheli, S.A. (2020). Aggressive Driving Behavior: A Case Study of Mehran UET,. In Proceedings of the International Conference on Industrial Engineering and Operations Management Dubai, (pp. 2350-2359). Dubai, UAE. Retrieved from http://www.ieomsociety.org/ieom2020/papers/175.pdf [Accessed December 2020].Kalwar, M.A., Khaskheli, S.A., Khan, M.A., Siddiqui, A.A., & Gopang, M.A. (2018). Comfortable Waiting Time of Patients at the OPD with Varying Demographics. Industrial Engineering Letters, 8(2), 20-27. Retrieved from https://core.ac.uk/download/pdf/234685697.pdf [Accessed December 2020].Kalwar, M.A., Mari, S.I., Memon, M.S., Tanwari, A., & Siddiqui, A.A. (2020). Simulation Based Approach for Improving Outpatient Clinic Operations. Mehran University Research Journal of Engineering and Technology, 39(1), 153-170. https://doi.org/10.22581/muet1982.2001.15Kalwar, M.A., Marri, H.B., & Khan, M.A. (2021). Performance Improvement of Sale Order Detail Preparation by Using Visual Basic for Applications: A Case Study of Footwear Industry. International Journal of Business Education and Management Studies, 3(1), 1-22.Kalwar, M.A., Shaikh, shakeel A., & Khan, M.A. (2020). Optimization of Target Calculation Method for Leather Skiving and Stamping: Case of Leather Footwear Industry. International Journal of Education and Management Studies, 7(1), 15-30.Kalwar, M.A., Shaikh, shakeel A., & Khan, M.A. (2021). Statistical Analysis of Driving Impatience Among Vehicle Drivers: A Case Study. International Journal of Education and Management Studies, 2(1), 1-32.Khaskheli, S.A., Kalwar, M.A., Siddiqui, A.A., Nagar, M.A. K., & Wadho, T.H. (2018). Impatience Among Drivers With Varying Demographics. In Professional Trends in Industrial and Systems Engineering (pp. 465-469). UET, Peshawar.Khaskheli, S.A., Marri, H.B., Nebhwani, M., Khan, M.A., & Ahmed, M. (2020). Compartive Study of Queuing Systems of Medical Out Patient Departments of Two Public Hospitals. In Proceedings of the International Conference on Industrial Engineering and Operations Management (Vol. 1913, pp. 2702-2720). Dubai, UAE. Retrieved from http://www.ieomsociety.org/ieom2020/papers/177.pdf [Accessed December 2020].Kuka, S., & Karamani, B. (2011). Using Excel and VBA for Excel to Learn Numerical Methods. In 1st International Sympsosium on Computing in Informatics and Mathematics (pp. 365-376).Lessa, D.J.R., Lessa, F.P.R., Magalhães Junior, P.A.A, & Guimarães, H.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.Mustafa, A., & Hatemi-J, A. (2020). A VBA module simulation for finding optimal lag order in time series models and its use on teaching financial data computation. Applied Computing and Informatics. https://doi.org/10.1016/j.aci.2019.04.003Norton, 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.20498Porter, D., & Stretcher, R. (2012). Automating Markowitz Optimizations Using VBA. Journal of Instructional Techniques in Finance, 4(1), 9-16.Sato, K., & Yokoyama, R. (2001). Teaching Aid for Remote Sensing and Map Imagery Analysis Using Excel Spreadsheet and VBA. In 22nd Asian Conference on Remote Sensing. Retrieved from http://www.crisp.nus.edu.sg/~acrs2001/pdf/015venka.pdf [Accessed December 2020].Walkenbach, J. (2013). Microsoft Excel VBA Programmining.Walkenbach, J. (2015). Controling Program Flow and Making Decisions. In 3rd (Ed.), Excel VBA Programming For Dummies (p. 153).Wettlaufer, G.J. (2010). Merlin.Net Automation of External Reports Verification Process. California Polytechnic State University. https://doi.org/10.1558/jsrnc.v4il.24Yan, 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 Ingeniería, 32(2), 335-341