Objectives: This assignment is designed to show how the topic – Security Analysis II – can be applied using real financial data and Excel. It is also to further develop your technical and analytical skills, which will help with your employability.

Important Note: The sample period and correlation applicable depends on the allocated tutorial with specific tutor, and it is the student’s responsibility to know the tutorial allocated to (e.g. Tutorial 01 – Mon 11am), as well as the tutor’s name.

Plagiarism & Collusion: Note that this is an individual assignment and that it is not a group work – please read all the relevant documents related to Plagiarism & Collusion on the Monash page and on

Moodle:  https://www.monash.edu/students/admin/policies/academic-integrity

Any violations are officially misconduct and will be dealt with seriously, and with the case potentially escalated to the Faculty.

Total Marks: 25 Marks [5% of Total Assessment]

Submission:  You will need to use the Excel Spreadsheet Template posted on Moodle and submit via Moodle by the due date & time as stated in the Unit Guide. Late submission will result in 20% penalty each day (including weekend & public holiday). Do NOT convert excel into PDF file as the workings cannot be marked and there will result in immediate 30% penalty.                                                                       You are also required to submit the ‘signed’ assignment cover sheet confirming that this is your own work. The assignment without the signed cover sheet will be deemed incomplete and the late penalty will apply.                                                                                                                                                                                   [IMPORTANT] After uploading the files (it will appear as draft, but the files are actually not submitted yet), you will  need to click  'submit' and the ‘Status’ will be  indicated  as ‘Submitted  for grading’ . Please note that you are only allowed one submission – so it is your responsibility to ensure that all the submitted files are correct. All files submitted are only to be in ENGLISH, files in other languages will not be marked.                                                                                                                                                                          Also, we would advise that, it is best to not leave until the last minute to submit (especially given that the ASM does not take that long to complete), please feel free to submit at least 1-2 days before the deadline. For the cover sheet, you can submit as a PDF file, or alternatively, you can have your signature as a picture and paste in the word document. If your signature is in Chinese (or another language), it is completely fine. For the ASM02 itself, you have to submit in excel file.

Tasks: You are required to conduct ratio analysis of stocks listed on the ASX (Tip: put ‘ .ax’ after the stock ticker and check that the company’s full name is correct), based on your Allocated Tutorial:

[Note 1]: Using incorrect stocks or sample period will incur immediate penalty of 50% [or possibly 100% if the entire assignment is then found to have copied/plagiarised another assignment].

[Note 2]: As stated, you are required to use the template provided – do not modify or add worksheets – This will also incur penalty. All results required can be calculated based on the template provide.

[Note 3]: You are required to show workings in the form of using formulae (i.e. do not copy and paste special as ‘value’) as the marker will check how the answers are calculated. Answers are required to have 2 decimal places.

PART 1 – W1 Ratio Analysis [10 Marks]

[1A]    Obtain the stocks’ financial data from Yahoo Finance: https://au.finance.yahoo.com/

[Note: The relevant data will be under ‘Financials’ tab.]

Input the raw data into the specified green cells in Worksheet W1-Ratio Analysis

[1B]    Calculate the ratio in the blue cells in Worksheet W1-Ratio Analysis

[Note: Do not copy & paste as value – you need to show the formula. 2 decimal places are required.]

[1C]     In  Worksheet  W1-Ratio  Analysis  –  Carry  out  the  DuPont  Analysis  by  inputting  the  values

calculated in [1B]. Confirm that the product in the grey cell matches the ROE.

[Note: Do not copy & paste the ROE value from the previous step into this cell – you need to show the formula]

[1D]    In Worksheet W1-Ratio Analysis – Calculate the growth rate (g)

[Note: ONLY USE the ‘Payout Ratio’ under the ‘Statistics’ tab as the input to calculate the Plowback Ratio.]

PART 2 – W2 Written Analysis [15 Marks]

In Worksheet W2 – Written Analysis – Answer the following questions in the empty cells provided.

[Note: Words Limit (including numbers) PER question is 120 words. Please use one paragraph for each company.]