Question

All parts A-D. For A please send picture of excel file with the answer and sensitivity report since I dont think you can send a file.

problem 2. The Bahama Nut Company sells three different half-pound bags of peanut mixes: Nuts, Mixed, and Premium Mix. These generate per-bag revenue of $1.00, $2.10, and respectively. The tables below show the makeup of each mix, the available ingredients for the next week, and the cost of each ingredient. Ingredients Peanuts Cashews Brazil Nuts Hazelnuts 100% Mixed 55% 25% 10% 10% Premium Mix 40% 20% 40% Party Nuts Pounds Available SCO Peanuts Cashew Brazil Nuts Hazelnuts 180 | 100 Cost per pound 1.5 5.35 6.25 75 180 The following shows the linear programming model to help Bahama determine how many bags of each type to produce to maximize contribution to profit. Let X,: the number of pounds of Party Nuts to produce Xa: the number of pounds of Mixed Nuts to produce X : the number of pounds of Premium Nuts to produce Therefore, the profit is 2(1)X, +2(2.1)X, +2(3.63)X, - 1.5(X, +0.55 X) - 5.35(0.25X2 +0.4 Xx) - 6.25(0.1 X2 + 0.2 X) - 7.5(0.1X2 + 0.4 X2) Maximize 0.5X, +0.6625 X2 + 0.87 X3 S.. X+ 0.55 X2 S 500 Peanuts 0.25X2 + 0.4 X3 S 180 Cashews 0.1X, +0.2 X3 S 100 Brazil Nuts 0.1X, +0.40 X, S 80 Hazelnuts X, X2, X, 20 up the above LP in Excel Solver. How many bags of each type should be produced and what is the maximal profit? Upload you excel file with the answer and sensitivity report on canvas along with your solution. b) Which constraints are binding? c) What is the shadow price for peanuts? Interpret the shadow price. ease the amount of peanuts by 1 pound and re-solve. Does the solution confirm the answer to part (c)? Give the new solution.

Answer 1

Part (a)

Answer:

The excel set up

Solver Parameters sos i Home Insert File Formulas Page Layout fic Set Objective: $C$111 C111 To O Max Min value of. By Changing Variable Cells: $C$107:5C$109 Add Subject to the Constraints: SC$113 <= $D$113 $C$114 <= $D$114 $C$115 <= $D$115 $C$116 <= $D$116 Change 107 X1 108 X2 109 X3 110 111 Objective fxn 112 113 Peanuts 114 Cashews 115 Brazil Nuts 116 Hazelnuts 117 118 119 Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help I solve Close

The answer output

E F G C A B C D 13 14 Objective Cell (Max) 15 Cell Name Original Value 16 $C$111 Objective fxn Total 0 17 Final Value 537.3333333 19 Variable Cells 20 Cell Name 21 $C$107 X1 Total $C$108 X2 Total $C$109 X3 Total Original Value 0 0 0 Final Value Integer 133.3333333 Contin 666.6666667 Contin 33.33333333 Contin 23 27 26 Constraints Cell Name 28 $C$113 Peanuts Total 29 $C$114 Cashews Total 30 $C$115 Brazil Nuts Total $C$116 Hazelnuts Total Cell Value Formula Status Slack 500 $C$113<=$D$113 Binding 180 $C$114<=$D$114 Binding 73.33333333 $C$115<=$D$115 Not Binding 26.66666667 80 $C$116<=$D$116 Binding o

Sensitivity Output:

0.17 6 Variable Cells 7 Final Reduced Objective Allowable Allowable Cell Value Cost Coefficient Increase Decrease 9 $C$107 X1 Total 133.3333333 0 0.5 0.309090909 0.284090909 $C$108 X2 Total 666.6666667 0 0.6625 0.15625 11 $C$109 X3 Total 33.33333333 0 0.87 0.68 0.25 12 13 Constraints 14 Final Shadow Constraint Allowable Allowable 15 Cell Name Value Price R.H. Side Increase Decrease $C$113 Peanuts Total 500 0.5 500 1E+30 133.3333333 17 $C$114 Cashews Total 180 1.133333333 180 20 18 $C$115 Brazil Nuts Total 73.33333333 0 1 00 1 E+30 26.66666667 19 $C$116 Hazelnuts Total 80 1.041666667 100 100 80

All the bags are half pound bags. Hence, the final answers are: Number of bags of:

• Party nuts = 2 x 133.33 = 267 (Nearest integer)
• Mixed nuts = 2 x 666.67 = 1,333
• Premium nuts = 2 x 33.33 = 67

The maximal profit = 537.33

Part (b)

Bonding constraints are: Peanuts, Cashews and Hazelnuts

Part (c)

Shadow price for peanut is 0.5

Shadow price means how much the objective function will change if there is a unit change in the availability of the variable.

Part (d)

The problem now:

х C111 Solver Parameters Set Objective: $C$111 То: o Max O Min value of. 107 X1 108 X2 109 X3 110 111 Objective fxn By Changing Variable Cells: $C$107:$C$109 112 501 Add Subject to the Constraints: $C$113 <= $D$113 $C$114 <= $D$114 $C$115 <= $D$115 $C$116 <= $D$116 113 Peanuts 114 Cashews 115 Brazil Nuts 116 Hazelnuts 117 118 100 Change Delete Reset All 119 Load/Save Make Unconstrained variables Non-Negative 121 Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. 125 126 Help solve close

And the solution now

E F G A B C D 14 Objective Cell (Max) 15 Cell Name Original Value 16 $C$111 Objective fxn Total 0 Final Value 537.8333333 19 Variable Cells Name $C$107 X1 Total $C$108 X2 Total $C$109 x3 Total Original Value 0 O 0 Final Value Integer 134.3333333 Contin 666.6666667 Contin 33.33333333 Contin 28 26 Constraints 27 Cell Name $C$113 Peanuts Total $C$114 Cashews Total $C$115 Brazil Nuts Total 31 $C$116 Hazelnuts Total 29 Cell Value Formula Status Slack 501 $C$113<=$D$113 Binding 0 180 $C$114<=$D$114 Binding 73.33333333 $C$115<=$D$115 Not Binding 26.66666667 80 $C$116<=$D$116 Binding

The objective function value has indeed increased from 537.33 to 537.83 and the increase is same as the shadow price of peanuts.

Answer 2

how to program this problem in BVA