Question

please write step by step. thank you.

4-28. A farmer has subdivided his land into three plots and wants to plant three crops in each plot: corn, rice, and soy. Plot sizes, crop acreage, profit per acre, and manure needed (pounds per acre) are given in the table at the bottom of this page. The maximum acreage for each crop denotes the total acres of that crop that can be planted over all three plots. Currently there are 450,000 pounds of manure available. To ensure that the plots are used equitably, the farmer wants the same propor- tion of each plot to be under cultivation. (The pro- portion of each plot under cultivation must be the same for all three plots.) How much of each crop should be planted at each plot to maximize total profit?

Answer 1

4-28)

Let 'AC", 'AR, and 'AS' represent the decision variables of number of acres of corn, rice and soy respectively in farm A. Similarly, let 'BC", "BR', 'BS', and 'cC, 'CR', and 'CS represent the decision variables for farm B and farm C respectively. The objective function is 600(AC + BC + CC)+450(AR+ BR +CR)+300(AS + BS +CS) Subject to the constraints < 500 AC + AR+ AS BC + BR + BS < 800 CC + CR+ CS < 700 AC+ BC + CC <900 AR+ BR+ CRS 700 AS + BS + CS <1000 (200(AC + BC + CC)+300(AR+ BR+ CR) +150( AS + BS + CS) <450000 (1/500)(AC+ AR+ AS)-(1/800)(BC + BR + BS) = 0 (1/800)(BC + BR + BS)-(1/700)(CC+CR+CS) =0

The above data is entered into an excel file as shown below. CS Decision variables CR AS BR BS AC AR BC Number of acres Profit per acre $ 600.00 $ 450.00 $ 300.00$ 600.00 $ 450.00 $ 300.00| $ 600.00 $ 450.00 $ 300.00 Constraints Max plot A area Max plot B area Max plot Carea

Steps to follow in excel are, 1. Enter the data details. The objective function is written as = SUMPRODUCT(B5:J5,SBS4:SJS4), where B5 to J5 correspond to the profit per acre cell, and B4 to J4 correspond to the decision variable cells. Currently, the value of the objective function is found to be zero. 2. Enter the constraints next. For example, the Max plot A constraint is written as = SUMPRODUCT(B8:J8,SBS4:SJS4). where B8 to J8 correspond to the coefficient cells of the constraint. This gives value zero in K8 cell of the column named "LHS". Repeat the same for all the plots, Max acreage and their proportions. Now, the whole column LHS shows value zero. 3. Enter the values in the RHS column using the above constraints. 4. From the 'Data' tab, select the 'solver option. A window opens up named 'Solver parameters'. i) Enter the cell corresponding to the objective function in the Set objective' value. ii) Select the cells corresponding to the 'number of pounds' of all decision variable cells simultaneously and enter them in 'By changing variable cells'. i) Choose 'Max' in the 'To' option. In the 'Subject to the constraints' option, click on 'Add'. A pop up window appears called 'Add constraint'. In the box named Cell reference' dlick on the LHS' of the first constraint, in the second box, choose the right inequality option and in the last box named 'Constraint select the RHS' of the first constraint. Then click on add. Repeat the same procedure for the next constraint till all are entered and finally click 'ok' to close the constraint window. (In the pop up window "Add constraint", this step can also be performed more easily by just selecting the whole column of LHS, putting it in "Cell Reference" box, then, choose the same sign taken above and then select the whole RHS column and put in "constraint", and click on "Add". Also, for the proportions, take the "equality" sign instead and proceed further). iv) Make sure that the 'Make unconstrained variables non negative' box is ticked. v) Select the Solving method as 'Simplex LP'. vi) Click on 'options' button and change the 'Integer optimality %' value to 0.05. In the 'Solving limits' section, set the 'Max time (seconds)' value to 100 and 'Iterations' to 100. In 'Evolutionary and Integer constraints' constraints, set the 'Max subproblems' value to 5000 and 'Max feasible solution' to 5000. Click on ok. Get back to the 'Solver parameters' window. vii) Click on solve button. 5. A window appears after processing called Solver Results'. Select the 'Keep Solver solution option. Under Reports, click on 'Answer Then click the ok button to close screen.

The above steps will automatically change the values in LHS column, the value of the objective function and will also provide us the values of the decision variables.

The results are: For Plot A, C=0 R = 500 acres S = 0 For Plot B, C =800 acres R=0 S = 0 For Plot C, C = 100| acres R = 200 acres S= 400 acres And, Maximum profit = $ 9,75,000.00