4
$\begingroup$

I changed jobs recently to a manufacturing firm. Job shop scheduling is made 100% manually and intuitively in Excel. Very time consuming and they have no idea if the schedule made is something close to best solution.

The production consists of multiple processes (in total 4). End product is made of 2 parts. Both parts are individually molded, then one of the parts are printed, part 1 and part 2 are assembled and then packed. Number of work centers available in each process is 23,15,15,5 respectively.

My question is that if I would be able to manage production scheduling in Excel solver by assigning orders to machines while maintaining a min OEE and limited tardiness and visualize it with a gantt chart. I have searched the web but did not find much so if you can point me to the right direction I would be very thankful.

Improve this question
$\endgroup$

5 Answers 5

Reset to default
4
$\begingroup$

The scheduling models in the real application are a bit different from the academic problems. For example, in a real situation, there are multi objectives/complicated constraints that should be satisfied. Another important thing is, which kind of scheduling model (parallel scheduling, job shop, etc.) you have faced in your machinery area. To achieve your desired goals you have many options.

  • If you are interested to develop a mathematical formulation based on MIPs, as @prubin mentioned, and your problem scale is not too large, you can implement your model in an excel-based solver like OpenSolver or solver studio (if you have to use excel). Also, you can interpret your model in the Python-based solvers like Pyomo or PulP for using the programming language benefits.

  • If you are going to use the heuristic methods, you can use LEKIN, excel-based Jensen Library and OptaPlanner. They have provided some useful Gantt chart facilities.

  • If you want to try commercial advanced scheduling software, I recommended using ASPROVA to learn about advanced software with dozens of features. Also, other web-based scheduling software could be found by googling.

Finally, it would be considered excel is not a strong scheduling software and it needs more time to provide/modify production data. By using optimization software, you can save your time and uses it to increase the firm's productivity. Therefore, I recommended you to start with one of the above fields and tried to figure out what you are looking for. I hope they would be helpful.

Improve this answer
$\endgroup$
8
$\begingroup$

It should be possible to model the production process using an integer or mixed-integer linear program. There is a lot of literature out there about MIP models for job shop scheduling. The data would fit in Excel, but the dimensions of the problem might be a bit much for the version of Solver included in Excel. There are some solver alternatives for Excel (such as OpenSolver) that might do the trick, but you might end up needing a commercial solver. If so, it might be easier to build and solve the model outside Excel (possibly pulling demand data etc. from Excel) and then push the solution into an Excel spreadsheet.

Improve this answer
$\endgroup$
3
$\begingroup$

In the industry a lot of decision making is done via Excel based modeling. This is usually a start and some problems outgrow very quickly especially scheduling problems given their combinatorial nature. Lot of folks have given good advice above wrt modeling. I would suggest seeing if you can mimic the intuitive method used to create a simple constructive heuristic with metrics you need using VBScript or Python (if you are familiar with programming). After that trying to do a simple local search moves to see if you can come up with a better solution. A good reference for this kind of work in scheduling is here: https://www.gerad.ca/~alainh/Scheduling.pdf

Improve this answer
$\endgroup$
2
$\begingroup$

Don't get hung up on having to use Excel - many off-the-shelf software packages don't even begin to address the issues job shops have. Excel is a great way to experiment and develop a system that works without spending a small fortune in the process.

Speaking of the process, it is often useful to apply the Theory of Constraints to your business, and use it as the framework for planning. Here are some other ideas that might help:

Improve this answer
$\endgroup$
1
$\begingroup$

There are a lot of good answers above if you are willing to develop your own model and visualisations.
There’s also an app that I’d recommend: https://www.modelandoptimize.com
It has a simple interface and I think your use case can be represented by their Job Scheduling demo.
They provide the solution in excel if you like.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.