Optimization and Process Analytics Spread Sheet Modeling

1. (Chapter 3 in Edition 4-Question 2: Production problem) In PC Tech’s product mix problem,

assume there is another PC model, the VXP, that the company can produce in addition to Basics

and XPs. Each VXP requires 8 hours for assembling, 3 hours for testing, $275 for component

parts, and sells for $560. At most 50 VXPs can be sold.

[a] Modify the spreadsheet model to include this new product, and use Solver to find the

optimal product mix.

[b] You should find that the optimal solution is not integer-valued. If you round the values

in the changing cells to the nearest integers, is the resulting solution still feasible? If not,

how might you obtain a feasible solution that is at least close to optimal?

2. (Chapter 3 in Edition 4-Question 34: Environment Problem) There are 3 factories on the

Momiss River. Each emits 2 types of pollutants, labeled P1 and P2, into the river. If the

waste from each factory is processed, the pollution in the river can be reduced. It costs $1500

to process a ton of factory 1 waste, and each ton processed reduces the amount of P1 by 0:10

ton and the amount of P2 by 0:45 ton. It costs $1000 to process a ton of factory 2 waste, and

each ton processed reduces the amount of P1 by 0:20 ton and the amount of P2 by 0:25 ton. It

costs $2000 to process a ton of factory 3 waste, and each ton processed reduces the amount of

P1 by 0:40 ton and the amount of P2 by 0:30 ton. The state wants to reduce the amount of P1

in the river by at least 30 tons and the amount of P2 by at least 40 tons.

a. Use Solver to determine how to minimize the cost of reducing pollution by the desired

amounts. Are the LP assumptions (proportionality, additivity, divisibility) reasonable in

this problem?

b. Use SolverTable to investigate the effects of increases in the minimal reductions required

by the state. Specifically, see what happens to the amounts of waste processed at the three

factories and the total cost if both requirements (currently 30 and 40 tons, respectively)

are increased by the same percentage. Revise your model so that you can use SolverTable

to investigate these changes when the percentage increase varies from 10% to 100% in

increments of 10%. Do the amounts processed at the three factories and the total cost

change in a linear manner?

Optimization and Process Analytics Spread Sheet Modeling – Excel and pivot tables.