Spreadsheet Modeling and Decision Analysis, 7th edition Chapter 4 #16 16. Use So

Question

Spreadsheet Modeling and Decision Analysis, 7th edition

Chapter 4 #16

16. Use Solver to create a sensitivity Report for question 17 at the end of Chapter 3, and answer the following questions.

a. Is the solution degenerate?

b. Would the solution change if the price of raising was $2.80 per pound?

c. WOuld the solution change if the price of peanuts was $3.25 per pound?

d. If you could relax on of the nutritional constraints, which one would you choose? WHy?

e. CreaTe a Spider Plot summarizing the impact on total cost of varying the cost per pound of each ingredient between 90% and 110% of its base case value in 2% increments. What does this chart reveal, and what are the managerial implications?

Chapter 3 # 17

Tuckered Outfitters plans to market a custom brand of packaged trail mix. the ingredients for the trail mix will include Raisins, Grain, Choc. Chips, Peanuts, and Almonds costing, respectively, $2.50, $1.50, $2.00, $3.50, and $3.00 per pound. The vitamin, mineral, protein, and caloric content of each of the ingredients as is follows:

VITAMINS: (raisins-20, grain-10, choc.-10, peanuts-30, almonds-20 MINERALS: raisins-7, grain - 4, choc.-5, peanuts-9, almonds-3 PROTEIN: raisins-4, grain-2, choc.-1, peanuts-10, almonds-1 CALORIES: raisins-450, grain-160, choc.-500, peanuts-300, almonds-500

The company would like to identify the least costly mix of these ingredients that provides at least 40 grams of vitamins, 15 grams of minerals, 10 grams of protein, and 600 calories per 2-pound package. Additionally, they want each ingredient to account for at least 5% and no more than 50% of the weight of the package.  

Explanation / Answer

Formulate the LP first factoring in the constraints and then use solver to obtain the optimal answer.

Objective is to minimize cost of trail mix.

Let's call the quantity of raisins, grains, chocs, peanuts and almonds r, g, c, p and a respectively.

Min Z = 2.5r + 1.5g + 2c + 3.5p + 3a st

Vitamin - 20r + 10g + 10c + 30p + 20a >= 40

Mineral - 7r + 4g + 5c + 9p + 3a >= 15

Protein - 4r + 2g + 1c + 10p + 1a >= 10

Calories - 450r + 160g + 500c + 300p + 500a >= 600

Weight of package: r + g + c + p + a = 2

Propotio: Each of r/r + g + c + p + a is > .1 and < 1 pound.

a) No. Solution is not degenrate as none of the basic variables take a zero value.

   Raisin - .75, Grain, Choc, Almond - .1 and Peanut - .95

   Min Cost = $5.85

b) Allowable increase of raisin co-efficient is .2. In other words, if price of raisin is changed from 2.5 to 2.7, optimal soln won't change. Since price is inc to $2.8, solutions changes.

c) Allowable decrease of peanuts is .33 or $3.167. Changing to $3.25 leaves solution the same.

d) The dual price for mineral constraint is -.5. In a minimization problem, this means that any change in RHS would lead to increase in objective function. Hence, Mineral is the candidate to relax nutritional constraint.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.