Context:

Assessments 4 assesses students’ understanding of risk and return on investments, valuation of stocks and spreadsheet applications. These are examined using a combination of a financial analysis case study and a 600-word summary outlining the steps involved in using (1) online database for financial information collection, and (2) EXCEL spreadsheet for risk and return calculations. Students are required to conduct independent research on how to use Microsoft EXCEL to complete the assessment tasks.

Tasks:

You will use Microsoft EXCEL to complete a series of related calculations for Assessment 4. Working individually, you are required to prepare solutions to all tasks using solely Microsoft EXCEL. You are also required to prepare a 600-word summary explaining your data collection process and the EXCEL commands/functions/operations you have used to solve the calculation tasks.

Submission Instructions:

Submit all Assessment 4 tasks (including the 600-word summary) as a single EXCEL file via the Assessment submission link in the main navigation menu in FINA2006: Principles of Finance. The Learning Facilitator will provide feedback via the Grade Centre in the LMS portal. Feedback can be viewed in My Grades. You will answer each task on a separate worksheet using EXCEL. Before submitting, name the EXCEL file using only your student number. Do NOT submit any other types of files, only submit one single EXCEL file containing answers to all tasks. Failure to adhere to these requirements will result in a deduction of 20 marks.

Financial Analysis Case Study Task (Risk and Return): (70 marks)

You will be randomly allocated a stock listed on the ASX by the Facilitator (list of stocks on next page). You will then collect the monthly stock prices of the stock and the monthly indices of the S&P/ASX 200 Index (^AXJO) over the period of 1st July 2016 to 30 June 2021. Using the collected data, perform the following calculations:

1.                 Monthly stock returns (5 marks)

2.                 Yearly stock returns (5 marks)

3.                 Average yearly stock returns over the five years (5 marks)

4.                 Monthly index returns (5 marks)

5.                 Yearly index returns (5 marks)

6.                 Average yearly index returns over the five years (5 marks)

7.                 Total risk associated to investment in the stock (perform one calculation using 60 monthly stock returns) (5 marks)

8.                 Total risk associated to investment in the index (perform one calculation using 60 monthly index returns) (5 marks)

9.                 Systematic risk of associated to investment in the stock (perform one calculation using 60 monthly returns) (10 marks)

10.             Unsystematic risk of associated to investment in the stock (perform one calculation using 60 monthly returns) (20 marks)

Case Analysis Summary Task: (30 marks)

Provide a 600-word summary detailing (1) how you have collected all necessary data as inputs for the tasks, including the source and coverage of the data, and steps undertaken to extract the data, and (2) the EXCEL commands/functions/operations you have used to perform the calculations. In your summary, you must explain clearly each step you performed in using EXCEL to arrive at the final answers, including the purpose of each calculation and the inbuilt EXCEL commands/functions employed.

List of Stocks

BHP: BHP GROUP LIMITED

CBA: COMMONWEALTH BANK OF AUSTRALIA

RIO: RIO TINTO LIMITED

CSL: CSL LIMITED

WBC: WESTPAC BANKING CORPORATION

NAB: NATIONAL AUSTRALIA BANK LIMITED

ANZ: AUSTRALIA AND NEW ZEALAND BANKING GROUP LIMITED

WES: WESFARMERS LIMITED

MQG: MACQUARIE GROUP LIMITED WOW: WOOLWORTHS GROUP LIMITED TLS: TELSTRA CORPORATION LIMITED FMG: FORTESCUE METALS GROUP LTD TCL: TRANSURBAN GROUP

APT: AFTERPAY LIMITED

ALL: ARISTOCRAT LEISURE LIMITED

COL: COLES GROUP LIMITED SYD: SYDNEY AIRPORT XRO: XERO LIMITED

REA: REA GROUP LTD

WPL: WOODSIDE PETROLEUM LTD SHL: SONIC HEALTHCARE LIMITED NCM: NEWCREST MINING LIMITED

FPH: FISHER & PAYKEL HEALTHCARE CORPORATION LIMITED

Referencing

It is essential that you use the APA 7th Edition Referencing style for citing and referencing research. Please see more information on referencing here in the Academic Writing Guide found via the Academic Skills website.

Academic Integrity

All students are responsible for ensuring that all work submitted is their own and is appropriately referenced and academically written according to the Academic Writing Guide. Students also need to have read and be aware of Torrens University Australia Academic Integrity Policy and Procedure and subsequent penalties for academic misconduct. These are viewable online.

Students also must keep a copy of all submitted materials and any assessment drafts.

FINA2006 Assessment Brief – Financial Analysis Case Study

Assessment Rubrics