代做25705 Financial Modelling and Analysis Autumn 2024调试数据库编程
- 首页 >> CS25705 Financial Modelling and Analysis
Autumn 2024
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.
Q9. Quality of writing and presentation [4 marks]
1. Sentences should be clearly connected and coherent. The sentences should flow logically from point to point. Written expressions should be clear, complete, and grammatically correct.
2. Chart formatting should be clear, only showing the information that is requested in each question. Make sure labels, series and numbers do not overlap.