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

- How to Number Your Documents Properly – A Document Numbering Strategy - April 24, 2017
- How to Avoid Repeating Words in a Headline - April 18, 2017
- Leveraging Multi-Function Printers With Document Imaging Software - April 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.