Component Feed Alloys 1 60 20 200 0 2006020 20 60 Price/kg $46,00 $38.00 $33,00 $25.00 Suppose that you wanted to produce alloys with a composition of 40 wt% A, an equal amount of B and C, and an unspecified wt% of D. Using the Excel Solver Add-In and plotting capability: a) Determine the range of product compositions (XA, XB, X, Xp) within which this specification is possible. Hint: Explore values of Xp ranging from 0 to 0.30 in steps of 0.01. b) Zooming in on the specific range of allowable compositions, generate solutions (i.e., alloy flow rates) for values of xp in steps of 0.002. Plot the flow rates of the four alloys versus Xp over this range. c) The product alloy can be sold for a price that depends on Xp as follows: Product Alloy Price ($) = -4133.3.X3 +1893. XB - 176.6 A first estimate of profit/loss can be made by the following equation: Profit/Loss ($) = Profit from Selling Product Alloy - Cost of Feed Alloys Plot this profit/loss as a function of Xs to determine the subset of product compositions will allow you to tum a profit and the alloy composition will yield the maximum profit. What other factors besides the raw materials cost and sale of the product will affect the profitability of this process? Component Feed Alloys 1 60 20 200 0 2006020 20 60 Price/kg $46,00 $38.00 $33,00 $25.00 Suppose that you wanted to produce alloys with a composition of 40 wt% A, an equal amount of B and C, and an unspecified wt% of D. Using the Excel Solver Add-In and plotting capability: a) Determine the range of product compositions (XA, XB, X, Xp) within which this specification is possible. Hint: Explore values of Xp ranging from 0 to 0.30 in steps of 0.01. b) Zooming in on the specific range of allowable compositions, generate solutions (i.e., alloy flow rates) for values of xp in steps of 0.002. Plot the flow rates of the four alloys versus Xp over this range. c) The product alloy can be sold for a price that depends on Xp as follows: Product Alloy Price ($) = -4133.3.X3 +1893. XB - 176.6 A first estimate of profit/loss can be made by the following equation: Profit/Loss ($) = Profit from Selling Product Alloy - Cost of Feed Alloys Plot this profit/loss as a function of Xs to determine the subset of product compositions will allow you to tum a profit and the alloy composition will yield the maximum profit. What other factors besides the raw materials cost and sale of the product will affect the profitability of this process
