Using Excel Solver for a complicated capacity problem

[RandallTex]

Thanks for viewing the post! Just for background information, I have used Solver in the past in school. However, the problems were not that difficult.

Currently, I am fighting upper management for my value stream production requirements. So far, scheduling has produced a capacity model (simple) showing that we need an additional machine in a cell. Management used a contractor for a time study and the consultant also stated we needed an additional machine. Still not convinced, management has asked for additional proof, using a Lean tool called a work combination table. The tool breaks down the part into smaller functions consisting of hands on work, automated tasks and walking.

Here is the problem:
One operator per shift
Four machines
Six Parts that have differing requirements between the operator and machine involving 13 steps
Each step might involve time for hand, automated and walking times.
The schedule is rigid for the week.

Example, for the week, there are different shipping requirements for each part:
Part A: 55
Part B: 6
Part C: 40
Part D: 15
Part E: 15
Part F: 2

I have seen several examples of minimizing the time to build a part through multiple machines, but the conflict in this is the operator. The operator can only do one operation at a time, so parts are started at different times and come off the machines at different times. I think we can meet the demand by adding an operator per shift and I started trying to use solver, but I am not sure how to set it up. Ultimately, I need to know how many days it takes to meet the schedule.

Should I give up and do this by hand using a cascading schedule chart typical production is about 100 parts per week. The issue there is knowing how to optmimize between the parts, I can't assume we should run all part A to start. It would be a mixture per machine per day.

Any input would help!

[Alf]

so parts are started at different times and come off the machines at different times.

As solver has no concept of time i.e. all things happened at the same instant this could be difficult to model.

The operator can only do one operation at a time

This can easily be modeled using a binary grid.

At the moment I’m able to imagine how complex you model have to be to take in account all the constraint you need to set up. It could be that the “normal” excel solver is not powerful enough for a task like this.

Perhaps you could contact the makers of solver and ask for their advice.

http://www.solver.com/

Alf

[shg]

Are these machines identical?

If so, it seems like the problem is to optimize machine operating time. A machine is necessarily idle when the operator is performing setup, but you want to avoid the case where another machine is idle at the same time.

[shg]

As an alternative to pure optimization, you could (one could) write an algorithm that does a relatively simple look-ahead as each machine becomes available, choosing, say, the next six or eight pieces to load that result in the least overlapping down time. That's keeps the number of evaluations down to a million or so at each step, which would be very fast.

You could run that simulation for your current four machines, then five machines, and see how throughput compares.

[RandallTex]

Thanks for your input. I think it is pretty difficult because of the dynamics between the operator tasks and the machine tasks being interdependent. I will contact Solver for advice.