Company Analysis Using Microsoft Excel

Assignment Questions:
1. Select company
Select one company according to steps explained on the previous page (the company list is provided on Moodle in the Excel file in the “Assignment” folder). Verify that the company has been trading on the Australian Securities Exchange between January 2016 and August 2020.
2. Stock analysis (20 Marks – 4 marks per question) – complete in an Excel file
a. Collect monthly share price data for the selected company between January 2016 and August 2020. Calculate realised monthly return each period for the selected company using share prices between January 2016 and August 2020 (note that you are permitted to use a longer investment period if data is available for your selected companies). Calculate average monthly return, variance, and standard deviation of returns for the stock.
b. Assume you use the estimated ASX 200 index returns as proxy for the market return. Collect monthly market index data for ASX 200 between January 2016 and August 2020.
Calculate realised monthly ASX 200 index return each period between January 2016 and August 2020 (note that the sample period should be consistent with question 2.a). Calculate average monthly return, variance, and standard deviation of returns for ASX 200.
c. Calculate the Beta for the stock.
d. Use the Capital Asset Pricing Model (CAPM) to estimate the stock return for the company, assume the monthly risk-free rate is 0.12% (hint: use the ASX 200 index returns as the market proxy).
e. Compare the return you estimated to the monthly stock return in September 2020. Do you think that the firm’s stock is correctly valued by the market? Why?
3. Discussion (30 Marks – 10 marks per question) – report in a PDF file
a. Based on its stock price over the recent four years, discuss the firm’s stock performance during the Covid-19 period (i.e., from February 2020 to August 2020). Your discussion should be up to maximum of 300 words.
b. Check the balance sheet of the firm in 2019 and 2020 and discuss the capital structure of the firm as well as the potential risk of the firm (in terms of capital structure) during the Covid-19 period. Your discussion should be up to maximum of 300 words.
c. Compare cash flows of the firm in 2020 to its cash flows over the past 3 years (refer to the firm’s cash flow statement). What are the possible reasons for the change of cash flows in 2020 (Hint: economy, industry, firm specific, customer, supplier, etc.)? Your discussion should be up to maximum of 300 words.

Open chat
Need assignment help?