Decision Models- excep spreadsheet with SOLVER
Steel Fabricators manufactures truck panels by blending ore from four different mines. Each ton of blended ore requires at least
10 pounds of element A per ton
90 pounds of element B per ton
18 pounds of element C per ton.
Each mine charges a different price, and each mine's ore provides a different blend of basic elements.
Mine
Element 1 2 3 4 Minimum
Requirement
lbs A / ton 18 6 16 4 10
lbs B / ton 80 120 75 110 90
lbs C / ton 25 12 10 15 18
$ Cost / ton 900 480 675 465
The objective is to minimize the cost of a ton of blended ore that satisfies the minimum requirements on elements A, B, and C. Answer the following independent questions.
(a) How much does a ton of the blend cost?
(b) How much element A is in the minimum cost blend? How much element B?
Justify your answers to (c), (d), and (e) by reference to the sensitivity report for your Solver solution to the above problem.
(c) If instead of requiring 10 pounds of A per ton in the blend, we needed 12, what would it cost to blend a ton of ore?
(d) How much would I have to decrease the amount of C required before the cost per blended ton drops to $576?
(e) We are not using any ore from mine 3. How much would it have to lower its price before we considered buying ore 3?