Automation of order costing analysis by using Visual Basic for applications in Microsoft Excel

Authors

  • Muhammad Ahmed Kalwar Shafi Private Limited
  • Muhammad Faisal Shahzad Industrial & Management Engineering, Faculty of Engineering, UET, Lahore, Punjab
  • Muzamil Hussain Wadho Electrical Engineering, Faculty of Engineering, BBSUTSD, KhairpurMirs, Sindh
  • Muhammad Ali Khan Industrial Engineering & Management, Faculty of Engineering, Mehran UET, Jamshoro, Sindh https://orcid.org/0000-0003-3956-3698
  • Shakeel Ahmed Shaikh Faculty of Engineering, Mehran UET, Jamshoro, Sindh

DOI:

https://doi.org/10.4995/jarte.2022.16390

Keywords:

footwear, optimization, order costing, visual basic for applications

Abstract

In the modern world of technologies (sciences of arts), optimum operation time is the prime consideration. Technologies with greater operation time are needed to be modified or replaced as per the requirement of the current scenario. In small and medium enterprises of developing countries, almost all the reporting activities are conducted in Microsoft Excel. The manual operation in Microsoft Excel takes a long time (17.37 minutes, only if there was one order of only one article with one color) of the users; furthermore, there can also be a greater chance of error in the report. In the same way, the order costing report at the ABC footwear company of Lahore is prepared manually in Microsoft Excel which used to take a long time to be prepared along with the greater probability of errors in the report. Because of the above-mentioned reasons, the order costing report was decided to be automated with the help of visual basic for applications (VBA) in Microsoft Excel. The manual procedure including all the activities was collected from the planning and costing department of ABC Company. Moreover, all the activities were listed down and their time study was conducted. VBA macros were programmed with the objectives of automating all the manual tasks of the order costing report in Microsoft Excel and eliminating the chance of error (calculation error made by the employee). Macros, were programmed by the use of conditional statements, (i.e. if and else conditions) and various types of loops, (i.e. Do while loop and for loop). Automated decisions were made with the help of conditional statements during the operation and the conditional statements were circulated with the help of loops across the whole worksheet. Two userforms were designed for the execution of macros by the click of command buttons (having macros at their back). Total eight worksheets were incorporated into the automated template. All the programmed VBA macros were explained with the help of flowcharts made in the Microsoft Visio. After the report was automated, a time study of operations performed after each click was conducted with the help of stop watch. The data obtained by the time study was entered into Microsoft Excel so that the time of old and suggested methods could be compared. Comparison of both methods indicated that the suggested method took 85.92% less time as compared to the old method of preparing the order costing report. At the same time, the report made by the suggested method was free of errors and the workload of employees was also reduced.

Downloads

Download data is not yet available.

Author Biographies

Muhammad Ahmed Kalwar, Shafi Private Limited

Assistant Manager, Production

Muhammad Faisal Shahzad, Industrial & Management Engineering, Faculty of Engineering, UET, Lahore, Punjab

Assistant Professor

Muzamil Hussain Wadho, Electrical Engineering, Faculty of Engineering, BBSUTSD, KhairpurMirs, Sindh

Assistant Professor

Muhammad Ali Khan, Industrial Engineering & Management, Faculty of Engineering, Mehran UET, Jamshoro, Sindh

Assistant Professor

Shakeel Ahmed Shaikh, Faculty of Engineering, Mehran UET, Jamshoro, Sindh

Co-Director (Post Graduate) & Associate Professor (Industrial), Faculty of Engineering

References

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

Antos, J. R. (1983). Analysis of Labor Cost: Data Concepts and Sources. In The Measurement of Labor Cost: Vol. I (pp. 153-182).

Arain, M. S., Khan, M. A., & Kalwar, M. A. (2020). Optimization of Target Calculation Method for Leather Skiving and Stamping: Case of Leather Footwear Industry. International Journal of Business Education and Management Studies, 7(1), 15-30.

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., Torres, W. M., Umbehaun, P. E., & Conti, T. N. (2011). Development of a Vba Macro-Based Spreadsheet Application for Relap5 Data Post-Processing. International Nuclear Atlantic Conference, 978-985.

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.18738

Blattner, D. E., & Valrico, F. (2007). Automatic Report Generation (Patent No. US 2007/0055688 A1).

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 (Issue March, pp. 110-136). https://doi.org/10.4018/978-1-5225-7277-0.ch007

Chatvichiencha, S. (2015). Enhancing Computational Thinking by Excel-VBA Based Problem Solving. The 2nd International Conference on Innovation in Education, 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.

