The Feed N Ship Ranch fattens cattle for local farmers and ships them to meat markets in Kansas City and Omaha. The owners of the ranch seek to determine the amounts of cattle feed to buy so that minimum nutritional standards are satisfied and at the same time total feed costs are minimized. The feed mix can be made up of the three grains that contain the following ingredients per pound of feed:

FEED (OZ.)

INGREDIENT STOCK X STOCK Y STOCK Z

A 3 2 4

B 2 3 1

C 1 0 2

D 6 8 4

The cost per pound of stocks X, Y, and Z is $2, $4, and $2.50, respectively. The minimum requirement per cow per month is 4 pounds of ingredient A, 5 pounds of ingredient B, 1 pound of ingredient C, and 8 pounds of ingredient D.

The ranch faces one additional restriction: it can obtain only 500 pounds of stock Z per month from the feed supplier, regardless of its need. Because there are usually 100 cows at the Feed N Ship Ranch at any given time, this means that no more than 5 pounds of stock Z can be counted on for use in the feed of each cow per month.

Detailed requirement:

Define the decision variables necessary for the LP formulation as follows:

X = pounds of stock X used

Y = pounds of stock Y used

Z = pounds of stock Z used

(Hint: The numbers in the table are the ounces of each ingredient contained by 1 pound of each type of feed. For example, the first number 3 means that every pound of stock X contains 3 ounces of ingredient A. Therefore, the minimum requirements for each ingredient should be converted to ounces. For example, total ingredient A must be at least 4lb * (16oz/lb) = 64oz.)

(a) Formulate appropriately as an LP to minimize the cost. Provide the algebraic formulation in your submission. Must be neatly presented.

(b) Input and solve the formulation using Excel. All constraints must be presented in the spreadsheet (i.e., not only in Solver). After running Solver, generate a sensitivity report.

(c) Answer the following questions in the Sensitivity Report worksheet. DO NOT modify or rerun the model. You must explain all the steps on how you answered the question based on the output of the one standard LP output which can include Excels sensitivity report. If you feel that it is not possible to answer the question with a single output, explain why this is the case.

i) In the optimal strategy, how many pounds of stock X, Y, Z will be purchased, respectively? What is the minimum cost for the ranch?

ii) What would be the optimal solution if the cost of stock X increases to $2.5? What would be the minimum total cost with such an increase? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.

iii) What would be the optimal solution if the cost of stock Z decreases to 80 cents? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.

iv) What will be the total cost if only 80 ounces of ingredient A are required? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication

v) What will be the total cost if 90 ounces of ingredient B are required? Must explain how you arrived to your answer based on the given output. Do not reformulate the LP to find out the implication.