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
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.
Mark,
This was a nice review for me on scenarios and the use of Excel's VBA and Solver. I believe I detected a few minor mistakes as listed below. I only spent time looking at the Basic Scenario.
- The time machine 3 spends on Product B is zero. Which can be fixed by the next two items.
- Cell C51 should be =_Rate_B*_k3B*(_T-_T1A-_T3C)
- Cell C52 should be = _Rate_C*_k3C*_T3C
Good luck with your retirement project,
Ronan
Thanks Ronan! Good eye. I made a bunch of changes before putting out the worksheet, and I should have caught that.
My project will be going on all summer. I will try to post an occasional photo so you can see how it is going. The project is actual part of a diabolical plan to draw my son, daughter-in-law, and granddaughter back to MN from MT more often.
mark
Mark,
Looking forward to your pictures.
Ronan