A chemical manufacturing company produces three types of industrial solvents, A, B and C. The profits per thousand gallons for the three solvents are $300, $500, and $800 respectively. The production process requires blending and purification. It also requires additional labor for handling the finished products. The company has 260 hours of blending, 300 hours of purification, and 160 labor hours available.

A chemical manufacturing company produces three types of industrial solvents, A, B and C. The profits per thousand gallons for the three solvents are $300, $500, and $800 respectively. The production process requires blending and purification. It also requires additional labor for handling the finished products. The company has 260 hours of blending, 300 hours of purification, and 160 labor hours available.

nooneyouknow added on 02/25/12 at 09:21 PM (PST):

ASSIGNMENT #2 - ADDITIONAL HELP

Part 1 - identical to example in text on page 155

Part 2
Please see the attached Excel file, and note the following:

1. In cell H6, you put the formula for your result.
2. In cell H7..H9, you put the formula for your constraints
3. In cell I7..I9, you indicate your limits.

Solver Parameters (using Microsoft Excel 2007, screens may be different for other version)

1. Cell H6 is also your target cell in defining the solver parameters. For the solver parameter, choose option “maximum”, since part 2 indicates “the maximum attainable profits” OR you could choose “value of” and enter 62,000. However, you will need to remove “value of” for part 3 and use “maximum”, since you want to see the effect of changing you limits on the result variable.

2. For the “by changing”, specify your decision variables.
3. For Constraints, specify H7..H9 and use the conditional signs to compare with I7..I9.
4. For option, make sure to specify assume linear model, and assume non-negative.


Part 3, you will need to note the effect on profits by an additional hour for each resource and indicated in 3a, 3b, and 3c. For example, you will observe that in 3a, an additional hour of blending will not impact total profits; therefore, it would not be profitable for management to purchase additional blending hours. Document your observation and recommendations in Part 4 based on the question asked.