Chaudhry, A. K., Khan, M. A., Kalwar, M. A., & Chaudhry, A. K. (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.14786

Choudhary, A. S. (2010). Cost Analysis in Garment Industry. International Journal of Recent Advances in Multidisciplinary Research, 11(3), 369-373. https://doi.org/10.1177/1461444810365020

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

Effiong Asuquo, S., & Oti A., P. (2012). Analytical evaluation of cost elements and their influence on productivity of manufacturing firms. Journal of Finance and Investment Analysis, 1(3), 171-180.

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

Gandhi, M. K., Poonkuzhali, S., Kumar, K. S., & Sarukesi, K. (2015). Effective cost analysis model for apparel industry. International Journal of Applied Engineering Research, 10(8), 20263-20276.

Hamermesh, D. S. (2014). Do labor costs affect companies' demand for labor? In IZA World of Labor (Issue May). https://doi.org/10.15185/izawol.3

Harahap, 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. In Society.

Howard, P. M. A., Essuman, D. M. A., & Asare, T. O. (2019). Strategies for Determining the Production Cost and Pricing of Garments in Ghana: A Study of the Fashion Industries. International Journal of Business and Social Science, 10(3), 75-87. https://doi.org/10.30845/ijbss.v10n3p7

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.13788

Kalwar, 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. https://www.ijbems.com/doc/IJBEMS-124.pdf

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. https://core.ac.uk/download/pdf/234685697.pdf

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.15

Kalwar, 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., Marri, H. B., Khan, M. A., & Khaskheli, S. A. (2021). Applications of Queuing Theory and Discrete Event Simulation in Health Care Units of Pakistan. International Journal of Science and Engineering Investigations, 10(109), 6-18.

Kalwar, M. A., Memon, M. S., Khan, M. A., & Tanwari, A. (2021). Statistical Analysis of Waiting Time of Patients by Queuing Techniques: Case Study of Large Hospital in Pakistan. Journal of Applied Research in Technology & Engineering, 2(2), 101-112. https://doi.org/10.4995/jarte.2021.14741

Kalwar, M. A., Shaikh, S. A., Khan, M. A., & Malik, T. S. (2020). Optimization of Vendor Rate Analysis Report Preparation Method by Using Visual Basic for Applications in Excel (Case Study of Footwear Company of Lahore). Proceedings of the International Conference on Industrial Engineering and Operations Management.

Keramatpanah, A., Kambiz, S., Saeed, Y., & Mohsen, K. (2016). A Mathematical Model Designing to Achieve Cost Management in Value Chain with Combinational Approach of AHP & GP (Case Study: Home Appliance Industries). SOCRATES: An International, Multi-Lingual, Multi-Disciplinary, Refereed (Peer-Reviewed), Indexed Scholarly Journal, 4(1), 30-51.

Khan, M. A., Kalwar, M. A., Malik, A. J., Malik, T. S., & Chaudhry, A. K. (2021). Automation of Supplier Price Evaluation Report in MS Excel by Using Visual Basic for Applications: A Case of Footwear Industry. International Journal of Science and Engineering Investigations, 10(113), 49-60. http://www.ijsei.com/papers/ijsei-1011321-08.pdf

Khan, M. A., Khaskheli, S. A., Kalwar, H. A., Kalwar, M. A., Marri, H. B., & Nebhwani, M. (2021a). Application of MultiServer Queuing Model to Analyzethe Queuing System of OPD During COVID-19 Pandemic: A Case Study. Journal of Contemporary Issues in Business and Government, 27(05), 1351-1367. https://doi.org/10.47750/cibg.2021.27.05.094

Khan, M. A., Khaskheli, S. A., Kalwar, H. A., Kalwar, M. A., Marri, H. B., & Nebhwani, M. (2021b). Improving the Performance of Reception and OPD by Using Multi-Server Queuing Model in Covid-19 Pandemic. International Journal of Science and Engineering Investigations, 10(113), 20-29.

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. Proceedings of the International Conference on Industrial Engineering and Operations Management, 2702-2720. http://www.ieomsociety.org/ieom2020/papers/177.pdf

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, 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.

Liu, F., Ding, Y., Gao, J., & Gong, P. (2017). Effects of cost factors on national manufacturing based on global perspectives. Economies, 5(45), 1-16. https://doi.org/10.3390/economies5040045

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

Mussatti, D. C., & Vatavuk, W. M. (2017). Cost Estimation: Concepts and Methodology. In Epa Air Pollution Control Cost Manual (pp. 1-45).

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

Porter, 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. 22nd Asian Conference on Remote Sensing. http://www.crisp.nus.edu.sg/~acrs2001/pdf/015venka.pdf

Şuteu, M. D., Meşter, L. E., Bugnar, N. G., Andreescu, N., & Petrica, D. M. (2016). The impact of costing methods on profitability of enterprises operating in the embroidery industry. Tekstil ve Konfeksiyon, 26(3), 239-243.

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.24

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.

Downloads

Published

2022-01-31

Issue

Section

Articles