Question

Need help solving a linear programming problem. Can you please use step by teps in excel solver and show work so I can follow. Thank you.

Portfolio

Xi= The amount of dollars to invest in stock i

i=1=A, 2=B, 3=C, 4=D, 5=E

Max Expected Return Z=4.5X1+5.2X2+6.0X3+7.2X4+4.2X5

Subject to:

X1+X3<=50,000

X2+X5<=50,000

X4<=50,000

X1>=20,000

X3<=0.2(X1+X3)

X1+X2+X3+x4+X5<=100,000

Xi>=0 for all i

Answer 1

For constraint #5, the work is as shown below:

X3 ≤ 0.2(X1 + X3)

X3 ≤ 0.2X1 + 0.2X3

0 ≤ 0.2X1 - 0.8X3

0.2X1 - 0.8X3 ≥ 0

Download the Excel QM Addin for Excel, and open the Linear Programming Module

Input the objective function and constraint coefficients in the grey cells as shown below and then go to Solver under the Data in the top ribbon, and click on Solve.

Linear, Integer and Mixed Integer Programming 3 Signs less than or equal to equals (You need to enter an apostrophe first.) greater than or equal to Results Problem setup area 8 Dat Slack/Surplus X 4 X 2 LHS 10 Objective 11 Constraint 1 12 Constraint 2 13 Constraint 3 14 Constraint 4 15 Constraint 5 16 Constraint 17 4.5 5.2 4.2 si 7.2 RHS 610000 < constraints >COnstraints 25000 50000 25000 50000 0000 50000 2500025000 2500025000 0 20000 20000 0.2 0 0 0.8 100000 100000 100000

Solver Parameters Set Target Cell: 51510 Equal To: ⓔMaxOMín Solve Oyalue of: Close By Changing Cells: $8$19:$F$19 Subject to the Constraints: Guess Options SM$11:SM$16SN$11:SN$16 $0$11:50$16 >= SP$11:SPS16 Add garge 「Beset Delete Reset Al Help

Solver Options 100 seconds Max Time: Iterations: Precision: Tolerance: Convergence: 0.001 Assume Linear Model OK 100 Cancel 0.000001 Load Model Save Model Help ■ use Automatic Scaling □Show Iteration Results Search Assume Non-Negative Estimates Derivatives O Tangent O Quadratic Forward Newton O Central O Conjugate

The results appear as shown below:

18 Results 19 Variables 20 Objective 500050000 20000 25000 0 610000

Optimum Solution: Z = $610000; X1 = $20000, X2 = $25000, X3 = $5000, X4 = $50000, X5 = 0