Use Excel Solver to generate a production schedule (English)
- Articles, Blog

Use Excel Solver to generate a production schedule (English)


Hello friends, this is James today, I would like to show you how to use Excel Solver to generate a production schedule for a department of a factory Before demonstrate how the solver works, let me explain the detail of this schedule to you What you see here is a production schedule for the knit Department Just for example, the start day of the schedule is 21st of December At the right hand side of this schedule Are the production information? Such as the work order number, the products to be made the production quantity and the due day for this department The standard minutes are the time needed to make a piece of product The loading is Actually the production quantity multiplied by the standard minutes. I then converted to hours These two columns are the work center reference and the work center number the W. C. reference is just a simple sequence The Excel Solver find the best work center for this order by changing this reference number Why we also have the work center code here? It is because in most cases we do not use simple number for the work center It is usually an alphanumeric code. In this Excel. I used Vlookup to find the work centre code. I have defined 20 work centres for example This is the OEE of the work centers OEE stand for overall equipment effectiveness You may call it efficiency This Excel worksheet finds the work center and its OEE based on the work center reference This column is telling you whether or not this order has enough time to make if a work Center is assigned to it. For example, this order, it may need more 34 hour to complete it then it is red in color The next columns are the start date and finish date of the orders These grey areas are cells with formula defined therefore you should not edit or change them Up here are two cells for the solver to set the objective You can only choose either one for the objective cell The lower one is the number of orders may overdue The upper one is the total hour overdue. We will tell the solver to minimize them Right here is telling you how many work centers has been chosen Let’s look at the right-hand side of the schedule This is a Gantt Chart The top row is the work hour of this day This is the date in the day-month format This is December 21st, December 22nd This colour bar represent an order from start production to finish production such as this one, start from 27th of December and completed on 8th of January The grey columns are non-working days and thus the work hours are zero The number in each cell is telling you a possible daily output All these are calculated automatically After the schedule has been generated an order may overdue, the finish date will be red in color Before we go into the solver I would like to show you how these dates are calculated Actually, I have define a work calendar in this worksheet so that we know when it is workday and when it is not The first day of the calendar should always point to the schedule start date Then it will generate the rest of the days in the year It is also define the week day You can input the number of work hours here if you want it nine hours per day, just input here I have set all Sunday to non-working days so no need to define one by one You can also define holiday here just define month and day of the holiday then you can create some common public holidays, such as Christmas and New Year Okay, finally we can see how the Solver work I get to erase all the old orders first You see the cells have formula defined, all become error Then I need to get my new orders Suppose you have your IT department or production department to provide you the orders in Excel format Then just copy and paste here Now the orders are sorted by the production due day from nearest to longest Since no work center has assigned, the formula cells are still error Let’s assume the schedule start is still 21st of December Now we can activate the solver program Bear in mind, you should always use the solver in the data menu Not the third party one in add-on It is not work in this case. I have already defined the solver conditions First set the objective cell I choose the number of orders overdue I want to minimize this number How to do it? I tell the Solver the variables cells are the work center reference It will keep change them until it find the best solution This must be some constraints we only need three constraints here Since we have 20 work centers the variables must between 1 and 20 For Excel Solver, we need 2 constraints to define the “between” condition “Less or equal to” and the “greater or equal to” The third constraint is the variable must be integer Three constraints are enough in our case You must choose the evolutionary solving method because our problem is not a linear program Or linear problem That’s an option to set Maximum time without improvement It is to tell the solver when to stop if it cannot find any further improvement The default value is 30 But it is too soon to stop in our case Since we have a lot of combinations of work orders and work centers We better make it bigger. I set to 200 Beware this range, they must cover all your orders in the schedule Now click [Solve] to let the Solver run you can see the numbers here are increasing It is normal if the number change very slow then it must be something wrong The subproblems here, in our case, it may increase up to over 10,000 Now you see the number in the objective cell is dropping That means it is trying to minimize the overdue and finding the best solution Since it may take some times to run let’s get back later Okay, the sofa has stopped this time it almost takes ten minutes to find the best solution Let’s look at the result It display a message Solver cannot improve the current solution. All constraints are satisfied It is okay as long as all constraints are satisfied By default, it keeps Solver solution Then we click OK to keep the solution Now you see no order will overdue, even not a minute to overdue but it only need 19 work centers to work It may be loading It may be the loading is not big enough Next time I should put more orders on the schedule You may notice the Gantt chart behave abnormal All orders are started on 21st of December It is fine as we still need one more step We need to sort the orders to make it reasonable We sort the orders by work centre first then by start date Now the Gantt chart becomes reasonable You see work center A01 works this order Work centre A04 work this order first and will finish on 24th of December Then it work on next order It also calculate the output by the time remain on this day So it is done You can download this Excel template on my website try it yourself and change it to fill your own situation I also made some tutorial on YouTube to show you how to make this schedule from scratch If you like this video Please. give me a like and subscribe. Thank you for watching. Bye. Bye

About Ralph Robinson

Read All Posts By Ralph Robinson

Leave a Reply

Your email address will not be published. Required fields are marked *