How to Calculate Optimal Product Mix with MS Excel “Solver” to Maximize Profits

To calculate optimal product mix with MS Excel “Solver” to maximize profits is easier than you thought.

Here is the situation:

Imagine you’re running a manufacturing plant, producing hats, shirts, gloves, and other similar items.

You know the following:

The number of each item you are now manufacturing.
The number of labor units you need to produce each item.
The number of raw material units you need to produce each item.
The unit price, unit cost, and unit profit for each item.
Maximum demand for each item.
Your total available labor units.
Your total available raw material units.

Here is a MS Excel 2007 worksheet that summarizes your data:

QUESTION: How would you know how many to manufacture from each item in order to maximize your profit WITHOUT going over your total labor and raw material limits?

SOLUTION: This is generally referred to as a OPTIMAL PRODUCT MIX problem and Excel solves these kinds of problems really well, especially when the solution conforms to a “linear solution” model.

Table of Contents

STEP 1

Make sure your Solver add-in module is added to the ribbon.
1) Click Excel Office Button. Select Excel Options > Add-Ins.
2) Make sure “Excel Add-Ins” is selected in the Manage drop-down list. Click Go.
3) In the Add-Ins dialog box, select the “Solver Add-In” and click OK to display the SOLVER add-in on the DATA tab of the ribbon:

STEP 2

Calculate the total labor used by entering this formula into the total labor cell (B12, in our example):

=SUMPRODUCT($B$2:$G$2,$B$3:$G$3) = 2639

This formula multiplies each cell of the Numbers Produced and Labor Required rows (arrays) with one another.

STEP 3

Calculate the total raw materials used by entering this formula into the total raw materials cell (B13, in our example):

=SUMPRODUCT($B$2:$G$2,$B$4:$G$4) = 2766.3

This formula multiplies each cell of the Numbers Produced and Raw Materials Required rows (arrays) with one another.

STEP 4

Click the Solver icon on the ribbon to display the Solver Parameters dialog box:

If you can fill in this box correctly and click the SOLVE button, you can find the solution in an instant.
Let’s do that.

Step 5

Here is how the filled-in dialog box looks like for this particular example:

SET TARGET CELL is the PROFIT CELL (G12). That’s what we are trying to MAXIMIZE. That’s the FINAL GOAL of this whole exercise.

BY CHANGING CELLS is the No. Produced row (B2:G2). That’s how we will maximize our profits — by varying the number produced from each item.

Make sure MAX option button is selected since we are trying to maximize our profit.

Yet we have TWO CONSTRAINTS:

(1) The total labor and (2) total raw material used cannot be more than what’s available. Thus the value of the cell B12 must be equal to or less than cell D12. Similarly, the value of the cell B13 must be equal to or less than cell D13.

Step 6

Click ADD button to display the ADD CONSTRAINT dialog box:

Enter the appropriate Cell Reference, the Arithmetical Relationship, and Constraint as follows:

Click Add.

(2) Also, the total number of items produced cannot exceed the total demand. If it does, you’d be wasting precious resources. Why produce items for which there is no demand?

Enter this constraint in the same way as explained above. Then click OK to go back to Solver Parameters dialog box.
Once both your constraint lines are displayed in the Subject to the Constraints list box, make sure one other thing:

Step 7

Click Options to display the SOLVER OPTIONS dialog box:

Make sure both Assume Linear Model and Assume Non-Negative check-boxes are checked. Click OK to close it.

Step 8

Back at SOLVER PARAMETERS dialog box, click the SOLVE button to calculate the OPTIMUM NUMBER OF PRODUCT-MIX that would maximize your profit.

Here is the result:

As you can see we have more than tripled our profits (from $26K to $88K !) while staying within the bounds of our labor and raw material constraints.

As you can see, MS Excel tells us to

(1) FORGET about SHIRTS, SCARFS or GLOVES,

(2) Produce ALL the COATS and GOWNS that we can, and

(3) Produce only 316 of the 356 possible HATS to realize a maximum profit of over $88K.

Isn’t that marvelous?

CAUTION: With this analysis, MS Excel cannot tell us if any other product mix could’ve generated the same amount of profit.

But we know this much for sure: with these givens, this ($88,912.67) is the ABSOLUTE MAXIMUM PROFIT we can generate.

If we need to generate more profits, we need to increase the demand, labor available, or raw materials available, or perhaps all of them or any combination thereof.

Could you have guessed that you do NOT need to produce ANY SHIRTS, SCARFS or GLOVES at all to realize a maximum profit of $88,912.67 ? Probably not. I hope now you can easily see the power of the SOLVER functionality.

VARIATION:

What if you need to make exactly $50,000 profit? What happens to your product mix then?
Easy.

In the SOLVER PARAMETERS dialog box, instead of selecting the MAX option button, select the VALUE OF option button and enter “50000” for value.

All else remains the same. Click SOLVE for the result: