FINC3017 Report 1 2021s2
FINC3017 Investments and Portfolio Management
Report 1: Asset Allocation and Portfolio Construction
Due: 4:00pm, Thursday 16th September 2021 (week 6)
Weight: 30%
You are hired as an investment consultant with the task of identifying optimal portfolios that meet various
criteria for prospective clients and explaining aspects of the portfolio construction process. You are to
prepare a professional report for your manager which addresses the investment issues for the three clients
summarised in Table 1. You also need to prepare a response to a question from each client. The common
theme for these clients is an interest in building a US equities portfolio.
Table 1: Client list and portfolio criteria
Client Investor information and portfolio criteria
Amy Zhao (A) – Risk aversion coefficient: 10
– Short-sale restriction: No access to short selling permitted
– Lending access: Can lend at the risk-free rate
– Borrowing access: Can borrow at the risk-free rate
– Client A question: I’ve heard diversification improves portfolio risk-adjusted
performance. Would my expected portfolio performance improve if I set a diversification
mandate in my portfolio that limits the maximum exposure to any single security in the
risky portfolio at 30%? Why?
Bill Agrawal (B) – Risk aversion coefficient: 8
– Short-sale access: Unrestricted
– Lending access: Can lend at the risk-free rate
– Borrowing access: Can borrow at the risk-free rate plus 50bps
– Client B question: I’ve heard that it’s always best to use the longest sample period and
most recent data, but your manager indicated that you are not using the Covid period in
your forecasts. What would happen if you did?
Cameron Richards (C) – Risk aversion coefficient: 1.5
– Short-sale access: Unrestricted on stocks with market cap > $500bn as at 30 June 2021,
otherwise not permitted.
– Client C question: I’ve heard you use some sort of optimization algorithm to determine
the portfolio recommendation. Why should I trust a “robot” when I’m paying humans to
provide financial advice?
An initial investment screen has identified a set of 20 US listed equities presented in Table 2. Your intern has
collected monthly total returns for these stocks for the period January 2015 to June 2021, as contained in the
spreadsheet ‘Report 1 2021S2 – data.xlsx’. Unless otherwise stated, risky portfolio recommendations are to
be comprised exclusively of these stocks and the data compiled by your intern.
You determine to use historical data estimate inputs of expected return, volatility and covariance for your
portfolio modeling. However, due to the impact of Covid-19 on financial markets you exclude the period
after January 2020 in your primary analysis. That is, you need to restrict your main sample to the 60-month
period from January 2015 to December 2019. You have also identified that the applicable risk-free rate for
your analysis is 2.0% p.a.
Table 2: Investment universe
Ticker Stock
Market Cap ($bn) as
at 30 June 2021
AAPL Apple Inc 2,285.5
BA Boeing Co 140.1
MCD McDonald’s Corp 172.4
PFE Pfizer Inc 219.2
WMT Walmart Inc 395.2
MGM MGM Resorts International 20.9
CAKE Cheesecake Factory Inc 2.5
RCL Royal Caribbean Group 21.7
AMZN Amazon.com Inc 1,735.0
RL Ralph Lauren Corp 5.7
YUM YUM Brands Inc 34.3
EBAY eBay Inc 47.8
GS Goldman Sachs Group Inc 129.1
NVDA Nvidia Corporation 498.5
ILMN Illumina Inc 69.1
GME Gamestop Corp 15.4
FB Facebook Inc 833.1
TSLA Tesla Inc 654.8
SAVE Spirit Airlines Inc 3.3
YELP Yelp Inc 3.0
Report structure
Your manager has requested the following structure and word limits for the report. Your task is to use the
Markowitz approach to calculate the optimal portfolio for each client incorporating their investment criteria
and prepare a report that addresses each of the items below. Where applicable, charts and tables are highly
recommended.
1. Executive summary of portfolio strategies and key issues.
(word limit: 100)
2. Calculate the expected return, volatility and correlation of the 20 stocks, presenting your numbers in
a neat table, and provide a concise written discussion of these results.
(word limit: 150, excluding numerical tables and figures)
3. Optimal portfolio recommendations for each investor. This section must clearly:
a. Identify the weight of each stock in the risky portfolios
b. Identify the net borrowing or lending position in the combined portfolio and a brief
discussion of the effect of leverage on the investment recommendation
c. Determine the annualised expected returns, standard deviation and Sharpe ratio for each
combined portfolio as well as the expected utility for each investor.
d. Discuss the differences in weights and Sharpe ratios between the different portfolio
recommendations for each investor.
e. Explain how each of the portfolio recommendations addresses the investor’s portfolio
criteria and, if applicable, specifically note any binding constraints on risky assets.
This part of the report does not need to consider the specific investor questions.
(word limit: 300, excluding numerical tables and figures)
4. Provide a response to Client A’s question.
(word limit: 250, excluding numerical tables and figures)
5. Provide a response to Client B’s question, with specific discussion relating to sensitivity of model
with and without the sample period 2020
(word limit: 250, excluding numerical tables and figures)
6. Provide a response to Client C’s question
(word limit: 250, excluding numerical tables and figures)
7. Conclude with a summary of the key recommendations and responses of your report.
(word limit: 150)
Further requirements
Assume that investor utility can be described by the function U = E(R) – ½As2. Your report will need to
present the weights for each portfolio you calculate as well as the expected returns, standard deviation and
Sharpe ratio for each portfolio. It is recommended you use the ‘MMULT’ function and Solver package in
Excel. If using Solver, please set the initial weights to be equal weights when conducting each optimisation.
Address the requirements of each question clearly. In preparing your report, it is recommended you consider
how charts, tables, subheadings and dot points can be used enhance the way you present your findings. The
data provided in this report is adapted from real stock market data and has been customised for the purposes
of this assignment. You should only use the data provided to you in completing this report (you are not
required to gather any additional data). Further, ignore any potential transaction costs, fees and taxes in
determining your responses.
Marks will be awarded for correct quantitative analysis, the clarity of your discussion, the structure of your
report and how you present your findings. Please use graphs and/or tables to support your discussion but do
not include the raw data in your written report. Please use minimum 11pt font with 2cm margins and include
all references, if required, in a bibliography. Faculty and/or university referencing requirements must be
followed and academic honesty policies apply. References do not count towards word limit.
Written reports must be submitted via the Turnitin link labelled ‘Report 1’ as a word of pdf document. You
also need to submit your workings. Workings will not be directly graded. Submit your workings as an Excel
spreadsheet via the ‘Report 1 – Supporting workings’ link in Canvas (or code if using an alternative
optimization package). Please ensure you have submitted both your report and supporting working and
that you document your submission through Turnitin (confirmation email/screenshot) to avoid late
submission penalty issues.
Some helpful tips
• [Sharpe ratio(Annual)] = sqrt(12) * [Sharpe ratio(Monthly)]
• [Expected return(Annual)] = 12 * [Expected return(Monthly)]
• [Standard deviation(Annual)] = sqrt(12) * [Standard deviation(monthly)]
• bps = Basis points = 1/100th of 1%, i.e. 0.01%. For example, 5% plus 1bps = 5.01%