I am trying to solve question 3-13 from Managerial Decision with Spreadsheets (B

Question

I am trying to solve question 3-13 from Managerial Decision with Spreadsheets (Balakrishnan, Render, and Stair). It is stated as: A brokerage firm has been tasked with investing $500,000 for a new client. The client has asked that the broker select promising stocks and bonds for investment, subject to the following quidelines

1. At least 20% in municipal bonds;

2. At least 10% each in a real estate and pharmaceutical stock;

3. At least 40% in a combination of energy and domestic automobile stocks, with each accounting for at least 15%;

4. No more than 50% of the total amount invested in energy and automobile stocks in a combination of real estate and pharmaceutical company stock.

Subject to these contraints, the client's goal is to maximize projected return on investments. The broker has prepared a list of high-quality stocks and bonds and their corresponding rates of return, as shown in the following table:

5.3%

8.8%

I have reference the textbook solution for this problem within Chegg, but, when I enter the problem in excel and use Solver, I don't come up with the correct outputs. The answer should be:

Total return=$38,708.33 and investing:

$100,000 in Miami; $191,666.67 in American Smart Car; $75,000 in GreenEarth Energy; $50,000 in Rosslyn Pharmaceuticals; and, $83,333.33 in RealCo.

How would the constraints be listed as? That is where I'm running into an issue, I believe.

Thank you in advance.

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%

Explanation / Answer

Table 1:

Table 2:

Table 3:

It is mentioned in the fourth constraint that at only 50% of the amount invested in energy and automobile stocks can be invested in the combination of pharma and real estate industry.

Let x be the further amount that is to be invested in energy and automobile stocks as a result 0.5x would be the amount that can be invested in combination of pharma and real estate industry.

Therefore x+0.5x =$100,000

1.5x =$100,000

x =66,666.67

0.5x =33,333.

As the return in American smart car is more the balance amount for that combination is invested in it, i.e., $66,667 and in the combination of Real estate and pharma as the return in real estate is more, $33,333.33 is invested in real estate shares,

Under mentioned is the final plan of investment:

Investment Annual return Minimumamount to be invested Reason Real co 10.4% $50,000.00 As it is mentioned that atleast 20% should be invested in real estate sector American smart car 8.80% $75,000.00 As it is mentioned that atleast 15% should be invested in Automobile industry Rosslyn Pharmacueticals 8.40% $50,000.00 As it is mentioned that atleast 20% should be invested in Pharmacuetical industry City of miami 5.30% $100,000.00 As it is mentioned that atleast 20% should be invested in municipal bonds Green earth energy 4.90% $75,000.00 As it is mentioned that atleast 15% should be invested in energy saving industry Total $350,000.00

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.