Question

CC

A brokerage firm has been tasked with investing exactly $300,000 for a new client. The client has asked that the broker select promising stocks and bonds for investing subject to the following guidelines 1) At least 25% of the total investment should be in municipal bonds 2) No more than 20 % of the total investment should be in real estate stock 3) At least 10% of the total investment should be in domestic automobile stock 4) No more than 40% of the total investment should be in combination of the amount invested in energy and pharmaceutical stocks. 5) The amount invested in a combination of real estate and pharmaceutical stock should be no more than 70% of the total amount that is invested in a combination of energy and automobile stocks. Subject to these constraints, the client's goal is to maximize the projected return on investment. A list of potential investments are in the table below:

City of Miami Municipial Bonds = 4.3%

American Smart car = 6.8%

GreenEarth Energy 7.9%

Rosslyn Pharm = 8.4%

RealCo = 12.4%

Formulate as an LP problem and solve in excel.

a) What is the total return for your investment strategy?

b) How much should be invested ($) in each investment?

c) Be sure to paste your Excel solution into this answer box for full/partial credit.

Answer 1

The constraints, Objective function and the Allocation are shown below:

N K M O P J L 8 Total Investment 300000 9 10 11 12 Allocated Investments Returns 4.30% 6.80% .90% 13 Municipal Bonds 75000.0 14 American Smart Car 45000.0 GreenEarth Energy 15 87352.9 8,40% 32647.1 16 Rosslyn Pharma 12.40% 17 RealCo 60000.0 18 Total Returns 7.79%SUMPRODUCT(M13:M17,N13:N17)/SUM(N13:N17) 19 20 Constraints 21 Constraints Formula 25.0% N13 /M3 1 Bonds >25% 22 20.0%N17/M3 2 Real estate 23 <=20% 3 Auto Stock 15.0%N14/M3 >10% 24 40.0% SUM (N15:N16)/M3 4 Combination of Energy and Pharma stock 25 <=40% Combination of Real estate and Pharma 5 stock/Combination of Energy and Auto 70.0%SUM(N16: N17)/SUM(N14: N15) 300000 SUM (N13:N17) 26 <=70% 27 6 Total Invesment 300000

The constraints used in the solver are shown below:

X Solver Parameters $M$19 Set Objective: Min To: 0 Value Of: Max By Changing Variable Cells: $N$13:$N$17 Subject to the Constraints: $M$22 25% $M$23 $M$24 $M$25 <40% $M$26 <70% $M$27 Add 20% >= 10% Change 300000 Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving GRG Nonlinear Options Method: 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 Solve Close

A. The total return on the investment = 7.79%

B. The following table gives the allocation for each of the investment types:

Investments	Returns	Allocated
Municipal Bonds	4.30%	75000.0
American Smart Car	6.80%	45000.0
GreenEarth Energy	7.90%	87352.9
Rosslyn Pharma	8.40%	32647.1
RealCo	12.40%	60000.0

C. The Excel solution is pasted above.

Hope I answered your query.