#### Latest posts by techwriter (see all)

- How to Count the Number of Days with an Incident and Chart with Running Averages in MS Excel - October 19, 2016
- FREE Online Video Course – MS Word Power Shortcuts - October 14, 2016
- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016

© Ugur Akinci

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.

## 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:

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.