Please help set up excel solver for this problem. EC Problem #2 A farm consists

Question

Please help set up excel solver for this problem.

EC Problem #2 A farm consists of 600 acres of land, of which 500 acres will be planted with corn, soybeans, and wheat, according to these conditions: a.) At least half of the planted acreage should be in corn. b.) No more than 200 acres should be soybeans. c.) The ratio of corn to wheat planted should be 2:1. It costs $20 an acre to plant corn, $15 an acre to plant soybeans, and $12 an acre to plant wheat. Formulate this problem as an LP model that will minimize planting cost while achieving the specified conditions.

Explanation / Answer

In the solver target cell, D6 is selected correctlt, but we need to minimize the total amount spent for this optimization model. HEnce, select Min in the "Equal To: " section just below the target cell.

In the changing cell: select the yellow cells, C3, C4, and C5. (as we need to find out the amount planted for each crop for the optimum result)

then come to constraints: press the 'Add' tab next to the 'Subject to the constraints' box. in the popup that opens on the left hand side space you will add the LHS of your constraints, from the middle drop down menu, you will select the appropriate ineuality sign that has been used in your constraint, and in the right hand side space provided you will fill in the RHS of your constraint equation. When done, press add, and the similar popup will appear for another constraint. When done with all the constraints, select done.

In your sheet for your constraint 3, in one cell enter '= C3-2*C5' to calculate the value of X1-2X3, say you did this in cell C7.

Similarly, In your sheet for your constraint 4, in cell enter '=C3-C4-C5' to calculate the value of X1-X2-X3, say you did this in cell C8.

then press the Solve tab on the upper right hand side corner of the Solver popup.

then you will get another popup. select Ok.

LHS Inequality RHS Constraint 1 C6 >= 500 Constraint 2 C6 <= 600 Constraint 3 C7 = 0 Constraint 4 C8 >= 0 Constraint 5 C4 <= 200 Constraint 6 C3,C4,C5 >= 0

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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