Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
FINS5513 Data Exercise Instructions
Group C
1. Submission deadline: The assessment links will be accessible on Moodle from 10:00 AM,
18th January to 23:55 PM, 29th January, Sydney Time.
• You may attempt the assessment at any time during the provided window, but
please note that the assessment will close sharply at 11:55 PM on January 29th. For
instance, if you begin your attempt at 11:25 PM on January 29th, you will have
only 30 minutes to complete the task.
• Please plan your time accordingly and avoid waiting until the last minute. Please
be aware that any failure to submit due to technical issues near the deadline will not
be considered a valid reason for applying for special consideration.
• Late submission: Late submissions without prior approval for special consideration
will incur a 5% mark deduction for each day or part thereof (including weekends)
beyond the due date and time.
2. Submission format:
• Please submit BOTH questionnaire and Excel work. If you only submit one of
them, you will not be able to receive any marks for this assessment. Both of these
links can be found in the Moodle section "Assessment - iLab: Data Exercise"
• Questionnaire: To submit your solutions for each question, please use the "Data
exercise Questionnaire" link. For successful submissions:
i. Please report your solutions as decimals (not percentages) exactly
following the example provided in the questionnaire.
ii. Only ONE attempt is allowed. So please prepare your solutions ready
before you start the questionnaire to avoid any technical issues.
iii. Your attempt will be submitted if and only if you click "Submit
questionnaire". Please note that you cannot make any further changes and
resubmit the questionnaire after you click the submission button.
iv. All the questions should be attempted (i.e., no questions left blank).
• Excel: Please submit your Excel spreadsheet via the "Data exercise Excel
submission" link.
i. Your marks will be determined by your answers in the questionnaire rather
than the Excel file. However, the Excel file may be used/investigated by
grader if any clarification or checking of your solutions is required. The
Excel file does not require a specific format, as long as it is clear and easily
understandable. For clarity, you might consider creating separate sheets for
different questions and labeling them appropriately.
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.
4. The total mark of this assessment is 54. It accounts for 20% of the final mark 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 EL-US
STOCK2 INTC-US
STOCK3 C-US
STOCK4 WFC-US
STOCK5 BA-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” on Moodle
NOTE:
• = 12 × ℎ
• = 12 × ℎ
• = ( ) = (12) ×
ℎ
• ℎ = −
3
Gift mark for assigned group number.
Q1: what is your assigned group number which can be found 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” before you start solving the
questions. Your calculation of the 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. Kind Reminder
Q46: 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.