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.


Figure 1: Illustration of the Manufacturing Situation.

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.


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.


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.

This entry was posted in General Mathematics. Bookmark the permalink.

3 Responses to Real-Life Equipment Optimization Problem

  1. Ronan Mandra says:


    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,


    • mathscinotes says:

      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.


  2. Ronan Mandra says:

    Looking forward to your pictures.



Leave a Reply

Your email address will not be published. Required fields are marked *