Finance Discipline Group
UTS Business School
25721 Investment Management
Assignment Part I – Worth 15%
Spring 2020
1. The assignment is to be completed individually.
2. Due date: A softcopy of the assignment must be submitted online on Canvas by 5.00pm
Friday 4th September 2020. Late submissions will not be accepted.
3. Assignments will only be marked if you attach the cover sheet (available on Canvas) to the
front of your Assignment and write your student number, name and sign the cover sheet.
Signing the cover sheet indicates that “I have carefully read, understood, and have taken into
account all the requirements and guidelines for this assessment. I affirm that this assignment
is my own work; that I have not paid a third party to write this assignment; that it has not been
previously submitted for assessment; and that I have acknowledged all sources used fully and
accurately according to requirements. I am fully aware that failure to comply with these
requirements is a form of cheating and could result in disciplinary action.”
4. If any parts of your assignment are found to not be your own work or contain sentences that
are identical or similar to those in assignments submitted by other students; on a solution or
feedback sheet provided to students in a previous semester; or copied from a source and not
correctly referenced, a breach of Student Rule 16.2.1(1)
http://www.gsu.uts.edu.au/rules/student/section-16.html) will have occurred. This breach will
be reported to the University as Academic Misconduct and you will receive a mark of zero for
the assignment.
5. The lecture material and textbook provide examples and discussion on the topics covered by
the questions in the Assignment.
Required Format:
The assignment computations are to be done in EXCEL, but the solutions may be pasted into Word
and formatted for submission. You should provide explanations and discussion to your work
and answers. The final report, including all text, tables and figures should be printed out on A4
paper with a minimum font size of 12. The final report, excluding the cover sheet and pages with
graphs/diagrams, should not exceed 10 pages in length.
Help:
Post questions on “Discussions” in Canvas. Note that email is not an efficient way for asking
questions about the assignment. You can also arrange a Zoom consultation with your tutor and/or
the subject coordinator.
Subject Coordinator: Tiffany Hutcheson
2
In this assignment you will look at asset return and risk, mean-variance optimisation and
mispricing. This will be done using data in an EXCEL worksheet called
AssignmentPart1Data_2020Spring.xlsx.
Data Description
The EXCEL worksheet contains monthly closing prices from July 2010 to July 2020 for stocks
in eight Australian companies, the ASX200 and the Reserve Bank of Australia’s (RBA) cash
rate target. Note: The cash rate is expressed as a percentage per annum (p.a.). The simple
monthly cash rate can be estimated by dividing the annual rate by 12.
EXCEL Calculations
You should complete the Lecture 1: Exercises on EXCEL and go through the calculations in
Lecture 2: Weights Optimum Portfolio before beginning work on the Assignment. This will
give you a basic understanding how to use EXCEL to do calculations.
To assist you doing some of your calculations you might like to go to EXCEL “Options” and
include the “Add-ins”, “Solver” and “Analysis Tool Pak”.
3
Question 1 (Asset returns and risk) – 5 marks)
Extreme volatility and falling share prices have been experienced in share markets across the
globe since late February. This is a dramatic change from the rising share prices that occurred
over the twelve months prior to February. Rooster Wealthy Investors Trust provides you with
share prices for eight (8) companies listed on the Australian Securities Exchange (ASX). They
ask you to write up an analysis of these companies. In the analysis you must:
(a) Briefly explain, in your own words, whether or not it would have been wise to trade on
the ASX since the start of 2020. Note: Australian newspapers have regularly printed
articles on this. (worth 1 mark)
(b) Identify the eight (8) companies in the EXCEL spreadsheet and in a table list their ASX
industry group and describe, in your own words, two of their main products or services.
The description for each company will require at least two sentences. (worth 1 mark)
(c) Graph the share prices of the companies and provide your own opinion, based on these
graphs, whether it would be best to buy or short sell each of the companies. (worth 1
mark)
(d) Estimate the monthly average continuous returns and sample standard deviations of the
monthly continuous returns for each of the companies and the ASX200 from July 2010
to July 2020. Summarise your estimations in a simple labelled table. (worth 0.5 mark)
(e) Estimate the sample correlation based on the monthly continuous returns for each pair
of the eight (8) companies. Summarise your estimations in a simple labelled table and
identify the pair that has the strongest relationship and the pair that has the weakest
relationship. (worth 0.5 mark)
(f) Based on your estimations in (d) and (e) select four (4) companies that you consider
would be the best to create a portfolio with. Provide a brief explanation of your decision.
(worth 1 mark)
4
Question 2 (Mean-variance optimization – 5 marks)
The investors in Rooster Wealthy Investors Trust have a risk aversion factor of A = 4. Rooster
Wealthy Investors Trust asks you to analyse a portfolio that consists of two risky assets and a
risk-free asset. In the analysis you are required to
(a) Explain what a risk aversion factor of A = 4 implies about the investors who buy units
in Rooster Wealthy Investors Trust. (worth 1 mark)
(b) The return and risk profile of investors in Rooster Wealthy Investors Trust can be
described by the quadratic utility function covered in Lecture 2. Rank the eight
companies from the one most preferred by the investors to the least preferred. Show
and explain why the companies are ranked in this order. (worth 1 mark)
𝑼𝑼𝒊𝒊(𝑬𝑬(𝒓𝒓),𝝈𝝈) = 𝑬𝑬(𝒓𝒓) − 𝟏𝟏
𝟐𝟐 𝑨𝑨𝒊𝒊𝝈𝝈𝟐𝟐
(c) Construct a portfolio containing the two (2) companies that have the strongest
relationship and a risk-free security. Use your estimations in Question 1 to calculate the
percentage of funds that would be invested in each of the two companies and the riskfree asset. Assume the return on the risk-free security is the average cash rate. Do your
calculations using trial and error (in a systematic way) in EXCEL and by using
EXCEL’s Solver.
i. List the percentage of wealth invested in each of the two companies and the
risk-free asset and the return and risk of the two (2) companies; the risk-free
interest rate security; the optimal risky asset portfolio and the optimal total
portfolio (worth 0.5 marks)
ii. In a simple labelled table list the return, risk and utility of ten of the risky asset
portfolios that lie near the optimal risky asset portfolio. Explain, in your own
words, why these ten (10) portfolios are not the optimum risky asset portfolio
(worth 1 marks)
iii. In a fully labelled mean-standard deviation diagram show the positions of the
optimal total portfolio, the optimum risky asset portfolio, the risk-free interest
rate security, the efficient frontier and capital allocation line. The diagram can
be hand drawn. (worth 1 mark)
(d) Provide a recommendation, in your own words, to Rooster Wealthy Investors Trust on
whether it would be better to invest in the optimum total portfolio; an ASX200 managed
fund; or a risk-free interest rate security. (worth 0.5 marks)
5
Question 3 (Asset Pricing Theory – 5 marks)
Rooster Wealthy Investors Trust asks you analyse whether any of the eight (8) companies are
mispriced. You decide to use the security market line (SML) to test for mispricing. The
equation below summarises the single index model (SIM).
𝑅𝑅𝑖𝑖𝑖𝑖 = 𝛼𝛼𝑖𝑖 + 𝛽𝛽𝑖𝑖𝑖𝑖𝑅𝑅𝑀𝑀𝑀𝑀 + 𝜀𝜀𝑖𝑖𝑖𝑖
𝑅𝑅𝑖𝑖𝑖𝑖 is the excess monthly return of stock i above the cash rate
𝑅𝑅𝑀𝑀𝑀𝑀 the excess monthly return of the ASX200.
(a) In your own words explain how the SML can be used to test for mispricing and one
weakness of using the SML to do this test. (worth 1 mark)
(b) Use the excess monthly share returns for the companies from July 2015 to July 2020 to
estimate their 𝛼𝛼𝑖𝑖 and 𝛽𝛽𝑖𝑖. In a simple labelled table list 𝛼𝛼𝑖𝑖 and 𝛽𝛽𝑖𝑖 for each of the
companies in alphabetical order of the company names. Note: The calculations that you
will be using are discussed in Lectures 3 and 4. The calculations can easily be done in
EXCEL using formula functions for “intercept” and “slope” or from a regression in
“Data Analysis” (the “Y-range” is the dependent variable, the column of excess stock
return, and “X-range” is the independent variable, the column of excess ASX200
returns). (worth 0.25 marks)
(c) Under the assumptions of the SIM and using your estimations in (b), calculate the
expected excess returns for each of the eight companies. List these in a simple labelled
table. (worth 0.25 marks)
(d) You run the regression equation below to test whether any of the companies is
mispriced:
𝑅𝑅�𝑖𝑖 = 𝛼𝛼 + 𝛾𝛾𝛽𝛽̂
𝑖𝑖 + 𝜀𝜀𝑖𝑖
𝑅𝑅�𝑖𝑖 is the estimated expected excess return of each company
𝛽𝛽̂
𝑖𝑖is the beta coefficient of each company
Explain what your estimates for 𝛼𝛼 and 𝛾𝛾 as well as their p values and t statistics say
about the relationship between 𝑅𝑅�𝑖𝑖 and 𝛽𝛽̂
𝑖𝑖. Note: The regression can be done in “Data
Analysis” (the “Y-range” is the dependent variable, the column of expected excess
stock returns and “X-range” is the independent variable, the column of betas). (worth
1.5 marks)
(e) Indicate whether any of the companies is mispriced by doing the following:
i. Plot the best-fitted line for the estimated 𝑅𝑅𝑖𝑖’s and 𝛽𝛽𝑖𝑖‘s and show the position of
the companies on the graph. Note: This can be generated by selecting the “Line
Fit Plots” option when doing the regression and in Design adding the Chart
Element “Trendline (linear)” (worth 0.5 marks)
ii. Calculate and explain the difference between the expected excess returns
calculated in (c) and the 𝑅𝑅�𝑖𝑖 estimated using the 𝛼𝛼 and 𝛾𝛾 in (d) and beta in (b).
(worth 0.5 mark)
(f) Identify whether the companies should be purchased or short sold. Explain why these
trades would be profitable. (worth 1 mark)