Problemas Resueltos Cap 3 Introduccion a La Investigacion De Operaciones

CHAPTER 3 LINEAR PROGRAMMING: FORMULATION AND APPLICATIONS SOLUTION TO SOLVED PROBLEMS
3.S1 Farm Management
Dwight and Hattie have run the family farm for over thirty years. They are currently planning the mix of crops to plant on their 120-acre farm for the upcoming season. The table below gives the labor hours and fertilizer required per acre, as well as the total expected profit per acre foreach of the potential crops under consideration. Dwight, Hattie, and their children can work at most 6,500 total hours during the upcoming season. They have 20 tons of fertilizer available. What mix of crops should be planted to maximize the family’s total profit. Formulate and solve a linear programming model in a spreadsheet. Labor Required (hours per acre) 50 60 105 Fertilizer Required (tonsper acre) 1.5 2 4 Expected Profit (per acre) $500 $600 $950

Crop Oats Wheat Corn

This is a resource-allocation problem. The activities are the planting of the three crops and the limited resources are land, labor, and fertilizer. We will therefore build a spreadsheet following the template for resource-allocation problems in Figure 3.4. Start by entering the data. The data for this problemare the labor required, fertilizer required, and expected profit for each crop (per acre). Following the template, the data in the spreadsheet would be entered as displayed below, where range names of ProfitPerAcre (C4:E4) and TotalAvailable (H7:H9) are assigned to the corresponding data cells.
B 3 4 5 6 7 8 9 Prof it (per acre) Resources Land (acres) Labor (hours) Fertilizer (tons) C Oats $500 DWheat $600 E Corn $950 F G H

Resources Used per Acre 1 1 1 50 60 105 1.5 2 4

Total Av ailable 120 6500 200

1

The decisions to be made in this problem are how many acres of each crop to plant. Therefore, we add three changing cells in C12:E12 with range name AcresPlanted. The values in AcresPlanted (C12:E12) will eventually be determined by the Solver. For now, an arbitrary value of 1is entered for each crop.
B 3 4 5 6 7 8 9 10 11 12 Prof it (per acre) Resources Land (acres) Labor (hours) Fertilizer (tons) C Oats $500 D Wheat $600 E Corn $950 F G H

Resources Used per Acre 1 1 1 50 60 105 1.5 2 4

Total Av ailable 120 6500 200

Acres Planted

1

1

1

The goal is to maximize the family’s total profit. Thus, the target cell should calculate the total profit. Inthis case, the total profit will be Total Profit = ($500)(acres of oats) + ($600)(acres of wheat) + ($950)(acres of corn) or Total Profit = SUMPRODUCT(ProfitPerAcre, AcresPlanted). This formula is entered into cell H12. With 1 acre of each crop planted, the total cost would be ($500)(1) + ($600)(1) + ($950)(1) = $2,050.
B 3 4 5 6 7 8 9 10 11 12 Prof it (per acre) Resources Land (acres) Labor(hours) Fertilizer (tons) C Oats $500 D Wheat $600 E Corn $950 F G H

Resources Used per Acre 1 1 1 50 60 105 1.5 2 4

Total Av ailable 120 6500 200 Total Prof it $2,050

Acres Planted

1

1

1

11 12

H Total Prof it =SUMPRODUCT(Prof itPerAcre,AcresPlanted)

2

The functional constraints in this problem involve the limited resources of land, labor, and fertilizer. Given theAcresPlanted (the changing cells in C12:E12), we calculate the total resources used in TotalUsed (cells F7:F9). For land, this will be =SUMPRODUCT(C7:E7, AcresPlanted). Using a range name or an absolute reference for the acres planted, this formula can be copied into cells F8:F9 to calculate the amount of labor and fertilizer used. The total resources used must be = Minimum Requirement 13 1.5 45 TotalCost $0.64

Unit Cost

Niacin Thiamin Vitamin C Quantity (per prisoner)

Nutritional Contents (mg) 3.2 4.9 0.8 1.12 1.3 0.19 32 0 93 0 2.574 0.484

Range Name MinimumRequirement NutritionalContents Quantity TotalCost TotalNutrients UnitCost

Cells H9:H11 C9:E11 C13:E13 H14 F9:F11 C5:E5

7 8 9 10 11

F Total Nutrients =SUMPRODUCT(C9:E9,Quantity ) =SUMPRODUCT(C10:E10,Quantity )...