Question

NEVIEW View Calibri (Body) 119 A- A A B Tube X, X A A E E + + Share A Styles Styles A E E Pane Decision 2: Financial - Assets Investment Richelieu Specialty Paints has been quite successful and has $1,500,000 cash to be invested. Amanda and her management team have developed four potential investments in addition to leaving the cash in a low interest deposit. The four new opportunities are: 1. Purchase some shares of a major supplier (30% risk of total loss, 15% potential return) 2. Invest in the research and development of a new product (60% risk of total loss, 50% potential return) 3. Buy a selection of corporate bonds (20% risk of total loss, 8% potential return) 4. Buy a selection of government bonds (2% risk of total loss, 2.5% potential return) Leaving the money in a low interest deposit is virtually riskless and has a 0.5% potential return. Richelieu's father had provided Amanda with some guidelines for Investment, aimed at diversification and risk management. These are: 1. Invest a maximum of 40% of the total cash in any one of the four new opportunities to ensure some risk control. 2. Invest at least $100,000 in each of the four new opportunities to ensure some diversity 3. Limit the total potential loss, on average, to $300,000, based on the risk factors indicated. 4. Ensure the highest return possible. Recommend the appropriate mix of the four investments and note how much money, if any, should be left in the low interest deposit. Also, how would this mix change, and what would happen to potential return, If Amanda decided to take a more conservative approach and limit the total potential loss to $150,000. 1205 Page 3 of 6 1748 Words OK O Focus

Answer 1

We will have to solve this question using a linear program. We need to set this up in excel solver.

Please see the snapshot below of the solver model. Cells in blue contain the excel formula in the adjacent cell. On the right hand side, you see the solver window:

Solver Parameters AM AN AO AP Set Objective: SAMSS Potential return 15% 50% To: O Max Min Amount 1 Instrument Invested Risk of total loss 2 Shares of major supplier 3 R&D of new product 4 Selection of corporate bonds 5 Selection of government bonds 6 Low interest deposit O Value Of 30% 60% 20% 2% 0% 8% By Changing Variable Cells: SAM$2:$AM$6 2.50% 0.50% 8 Objective function O'=SUMPRODUCT(AM2:AM6,A02:406) Add Change Subject to the Constraints: $M$11 <= $AO$11 $A $14 <= $40$14 SAM$2 <= SAOS12 $A$2 >= $A0$13 SAMB <= $40$12 SAM$3 >= SAOS13 SAMS <= $A012 $A $4 >= $AO$13 $A$5 <= $40512 $A$5 >= 5A0513 O 'ESUM(AM2:AM6) 10 Constraints 11 Total investment 12 Maximum investment 13 Minimum investment 14 Maximum loss Delete 1,500,000 600,000 -40%*A011 100,000 300,000 Reset All O 'ESUMPRODUCT(AM2:AM6,AN2:AN6) 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 engin for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help T solve close Sheet1 Sheet? Sheet3 Sheeta Sheets Sheeth Sheet7 Sheets

And the solver solution:

AP AL AM AN АО Amount Potential 1 Instrument Invested Risk of total loss return 2 Shares of major supplier 100,000 30% 15% 3 R&D of new product 396,667 60% 50% 4 Selection of corporate bonds 100,000 20% 8% 5 Selection of government bonds 600,000 2% 2.50% 6 Low interest deposit 303,333 0.50% 7 8 Objective function 237850 'ESUMPRODUCT(AM2:AM6,AO2:A06) 0% 9 1500000 'ESUM(AM2:AM6) 10 Constraints 11 Total investment 12 Maximum investment 13 Minimum investment 14 Maximum loss 15 1,500,000 600,000 '=40%*A011 100,000 300,000 300000 'ESUMPRODUCT(AM2:AM6,AN2:AN6)

Hence, the investment mix is:

Instrument	Amount Invested
Shares of major supplier	100,000
R&D of new product	396,667
Selection of corporate bonds	100,000
Selection of government bonds	600,000

Amount left in low interest deposit = $  303,333

And the return = $  237,850

--------------------------------------

When the limit to total loss is 150,000 then the set up changes as shown below:

The only change that takes place is in orange colored cell:

AL AN AO AP Risk of total loss AM Amount 1 Instrument Invested 2 Shares of major supplier 3 R&D of new product 4 Selection of corporate bonds 5 Selection of government bonds 6 Low interest deposit 30% 60% 20% Potential return 15% 50% 8% 2.50% 0.50% 2% 0% 8 Objective function "=SUMPRODUCT(AM2:AM6,AO2:A06) 9 'ESUM(AM2:AM6) 10 Constraints 11 Total investment 12 Maximum investment 13 Minimum investment 14 Maximum loss 1,500,000 600,000 =40%*A011 100,000 150,000 'ESUMPRODUCT(AM2:AM6,AN2:AN6)

And the solution now is:

AL AM AN Risk of total loss Amount 1 Instrument Invested 2 Shares of major supplier 100,000 3 R&D of new product 146,667 4 Selection of corporate bonds 100,000 5 Selection of government bonds 600,000 6 Low interest deposit 553,333 30% 60% 20% 2% 0% AO Potential return 15% 50% 8% 2.50% 0.50% 8 Objective function 114,100 =SUMPRODUCT(AM2:AM6, AO2:A06) 1,500,000 *=SUM(AM2:AM6) 10 Constraints 11 Total investment 12 Maximum investment 13 Minimum investment 14 Maximum loss 1,500,000 600,000 "=40%*A011 100,000 150,000 150,000 "=SUMPRODUCT(AM2:AM6,AN2:AN6)

The investment mix now is:

Instrument	Amount Invested
Shares of major supplier	100,000
R&D of new product	146,667
Selection of corporate bonds	100,000
Selection of government bonds	600,000

Amount left in low interest deposit = $  553,333

And the return is = $  114,100

The potential return diminishes significantly.