Question

please solve it don't right the steps becasue i don't know how to do it that my second time send it. Thanks in advance

Instructions A company produces two products made from aluminum and copper. The table below gives the unit requirements, the unit production man-hours required, the unit profit and the availability of the resources (in tons). Aluminum Copper Man-hours Unit Profit Product 1 2 3 4 30 Product 2 1 1 3 50 Available 20 16 44 1. What are decision variables? 2. What is the optimal solution? Please use Excel solver 3. Within what range for the profit on product 2 will the solution in (a) remain optimal? 4. Suppose that the unit profits on Product 1 changed from 30 to 55. Would the optimal solution change? 3. Explain the meaning of the "Shadow Price column Given the optimal solution, what does shadow price mean? What is the increase in the value of the objective function for an extra unit of aluminum! Please save your answer into Word and Excel files: then upload them into this Dropbox.

Answer 1

Please give positive rating your feedback is valuable to me.

In case , any problem please leave a comment.

Answer-

1) Decision varaiables are the quanitiy of products 1 and 2 that we should produce 2) Optimal solution is that we need to produce 14 units of product 2 and zero units of Product 1 with a maximumprofit of 733 3) Refer to Image-4, and the row 10. Look for allowable Increase and decrease column The min value of unit profit on product 2 without affecting the optimal solution would be 50 - 27.5 = 22.5 The max value of unit profit on product 2 without affecting the optimal solution would be Infinite Hence range of values is 22.5 to Infinite 4) No, as the allowable increase for unit profit on product 1 is 36.67. Hence unit profit of Product 1 can change to a maximum of 30 + 36.67 = 66.67 without the optimal solution getting changed 5) Shadow price is the change in the optimal value of the objective function obtained by relaxing the constraint. In the solution, we see that man-hours has a shadow price of 16.67. It means that if we change the availability of man-hours from 44 to 45 or 43, the optimal profit would increase or decrease by 16.67 respectively. For man-hours availability = 45 Optimal profit would be 733.33+16.67 = 750 Aluminium's shadow price is zero. Hence any increase in it's value won't change the objective function

Calib 11 -A A 2 Wrap Text General 14 X Cut Copy Format Painter Cliaboard Date BIU $ -% -%% Insert Dell Merge & Center Picker Conditional formal as Cell Formatting Table Styles Styles Font Alignment 15 Number Dalt Ce X ✓ f A B c D E 71 G H 1 к L M N o P N Aluminium Copper Man hours Unit Profit Product 1 Product 2 Available 1 20 3 1 16 4 3 44 30 50 Quantity to be produced 1 1 Objective function, Max 80 Constraints Aluminum used Copper used Man hours used 3 4 7

Name Insert Function CES in Formula Manager Create from Selection Defined Names AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference Trig Functions Function Library f Trace Dependents A Error Checking FRemove Arrows Evaluate Formula Formula Auditing Watch Window Calculation Options C 12 A B с E F G 1 2 Product 1 Product 2 Available 3 2 1 20 16 4 Aluminiuri Copper Man hours Unit Profit 5 6 7 3 4 30 1 3 50 Quantity to be produced 0 9 10 Objective function, Max -SUMPRODUCTICB:08,C6:06) 11 Constraints 12 13 14 15 15 17 Aluminum used Copper used Man hours used -SUMPRODUCTIC3:03,C8:08) -SUMPRODUCTC4:D4,CB:08) ASUMPRODUCTIC5:05,C8:08) 18 19 20 21 22 24 25 26

From Text/CSV Recent Sources Existing Connectices Queries & Connections Properties Refresh All Edit Links Queries & Connections Flash Fill Se Consolidate Remove Duplicates Relationships Text to Columns Data Validation Manage Data Model Sort From Table Range Z a leapply File Advanced Sort & Filter What If Forecast Analysis Sheet Get & Transform Data Data Tools Forecast A B C D E Solver Rarameters X 0 P Q Set Objectives 50510 1 Product 1 2 3 Product 2 Available 1 20 1 16 3 44 50 To: Min Ovale Ot: Aluminium Copper Man hours Unit Profit 0 30 By Changing Variable Cele: SC585058 + Quantity to be produced 1 1 Subject to the Constraints: SCS14 - SE53 SCS15 - $F54 SCS16 <= SESS Add Objective Function, Max 80 Change Constraints Delete Beset All F Aluminum used Copper used Man hours used 3 4 7 LoadSave F Make Uncontrained variables Non-Negative Select Solving GRG Nonlinear Method: Options Sching Method Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear. Select the LP Simplex engine tar Inear Solver Prablems and select the Facutionary engne tor Solver problems that are non smooth Hep Salve

Cell Name $C$8 Quantity to be produced Product 1 $D$B Quantity to be produced Product 2 Final Reduced Objective Allowable Value Cost Coefficient Increase 0 -36.66666667 30 36.66666667 14.66666667 0 50 1E+30 Allowable Decrease 1E+30 27.5 5 6 Variable Cells 7 8 9 10 11 12 Constraints 13 74 15 16 17 18 79 Cell Name $C$14 Aluminum used Product 1 $C$15 Copper used Product 1 $C$16 Man hours used Product 1 Final Value 14.66666667 14.66666667 44 Shadow Price 0 0 16.66666667 Constraint R.H. Sids 20 16 44 Allowable Allowable Increase Decrease 1E+30 5.333333333 1E+30 1.333333333 4 44 2 23