Question

Chewy’s produces a low-calorie dog food for overweight dogs. This product is made from beef products and grain. Each pound of beef costs $0.90, and each pound of grain costs $0.60. A pound of the dog food must contain at least 9 units of Vitamin 1 and 10 units of Vitamin 2. A pound of beef contains 10 units of Vitamin 1 and 12 units of Vitamin 2. A pound of grain contains 6 units of Vitamin 1 and 9 units of Vitamin 2. Formulate this as an LP problem to minimize the cost of the dog food. How many pounds of beef and grain should be included in each pound of dog food? What is the cost and vitamin content of the final product?

Please show me how to put the constraints for this into excel QM, if you're not going to show how to do that, please do not answer!

Answer 1

Answer: ---- Date: ----30/5/2019

Let the quantity of beef be Xi and Grain be X2 Objective function is Zmin- 0.9 X1+0.6 X2 Subject to constraints, Vitamin A-10 Xi+6 X2 Vitamin D-12 Xi+9 X2 Where Xı, X220 Now solve the equation using excel f SUMPRODUCT(C4:D4,C5:D5) F5 2 X1 Cost 0.9 0.6 0 10 Vitamin A 0 Vitamin D 12 0 10> 10

The cell F5 contains formula for solver parameters SUMPRODUCT(C8:D8,C4:D4) E8 2 X1 X2 4 0.9 Cost 0.6 C) 10 Vitamin A C) 10> Vitamin D 12 C) 10 Now use solver to calculate optimum value

Now use solver to calculate optimum value Solver Parameters 3 Set Objective: SF$5 To: Max Yalue Of: Ey Changing Variable Cells: Subject to the Constraints: 59 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Simplex LP Ontions Solving Method engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Solve Close

Now, the output will be as follows 2 X1 X2 0.9 4 0.9 Cost 0.6 0.81 10 Vitamin A Vitamin D 12 10.8 10> 10 Hence the company should use 0.9 units of beef in its dog food to reduce the cost