FINS5513 Data Exercise Instructions
2023 Term 2
Group A
1. Submission deadline: The assessment links will be available on Moodle from 10:00 AM, 7th
July to 23:55 PM, 28th July, Sydney Time. You can attempt the assessment anytime within
the time window above, but the assessment will close at 23:55 PM, 28th July sharply. For
example, if you start your attempt at 11:35 PM, on 28th July, you will only have 20 mins to
finish this task. Please plan accordingly and do not leave it to the last minute. The failure of
submission due to any technical issues near the deadline will not be considered as a valid reason
for special consideration application. Any late submission without the approval of the special
consideration would incur a 5%-mark penalty per day or part thereof (including weekends)
from due date and time. The assessment will not be accepted after 5 days (120 hours) of the
original deadline unless special consideration has been approved.
2. Submission format:
a. Enter your solutions to each question via “Data exercise questionnaire” link, AND submit
your Excel spreadsheet via “Data exercise Excel submission” link. Both links will be
available under the Moodle section “iLab: Data exercise”.
i. if you only submit the Excel but do not enter the solutions into Moodle questionnaire,
you will not receive any marks for this assessment.
ii. if you only answer the questionnaire but do not submit the Excel, you will not receive
any marks for this assessment.
b. Questionnaire: the questions in the Moodle questionnaire will be the same as the questions
provided in this instruction file. Your solutions should be reported as decimals (not
percentages) exactly following the example provided in the questionnaire. You can only
submit the questionnaire ONCE. Your attempt will be submitted if and only if you click
"Submit questionnaire". Therefore, don't forget to click "Submit questionnaire" when you
are fully ready to submit your answers, and please notice that you cannot make any further
changes and resubmit the questionnaire after you click the submission button. You need to
attempt all the questions (i.e., no questions left blank) for successful submission.
c. Excel: your marks will be determined by your performance in your submitted questionnaire
rather than Excel. However, the Excel file may be used/investigated by grader if any
clarification or checking of your solutions is required. There is no strict formatting
requirement on your Excel working process. As long as your work is clear and easy to
follow, it will be fine. For example, you can create separate sheets in your Excel for
different questions and name them accordingly to make it easy to follow.
3. About enquiries: If you have questions regarding this assessment, please raise them on Moodle
discussion forum. However, please keep in mind that this assessment is one of the
individual assessments for this course (i.e., please treat it as a takehome exam). Therefore,
only clarification-type questions (e.g., the ambiguity of the question) will be answered. Any
questions regarding the verification of the solutions will be disregarded directly.
4. The mark of this assessment is 60. It accounts for 20% of the total assessment for this course.
2

You are evaluating a portfolio of U.S. equities drawn from the S&P500. The five stocks in
your portfolio have the FactSet identifiers as follows:
Group A Group A Factset stock identifier
STOCK1 AXP-US
STOCK2 BLK-US
STOCK3 HD-US
STOCK4 IBM-US
STOCK5 MCD-US
S&P500 SP50
• Assume the annualised risk-free rate is 3% for this assessment
• The monthly return data can be found in the Excel “Data pool_2023 T2” on Moodle
NOTE:
• = 12 × ℎ
• = 12 × ℎ
• = ( ) = (12) ×
ℎ
• ℎ =
−
3
Gift mark for assigned group number
Q1: what is your assigned group number appearing on the first page of your instruction file?
(2 marks)
For Q1 to Q46: using the monthly data from 2014 Jan to 2018 Dec for the portfolio construction.
Please calculate the annualised average return and annualised variance of your assigned stocks and
S&P 500 index over the sample period (i.e., 2014 Jan to 2018 Dec), and check your basic summary
statistics with the solutions provided in the excel “Data pool_2023 T2” before you start solving
the questions. Your calculation of this basic summary statistics will not be marked but the
purpose of this step is to make sure you do not make naive mistakes (e.g., copy and paste
errors) at the very beginning of this assessment.
1. Markowitz optimization
• For the group of stocks assigned to you, form the minimum variance frontier. What is
the minimum attainable annualised standard deviation of the portfolio:
Q2: at an annualised average return level of 0% (1 mark)
Q3: at an annualised average return level of 15% (1 mark)
Q4: at an annualised average return level of 30% (1 mark)
• Calculate the portfolio weight for Global Minimum Variance Portfolio (GMVP). What is
GMVP portfolio weight? What is the annualised expected return and annualised standard
deviation of GMVP?
Q5: GMVP portfolio weight in STOCK 1 (1 mark)
Q6: GMVP portfolio weight in STOCK 2 (1 mark)
Q7: GMVP portfolio weight in STOCK 3 (1 mark)
Q8: GMVP portfolio weight in STOCK 4 (1 mark)
Q9: GMVP portfolio weight in STOCK 5 (1 mark)
Q10: GMVP annualised average return (1 mark)
Q11: GMVP annualised standard deviation (1 mark)
• Calculate the portfolio weight for the Optimal Risky Portfolio (P*). What is the P* portfolio
weight? What is the annualised expected return and annualised standard deviation of P*?
Q12: P* portfolio weight in STOCK 1 (1 mark)
4
Q13: P* portfolio weight in STOCK 2 (1 mark)
Q14: P* portfolio weight in STOCK 3 (1 mark)
Q15: P* portfolio weight in STOCK 4 (1 mark)
Q16: P* portfolio weight in STOCK 5 (1 mark)
Q17: P* annualised expected return (1 mark)
Q18: P* annualised standard deviation (1 mark)
• Suppose your utility function is = () − . Form an optimal complete portfolio by
combining P* with the risk-free asset. What is the portfolio weight on each of individual asset
in this optimal complete portfolio? What is the max utility score that you can achieve?
Q19: complete portfolio weight in STOCK 1 (1 mark)
Q20: complete portfolio weight in STOCK 2 (1 mark)
Q21: complete portfolio weight in STOCK 3 (1 mark)
Q22: complete portfolio weight in STOCK 4 (1 mark)
Q23: complete portfolio weight in STOCK 5 (1 mark)
Q24: complete portfolio weight in Risk-free asset (1 mark)
Q25: Utility score is? (1 mark)
2. Short Selling Constraint
• Construct the GMVP and P* with the short selling constraint:
Q26: GMVP(with short selling constraint) annualised average return (1 mark)
Q27: GMVP(with short selling constraint) annualised standard deviation (1 mark)
Q28: P*(with short selling constraint) annualised average return (1 mark)
Q29: P*(with short selling constraint) annualised standard deviation (1 mark)
Q30: Compare the GMVP that you construct without and with short-selling constraints. Make
comments on their performance and explain why short selling constraints may affect the
optimization procedure (3 marks)
Q31: Compare the P* that you construct without and with short-selling constraints. Make
comments on their performance and explain why short selling constraints may affect the
optimization procedure (3 marks)
3. SIM Optimization
5

