Assignment Task
Q1 Prepare the projected income statement and balance sheet for the years 2024-2028 for your chosen company below row 98
Q2a4Revenue growth rate Q2b2Revenue tc, corporate tax rate
Q2c1Tangible, intangible and goodwill assets (carrying amount from balance sheet)
Q2d1Net operating working capital (from balance sheet)
Q2c0.5EBIT (from income statement)
Q2d1Income tax on EBITQ2e0.5Depreciation & amortisation expense (from income statement)
Q2f3Capital expenditureQ2g2Change in NOWC
Q2h4Free cash flow to the firm (FCFF)
Q3a2rf (retrieve from online source (e.g., Bloomberg or Reuters website) to get 10 year government bond yields)
Q3b2betaE (retrieve from Morningstar DatAnalysis Premium)
Q3c2MRP (retrieve from reputable online source such as Fernandez, Pablo et al (April 21, 2023), Available at SSRN: https://papers.ssrn.com/sol3/papers.cfm?abstract_id=4407839
Q3d2rE (calculate based on CAPM)
Q4a1Debt (book value. Retrieve from balance sheet)
Q4b1rD draft estimate based on calculation of InterestExpenseOverYear0 / BookDebtOfYear-1
Q4c1rD draft estimate using alternative method such as traded corporate bond yields available from Reserve Bank of Australia, or whatever you think sensible
Q4d2rD final estimate, used as an input into WACC after tax
Q4e0.5Equity (traded market value, retrieve from Morningstar DatAnalysis Premium)
Q4f0.5Equity (book value, retrieve from balance sheet)
Q4g4D / V final estimate. This debt-to-assets ratio will be used as input into WACC after tax. Calculate using the above debt book value and equity market value.
Q4h1WACC after tax. Calculate using corporate tax rate, rD final estimate, rE, D/V ratio, and other data stated above using a formula.
Q4i0.5Please `copy, paste special, value` the WACC after tax in the above cell to this question`s yellow cell, and base all calculations below on this hard-coded WACC after tax. This cell must not contain a formula because otherwise the Goal Seek process needed in
Q6 and the `Data Table` process needed to do a sensitivity analysis in
Q8 will not work.
Q5a2PV of FCFF for each year 1 to 5. Calculate using above data, ensure WACC after tax from
Q4i is used.Q5b2Long-run growth rate
Q5c2Terminal value as at year 5 based on perpetuity of year 5 FCFF growing at long-run growth rate forever. Calculate using above data.
Q5d2PV of the terminal valueQ5e2Enterprise value
Q5f0.5Cash (retrieve from balance sheet)
Q5g2Equity value (DCF model estimated value. Calculate using above data)
Q5h0.5Units of all above cash flows. Retrieve from financial statements. For example, if in millions, then type 1,000,000.
Q5i0.5Number of shares. Retrieve from online source or financial statements. Ensure consistent units with items above. For example, if the number of shares is 700 million, but your cash flows above are all in millions, then your number of shares here should be 700.
Q5j2Share price in dollars per one share (DCF model estimated share price. Calculate based on above data)
Q5i2Share price in dollars per one share (traded market share price. Retrieve from online source at the same recent data that the number of shares and market capitalisation of equity were found above)
Q5k2NPV in dollars of buying one share assuming DCF model is correct and market price is not correct. Calculate based on above data
Q63Find the WACC after tax that makes the market and DCF model-estimated share prices equal. In other words, find the IRR. Note that you will have to use Goal Seek to complete this. Using the IRR formula won`t work properly since the WACC in the terminal value will not be adjusted properly. When using Goal Seek, the `by changing cell` should be your hard-coded WACC after tax from
Q4i, not the formula from
Q4h. Once you`ve found your answer to this question using Goal Seek, copy this hard-coded `WACC after tax` from
Q4i into the yellow cell provided in this question, then overwrite Q4i`s yellow cell back to its original hard-coded value that matches your answer in Q4h, using `copy and paste by value`.
Q73Find the run-growth rate that makes the market and DCF model-estimated share prices equal. Note that you will have to use Goal Seek to complete this. Once you`ve found your answer to this question using Goal Seek, copy this hard-coded long-run growth rate from
Q5b into the yellow cell provided in this question, then overwrite Q4i`s yellow cell back to its original hard-coded value that matches your answer in Q4h, using `copy and paste by value`.
Q86Conduct a 2-dimensional sensitity analysis of your estimated share price (based on Question 5`s DCF model) by varying the WACC after tax (in the table`s left column) and the year long-run growth rate (in the table`s top row). Make the WACC numbers increase from smaller to bigger as they`re written from top to bottom, and make the growth rate numbers increase from smaller to bigger as they`re listed from left to right. Ensure that the table shows your base case share price bolded in the middle somewhere. WACC after tax
Q920Compare the estimated share price from your DCF model to the market share price and provide a recommendation to buy, hold or sell your chosen company`s shares, clearly stating why. Your recommendation should be supported by arguments that discuss the differences between the estimated share price from your DCF model and the market price with regard to the assumptions you have made in your DCF model and the risk analysis you performed in questions 6, 7 and 8 above. You should write your answer in the yellow text box below, and it should not be more than 300 words. 100 This cell should be C98. If it`s not, you`ve inserted rows or columns and the auto-marking formulas will not work which will result in a mark of zero. Start again by re-downloading this spreadsheet and refill your answers carefully.
Purpose
The purpose of this assessment is to apply the skills developed on discounted free cash flow models and financial statement modelling in weeks four and five to a company listed on the Australian Securities Exchange (ASX). In this assessment, you will develop your skills in financial modelling, which is a valuable skill sought by employers.
Outcomes addressed
This assessment addresses the following unit learning outcomes:
3. Apply financial theory to real world and personal financial decisions; and
4. Analyse real financial data to evaluate the well-being of companies.