COMM3500 Assignment 2: Investment Strategy of Wayne, Inc.
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
COMM3500 Assignment 2: Investment Strategy of Wayne, Inc.
Bruce Wayne has an investment advisory firm that manages more than $100 billion worth of assets owned by Wayne family and the Wayne, Inc. partners (clients). Lucius Fox, the trusted advisor of Mr. Wayne, develops an asset allocation model that recommends a portfolio that consists of a growth stock fund, an income fund, and a money market fund. To maintain diversity in each client’s portfolio, Lucius places limits on the percentage of each portfolio that may be invested in each of the three funds. General guidelines indicate that the amount invested in the growth fund must be between 20% and 40% of the total portfolio value. Similar percentages for the other two funds stipulate that between 20% and 50% of the total portfolio value must be in the income fund and that at least 30% of the total portfolio value must be in the money market fund.
In addition, the firm attempts to assess the risk tolerance of each client and adjust the portfolio to meet the needs of the individual investor. For example, Lucius just contracted with a new client, Diana of Themyscira, who has $800,000 to invest. Based on an evaluation of the client’s risk tolerance, Lucius assigned a maximum risk index of 0.05 for the client. According to Lucius’ calculations, the firm’s risk indicators show the risk of the growth fund at 10%, the income fund at 7%, and the money market fund at 1%. An overall portfolio risk index is computed as a weighted average of the risk rating for the three funds, where the weights are the fraction of the client’s portfolio invested in each of the funds. Additionally, Lucius is currently forecasting annual yields of 18% for the growth fund, 12.5% for the income fund, and 7.5% for the money market fund.
Based on the information provided, how should the new client be advised to allocate the $800,000 among the growth, income, and money market funds? Develop a linear programming model that will provide the maximum yield for the portfolio. Solve your model in Excel. Use your model to develop a managerial report.
Each group needs to submit (1) a 4 – 6 pages (pdf) report as if it was submitted to your supervisor, meaning it not only needs to answer the questions but also to use Excel screenshots including some key formulas, reports, calculation steps with explanations wherever relevant to support your recommendations, (2) the Excel file used to answer the questions, and (3) the
Excel Model (only one file)
- Open a new Excel file for your work. Add a new worksheet each time you update/resolve the model.
- You are expected to develop the spreadsheets in a format that is similar to the lecture/lab examples (clearly show decision variables, constraints, objective, specify LHS of the constraints, etc.)
- Add a brief ‘Results’ section (you can present it just below the LP model) in your spreadsheet to show the following
- Allocation of the funds (in %),
- Yield (in %),
- Total risk of the portfolio (in %)
- Any reported result that is not supported by a clearly constructed Excel model will receive no marks! Late submissions are not accepted. Even in extraordinary circumstances, late submissions will incur a penalty.
Managerial Report (pdf, 4 – 6 pages)
- Develop the LP model. Define decision variables, develop the objective function and lay out the constraints. I recommend enumerating the constraints for future reference.
- Recommend how much of the $800,000 should be invested in each of the three funds. What is the annual yield you anticipate for the investment recommendation? (Support your answer with the screenshots of your Excel model and Sensitivity Report).
- Assume that Diana’s risk index could be increased to 0.055. How much would the yield increase (use the sensitivity report)? How would the investment recommendation change (re solve the problem)?
- Refer again to the original situation, in which Diana’s risk index was assessed to be 0.05. How would your investment recommendation and the annual yield change if the annual yield for the growth fund were revised downward to 16% or even to 14%?
- Assume that Diana expressed some concern about having too much money in the growth fund. How would the original recommendation change if the amount invested in the growth fund is not allowed to exceed the amount invested in the income fund? Should Lucius adopt this new portfolio? Why (not)?
- The asset allocation model you developed may be useful in modifying the portfolios for all of the firm’s clients whenever the anticipated yields for the three funds are periodically revised. What is your recommendation as to whether use of this model is possible?
Marks Breakdown (Excel model/sensitivity report and report combined)
Formatting of the Excel file and the report: 1 Mark
2026-04-01