Financial Modelling and Analysis Autumn
Posted on July 15, 2023 by Cheapest Assignment
Order Now
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.
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 file name (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 file name (e.g., 25705 Case Study 13333333.xlsx)
Submission: Both files (report and spreadsheet) should be submitted on Canvas before 11:59 pm on Friday, 12 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 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 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 the first day of 2023.
- 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 2018-2019 period
o 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:
o Model 1: 𝑌̂ = 𝛽̂
0 + 𝛽̂
1𝑋1𝑖 + 𝛽̂
2𝑋2𝑖 + 𝑒𝑖
o Model 2: 𝑌̂ = 𝛽̂
0 + 𝛽̂
1𝑋1𝑖 + 𝛽̂
2𝑋2𝑖 + 𝛽̂
3𝑋3𝑖 + 𝑒𝑖
- Use the first 32 periods as training, and the last 4 periods as test data.
- Please report and discuss your main findings.
Q9. Quality of writing and presentation [4 marks]
- 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.
- Chart formatting should be clear, only showing the information that is requested in each question. Make sure labels, series and numbers do not overlap.
Order Now
