Looking for some help with a production optimization problem

JT_Crackin 

To use Excel Solver to minimize the downtime on Machine B and C and keep them running as much as possible, you need to set up an optimization model. Here is a step-by-step guide to achieve this:

Step 1: Define Your Variables

1. Create columns for each batch indicating:
• Batch Number
• Run Time on Machine A
• Run Time on Machine B/C
2. Create cells for start and end times for each batch on Machine A, B, and C.

Step 2: Set Up the Data

Let's assume you have the following data in columns A to C:

Step 3: Set Up the Variables

1. Create columns for the start and end times of each batch on Machine A, B, and C.

In columns D to I, you might have:

Step 4: Set Up Constraints

1. Add constraints to ensure that Machine A's batches do not overlap and the same for Machines B and C.
2. Ensure Machine B and C can only start after the batch is completed on Machine A.

Step 5: Use Solver

1. Open Solver by going to Data > Solver.
2. Set the objective to minimize the total downtime of Machines B and C.
3. Set the variable cells to be the start and end times for Machine A, B, and C.
4. Add constraints:
• End Time A of batch i must be less than or equal to Start Time A of batch i+1.
• End Time B/C of batch i must be less than or equal to Start Time B/C of batch i+1.
• Start Time B/C of each batch must be greater than or equal to the End Time A of that batch.

Example Solver Setup

1. Objective:
• Set Objective: Minimize G2 (where G2 is the cell containing total downtime of B and C)
2. Variable Cells:
• D2:Dn (Start Times for A)
• E2:En (End Times for A)
• F2:Fn (Start Times for B/C)
• G2:Gn (End Times for B/C)
3. Constraints:
• E2:E(n-1) <= D3:Dn (No overlapping batches on A)
• G2:G(n-1) <= F3:Fn (No overlapping batches on B/C)
• F2:Fn >= E2:En (B/C can only start after A finishes)

Additional Tips

• Add constraints to ensure that start and end times are within the working hours of Machines A, B, and C.
• Set an upper limit for the total downtime on Machines B and C, if necessary.
• Use binary decision variables if you want to explore different scheduling options (e.g., a batch can run on either B or C, but not both).

Example Formulas

• End Time A:

=D2 + C2

• Start Time B/C:

=E2

• End Time B/C:

=F2 + D2

By setting up the problem this way, Solver can optimize the schedule to minimize downtime on Machines B and C, ensuring they are utilized as efficiently as possible.

NOTE: My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together. Maybe it will help you further in your project, if not please just ignore it.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.