This presentation is the property of its rightful owner.
1 / 11

# Lecture 9: LP in Excel (Review Assign. 1) PowerPoint PPT Presentation

Lecture 9: LP in Excel (Review Assign. 1). AGEC 352 Spring 2012 – February 13 R. Keeney. The 3 Crop Problem. Excel Setup of 3 Crop Problem (Show Formulas). Note the use of absolute cell references (e.g. \$B\$3), which allows copying of the formulas down the LHS column.

Lecture 9: LP in Excel (Review Assign. 1)

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

## Lecture 9: LP in Excel (Review Assign. 1)

AGEC 352

Spring 2012 – February 13

R. Keeney

### Excel Setup of 3 Crop Problem(Show Formulas)

Note the use of absolute cell references (e.g. \$B\$3), which allows copying of the formulas down the LHS column.

### Question 1: Why split up the labor?

• When given a question like this, you should always feel free to solve the model again as part of you answer.

### Question 1: Labor modeling

• The model without the labor split would have us overplanting corn relative to seasonal labor availability.

• That model also shows us that if we could move labor around in time or get more than 1600 hours in Jan-Apr we could earn more profits.

### Questions 2-4.

• Planting

• Corn = 275

• Wheat = 120

• Oats = 105

• Profits = 18,315

• Binding Constraints (LHS = RHS)

• Total Land(500)

• Wheat allotment (120)

• Jan-Apr labor (1600)

### Q4: Binding Constraints

• Why do we identify these?

• 1) While all of the constraints are part of the problem, only the constraints that bind are meaningful.

• They define the corner point of the solution.

• 2) Having more of the resource in a binding constraint would make us better off.

• Without solving the model you can say that removing wheat allotment will increase profits. Solve the model to know how much…

### Questions 5 and 6: More land and what is it worth?

• Solve the model again assuming we have 501 acres of total land.

• Corn = 274.75

• Wheat = 120

• Oats = 106.25

• Increase oats by 1.25 while reducing corn by 0.25

• Add’l land lets us use more of our labor

• Same constraints bind

• Profits = 18329

• 18329 – 18315 = ~14.00 is the maximum amount of rent that can be paid for land.

### Question 7: No wheat allotment

• Constraint binds so we know profits should increase

• Only reason we produce oats is the wheat allotment

### Question 8: Corn Only

• Set Corn to its max value and compare

• The value of the crop mix is that we use resources more fully…