Development of MS Excel and Power BI Integrated Production Scheduling System for an MSME
Keywords:
Enterprise resource planning (ERP), Micro, small and medium enterprises (MSME), Microsoft Excel (MS Excel), Power BI, Production planning (PP), Production SchedulingAbstract
Industry 4.0, or I4.0, uses digitalization, blockchain technology (BCT), artificial intelligence (AI), and machine learning (ML) to improve supply chain responsiveness and efficiency while cutting costs. Production planning (PP) is emphasized in manufacturing, a critical stage of supply chain management (SCM). In order to meet changing customer demands and optimize manufacturing processes, researchers concentrate on creating customized PP modules for use within enterprise resource planning (ERP) systems. ERP modules support predictive analytics for ideal inventory levels, resource needs, and supply chain risks in addition to managing operations. However, it is financially difficult for micro, small, and medium enterprises (MSMEs) to implement a comprehensive ERP system. Implementing ERP in MSMEs for production scheduling is challenging due to time, information technology (IT) expertise, and cost constraints, especially for make-to-order (MTO) MSMEs. Microsoft Excel (MS Excel) and Power BI offer a better alternative with easier learning, customization, quicker implementation, and lower cost. This solution integrates both for efficient production scheduling and resource planning. A concurrent, adaptable PP system that integrates MS Excel and Power BI is suggested as a solution to this problem. Machine schedules and important performance indicators are projected onto an operational dashboard by this system, which is intended for a parallel machine environment. The objective is to find the best combination of shifts (s = 1 to 3) and machines (m = 1 to 6) for a workload through 18 simulations, helping planners to meet delivery deadlines. The PP system's ideal combination changes over the course of six weeks of simulations, from 1s-1m to 3s-5m to 2s-3m, demonstrating its flexibility in response to shifting production demands. Despite fluctuating workload over six weeks, (i) 92% orders met the 45-day lead time, (ii) plant ran continuously for a month (100% achievement), and (iii) visibility for stakeholder was enhanced with efficient resource planning and providing scope for further detailed analysis towards improving important performance indicators.
References
H. Allaoui, Y. Guo, J. Sarkis, ‘Decision support for collaboration planning in sustainable supply chains’, Journal of Cleaner Production, vol. 229, pp. 761-774, Aug. 2019, doi: 10.1016/j.jclepro.2019.04.367.
R. Roundy, D. Chen, P. Chen, M. Çakanyildirim, M. B. Freimer, V. Melkonian, ‘Capacity-driven acceptance of customer orders for a multi-stage batch manufacturing system: models and algorithms’, IIE Transactions, vol. 37, no. 12, pp. 1093-1105, 2005, doi: 10.1080/07408170500288042.
D. Mourtzis, ‘Advances in adaptive scheduling in industry 4.0’, Frontiers in Manufacturing Technology, vol. 2, no. 2022, art. no. 937889, Jul. 2022, doi: 10.3389/fmtec.2022.937889.
S. A. Murad, A. J. M. Muzahid, Z. R. M. Azmi, M. I. Hoque, M. Kowsher, ‘A review on job scheduling technique in cloud computing and priority rule based intelligent framework’, Journal of King Saud University - Computer and Information Sciences, vol. 36, no. 6A, pp. 2309-2331, Jun. 2022, doi: 10.1016/j.jksuci.2022.03.027.
W. R. Faranita, M. Nugroho, ‘Implementation of the enterprise resource planning (ERP) system on micro, small and medium enterprises (MSME) business actors’, Journal of Business Theory and Implementation, vol. 12, no. 2, pp. 86-93, Sep. 2021, doi: 10.18196/jbti.v12i2.12191.
A. Basu, S. Dutta, R. Jha, ‘A comprehensive approach to study the adoption and implementation of cloud-based ERP among SMEs’, International Journal of Business Information Systems, vol 42, no. 3/4, pp. 305-330, 2023, doi: 10.1504/ijbis.2023.129714.
S. Trivedi, S. Negi, N. Anand, R. Patankar, G. Kumar, ‘ERP solution for effective supply chain of micro, small and medium-sized enterprises: a case study of customised ERP solution development and deployment for MSMEs in the Haryana state of India’, International Journal of Business Innovation and Research, vol. 17, no. 4, pp. 516-535, Nov. 2018, doi: 10.1504/IJBIR.2018.096371.
I. A. A. Alsibhawi, J. B. Yahaya, H. B. Mohamed, ‘Business intelligence adoption for small and medium enterprises: conceptual framework’, Applied Sciences (Switzerland), vol. 13, no. 7, art. no. 4121, Mar. 2023, doi: 10.3390/app13074121.
J. Zheng, H. Khalid, ‘The adoption of enterprise resource planning and business intelligence systems in small and medium enterprises: a conceptual framework’, Mathematical Problems in Engineering, vol. 2022, art. no. 1829347, Jun. 2022, doi: 10.1155/2022/1829347.
L. T. Becker, E. M. Gould, ‘Microsoft Power BI: extending excel to manipulate, analyze, and visualize diverse data’, Serials Review, vol. 45, no. 3, pp. 184-188, Jul. 2019, doi: 10.1080/00987913.2019.1644891.
E. Oey, S. S. S. Harno, and C. Zain, ‘Developing Integrated Performance Dashboards with Power BI – a Case Study in a Medium-Size Manufacturer’, in 2021 International Conference on Information Management and Technology (ICIMTech), Jakarta, Indonesia: IEEE, Aug. 2021, pp. 265–270. doi: 10.1109/ICIMTech53080.2021.9535105.
D. H. Nabil, Md. H. Rahman, A. H. Chowdhury, and B. C. Menezes, ‘Managing supply chain performance using a real time Microsoft Power BI dashboard by action design research (ADR) method’, Cogent Engineering, vol. 10, no. 2, p. 2257924, Dec. 2023, doi: 10.1080/23311916.2023.2257924.
M. Belghith, H. Ben Ammar, F. Masmoudi, and A. Elloumi, ‘Data Visualization for Industry 4.0: Developing Dashboards with Power BI – A Case Study in a Pharmaceutical Company’, in Design and Modeling of Mechanical Systems - V, L. Walha, A. Jarraya, F. Djemal, M. Chouchane, N. Aifaoui, F. Chaari, M. Abdennadher, A. Benamara, and M. Haddar, Eds., Cham: Springer International Publishing, 2023, pp. 402–408. doi: 10.1007/978-3-031-14615-2_45.
J. Ding, L. Shen, Z. Lü, and B. Peng, ‘Parallel machine scheduling with completion-time-based criteria and sequence-dependent deterioration’, Computers & Operations Research, vol. 103, pp. 35–45, Mar. 2019, doi: 10.1016/j.cor.2018.10.016.
M. R. Bazargan-Lari, S. Taghipour, A. Zaretalab, and M. Sharifi, ‘Production scheduling optimization for parallel machines subject to physical distancing due to COVID-19 pandemic’, Oper Manag Res, vol. 15, no. 1–2, pp. 503–527, Jun. 2022, doi: 10.1007/s12063-021-00233-9.
M. Ghaleb and S. Taghipour, ‘Dynamic shop-floor scheduling using real-time information: A case study from the thermoplastic industry’, Computers & Operations Research, vol. 152, p. 106134, Apr. 2023, doi: 10.1016/j.cor.2022.106134.
M. Arani, M. Momenitabar, and T. J. Priyanka, ‘Unrelated Parallel Machine Scheduling Problem Considering Job Splitting, Inventories, Shortage, and Resource: A Meta-Heuristic Approach’, Systems, vol. 12, no. 2, p. 37, Jan. 2024, doi: 10.3390/systems12020037.
K. R. Baker, ‘Introduction to sequencing and scheduling’, Wiley and Sons, New York, 1974.
T. E. Morton, D. W. Pentico, ‘Heuristic scheduling systems’, Wiley-Inderscience, New York, 1993.
W. L. Winston, ‘Operations research: applications and algorithms’, Duxbury Press, Wadsworth Publishing Company, Belmont, CA, 1994.
I. M. Alharkan, ‘Algorithms for sequencing and scheduling’, Industrial Engineering Department, King Saud University, Riyadh, Saudi Arabia, pp. 3.1-3.30, 2005, available at http://ikucukkoc.baun.edu.tr/lectures/EMM4129/Algorithms_for_Sequencing_and_Scheduling.pdf
M. F. Anjos, M. V. C. Viera, ‘Mathematical optimization approaches for facility layout problems: the state-of-the-art and future research directions’, European Journal of Operational Research, vol. 261, no. 1, pp. 1-16, Aug. 2017, doi: 10.1016/j.ejor.2017.01.049.
A. Kramer, M. Dell’Amico, D. Feillet, M. Iori, ‘Scheduling jobs with release dates on identical parallel machines by minimizing the total weighted completion time’, Computers and Operations Research, vol. 123, art. no. 105018, Nov. 2020, doi: 10.1016/j.cor.2020.105018.
Z-L. Chen, N. G. Hall, ‘Supply chain scheduling’, Springer Cham; pp. 23-49, 2022.
M. Nattaf, S. Dauzère-Pérès, C. Yugma, C-H. Wu, ‘Parallel machine scheduling with time constraints on machine qualifications’, Computers and Operations Research, vol. 107, pp. 61-76, Jul. 2019, doi: 10.1016/j.cor.2019.03.004.
Y-K. Lin, ‘Scheduling efficiency on correlated parallel machine scheduling problems’, in Operational Research, vol. 18, pp. 603-624, Oct. 2018, doi: 10.1007/s12351-017-0355-0.
G. E. Vieira, J. W. Herrmann, E. Lin, ‘Predicting the performance of rescheduling strategies for parallel machine systems’, Journal of Manufacturing Systems, vol. 19, no. 4, pp. 256-266, 2000, doi: 10.1016/S0278-6125(01)80005-4.
T. Chung, J. N. D. Gupta, H. Zhaoc, F. Werner, ‘Minimizing the make span on two identical parallel machines with mold constraints’, Computers and operations research, vol. 105, pp. 141-155, May 2019, doi: 10.1016/j.cor.2019.01.005.
Y. Yin, Y. Wang, T. C. E. Cheng, W. Liu, J. Li, ‘Parallel-machine scheduling of deteriorating jobs with potential machine disruptions’, Omega, vol. 69, pp. 17-28, Jun. 2017, doi: 10.1016/j.omega.2016.07.006.
K. Chen, P. JI, Q. Wang, ‘A case study for advanced planning and scheduling (APS)’, Journal of System Science and Systems Engineering, vol. 20, pp. 460-474, Dec. 2011 doi: 10.1007/s11518-011-5180-z.
T. B. de Sousa, C. E. S. Camparotti, F. M. Guerrini, A. L. Silva, T. M. Biondo, ‘Describing the APS systems: a software overview’, 8th international conference on industrial engineering and industrial management, 20th international conference on industrial engineering and operations management, international IIE conference, Málaga, Spain, 2014, pp. 421-428. Available at http://www.adingor.es/cio2014/Documents/Book_Of_Proceedings_CIO_ICIEOM_IIIE_2014.pdf
H. Stadtler, ‘Supply chain management and advanced planning - basics, overview and challenges’, European Journal of Operational Research, vol. 163, no. 3, pp. 575-588, Jun. 2005, doi: 10.1016/j.ejor.2004.03.001.
M. Gen, L. Lin, H. Zhang, ‘Evolutionary techniques for optimization problems in integrated manufacturing system: state-of-the-art-survey’, Computers and Industrial Engineering. vol. 56, no. 3, pp. 779-808, Apr. 2009, doi: 10.1016/j.cie.2008.09.034.
J. C. de Man, J. O. Strandhagen, ‘Spreadsheet application still dominates enterprise resource planning and advanced planning systems’, IFAC-PapersOnLine, vol. 51, no. 11, pp. 1224-1229, 2018, doi: 10.1016/j.ifacol.2018.08.423.
S. Sahran, F. A. Goni, M. Mukhtar, ‘ERP implementation challenges in small and medium enterprise: a framework and case study’, in Advanced Materials Research, vol. 139-141, pp. 1636-1639, Oct. 2010, doi: 10.4028/www.scientific.net/AMR.139-141.1636.
S. A. Menon, M. Muchnick, C. Butler, T. Pizur, ‘Critical challenges in enterprise resource planning (ERP) implementation’, International Journal of Business and Management, vol. 14, no. 7, pp. 54-69, 2019, doi: 10.5539/ijbm.v14n7p54.
G. L. Curry, R. M. Feldman, ‘Manufacturing systems modeling and analysis’, Springer Berlin, Heidelberg; 2009, doi: 10.1007/978-3-642-16618-1.
G. Rauchecker, G. Schryen, ‘Using high performance computing for unrelated parallel machine scheduling with sequence-dependent setup times: development and computational evaluation of a parallel branch-and-price algorithm’, Computers and Operations research, vol. 104, pp. 338-257, Apr. 2019, doi: 10.1016/j.cor.2018.12.020.
Downloads
Published
How to Cite
Issue
Section
License
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.
