© Ugur Akinci
This exercise build directly on the following MS Excel 2007 and 2010 exercises:
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
You accomplish that by adding a new Minimum No. of units row to your spreadsheet. Here is the updated worksheet:
Display the SOLVER PARAMETERS dialog box (DATA tab > Solver) and add the following new constraints:
Then click SOLVE for the result:
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.