ACCT6003 Fundamental Analysis for Equity Investment
Week 10 Assignment on Financial Ratio Analysis
Marks: 6/100
Deadline for submission: Before the lecture of Week 11.
In this assignment, you are required to analyse the profitability of Woolworths Group Ltd during 2012-2017, using the provided reformulated statements of Woolworths in the Excel spreadsheet that is available on Canvas.
Required:
1. In the Excel worksheet named ‘Q1. Profitability’, you are required to use the provided template to perform profitability analysis for Woolworths during 2013-2017 using the reformulated statements, for the following ratio decompositions:
• ROCE decomposition in operating, financing, and minority interest parts.
• ROCE decomposition as RNOA, FLEV, Spread, MILEV and MISpread.
• RNOA decomposition as ROOA, OLLEV and OLSpread.
• RNOA decomposition asATO and OPM.
• OPM decomposition as GPM and individual operating expense ratios.
• ATO decomposition as inverse of individual operating turnover components.
• Analysis of the operating cash-to-cash cycle.
You may need to add more rows in some decompositions to complete the above analysis. You are required to perform checks and round the checks to the nearest fourth decimal point as indicated in the template example using the Excel formula ROUND(cell,4). You are also required to document any assumptions that you need to make in the ‘Notes’ area at the end of the worksheet. [2 marks]
2. In the worksheet named ‘Q2. Insights’ and in less than 300 words, discuss what you consider to be the two most important business insights from this profitability analysis. You are required to use ratio analysis to make the case why the insights are very important for Woolworths and then support your arguments using evidence from the annual reports to explain the movement in ratios and why these are important for Woolworths. [2 mark]
3. Consider that you are an analyst in 2017 who is responsible for issuing an investment recommendation for Woolworths at year-end 2017. You are using the Residual Operating Income (ReOI) valuation model, and you plan to project pro-forma statements for 2018-2022 for the finite forecasting horizon. You have access to these reformulated financial statements and the accompanying profitability analysis. In the worksheet named ‘Q3. Projection’ and in less than 300 words, explain how you could use this historical data to help you project informed pro-forma statements and apply the ReOI model. [2 mark]