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

- Test Your Knowledge of 4 Basic Fonts – Drag & Drop - January 27, 2017
- How NOT to Design a Web Site - January 25, 2017
- Hazards of Poorly Written Technical Documentation - December 26, 2016

© 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.