25705 Financial Modelling and Analysis Assessment

Instructions

Please access the Stock Allocation - Autumn 2023.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 Factset from 31 December 2004 to 31 December 2022. Seminar 1 in-class activity shows how to do this.
• Dataset2 | For your all three stocks and the S&P/ASX200 (XJO-ASX), download weekly prices from 31 December 2017 to 31 December 2022.
• Dataset3 | For your Main stock, also download half-yearly Income Statement from June 2005 to December 2022 (36 periods)

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.
  • Please create a cover page for the report, containing subject number and name, report title, student name, ID, and UTS email.
  • All text should be 1.5 lines space with 12-size font.
  • The page limit is 10-A4 pages, excluding the cover and the reference list. Any materials beyond the page limit will not be considered.
  • Please name the report by including your Student ID number after the original file name (e.g., 25705 Case Study 13333333.pdf)
• The spreadsheet should contain all calculations and be formatted appropriately:
  • One worksheet per Dataset (Dataset1, Dataset2, Dataset3)
  • One worksheet per question. Each labelled Q1, Q2, etc.
  • Input and calculation formats should be clearly identified
  • 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.
  • Please name the spreadsheet by including your Student ID number after the original file name (e.g., 25705 Case Study 13333333.xlsx)

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:

• Main Bench1
• Main Bench2
• 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 a hypothesis 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 a hypothesis 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 2005 to 30 June 2022 and a hold-out period going from 1 July 2022 to 31 December 2022. Implement the SES method to forecast volatility using an initial ? defined by you. Use the estimation period volatility data and Excels Solver determine the optimal ?.

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:
  • The 2018-2019 period
  • The 2021-2022 period
• 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:
• Use the first 32 periods as training, and the last 4 periods as test data.
• Please report and discuss your main findings.

Q9.[4 marks]

Quality of writing and presentation

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.