Question

Kathleen Taylor has been working for a government contractor, Summit Solutions, in Washington, DC, for over a year. She is now eligible to participate in the company’s 401(k) retirement plan. The company has provided Kathleen with the information in the table on the following page about the various funds that are available for her to invest in. International funds invest in global/overseas companies; small-cap funds invest in companies that have a market capitalization (i.e., the number of outstanding shares multiplied by the stock price per share), in general, between $300 million and $2 billion; mid-cap funds invest in companies with a market capitalization between $2 and $10 billion; and large-cap funds invest in companies over $10 billion. The Evening Star rating is developed by an independent investment analysis firm, and it rates funds based on their risk-adjusted performance over various time periods. "5" is best, "1" is worst; stocks trading close to their analysts' fair value estimates receive a "3", while stocks trading at large discounts compared to their analysts' fair value estimate receive a "4" or "5" rating. The expense ratio is the total percentage of fund assets used for operating expenses (i.e., administrative, management, advertising, etc.). Since Kathleen is young and expects to build her 401(k) plan over a long period of time, she wants to employ a relatively aggressive investment strategy. She has read investment literature that suggests a relatively aggressive plan would invest 5% to 35% in international funds, 5% to 25% in small-cap funds, 5% to 35% in mid-cap funds, 20% to 50% in large-cap funds, and 5% to 10% in bond funds. Kathleen plans to contribute $900 of her salary each month to her plan, which the company will match. She has also developed a few of her own investment guidelines: to diversify, she wants to invest in five funds, one in each investment category; she wants to achieve an average Evening Star rating of at least 3.7; she wants to invest in funds that average at least $10,000 million in size; she wants to achieve an average expense ratio for her five funds of no more than 1.10; and she wants to maximize the average 5-year return of the five funds she selects, weighted by the amount she invests in each. Develop an investment plan for Kathleen using linear programming. What would be the decision variable and constraints for this problem?

Answer 1

*****Please please please LIKE THIS ANSWER, so that I can get a small benefit, Please*****

Kathleen needs to prepare an investment plan. In this plan she needs to invest in five funds each from different investment category.

Develop an investment plan for Kathleen using linear programming model in excel sheet as shown below:

Formulate an excel sheet using given formulas in excel spreadsheet:

Figure 1: Screenshot of formulas used in spreadsheet

Figure 2: Screenshot of formulas used in spreadsheet

Figure 3: Screenshot of formulated spreadsheet

Now, click on excel “Solver” option. Put the following values in the “Solver” window:

Figure 3: Screenshot of Excel solver

Click on “Solve” option. Then, click on “OK” option. It would give the following results:

Figure 5: Screenshot of final results

Hence, it is concluded that the maximum value of average 5-year returns on the fund would be 7.272. To achieve this objective, Kathleen needs to invest in funds 4, 6, 12, 17, and 23.

Formulate and solve a linear programming model using excel spreadsheet in order to maximize the ES ratings for Kathleen, as shown below:

Figure 6: Screenshot of formulas used in spreadsheet

Figure 7: Screenshot of formulas used in spreadsheet

Figure 8: Screenshot of formulated spreadsheet

Now, click on excel “Solver” option. Put the following values in the “Solver” window:

Figure 9: Screenshot of Excel solver

Click on “Solve” option. Then, click on “OK” option. It would give the following results:

Figure 10: Screenshot of final results

Hence, it is concluded that the maximum value of ES rating would be 4.4. To achieve this objective, Kathleen needs to invest in funds 4, 6, 14, 19, and 21.

*****Please please please LIKE THIS ANSWER, so that I can get a small benefit, Please*****