Engineering Access (Jul 2024)

Development of MS Excel and Power BI Integrated Production Scheduling System for an MSME

  • Pranav Shivraj Patil,
  • Srishti Sudhir Patil,
  • Sudhir Madhav Patil,
  • Maneetkumar R. Dhanvijay

DOI
https://doi.org/10.14456/mijet.2024.15
Journal volume & issue
Vol. 10, no. 2
pp. 124 – 142

Abstract

Read online

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.

Keywords