• Estimate the Single Index Model and , for each stock in your portfolio using the
regression equation:
= + +
where and are the excess return for individual stocks and S&P 500 index
Q32: What was the lowest beta out of your 5 stocks? (1 mark)
Q33: What was the highest beta out of your 5 stocks? (1 mark)
• Decompose the total variance using
=
+
for all 5 stocks
Q34:What was the lowest
2 out of your 5 stocks? (1 mark)
Q35: What was the highest
2 out of your 5 stocks? (1 mark)
• Construct GMVP and P* under the SIM.
Q36: GMVP annualised average return (1 mark)
Q37: GMVP annualised standard deviation (1 mark)
Q38: P* annualised average return (1 mark)
Q39: P* annualised standard deviation (1 mark)
Q40: Briefly describe and comments on the key differences between the Markowitz and SIM
optimization procedure (hint: You should point out the additional assumption that SIM made
on the variance-covariance matrix construction, and provide your own opinion on whether the
assumption fits the data or not) (4 marks)
4. Treynor-Black Model
• Explore the potential mispricing opportunities of the 5 individual stocks under the
Treynor-Black Model. Form a new optimal risky portfolio (New P*) by combing the 5
individual stocks and S&P 500, what is your optimal portfolio weights on each stock and
S&P 500 in your new optimal risky portfolio:
Q41: STOCK 1 (1 mark)
Q42: STOCK 2 (1 mark)
Q43: STOCK 3 (1 mark)
Q44: STOCK 4 (1 mark)
Q45: STOCK 5 (1 mark)
Q46: S&P500 (1 mark)
6

5. out-of-sample evaluation using monthly data from 2019 Jan to 2022 Dec
For Q47 to Q50, use the portfolio weights you have derived in the previous questions and the
monthly data from 2019 Jan to 2022 Dec to evaluate the portfolio performance. Please calculate
the annualised average return and annualised variance of your assigned stocks and S&P 500 index
over the sample period from 2019 Jan to 2022 Dec, and please check your basic summary statistics
with the solutions provided in the excel “Data pool_2023 T2” before you start solving the
remaining questions. Again, your calculation of this basic summary statistics will not be marked,
but the purpose of this step is to make sure you do not make naive mistakes (e.g., copy and paste
errors) at the very beginning.
• For the optimal risky portfolio (P*) weights that you derived in Q12 to Q16, new optimal
risky portfolio (new P*) weights that you derived in Q41 to Q46, and S&P500 index:
calculate the annualised Sharpe ratio using the data from 2019 Jan to 2019 Dec?
Q47: P* annualised Sharpe ratio (1 mark)
Q48: new P* annualised Sharpe ratio? (1 mark)
Q49: S&P500 annualised Sharpe ratio? (1 mark)
Q50: Based on your calculation from Q47 to Q49, compare P* and new P* performance with
S&P500 and make comments on the relative performance, and provide possible explanations
(3 marks)
6. Kind Reminder
Q51: Have you submitted your Excel via the related link as required for this assessment?
Notice that if you only submit the questionnaire but do not submit the Excel, you will not
receive any marks on this assessment.