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

- 101 Tips and Tutorials to Write Like a Pro - August 17, 2017
- How to Find a Technical Writing Job – Some Ideas and Resources - August 9, 2017
- BOOK REVIEW: “Design for How People Learn” by Julie Dirksen - July 10, 2017

© Ugur Akinci

## Prerequisite

This exercise build directly on the following MS Excel 2007 and 2010 exercises:

/how-to-calculate-optimal-product-mix-with-ms-excel-2007-solver/

/how-to-calculate-optimal-product-mix-with-ms-excel-2010-solver-to-maximize-profits/

You have start from one of these previous posts to make sense of this one.

## Minimum Production Quotas

In this exercise, there is a new condition, a new CONSTRAINT on maximizing profits:

The plant manager wants us to produce the following MINIMUM AMOUNTS and will not accept zero production on any of his lines because he wants to keep the workers employed on every assembly line:

**50 Shirts, Hats, and Coats; 400 Scarfs and Gloves; and 200 Gowns.** These are the minimum amounts that the manager insists the plant should produce AND maximize the overall profits.

How would you do that? Easy.

## Add New Constraints

## Step 1

You accomplish that by adding a new Minimum No. of units row to your spreadsheet. Here is the updated worksheet:

## Step 2

Display the **SOLVER PARAMETERS dialog box** (**DATA tab > Solver**) and add the following new constraints:

Then click **SOLVE** for the result:

## LOST PROFITS

As you can see, the minimum quota condition has been met for each item produced but as a result **our overall profits suffered**: they dropped from a maximum possible **$88K** to roughly **$82K**. (Compare with the PROFIT figures in http://www.hotexcel.com/2012/03/06/how-to-calculate-optimal-product-mix-with-ms-excel-2007-solver/ and http://www.hotexcel.com/2012/03/06/how-to-calculate-optimal-product-mix-with-ms-excel-2010-solver-to-maximize-profits/)

**NOTE:** This solution is the same for both 2007 and 2010 versions of MS Excel. The only thing differs is the way SOLVER PARAMETERS dialog box is designed. But all the fields and functions are exactly the same and you can apply this solution in either version of MS Excel.