Major Project in Statistics, ECON1314 – Semester 1, 2023
This is an individual project. This project is designed to acquaint students with the application of statistical concepts in business. This involves planning research, collecting data, carrying out appropriate hypothesis tests, analysing data using regression analysis, and writing a business report based upon this empirical work. This is an authentic assessment task and is exactly the kind of work that data analysts and financial officers often undertake.
This assignment requires the use of Microsoft Excel. If you have Windows, you will need to use the Data Analysis ToolPak. If you have a Mac with Excel 2011, you may need to use StatPlus:MAC LE.
Scope: maximum of 3 pages submitted (excluding any appendices).
Submission: Via Assignments folder in Canvas.
Marks: This project comprises 100 marks and will contribute 25% to your overall mark.
Due date: 11 April 2023 at 23:59 Melbourne time.
Economists and Businesses are often interested in the relationship between stock returns of an individual company and returns of a selected index. Your first task is therefore to download historical weekly data (prices)for the index S&P/ASX 200 (symbol ^AXJO), andhistorical weekly data (prices) for your assigned company,for the period 1 Jan 2022 – 31Dec 2022(see Canvas for details on your assigned company). Please note that Yahoo Finance will not allow you to directly download the AXJOdata so I have done that for you and it is available in the assignment 1 page in canvas. You will be able to directly download the data for your assigned company.To do this, you will need to go to the Yahoo Finance website (https://au.finance.yahoo.com) and search for the required data. You can search for the databy typing the index symbol (AXJO)in the search box (and then do the same for your allocated company’s symbol). The data will be available under the ‘Historical data’ tab.Important:You will need to set the frequency to ‘Weekly’, the time period to the 2021financialyear (1 Jan 2022 – 31 Dec 2022 and press Apply before you download your dataset.
For both the company (allocated to you) and index (S&P/ASX 200) you can delete the columns: High, Low, Adj Close, Volume. You will then have the columns:
- ‘Date’(this is the date the week began)
- ‘Open’ (the opening price for the week which is also the last recorded price for the previous week),
- ‘Close’ (the last recorded price for the week).
Your next task is to calculate weekly returns for the Open and Close prices and name those new variables PWR and CWR respectively.
Weekly return is calculated by: (Price in current week – Price in the previous week)/Price in the previous week.
For example, given the below table, PWR for Open for the week 7/01/2018for the ASX Index is calculated by: (6122.3-6065.1)/ 6065.1=0.009431.
Example data from an earlier time period is used here for illustrative purposes, the ASX 200 dataset should now look like this:
|Date||Open||Close||PWR – S&P200||CWR – S&P200|
You should now calculate returns for your assigned company in a similar way.If for example your assigned company is ANZ (symbol ANZ.AX), your dataset after merging both datasets for the first three weeks of the year should look like this:
|Date||PWR – S&P200||CWR – S&P200||PWR – ANZ||CWR – ANZ|
Once the 4 new variables are constructed you can begin your analysis.
- Start your business report with ashort formal introduction stating the background of the company and what you are studying.(10 marks).
- Calculate relevant descriptive statistics for all 4 new variables. Include one table that summarises this information and comment on each relevant measure.(30 marks).
- Using regression analysis, test the predictive power of PWR on CWR. Specifically, you will be performing two regression analyses:
- using PWR – S&P200 as your independent variable(X) to predict the dependent variable CWR (Y) of your allocated company (ANZ in the above example), and
- using PWR of your allocated company as your independent variable to predict the dependent variable CWR of the index (CWR – S&P200).
DO NOT include the Excel regression output with your submission. Instead, create a new table (one for both analyses) that summarises the key measures – Number of Observations, R Square, Value of theSlope,t-statistic and P-Value of the Slope. Be sure to comment on each measure.(40 marks).
- Summarise your results and provide conclusions.Includeany relevant recommendations and factors that you believe could improve the regression model. (20 marks).