PLEASE USE SOLVER IN EXCEL AND LAYOUT IN SIMPLE WAY Working with chemists at Vir

Question

PLEASE USE SOLVER IN EXCEL AND LAYOUT IN SIMPLE WAY

Working with chemists at Virginia Tech and George Washington Universities, landscape contractor Kenneth Golding blended his own fertilizer, called "Golding-Grow." It consists of four chemical compounds, C-30, C-92, D-21, and E-11. The cost per pound for each compound is indicated as follows:

Chemical Compound                Cost per pound

C-30                                                0.12

C-92                                                0.09

C-21                                                0.11

E-11                                                 0.04

The specifications for Golding-Grow are as follows:

(1) E-11 must constitute at least 15% of the blend; (2) C-92 and C-30 must together constitute at least 45% of the blend; (3) D-21 and C-92 can together constitute no more than 30% of the blend; and (4) Golding-Grow is packaged and sold in 50 pound bags.

(a) Formulate an LP problem to determine what blend of the four chemicals will allow Golding to minimize the cost of a 50 pound bag of the fertilizer.

Explanation / Answer

The Excel file portion where the Solver was used is shown below:

The 2nd column are the per pound costs of the chemical compounds.

The 3rd column are the portions of the chemical compounds taken in the mixture. Note that the total of the 3rd column is 50 pounds.

The 4th column, 1st row = sum of portions of C-92 and C-30 = 3rd column, 1st row + 3rd column, 2nd row. This should be at least 45% of 50 pounds, i.e., 22.5, which is the 1st row, 5th column. This is constraint 1.

The 4th column, 2nd row = sum of portions of C-92 and D-21 = 3rd column, 2nd row + 3rd column, 3rd row. This should be no more than 30% of 50 pounds, i.e., 15, which is the 2nd row, 5th column. This is constraint 2.

The 4th column, 3rd row = sum of portions of C-92, C-30, D-21 and E-11 (all four) = sum of 3rd column, all rows. This should be total 50 pounds, which is the 3rd row, 5th column. This is constraint 3.

The 4th column, 4th row = 3rd column, 4th row. This should be at least 7.5, which is the 4th row, 5th column. This is constraint 4.

Apart from these constraints, we also have all of the 3rd column elements > 0, because the % constituent of the mixture cannot be negative. These are constraints 5-8.

Solving this from Excel Solver, we get:

3.35 0.12 7.5 22.5 22.5 0 0.09 15 15 15 0 0.11 0 50 50 0 0.04 27.5 27.5 7.5 0

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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