Question

​A recent MBA graduate, Dara, has gained control over custodial accounts that her parents had established. Currently, her money is invested in four funds, but she has identified several other funds as options for investment. She has $100,000 to invest with the following restriction:

​-Keep at least $5,000 in savings.

​-Invest at least 14% in the money market fund.

​-Invest at least 16% in international funds.

​-Keep 35% of funds in current holdings.

​-Do not allocate more than 20% of funds to any one investment except for the money market and savings account.

​-Allocate at least 30% into new investments.

	Average Return	Expenses
1. Large Cap blend	17.20%	0.93%	(current holding)
2. Small cap growth	20.40%	0.56%	(current holding)
3. Green fund	26.30%	0.70%	(current holding)
4. Growth and income	15.60%	0.92%	(current holding)
5. Multicap growth	19.80%	0.92%
6. Midcap index	22.10%	0.22%
7. Multicap core	27.90%	0.98%
8. Small cap international	35.00%	0.54%
9. Emerging international	36.10%	1.17%
10. Money market fund	4.75%	0.00%
11. Savings account	1.00%	0.00%

Develop a linear optimization model to maximize the net return.

Answer 1

I have answered the question below using excel and have attached the image below.

Please up vote for the same and thanks!!!

Do reach out in the comments for any queries

Answer:

Average return Expenses Net return Large cap blend 17.20% 0.93% (current holding) 16.27% Small cap growth 20.40% 0.56% (current holding) 19.84% Green fund 26.30% 0.70% (current holding) 25.60% Growth & income 15.60% 0.92% (current holding) 14.68% Multi-cap growth 19.80% 0.92% 18.88% Mid-cap index 22.10% 0.22% 21.88% 7 Multi-cap core 27.90% 0.98% 26.92% Small cap international 35.00% 0.54% 34.46% 9 Emerging international 36.10% 1.17% 34.93% 10 Money market fund 4.75% 0.00% 4.75% 11 Savings account 1.00% 0.00% 1.00%

Dara must decide how much to invest in each alternative that may be available. Define: S, = amount ($) invested in i investment plan, i = 1, 2,...,11

From the restrictions given, the mathematical model of the above problem is given below.

Maximize 1.16278, +1.1984S, +1.25605, +1.14685, +1.1888S, +1.21885, +1.26925, +1.34465g +1.34935, +1.04755 +1.015

S, +S+S,+SA+S,+S,+S,+SE+S,+S,+S1 = 100,000 SE+S,+S + S + S0 +5.2 30,000 S, +S,+SA+S4 = 35,000 SA+S, 216,000 5.2 14,000 S. 25,000 S, s 20,000, i=1,2,...,9

А в 1 Data 2 1 Large cap blend 3 2 Small cap growth 4 3 Green fund 5 4 Growth & income 6 5 Multi-cap growth 7 6 Mid-cap index 8 7 Multi-cap core 9 8 Small cap international 10 9 Emerging international 11 10 Money market fund 12 11 Savings account 13 Model 14 1 $ 15 2 $ Expenses Net return 0.93% (current holding) 16.27% 0.56% (current holding) 19.84% 0.70% (current holding) 25.60% 0.92% (current holding) 14.68% 0.92% 18.88% 0.22% 21.88% 0.98% 26.92% 0.54% 34.46% 1.17% 34.93% 0.00% 4.75% 0.00% 1.00% Average return 17.20% 20.40% 26.30% 15.60% 19.80% 22.10% 27.90% 35.00% 36.10% 4.75% 1.00% Return amount =B14*(1+F2) =B15*(1+F3) =B16*(1+F4) =B17*(1+F5) =B18*(1+F6) =B19*(1+F7) =B20*(1+F8) =B21*(1+F9) =B22*(1+F10) =B23*(1+F11) =B24*(1+F12) Constraints =SUM(B14:B24) =B24 =B23 =B21+B22 =B14+B15+B16+B17 =SUM(B18:B24) YYYY. $ 100,000 $ 5,000 $ 14,000 $ 16,000 $ 35,000 $ 30,000 Total amount =SUM(C14:C24)

Now click on Data Solver and fill the dialog box as shown below.

Solver Parameters E Solve Set Target Cell: $C$26 Equal To: Max Min By Changing Cells: $B$14: $B$24 value of: 0 Close ERE Guess Options Add Subject to the Constraints: $B$14: $B$22 <= 20000 $D$16 = $F$16 $D$17 >= $F$17 $D$18 >= $F$18 $D$19 >= $F$19 $D$20 = $F$20 $D$21 >= $F$21 Change Reset All Delete Help

Click on options and fill the dialog box as shown below.

Solver Options seconds Max Time: Iterations: 100 100 OK Cancel Precision: 0.000001 Load Model... Save Model... Tolerance: 5 % Convergence: 0.0001 Help Assume Linear Model Assume Non-Negative Estimates Derivatives Tangent Forward Quadratic Central Use Automatic Scaling Show Iteration Results Search Newton Conjugate

Click on OK and then click on Solve. We get the following result

B F 1 Data 2 1 Large cap blend 3 2 Small cap growth 4 3 Green fund 5 4 Growth & income 6 5 Multi-cap growth 7 6 Mid-cap index 8 7 Multi-cap core 9 8 Small cap international 10 9 Emerging international 11 10 Money market fund 12 11 Savings account 13 Model 14 1 $ 15 2 $ 15,000.00 16 3 $ 20,000.00 17 4 5 - 18 5 $ - 19 6 $ - 6,000.00 20,000.00 20,000.00 23 10 $ 14,000.00 24 11 $ 5,000.00 C D Average return Expenses Net return 17.20% 0.93% (current holding) 16.27% 20.40% 0.56% (current holding) 19.84% 26.30% 0.70% (current holding) 25.60% 15.60% 0.92% (current holding) 14.68% 19.80% 0.92% 18.88% 22.10% 0.22% 21.88% 27.90% 0.98% 26.92% 35.00% 0.54% 34.46% 36.10% 1.17% 34.93% 4.75% 0.00% 4.75% 1.00% 0.00% 1.00% Return amount $ - $ 17,976.00 Constraints $ 25,120.00 $100,000.00 $ 100,000 $ . $ 5,000.00 $ 5,000 $ - $ 14,000.00 $ 14,000 $ - $ 40,000.00 $ 16,000 $ 7,615.20 $ 35,000.00 $ 35,000 $ 26,892.00 $ 65,000.00 $ 30,000 $ 26,986.00 $ 14,665.00 $ 5,050.00 20 7 $ 26 Total amount $ 124,304.20

From the above result, Dara should invest $15,000 in Small cap growth, $20,000 in Green fund, $6000 in Multi-cap core, $20,000 in Small cap international, $20,000 in Emerging international, $14,000 in Money market fund and $5,000 in Savings account to get maximum return.