BFC5935: Portfolio Management Theory INDIVIDUAL ASSIGNMENT 2 [ASM02] Security Analysis: Ratio Analysis, Plowback Ratio calculation, liquidity / operating efficiency / profitability / financial leverage / growth / analysis and relative valuation
Objectives: This assignment is designed to show how the topic – Security Analysis – 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.
Total Marks: 30 Marks [10% 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 a 20% penalty each day (including weekends & public holidays). Do NOT convert excel into a PDF file as the workings cannot be marked, and there will result in an immediate 30% penalty.
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 the ‘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 [20 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.]
[2A]
With reference to W1 – Ratio Analysis & based on the specific ratio(s), briefly discuss & compare the liquidity of the two companies.
[2B]
With reference to W1 – Ratio Analysis & based on the specific ratio(s), briefly discuss & compare the operating efficiency of the two companies.
[2C]
With reference to W1 – Ratio Analysis & based on the specific ratio(s), briefly discuss & compare the profitability of the two companies.
[2D]
With reference to W1 – Ratio Analysis & based on the specific ratio(s), briefly discuss & compare the financial leverage of the two companies.
[2E]
With reference to W1 – Ratio Analysis & based on the specific ratio(s), briefly discuss & compare the growth of the two companies, and specifically the determinants of g based on the formula.
[2F]
With reference to the ‘Forward P/E’ ratios of the two companies (under the ‘Statistics’ tab), briefly discuss the relative valuation of the two companies; and comment on whether this relative valuation is consistent with the growth rates calculated in [1D].