Real-Life Equipment Optimization Problem

Quote of the Day

First forget inspiration. Habit is more dependable. Habit will sustain you whether you’re inspired or not.

— Octavia Butler. It is very important to cultivate habits that promote excellence. Jack London used to say that "You can't wait for inspiration, you have to go after it with a club." I believe that as well. I often find that inspiration comes in the quiet times that occur after I have laid siege to a problem for a while.

Introduction

Figure 1: Illustration of the Manufacturing Situation.

I recently was asked to provide a recommendation on how to schedule the operating time for three different machines that were producing three different products. This is not a made up problem, but reflects a real production situation. My solution uses Excel's Solver and its linear programming-based optimization routine to find an optimal machine scheduling plan. The solution I provided has proven to be useful to the folks who asked for it, and I thought it was worth sharing my solution here.

I should mention that the model I am showing here is only the first-round of my modeling effort. I am continuing to work with the group that is responsible for this manufacturing effort to create a more detailed model of their manufacturing process. The effort has been very interesting and has provided much insight into their manufacturing process.

For privacy reasons, I have normalized all product costs and have changed all the default values from their true values.

Problem Description

Figure 1 shows a high-level illustration of the manufacturing situation. The following list defines the problem and its constraints:

• There are three machines called 1, 2, and 3.
• There are three products being manufactured called A, B, and C.
• Each product has a different unit market value.
• We will be be optimizing the total product value produced by these three machines each month.
• Machine 1 can only produce product A.
• Machine 2 can only produce product A.
• Machine 3 can produce either product B or C.
• Machine's 1 and 3 share the same electrical supply and only one of these machines can be run at a time. I should mention that these machines are enormous, in a remote location, and take a considerable amount of power. Upgrading the electrical service is prohibitively expensive.
• Machine 3 requires time to switch from producing B to C and vice-versa. I will not be modeling this loss of production – I will include switch-over time in a later model. I needed to get an initial solution out immediately.
• There are production contracts that mandate that a minimum number of A, B, and C products must be manufactured each month.
• The machines have a certain amount of downtime that is related to their hours of operation. I have modeled these maintenance operations as a loss of efficiency.

Analysis

I am not going to go through the details of the spreadsheet here in text – I recommend you just look at the spreadsheet itself. Here are its critical features:

• The spreadsheet uses simple VBA macros to select canned scenarios and run the solver.
• I have defined four scenarios that you can select using radio buttons.
• You can change the inputs to whatever you want and click the solve button to generate a custom solution.

Here is my spreadsheet.

Conclusion

The model provided us a very useful discussion vehicle for understanding and evaluating the manufacturing process in terms of:

• machine efficiency
• switching time
• product mix
• electrical power

While I plan to continue to refine the model, the spreadsheet included here is proving to be quite accurate.