# 1. Use Solver to create a Sensitivity Report for, and answer the following questions: a. Is the...

## Question:

1.

Use Solver to create a Sensitivity Report for, and answer the following questions:

a. Is the solution degenerate?

b. Is the solution unique?

c. Explain the signs of the reduced costs for each of the decision variables. That is, considering the optimal value of each decision variable, why does the sign of its associated reduced cost make economic sense?

d. Suppose the cost per pound for Feed 3 increased by $3. Would the optimal solution change? Would the optimal objective function value change?

e. If the company could reduce any of the nutrient requirements, which one should it choose and why?

f. If the company could increase any of the nutrient requirements, which one should it choose and why?

g. Suppose there is some uncertainty about the cost estimates for each type of feed. Prepare a Spider Plot varying the cost per pound of each feed type between plus and minus $0.25 of its stated value in $0.05 increments and tracking the impact on the optimal cost. What does this chart reveal, and what are the managerial implications?

2.

The Beef-Up Ranch feeds cattle for Midwestern farmers and delivers them to processing plants in Topeka, Kansas, and Tulsa, Oklahoma. The ranch must determine the amounts of cattle feed to buy so that various nutritional requirements are met while minimizing total feed costs. The mixture fed to the cows must contain different levels of four key nutrients and can be made by blending three different feeds. The amount of each nutrient (in ounces) found in each pound of feed is summarized as follows:

| Nutrient (in ounces) per Pound of Feed | ||

Nutrient | Feed 1 | Feed 2 | Feed 3 |

A | 3 | 2 | 4 |

B | 3 | 1 | 3 |

C | 1 | 0 | 2 |

D | 6 | 8 | 4 |

The cost per pound of feeds 1, 2, and 3 are $2.00, $2.50, and $3.00, respectively. The minimum requirement per cow each month is 4 pounds of nutrient A, 5 pounds of nutrient B, 1 pound of nutrient C, and 8 pounds of nutrient D. However, cows should not be fed more than twice the minimum requirement for any nutrient each month. (Note that there are 16 ounces in a pound.) Additionally, the ranch can only obtain 1,500 pounds of each type of feed each month. Because there are usually 100 cows at the Beef-Up Ranch at any given time, this means that no more than 15 pounds of each type of feed can be used per cow each month.

a. Formulate a linear programming problem to determine how much of each type of feed a cow should be fed each month.

b. Create a spreadsheet model for this problem, and solve it using Solver

c. What is the optimal solution?

**Related Book For**

## Spreadsheet Modeling and Decision Analysis A Practical Introduction to Business Analytics

ISBN: 978-1285418681

7th edition

Authors: Cliff Ragsdale