Task: Based on the same company chosen in the group report, perform financial analysis and prospective analysis as if you were a business analyst Specifically, reformat the company’s financial statements for the past five years; analyse key ratios and cash flows to evaluate current and past performance of the company; forecast future financial performance of the company for the next five years and beyond; apply valuation models to estimate the company’s intrinsic value; perform sensitivity analysis on key forecasting assumptions, and discuss potential opportunities and challenges for the company to improve value. Detailed requirements are as follows:
Reformatting (4 marks):
• Prepare detailed past five years’ reformatted financial statements in an excel spreadsheet and attach as an appendix in the report.
Ratio and cash flow analysis (8 marks):
• Perform DuPont analysis. Calculate and discuss key ratios such as ROE, RNOA, PM, ATO, FLEV and NBC.
• Break down and analyse PM and ATO ratios in further details. Identify and discuss significant expense items that have caused major changes in profit margin. Identify and discuss major assets or liabilities whose turnover ratios have contributed to the overall change in assets efficiency.
• Briefly describe the ratios trend. The analysis should elaborate on the economic, industry and business factors that drive the changes in ratios. The discussion should consistently reflect the same firm fundamentals identified in the group report.
• Calculate other relevant liquidity, solvency and cash flow ratios that are not covered in DuPont analysis. Analyse financial risk and cash flow management of the company based on these ratios.
Forecasting (8 marks):
• Prepare your forecasts in an excel spreadsheet following a 10-step forecasting template. The forecasting table should include your specific forecasts for the next five years as well as the long-term forecast beyond the five-year forecast horizon. The forecasting table should be included as an appendix in the report.
• Explain the reasons for your initial forecast assumptions, i.e. assumptions for sales growth, ATO, PM, net dividend payout ratio, cost of debt and cost of equity.
Valuation (4 marks):
• Apply four valuation models, i.e. dividend discount model, residual income model, residual operating income model and free cash flow model, to estimate the intrinsic value of the company. Provide calculations and results of four valuation models in a table and include it as an appendix in the report]
• Compare and discuss the estimates obtained from the four models. Compare the estimates with the actual share price observed in the stock market when valuation is performed and conclude whether the firm is currently overvalued or undervalued.
sensitivity Analysis (4 marks):
• Adjust your initial forecast assumptions to reflect your optimistic and pessimistic forecasts for sales growth, ATO, PM, net dividend payout ratio, cost of debt and cost of equity. Recalculate the estimated share value from residual operating income model only. Provide the sensitivity analysis results in a table.
• Explain how optimistic and pessimistic forecasts for the various assumptions are chosen.
• Identify and discuss the key assumptions that valuation is most sensitive to.
• Based on sensitivity analysis and the key factors that have significant impact on valuation, discuss
ing 2021)
Page 9 of 14
potential opportunities and challenges for the company to maintain or improve value.
Overall Report Quality (2 marks):
• The report should be readily comprehensible, condensed and within the word limit. Information should be collected from various reliable sources to inform analysis and references are properly cited. Tables and graphs should be used to effectively present information.