Internal Code: 1HBDC
Report Writing Assignment:
- At the start of semester each student is allocated a company listed on the Australian Securities Exchange (ASX). Look for your allocation in the Excel file “Semester 181 Assignment Workings.xlsx”. There are multiple sheets in this workbook. Your company allocation is listed on the “Company Allocation” sheet. If you are submitting as a pair, choose one of the partner’s allocated company.
The purpose of completing the analysis and doing early research is for you to get a broad understanding of recent events and trends in global financial markets, and how these affect the real economy and impact your company’s operations. You will learn how to search for financial market data, arrange it in an appropriate format, summarise and present it in a professional manner. You are required to start your research on your allocated company, and key events impacting global financial markets. You will start thinking of how these events and driving factors have affected the Australian market and particularly your company’s performance.
This assignment has been embedded in your weekly tutorial tasks. Use tutorial times (for on-campus students), and weekly Discussion Board threads (for off-campus students’) to discuss elements of completing this assignment in stages with other students and your instructors. These are opportunities for you to gain feedback on your work-in-progress before submitting the final report.
For this assignment, you are taking on the role of an equity analyst and you expect the reader of your report to be a client looking for market research and an investment recommendation.
Task 1 – Watch, review, research:
Your first task is to review sheet “Market Data”. Watch the first Assignment Video. This introduction to key events and driving factors in global financial markets will assist you in putting your assignment in context. Your aim is to get an understanding of your study period for this assignment. You can also do some research on financial market drivers over the past few years.
Task 2 – Research:
Do some research on your allocated company. Find out what the company’s core operations are to get an understanding of its industry/ sector. Write a discussion to describe your company, its operations and its sector. Ensure you provide appropriate in-text and end-text referencing in your final report.
Task 3 – Extract financial market data:
You have been provided with a partly filled Excel workfile. The sheet “Your workings and analyses” already lists the data required for the overall stock market index, the S&P/ASX200. You must fill in Column C with the corresponding weekly adjusted closing price of your allocated company’s stock.
Go to the Yahoo! Finance website (https://au.finance.yahoo.com) and download weekly share prices of your company. Make sure you go to the Australian site for Yahoo! so that all data is derived in Australian dollars.
You will be downloading weekly adjusted closing prices from 15 February 2013 to 15 February 2018.
There is an Assignment Video to show you how.
We are using adjusted closing prices
in this assignment, which includes both capital gain and dividend yield components. Therefore, returns calculated using this data are total returns for each period.
Data for the overall Australian stock market index S&P/ASX200 (Yahoo! Code: AXJO) is already provided in Column A. The S&P/ASX200 Index in this case study represents a diversified portfolio of stocks, covering all key sectors in Australia.
Note: When downloading from Yahoo!, be sure to check the date entry format. Enter the date rates in mm/dd/yyyy format and click on “Apply”. Check that the website has refreshed to the date range you entered, before clicking on the “Download” button. When you open the downloaded file, check that your data has been sorted from oldest to newest
with the earliest date at the top row, and the newest date at the bottom of your data array. Follow the dates provided to you in this instruction document. Yahoo! dates are presented at the start of the week (instead of end week) but the prices are correct. Your data should occupy C3:C264 (262 observations). Type the name or code of your company in C2.
Task 4 –Convert weekly price data to weekly percentage returns:
Calculate the weekly returns of your company and the S&P/ASX200 index over the specified 5-year period using the formula example:
Repeat the calculation for every subsequent week for the index and company share. You should have 261 weekly returns for each, because there are 262 price observations. Your results should occupy the highlighted cells in columns E and F. There is an Assignment Video to show you how.
Task 5 – Prepare charts to illustrate price and return movements over time:
Use Excel to prepare a single graph illustrating the market movements of your company’s share price and the movements of the S&P/ASX200 index from your data spanning 2013 - 2018. Format the S&P/ASX200 index data to follow the primary vertical axis, and your company’s share price to follow the secondary vertical axis. Present this graph neatly and clearly in your Word document.
Then, use Excel to prepare a single returns graph illustrating the weekly percentage changes to share prices and the overall market index over the 5-year period. There should be one vertical axis to measure the percentage changes for both series. Present this graph neatly and clearly in your Word document.
There is an Assignment Video to show you how.
Task 6 – Summary statistics to describe market performance over the study period:
Calculate the performance statistics based on the weekly returns of your company and the stock market index. Populate your results in cells R26:T31. Express your results in percentages, with four decimal places. Copy the table and present it neatly and clearly in your Word document.
Task 7 – Analyse, research and write:
||Your company code/ name
|Average weekly return
|Standard deviation of weekly returns
|Maximum week return
|Minimum week return
= Standard deviation / Average
Write a discussion to address the requirements below. Ensure you substantiate your discussion with evidence of relevant and credible research.Cite your references within the body of your discussion (in-text referencing), and provide a reference page (end-text referencing). Refer to the ECU Referencing Guide.
Analyse - Compare the share price trends of your allocated company and the overall stock market index. You may wish to annotate the price chart. Have there been similar or opposing trends over the 5-year period? Discuss these price trends. Better students will start to relate these trends with what’s been going on in the market.
Research – Do some research on key events and driving factors impacting global financial markets.
Discuss – Relate how these events and driving factors have impacted the overall Australian economy, the overall stock market index, and affected your company.
Task 8 – Analyse, synthesise with theory, research and write:
The purpose of the next stage is for you to combine your understanding of past market events, the company you are researching, your diagrams and calculations in order to take the perspective of a market investor. You are required to consider the market performance of a single investment in your company’s shares, compared to an investment in a diversified portfolio of shares represented by the overall stock market index. You are also required to discuss systematic and unsystematic risks affecting your company from 2013 – 2018. Your research scope should focus on issues affecting your company’s industry/ sector, and key issues faced by your over this period of time. We suggest you start by reading the key highlight of your company’s Annual Reports over these years. Search for news articles relating to your company and its industry over this period. Discussions with your peers and instructors during classes (and discussion board threads) will also be a useful resource to assist you with gauging your progress and ideas. You will find Lecture material for Weeks 7 and 8 particularly useful here.