Question

A brokerage firm has been tasked with investing exactly $100,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 20% of the total investment should be in municipal bonds

2) At least 10% 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) At least 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 50% 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:

Investment	Annual rate of Return
City of Miami (Municipal) Bonds	5.3%
American Smart Car	8.8%
GreenEarth Energy	4.9%
Rosslyn Pharmaceuticals	8.4%
RealCo (real estate)	10.4%

Formulate this 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

Answer:

A	B	C	D	E	F	G	H	I	J	K
2
3		Investment	Annual rate of return
4		City of Miami (Municipal) bonds	5.30%
5		American Smart Car	8.80%
6		GreenEarth Energy	4.90%
7		Rosslyn Pharmaceuticals	8.40%
8		RealCo (real estate)	10.40%
9
10		Total Amount to invest	$100,000
11
12		Investment	Amount invested	Weight in portfolio
13		City of Miami (Municipal) bonds	$20,000.00	20.00%	=D13/SUM($D$13:$D$17)
14		American Smart Car	$30,000.00	30.00%	=D14/SUM($D$13:$D$17)
15		GreenEarth Energy	$23,333.33	23.33%
16		Rosslyn Pharmaceuticals	$16,666.66	16.67%
17		RealCo (real estate)	$10,000.00	10.00%
18
19		Total amount	$99,999.99	<=	$100,000	=D10
20
21		Wt. in municipal bond	20.00%	>=	20%
22		Wt. in real estate stock	10.00%	>=	10%
23		Wt. in domestic automobile	30.00%	>=	10%
24		Wt. in energy and pharma	40.00%	>=	40%
25		Total amount in real estate and pharma	$26,666.66	<=	26666.66	=50%*(D15+D14)
26
27		Portfolio Return	7.28%	=SUMPRODUCT(D4:D8,E13:E17)
28
29			Target Variable (Maximize Return)
30			Changing variable (Amount invested in different funds)
31			Constraints
32
33		Using the solver, amount invested in each fund can be calculated at which the return is maximum with the given constraints.
34
35		Hence,
36		a)
37		Maximum Return is	7.28%	=D27
38
39		b)
40
41		Investment	Amount Invested
42		City of Miami (Municipal) bonds	$20,000.00
43		American Smart Car	$30,000.00
44		GreenEarth Energy	$23,333.33
45		Rosslyn Pharmaceuticals	$16,666.66
46		RealCo (real estate)	$10,000.00
47

Solver Settings

Solver Parameters X Set Objective: $D$27 To: Max Min O Value of: 0 By Changing Variable Cells: $D$13:5D$17 Subject to the Constraints: SDS19 <= SFS19 $D$21:$D$24 >= $F$21:$F$24 $D$25 <= $F$25 Add Change 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 Solve Close

NOTE:: I HOPE YOUR HAPPY WITH MY ANSWER....***PLEASE SUPPORT ME WITH YOUR RATING...

***PLEASE GIVE ME "LIKE"...ITS VERY IMPORTANT FOR ME NOW....PLEASE SUPPORT ME ....THANK YOU