2.1 DEVELOPMENT OF PRODUCTION PLANNING SYSTEM The idea was to develop a system which works automatically with the input of demand & cutting data in the cutting section. The primary requirement is that the system should be simple to apply and cater to the dynamic environment. For this MS Excel is used. 2.2 USING MS EXCEL FOR PRODUCTION PLANNING MS EXCEL spreadsheet has been used for production planning. This approach is sometimes the practical solution for a small project in a big organization. Microsoft Excel is available as a part of the popular MS Ofce package. It has an add-in module for solving problems, called – Solver, that can be used for tasks solving. As MS Ofce Excel is a widespread and used software system it has the advantage to be well known, thus making sense to use is as a tool for production planning. 2.3 MATHEMATICAL MODEL & LOGIC USED a) The batch size “column L in the sheet in gure 4.1” is calculated as below :- =ROUND((IF(B7/50*4/3<=10,IF(B7/50*4/3<=4,(B7/50*4/3)* 4,(B7/50*4/3)*2),(B7/50*4/3))),0) Where, B7 denotes the annual demand of the item. To keep some buffer we have decided to do the work of one month in three weeks. b) The frequency “column M in the sheet in gure 4.1” of each item i.e. whether it should be taken Weekly (W), Fortnight (F) or Monthly for cutting, is calculated as below :- =IF(B7/50*4/3<=10,IF(B7/50*4/3<=4,"M","F"),"W") Where, B7 denotes the annual demand of the item. c) The time required “column N in the sheet in gure 4.1” by each item in one month cutting to meet the monthly demand, is calculated as below :- =((K7*IF(M7="M",L7,IF(M7="F",L7*2,L7*4)))+(IF(M7="M",J7, IF(M7="F",J7*2,J7*4))))/60*(L7/IF(L7>0,L7,1)) Where, K7 denotes the machine time. J7 denotes the setup time. L7 denotes the batch size. M7 denotes the frequency of the batch in a month. d) The net cutting “column V in the sheet in gure 4.1” in any month is calculated as below :- =(P7+Q7+R7+S7+T7+U7) Where, P7,……..,U7 denotes the quantity cut at each time in one month. e) The cumulative cutting “column W in the sheet in gure 4.1” for any item from the starting of the year, is calculated as =W7+AH7 Where, W7 is cumulative cut in previous month. AH7 is net cut in this month. f) The time taken by each item “column X in the sheet in gure 4.1” in one month i.e. job hours booked in the month for each item, is calculated as below :- =((K7*P7)+(J7*P7/IF(P7>0,P7,1))+(K7*Q7)+(J7*Q7/IF(Q7>0 ,Q7,1))+(K7*R7)+(J7*R7/IF(R7>0,R7,1))+(K7*S7)+(J7*S7/IF( S7>0,S7,1))+(K7*T7)+(J7*T7/IF(T7>0,T7,1))+(K7*U7)+(J7* U7/IF(U7>0,U7,1)))/60 In this, the time taken has been calculated on the basis of no. of setups taken, no. of quantity cut each time and the machining time. g) The inventory “column FF in the sheet in gure 4.1” in the line of any item, is calculated as below :- =IF((-Fd82 +V82+ Ah82+ At82+ Bf82+ Br82+ Cd82+ Cp82+ D B 8 2 + D N 8 2 + C Z 8 2 + E L 8 2 + E X 8 2 + F E 8 2 ) > = 0 , ( - FD82+V82+AH82+AT82+BF82+BR82+CD82+CP82+DB82 +DN82+CZ82+EL82+EX82+FE82),0) DEVELOPMENT OF PRODUCTION PLANNING SYSTEM IN A MANUFACTURING ORGANISATION Original Research Paper Vinod Kumar IIMT College of Engineering, Greater Noida, U.P., India X 115GJRA - GLOBAL JOURNAL FOR RESEARCH ANALYSIS Mechanical Engineering Production planning, scheduling, monitoring & controlling plays an important role in accomplishment of the organizational objectives of manufacturing companies. A well designed rule, with an information system that keeps track of the current situation of the shop in a real time manner, is a very effective tool for exercising management control over the job-shop. These are especially necessary in very large job shops. A practitioner would like a production plan meeting all the due dates while keeping overtimes and sub contracting costs to minimum, because failure to meet due dates may result into customer dissatisfaction, bad reputation and such other undesirable consequences. In production planning the jobs that exist on the job shop at the time of scheduling are considered and a schedule is prepared. In real situation, these schedules become obsolete as new jobs arrive. To manage such a changing environment, a rolling schedule is required. Most of the productions planning problems are dynamic in nature, i.e. new orders are received continuously during the production process. The created production schedule may be changed to cope the changes of production orders and manufacturing conditions during production process. For production planning I have used Microsoft EXCEL spreadsheet. This approach is sometimes the practical solution for a small project in a big organization. I've experimented here using the EXCEL spreadsheet format. I have used lot of logic and formulas to make the production planning Excel sheet which is of great use in the shop oor production environment. I've tried to keep it simple and have not used any macros or VB, so it should be straight forward. ABSTRACT KEYWORDS : Kapil Kumar JSS Academy of Technical Education ,Noida, India*Corresponding Author VOLUME-8, ISSUE-12, DECEMBER-2019 • PRINT ISSN No. 2277 - 8160 • DOI : 10.36106/gjra 116 X GJRA - GLOBAL JOURNAL FOR RESEARCH ANALYSIS In this, the logic used is by keeping an eye on the input taken and output gone in the form of nal product. h) The difference in cutting pattern of two machines “column O in the sheet in gure 4.1”is calculated as below :- =IF(OR(A7="A",A7="N"),IF(AND(40<=F7,F7<=60),N7/3,(IF( AND(60 100, only one piece can be cut. This logic is taking into account itself these considerations depending upon we are sending an item to the machine. 3.1 PRODUCTION PLANNING EXCEL SHEET The nal Excel sheet (Figure 4.1) which I have made have all the required features. The excel sheet is looking very small but all the features are present. Different buttons are there to make it compact in size. If we will click on these buttons the full size Excel sheet will be opened. This Excel sheet is like small software doing all the work which is required in shop oor production planning. This sheet is able to cope up the changes of production orders and manufacturing conditions during production process. Only jobs that are existing on the job shop at that time are considered and a schedule is prepared. These previous schedules become obsolete as new jobs arrive or production environment changes and new schedule is prepared it self. Figure 3.1:- Production Planning Excel Sheet 3.2 FEATURES OF PROPOSED PRODUCTION PLANNING SYSTEM Production planning sheet is a tentative plan showing how much production will occur in the next several time periods. Production planning sheet also determines expected inventory levels, as well as the workforce and other resources necessary to implement the production plans. Production planning is done using an aggregate view of the production facility, the demand for products, and even of time (using monthly time periods). 3.3 SCHEDULING IN TERMS OF BATCH SIZE & FREQUENCY The current system is far away from the concept of scheduling. Monthly and yearly target is projected by Marketing Peoples . These targets are lled in left hand side under the column quantity required and automatic scheduling is done showing the batch size, frequency in one month. The proposed system can reschedule itself if there is any change in the demand of the product. It is very much exible and easy to use. Just our work is to ll the daily entry of cutting under the column Cutting Pattern. It will automatically keep accountability of different sub items, job card hours booked by employees, inventory in the shop oor for various sub items. The schedule will repeat itself if everything goes according to the plan. Now, the excel sheet shown below in gure 4.2 is showing the schedule in the form of batch size and frequency of the batch in a month. Figure 3.2 – Auto Planning For April Month 3.4 RE-PLANNING Re-planning is an important activity in dynamic situation. This feature has been included in the current system. In a certain month if the work is not done as scheduled then this will be automatically reected in the next month plan as shown in gure 4.3. Further if new orders are received during the production process, the created schedule will be changed to cope the changes of production orders and manufacturing conditions. Production order changes include removal of an order that is canceled by a customer and insertion of an order that has to be completed within a short period of time. Manufacturing condition changes include disturbance events of resources such as breakdowns of machines and sickness of workers. Figure 3.3 – Auto Re-Planning for May Month 3.5 TRACKING The existing system is having lot of inventory at the input stage as well as in the form of WIP (Work in Progress). The proposed system is tracking the inventory level and is focused towards reducing the inventory in line based upon the priority rule which and aimed at rolling the items as soon as possible. 3.6 PRIORITIZATION To give the priority we can see the inventory level of the various items in the sheet. The Sheet shown in gure 4.4 is showing the WIP in the shop oor for various items. It will help to cut only those items, which are scarce. Priority rule is focused towards reducing the inventory and aimed at rolling the items as soon VOLUME-8, ISSUE-12, DECEMBER-2019 • PRINT ISSN No. 2277 - 8160 • DOI : 10.36106/gjra as possible. Figure 3.4 – Inventory Level for Sub Items of Projects 3.7 LOAD CALCULATION FOR SUB CONTRACTING In the existing planning system also, the mangers are trying to cut each and everything on the machine without having any idea of how much can be given outside for sub contracting. If they are giving more items outside then the machines will be idle and the company will lose the costly machine hours and if they are giving lesser amount outside, then they will not be able to meet the target resulting in banging's from the corporate management. In such a situation it is important to know the current loading in the shop as shown in the gure 3.6 the total loading on the two machines can be calculated by this sheet. Figure 3.5 – Load to be Given for Subcontracting The available hours for a machine in one month are 20*24 = 480 hours. It is showing which machine has more loads as shown in gure 4.7 so we can redistribute the load also. The load which we should give outside for sub contracting is shown by “N”. Figure 3.6- LOAD DISTRIBUTION The production planning sheet is showing the exact load which should be given outside to meet the demand. Further, it is showing we should give those items outside having lesser dimensions, because it will reduce the transportation cost. Also, the employees are taking advantage of putting more than one item on “AMADA” machine and doing the work for very less time. It is because there is xed timing for each piece whether it is small or large and after doing three pieces in a single pass employees are not doing anything and taking advantage of the situation. So, we can give tubes, rods of lower outer diameter and only the tubes of large outer diameter should be cut in the section. This will improve the production and reduce the idle machine hours. 3.8 EXPECTED BENEFITS OF THE PROPOSED SYSTEM The proposed system is expected to have following benets with respect to improved efciency and effective decision making Ÿ Increased Efciency & Effective Decision Making Ÿ Eliminate hours spent scheduling and calling staff on the phone — managers will have more time to manage. Ÿ Instant visibility of schedules, risk areas and budget/cost impacts — do more with less staff. Ÿ Minimized make span. Ÿ Minimized work in process. 4. CONCLUSION 4.1 SUMMARY Basically the production planning and control system provide information to efciently manage the ow of materials, effectively utilize people and equipment, coordinate internal activities with those of suppliers, and communicate with customers about the market requirements. The PPC system does not make decisions or manage the operations-manager perform those activities. The system provides the support for them to do so wisely. Current study was done in the cutting section of the plant. The cutting section was chosen for the study because there are lots of problems in the cutting section which affect the performance and immediate attention was required. Since there is no systematic planning and control in the cutting section further improvement with respect to e.g. quality etc is difcult to implement. The proposed methodology of production planning by means of the MS Excel spreadsheet is like small software doing all the work required in shop oor production. The proposed planning system uses the advantage of the popularity of the MS Excel and can be easily applied, modied and extended to other sections also. By following the production planning excel sheet, the cutting section will be under full control. After having full control over the section, the other aspects of manufacturing like six sigma, quality improvement etc can be implemented. 4.2 RESISTANCE FACED The main resistance faced is human nature of accepting any change. Any change whether it is good or bad is opposed. The production planning excel sheet is of great use but nobody wants to use it and still people want to keep sticking to the existing system. In the proposed planning system employees will not be able to exploit the situation, so after some time they also started ignoring this work. Nobody has shown any type of interest in knowing the importance of this planning excel sheet. Only, the cutting shop in charge is using this production planning excel sheet for keeping the track of items leaving behind other aspects of this sheet. 4.3 RECOMMENDATION Maintaining this production planning excel sheet must be an integral part of the activities of the shop. Like quality, customer service, and plant safety, this work must be promoted throughout the organization as everyone's responsibility. This attitude must start at the top levels. X 117GJRA - GLOBAL JOURNAL FOR RESEARCH ANALYSIS VOLUME-8, ISSUE-12, DECEMBER-2019 • PRINT ISSN No. 2277 - 8160 • DOI : 10.36106/gjra VOLUME-8, ISSUE-12, DECEMBER-2019 • PRINT ISSN No. 2277 - 8160 • DOI : 10.36106/gjra 4.4 LIMITATIONS Using MS EXCEL for the production planning purpose has two limitations. First, it is not having GUI and hence do not appeal to the user. Second, the complex logics used in the system may not be understandable to workers. 4.5 FUTURE WORK With good information system and aided by simulation as tool, we can expand our work to the other section also i.e. next stages of operations. Then a manager can know where exactly the problem lies and come up with some alternate solutions to eliminate the problem. Thus, the congested atmosphere of a typical shop oor production can be systematically reduced, by effective controls on operations through proper scheduling, aided by simulation and a well designed information system. REFERENCES 1) Vollmann T.E., Whybark D.C and Berry W.L. “Manufacturing Planning and Control System”, 5th Edition. Boston: McGrawHill (2005). 2) Corke D.K. “Production control in engineering”, 4th edition (1997). 3) SubashBabu A (Coordinator), “Workshop on production management” IIT Bombay, April 24-28, 2007. 4) Bauer, Bowden, Browne, Duggan and Lyons, “Shop oor control systems” publisher- Chapman & Hall (1998). 5) King J.R., “production planning and control” Pergamon Press Oxford (1975). 6) Chase R.B., Aquilano N.J. and Jacobs F.R., “Production and Operation Mangement”, TMH, 8th edition (1999). 7) Goodwin, Cardillo J. and Bergmann E., “Implementing ERP in big way” APICS- The performance advantage, June (1996) 8) Abumaizar R.J. and Svestka J.A. (1997), “Rescheduling job shops under random disruptions, International Journal of Production Research”, pp. 2065- 2082. 9) Lee C.Y., Liman S.D. (1993), “Capacitated Two-Parallel Machines Scheduling to Minimize Sum of Job Completion Times”− Discrete Appl. Math, pp. 211–222. 10) Mula J., Poler r., Garcia-Sabater J.P., Lario F.C. (2006), “Models for production planning under uncertainty: A review” Int. J. Production Economics, pp.271–285. 11) Sanmarti E., Huercio A., Espuna A., Puigjaner L. (1996), “A Combined Scheduling/Reactive Scheduling Strategy To Minimize the Effect of Process Operations Uncertainty in Batch Plants.” 12) Fylstra, Lasdon D.L., Watson J., Waren A. (1998), “Design and Use of the Microsoft Excel Solver”, pp. 29-55. 118 X GJRA - GLOBAL JOURNAL FOR RESEARCH ANALYSIS