Question

10. An investment advisor at Shore Financial Services wants to develop a model that can be used to allocate investment funds among four alternatives: stocks, bonds, mutual funds, and cash. For the coming investment period, the company developed estimates of the annual rate of return and the associated risk for each alternative. Risk is measured using an index between 0 and 1, with higher risk values denoting more volatility and thus more uncertainty. Annual Rate of Return (%) Risk Investment Stocks 10 0.8 Bonds 3 0.2 Mutual funds 4 0.3 Cash 0.0 Because cash is held in a money market fund, the annual return is lower, but it carries essen- tially no risk. The objective is to determine the portion of funds allocated to each investment alternative in order to maximize the total annual return for the portfolio subject to the risk level the client is willing to tolerate. Total risk is the sum of the risk for all investment alternatives. For instance, if 40% of a client's funds are invested in stocks, 30% in bonds, 20% in mutual funds, and 10% in cash, the total risk for the portfolio would be 0.40(0.8) + 0.30(0.2) 0.20(0.3) 0.10(0.0) = 0.44. An investment advisor will meet with each client to discuss the client's investment objectives and to determine a maximum total risk value for the client. A maximum total

Please walk me through steps to enter into excel! Thank you.

Answer 1

Answer:

(a) A linear program model is a mathematical model with a linear objective function, set of linear constraints, and nonnegative variables. Let S be the proportion of funds invested in stocks, B be the proportion of funds invested in bonds, M be the proportion of funds invested in mutual funds, and C be the proportion of funds invested in cash a Max 0.1S+0.03B+0.04M+0.01C s.t. S+B+MC= 1 0.8S+0.2B+0.3M< 0.4 Maximum Risk S0.75 -B+M20 C20.1 CS0.3 S, B, M, C20 Now, formulate the linear programming problem in excel, as shown below: P A C D F 1 S C 2 Allocation of fund 3 Rate of Return 0.01 0.1 0.03 0.04 4 Risk 0.3 0 0.8 0.2 |=((B2*B3)+(C2*C3)+(D2*D3)+( E3* E2)) 5 Total return 6 7 8 Constraints -SUM(B2:E2) 9 Total Investment 1 |=(B4*B2)+(C4*C2)+(D4*D2)+(E4*E2)) 10 Maximum Risk 0.4 11 Stock investment 0.75 -B2 12 Mutual fund and Bond composition -D2 13 Cash 14 Cash -C2 0.1 -E2 EE2 0.3

Now, set up solver as shown below Solver Parameters Set Objective To: OMip Oyalue Of Max B Changing Variable Cells SB52SES Subject to the Constraints $B59 $D$9 $B510SDS10 $B$11S0S11 $B$12S0S12 SB$13S0S13 SBS8 SDS8 dd Change Delete Beset All Load/Save Make Unconstrained Variables Non-Negative Simplex LP Sglect a Solving Method 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 Cigse X

Click on "Solve", to generate the results as shown below A D E M C 2 Allocation of fund 3 Rate of Return 4 Risk 5 Total return 40.9% 14.5% 4% 14.5% 30.0 % 10% 3% 1% 0.2 0.8 0.3 5.41% 6 7 Constraints 1.00 8 Total Investment 1.00 9 Maximum Risk 10 Stock investment 11 Mutual fund and Bond composition 12 Cash 13 Cash 0.40 = 0.40 0.41 0.75 0.15 0.30 0.30 0.15 0.10 0.30 Optimal Allocation: Mutual Fund Stock Bond Cash 40.9% 30.0% 14.5% 14.5% Total Return: 5.41% Total risk:0.4 or 40%

(b) The optimal allocation of investment funds for a more conservative client with risk 0.18 B D A C 1 S B M C 2 Allocation of fund 0.9% 34.5% 34.5% 30.0% 3 Rate of Return 10% 3% 4% 1% 4 Risk 0.8 0.2 0.3 0 5 Total return 2.81% 6 7 Constraints 8 Total Investment 1.00 1.00 9 Maximum Risk 0.18 < 0.18 10 Stock investment 0.01 0.75 11 Mutual fund and Bond composition 0.35> 0.35 0.30 12 Cash 0.10 0.30 13 Cash 0.30 Optimal Allocation: S B M C 0.9% 34.5% 34.5% 30.0% Total Return: 2.81 % Total risk:0.18 or 18%

(c) The optimal allocation of investment funds for a more aggresive client with risk 0.7 B C E 1 B M C 2 Allocation of fund 3 Rate of Return 0.0% 3% 75.0% 15.0% 10.0% 1% 10% 4% 0.2 0.3 4 Risk 0.8 5 Total return 8.20% 6 7 Constraints 8 Total Investment 9 Maximum Risk 10 Stock investment 11 Mutual fund and Bond composition 12 Cash 13 Cash 1.00 1.00 0.70 0.65 0.75= 0.15 0.75 0.00 0.10 0.10 0.10 0.30 Optimal Allocation C 15.0% 75.0% 0.0% 10.0% Total Return: 8.20% Total risk 0.65 or 65% LON

(d) No, the aggressive client will not need an advisor, as he is already investing max in stocks and is having at least 10% in cash]