Automation of Post-Order Costing Analysis By Using Visual Basic For Applications In Microsoft Excel: A Case Study
Submitted: 2022-11-03
|Accepted: 2023-05-16
|Published: 2023-06-27
Copyright (c) 2023 Muhammad Ahmed Kalwar
This work is licensed under a Creative Commons Attribution 4.0 International License.
Downloads
Keywords:
macros, VBA programming, report automation, reporting efficiency
Supporting agencies:
Abstract:
Microsoft Excel is used to carry out reporting tasks in small and medium companies across the globe. Most people make reports manually in Microsoft Excel and the manual work takes a tremendous amount of time. The manual work can be easily automated with some effort in Microsoft Excel. This research is about the automation of report that was used to be made manually in Microsoft Excel.The present research is an extension of the previously conducted research (Kalwar, Shahzad, et al., 2022). The manual process to make the post-order costing analysis report in MS excel requires a lot of user time (22.80 minutes, just if there is a single order of only one article with one color), with the greater possibility of human error. Employees in ananonymous footwear company in Lahore used to generate the post-order costing report manually in Excel.Due to the above-mentioned reasons, it was proposedto automate the report using visual basic for applications (VBA) in Microsoft Excel.The planning and costing departmentof the company provided every single step required to make the report manually. A time study was conducted for each stepof report preparation using the stopwatch. VBA macros were programmed for automating all manual report-related processes in excel andthe accuracy of the report was also verified. After the report was automated, the time study was again conducted to measure the execution time after each click. A comparison of both methods indicated that the report automation saved 83.18% of employees' time Additionally, the automated method resulted in an error-free report, and the employee`sworkload was reducedas well.
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
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
Bukhsh, M., Khan, M. A., Zaidi, I. H., Yaseen, R., Khalid, A., Razzaque, A., & Ali, M. (2021). Productivity Improvement in Textile Industry using Lean Manufacturing Practice of Single Minute Die Exchange ( SMED ). Proceedings of the 11th Annual International Conference on Industrial Engineering and Operations Management Singapore, March 7-11, 2021. http://www.ieomsociety.org/singapore2021/papers/1282.pdf
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. http://www.ijsei.com/papers/ijsei-1011121-02.pdf
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/581356.581359
George, T. (2023). What Is Action Research? | Definition & Examples. Scribbr.
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.
Iftikhar, Z., Khan, M. A., Kumar, R., Bux, K., & Haseeb, A. (2021). Productivity Improvement of Garments Industry by Assembly Line Technique of Lean Manufacturing. Proceedings (Abstract) of the International Conference on Industrial & Mechanical Engineering and Operations Management Dhaka, Bangladesh, December 26-27, 2021., 908. https://ieomsociety.org/proceedings/2021dhaka/497.pdf
Iftikhar, Z., Khan, M. A., Soomro, A. S., Kumar, R., Bux, K., Haseeb, A., & Naz, A. (2022). Productivity Improvement of Assembly Line in Textile Stitching Unit by Lean Techniques of Line Balancing and Time and Motion Study. International Journal of Science and Engineering Investigations (IJSEI), 11(127), 51-60. http://www.ijsei.com/papers/ijsei-1112722-07.pdf
Iftikhar, Z., Kumar, R., Bux, K., Haseeb, A., Khan, M. A., Naz, A., Hassam, & Soomro, A. S. (2022). Lean Manufacturing Tools and Techniques for the Productivity Improvement in Assembly Lines Operations of Industries. International Research Journal of Modernization in Engineering Technology and Science (IRJMETS), 4(7), 4554-4562. https://www.irjmets.com/uploadedfiles/paper//issue_7_july_2022/28986/final/fin_irjmets1663258443.pdf
Jaleel, N., Khan, M. A., Jamal, M., Safeeruddin, M., Shajee, M. M., & Mughal, U. (2021). Productivity Improvement by Lean Methodologies at Dyeing & Printing Plant. Proceedings (Abstract) of the International Conference on Industrial & Mechanical Engineering and Operations Management Dhaka, Bangladesh, December 26-27, 2021., 905. https://ieomsociety.org/proceedings/2021dhaka/495.pdf
Kalwar, Muhammad Ahmed; Wassan, A. N., Khan, M. A., Wadho, Muzamil Hussain; Shaikh, S. A., & Marri, H. B. (2023). The Automation of Plan Paper Generating Workbook at Leather Footwear Company of Lahore by Using VBA in Microsoft Excel. Journal of Applied Research in Technology & Engineering (JARTE), 4(2). https://doi.org/10.4995/jarte.2023.18941
Kalwar, M. A., & Khan, M. A. (2020a). 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), 6(1), 213-220. https://ijbems.com/doc/IJBEMS-124.pdf
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 (IJBEMS), 5(2), 80-100.
Kalwar, M. A., Khan, M. A., Phul, Z., Wadho, M. H., Shahzad, M. F., & Marri, H. B. (2022). The analysis of performance before and after ERP implementation: a case of a manufacturing company. Journal of Applied Research in Technology & Engineering, 3(2), 115-121. https://doi.org/10.4995/jarte.2022.17789
Kalwar, M. A., Shahzad, M. F., Wadho, M. H., Khan, M. A., & Shaikh, S. A. (2022). Automation of order costing analysis by using Visual Basic for applications in Microsoft Excel. Journal of Applied Research in Technology & Engineering (JARTE), 3(1), 29-59. https://doi.org/10.4995/jarte.2022.16390
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
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, K., Khan, M. A., Thebo, J. A., Ahmed, T., & Rahoo, L. A. (2021). Examining The Human Resource Architecture Relationship With Employee Productivity Of Chemical Industries. Journal of Contemporary Issues in Business and Government (CIBG), 27(2), 5847-5856. https://www.cibgp.com/article_11267_91767391154f6eee74a8fa4a1c11a1c6.pdf
Khan, M. A. (2018). Preliminary Study on Lean Manufacturing Practices at Yarn Manufacturing Industry - A Case Study [Masters of Engineering (Thesis), Department of Industrial Engineering & Management, Mehran University of Engineering & Technology, Jamshoro, Sindh, Pakistan.]. https://doi.org/10.13140/RG.2.2.31557.37609
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 (JARTE), 2(2), 89. https://doi.org/10.4995/jarte.2021.14786
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 (IJSEI), 10(113), 49-60. http://www.ijsei.com/papers/ijsei-1011321-08.pdf
Khan, M. A., Khatri, A., & Marri, H. B. (2020). Descriptive analysis of lean manufacturing practices in textile industry. Proceedings of the 5th NA International Conference on Industrial Engineering and Operations Management Detroit, Michigan, USA, August 10 - 14, 2020, 3870-3871. http://www.ieomsociety.org/detroit2020/papers/753.pdf
Khan, M. A., Marri, H. B., & Khatri, A. (2020). Exploring The Applications Of Lean Manufacturing Practices In Textile Industry. Proceedings of the International Conference on Industrial Engineering and Operations Management, Dubai, UAE, March 10-12, 2020, 2360-2361. http://www.ieomsociety.org/ieom2020/papers/545.pdf
Khan, M. A., Memon, M. S., & Soomro, A. S. (2020). Exploring the applications of lean manufacturing practices in automobile industry. Proceedings of the 5th NA International Conference on Industrial Engineering and Operations Management Detroit, Michigan, USA, August 10 - 14, 2020, August. http://www.ieomsociety.org/detroit2020/papers/727.pdf
Khan, M. A., Soomro, A. S., Shaikh, S. A., Memon, M. S., & Marri, S. (2020). Lean manufacturing in pakistan: A comprehensive review. Proceedings of the 5th NA International Conference on Industrial Engineering and Operations Management Detroit, Michigan, USA, August 10 - 14, 2020, August. http://www.ieomsociety.org/detroit2020/papers/728.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.
Kumar, S., Khan, M. A., Ahmed, S., Rehman, A., & Luhar, E. (2020). A Case Study for Performance Evaluation of Motorcycle Assembly Line through the Lean Manufacturing Practice of Overall Equipment Effectiveness ( OEE ). Proceedings of the International Conference on Industrial Engineering and Operations Management Dubai, UAE, March 10-12, 2020, 1622-1623. http://www.ieomsociety.org/ieom2020/papers/192.pdf
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.
Marin-Garcia, J. A., Garcia-Sabater, J. P., & Maheut, J. (2022). Case report papers guidelines: Recommendations for the reporting of case studies or action research in Business Management. WPOM-Working Papers on Operations Management, 13(2), 108-137. https://doi.org/10.4995/wpom.16244
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.
Rajput, S., Khan, M. A., Samejo, S., Murtaza, G., & Ali, R. A. (2020). Productivity Improvement by the Implementation of lean manufacturing practice ( takt time ) in an automobile assembling plant. Proceedings of the International Conference on Industrial Engineering and Operations Management Dubai, UAE, March 10-12, 2020, 1618-1619. http://www.ieomsociety.org/ieom2020/papers/190.pdf
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
S. Ajinkya, W., S. Sachin, R., D. Manoj, S., M. Rajesh, R., & B. Mangesh, J. (2017). PREPARING EXCEL SHEET FOR ESTIMATION AND COSTING. INTERNATIONAL JOURNAL OF ENGINEERING SCIENCES & MANAGEMENT, 7(1), 310-317.
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
Ş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.
Wettlaufer, G. J. (2010). Merlin.Net Automation of External Reports Verification Process (California Polytechnic State University). 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.
