Note: Supports multi-function functions such as sorting and multi-group mixing. It is recommended to run it with excel2016 to reflect the full effect.
First, the idea of automatic scheduling
1.if(or(Order quantity = 0, production status <>.)"", Unfinished Quantity < = Current Daily Planned Quantity Summary, Production Attribute ="", Group Daily Planned Total Working Hours = 0, Scheduleable Date > Daily Planned Date, Order Receiving Date + Delivery Cycle > Production Date), if(or(mps!$j12<=0,mps!$u12<>"",mps!$s12<=sum(mps!$ah12:ah12),isblank(mps!$w12),mps!Group Day Planned Total Man-Hours = 0, mps!$ad12>mps!ai$11,sum(mps!$e12,mps!$y12)>mps!ai$11),2.if( first scheduling of the group = 1st scheduling, min (unfinished quantity - cumulative planned output in the previous period), hourly output of the group * total planned working hours of the group per day, average output quantity), if(mps!).Group first scheduling = 1, min (mps!).$s12-sum(mps!$ah12:ah12),mps!$aa12*mps!Group Day Planned Total Man-Hours, mps!$ab12),3.if(The daily planned quantity is currently summarized in the above columns) (Daily production capacity = Hourly production capacity * Daily planned working hours) Group daily planned total working hours》=1,,if(mps!Group Day Planned Work mps!Group Day Planned Total Man-Hours" = 1,4min((1- Group daily planned working hours Group daily planned total working hours) * (daily production capacity = hourly production capacity * daily planned working hours) * group daily planned total working hours, min((1-mps!.)Group Day Planned Work mps!Group Day Planned Total Man-Hours, mps!$aa12*mps!Group Day Planned Total Man-Hours, 5Incomplete quantity - sum (daily planned quantity accumulated in the current row), daily averaged quantity, (daily capacity = hourly capacity * daily planned working hours) * group daily planned total working hours))).
mps!$s12-sum(mps!$ah12:ah12),mps!$ab12,mps!$aa12*mps!Group Day Planned Total Man-Hours)))).
Note: The evaluate function in the total output hours of the group day is a macro table function used to calculate the expression.
2. Cell formatting
Overdue red display: =$l 12
The planned date week is not populated in yellow: =or(mod(k11,7)=0,mod(k11,7)=1).
The data in blue font is brought out for formula or VBA.
The planned output data area is filled with gray cells to indicate that it has been manually changed: =not(isformula(ai12)).
Start date, finish date cell format: mm-dd;;
Planned output area cell format: g generic format;
3. Reference of production unit and daily output
Fourth, the main role of automatic scheduling
1. It can be operated manually and automatically, so as to achieve strong compatibility, flexibility and flexibility, fast response, and reduce production scheduling time.
2. Accurate scheduling according to hourly production capacity, planned working hours, material delivery time, and production load, and the planned working hours are supported by expressions.
3. Whether the current order is saturated and whether it can be completed according to the updated delivery date will form a red word early warning display.
4. Through the start and completion dates, guide the unity of all plans and act as a baton in the plan.
Fifth, the rules of planning and scheduling
In order to ensure the stability, rationality and principle of the production plan, it is implemented according to the flexible mode of 5d*10h+1d*8h+1d*0h, that is, under normal circumstances, Monday to Friday is 10h a day, 8h on Saturday, and rest on Sunday.
Plan sorting: production status, update delivery date, order receiving date, material complete set date, (customer classification), inventory name, production pull, production deficit, (sales *** level), all in ascending order.
1.The principle of delivery on a first-come-first-served basis
Standard Order: The shorter the delivery time and the more urgent the delivery time of the product (urgent orders must be indicated on the internal order), the earliest time should be arranged.
Rework Order: E-commerce customers return to the warehouse unpackaged need to meet 2 hours of production to schedule, other customers to return to work at least 1 day in advance to notify the plan arrangement, to ensure the stability and continuity of the production plan, such as the efficiency of rework with the line can meet the efficiency of normal production can be reworked with the line, otherwise the special line scheduling rework.
2.The principle of order first-served
In the case of the same delivery date, the default arrangement will be made on a first-come-first-served basis.
3.The principle of complete set of materials
[Set of time].In principle, the materials must be put into storage before 12 a.m. the day before and notified to prepare and pick.
Alternate MaterialWhen a variety of common raw materials exist at the same time, it is necessary to take into account the requirements of the "ECN Change Notice" and the "Material Substitution Relationship List" issued by the Ministry of Engineering to use alternative materials.
4.Customer Segmentation Principles
There are key customers and general customers, and the more focused customers, the more attention should be paid to their scheduling. If a company classifies its customers according to the ABC method based on sales, category A customers should be given the highest priority, followed by category B, and category C should be given the next priority.
5.Process flow principles
[Operator].Before compiling the plan, it is necessary to fully understand the total number of production operators in the workshop and the personnel of each shift, and approve the personnel required for the operation plan according to the process quota requirements, and control the total personnel of each shift in the workshop. In the process of arranging personnel for operation planning, the difference between the personnel of each shift is within 5 people, the personnel of the corresponding shift between each day is controlled within 10%, and the personnel requirements for the corresponding product needs of each line are balanced. The more processes there are, the longer the manufacturing time, and it should be paid attention to.
Device ResourcesWhen preparing the plan, fully grasp the status of existing equipment resources, arrange the parts and components on the most suitable machine for SOP data issued by the engineering department, and try to control the production of the same product on the same machine production principle. When there is a bottleneck in equipment resources, production should be arranged at different times to improve the utilization rate of equipment. When the production load is insufficient, the most economical machine or line must be selected for the same model.
[Mold change and line change].Considering the number of molds, the production of the same series of the same mold products should be taken into account to reduce the mold change time as much as possible, the number of mold changes should be taken into account when the operation plan is prepared, and the time difference between the two sets of molds before and after the mold change should be minimized, and the control of the number of mold changes between each group should be relatively balanced. Produce a product model or series on a daily or per shift or per line to the best and the lowest and the smaller the difference between them, the better.
[Dedicated line only].For special line products, arrange special line production as much as possible, such as which line is preferred in which trial production or mass production has been produced. Ensure that the same product is arranged to be produced on the same line as much as possible, and do not appear that the same product is produced today and transferred to another line tomorrow, or this week is produced on one line and another line next week.
6.The principle of capacity balancing
The production of each production line should be smooth, the production speed of the semi-finished product production line and the finished product production line should be the same, the machine load should be considered, and the production bottleneck should not be generated, and the line should be stopped and waiting for materials.
7.Pin *** principle
There is a good pin *** priority arrangement. Sales include a reasonable delivery cycle when placing an order, the real demand time of customers, the punctual provision of customer supplies, the timely delivery of inventory, and the punctual payment of customers.
6. Master Production Schedule (MPS).
MPS is a part of the closed-loop planning system. The essence of MPS is to ensure that the specified requirements (what, how much and when) of the sales and production planning are consistent with the resources used.
MPS takes into account business planning and sales planning and coordinates production planning with them.
It looks at what is sold and what can be manufactured, which makes it possible to create a suitable "master production schedule" for the shop floor and adjust this plan with coarse capacity data until the load is balanced.
If you need the original document, please send a private message to "13068".