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

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 an MS Excel 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.

STEP 1

Make sure your Solver add-in module is added to the ribbon.
1) Click File > 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 OBJECTIVE is the PROFIT CELL (G12). That’s what we are trying to MAXIMIZE. That’s the FINAL OBJECTIVE of this whole exercise.
BY CHANGING VARIABLE 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.
• SimplexLP method is selected from the drop-down list for solving relatively-simpler linear problems.
• Make Unconstrained Variables Non-Negative check box is checked.

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. Click Add and then OK to return to the Solver Parameters screen.

Step 7

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:

As you can see, by just manufacturing roughly 344 gowns (out of a total possible 390) and nothing else we can realize $50,000 in profits.