Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
Case Study
This document is available on Canvas under “Assignments/ Case Study”.
25705 Case Study
Instructions
Please access the Stock Allocation - Autumn 2024.xlsx spreadsheet to get the details of the
stocks assigned to you (Main, Bench1 and Bench2) based on your Student ID.
• Dataset1 | For all three stocks, please download daily data (Price, Cvol, Open,
High, Low) from Factsetfrom 31 December 2009 to 31 December 2023. Seminar 1 in-class activity showshow to do this.
• Dataset2 | For your all three stocks and the S&P/ASX200 (XJO-ASX), download weekly prices from 24 June 2016 to 30 June 2023.
• Dataset3 | For your Main stock, also download half-yearly Income Statement from June 2005 to June 2023 (37 periods). If not available from June 2005, please download from the first available period.
Report Format
• Please submit your report in PDF format and your workings in an Excel spreadsheet.
• The report should include your answers and conclusions, as well as the tables and charts you judge relevant.
o Please create a cover page for the report, containing subject number and name, report title, student name, ID, and UTS email.
o All text should be 1.5 lines space with 12-size font.
o The page limit is 10-A4 pages, excluding the cover and the reference list. Any materials beyond the page limit will not be considered.
o Please name the report by including your Student ID number after the original filename (e.g., 25705 Case Study 13333333.pdf)
• The spreadsheet should contain all calculations and be formatted appropriately:
o One worksheet per Dataset (Dataset1, Dataset2, Dataset3)
o One worksheet per question. Each labelled Q1, Q2, etc.
o Input and calculation formats should be clearly identified
o Calculations should be transparent and show proficiency in Excel
. Hard-coded values are only appropriate for inputs or for outputs of Data Analysis steps. Please clearly specify if you have used any Data Analysis steps in your calculation.
o Please name the spreadsheet by including your Student ID number after the original filename (e.g., 25705 Case Study 13333333.xlsx)
Submission: Both files (report and spreadsheet) should be submitted on Canvas before 11:59 pm on Friday, 3 May.
Penalty for non-compliance: Failure to follow the instructions on the report format carries a penalty up to 10 marks. A penalty of 10 marks will be exercised for each day (or part of) that the report is late.
Descriptive Statistics and Visual Analysis
Q1. [2 marks] For each of the three stocks you have been assigned, please use Dataset1 to:
• Calculate daily returns and daily volatility (using the high/low measure).
• Compute the descriptive statistics for returns, volatility, and volume for the entire period.
• Compare results across stocks and comment on your findings.
Correlations
Q2. [2 marks] For each of the 3 stocks, please use Dataset1 to:
• Compute the correlations across returns, volatility, and volume.
• Compare results across stocks and comment on your findings.
Q3. [2 marks] Please use Dataset1 to:
• Compute the correlations of returns across each pair of the three stocks:
o Main - Bench1
o Main - Bench2
o Bench1 - Bench2
• Use a scatter plot chart to illustrate the correlations between each pair.
• Compare results and comment on your findings.
• Which of the two benchmark stocks provides more diversification benefits?
Hypothesis Testing
Q4. [2 marks] A colleague asks you to corroborate whether the difference in average returns for the Main stock and the Bench1 stock is statistically significant at the 1% level. Using
Dataset1, please:
• Formulate the null and alternative hypotheses,
• Specify if you need to perform a one or a two-tail test, and
• Run ahypothesis test at the 1% level of significance and provide your conclusion.
Q5. [2 marks] A colleague asks you to corroborate whether the difference in average
volatility for the Main stock and the Bench2 stock is statistically significant at the 1% level. Using Dataset1, please:
• Formulate the null and alternative hypotheses,
• Specify if you need to perform a one or a two-tail test, and
• Run ahypothesis test at the 1% level of significance and provide your conclusion.
Forecasting Volatility
Q6. [2 marks] Using Dataset1, please forecast daily volatility for your stock using an estimation period going from 1 January 2010 to 30 June 2022 and a hold-out period going from 1 July 2022 to 31 December 2023.
• Implement the SES method to forecast volatility using an initial α defined by you. Use the estimation period volatility data and Excel’s Solver determine the optimal α .
• Using the optimal SES parameter you obtained, calculate the MSE in the hold-out period and report it in the table provided in worksheet
• Re-estimate the α using all the data and forecast volatility for 2 Jan 2024.
• Report and discuss your main findings. Is SES an appropriate method to forecasting volatility?
Simple Linear Regression
Q7. [2 marks] Using Dataset2, please:
• For each of the three stocks (Main, Bench1 and Bench2), estimate Beta (measure of systematic risk) for:
o The 156 weeks from 1 July 2016 to 21 June 2019
o The 156 weeks 10 July 2020 to 30 June 2023
• Report and discuss your main findings.
Multiple Linear Regression
Q8. [2 marks] Using half-yearly Sales as reported in the Income Statement (Dataset3), please:
• Build two alternative multiple regression models you believe will have explanatory power over your Main stock Revenue/Sales. You can source these independent variables from Factset or from other sources. The difference between the two models could be just one independent variable:
o Model 1:
o Model 2:
• Use the first 32 periods as training, and the last 5 periods as test data.
Note: If your company had fewer than 37 periods, the total number of periods minus 5 as training data. For example, if you only have 24 periods, use 19 for training and 5 for testing.
• Please report and discuss your main findings.