Financial Analysis and Valuation: DCF Model, and Sensitivity Analysis Assignment

Assignment Task

Question 1

• Revenue growth rate (For past: calculate based on past revenues. For forecast: provide reasonable estimate. Note that the year 5 growth rate will be used in the OFCF perpetuity formula. Ensure it's a reasonable choice)
• Revenue (For past: retrieve from P&L. For forecast: calculate based on growth rates)
• Net Income (For past: retrieve from P&L. For forecast: calculate based on growth rates, or estimate based on forecast P&L done somewhere below line 115)
• Depreciation & amortisation expense (calculate based on growth rates, or estimate based on forecast P&L done somewhere below line 115)
• Tangible and intangible capital assets, carrying amount (For past: retrieve from balance sheet. For forecast: calculate based on growth rates, or estimate based on forecast balance sheet done somewhere below line 115)
• CapEx (calculate based on above net capital assets and other items)
• Net operating working capital (For past: retrieve from balance sheet. For forecast: calculate based on growth rates, or estimate based on forecast balance sheet done somewhere below line 115)
• DeltaNWC (calculate based on above operating working capital and other items)
• Interest expense (calculate based on growth rates, or estimate based on forecast P&L done somewhere below line 115)
• tc, corporate tax rate (feel free to change this if you wish)
• OFCF (calculate based on above items)

 Question 2

• rf (retrieve from online source (e.g., Reserve Bank of Australia Website) to get 10 year government bond yields)
• betaE (retrieve from reputable online source such as Reuters)
• MRP (retrieve from reputable online source such as Fernandez, Pablo et al (June 6, 2021), Available at SSRN: https://ssrn.com/abstract=3861152 
• rE (calculate based on CAPM)

 Question 3

• Debt (book value. Retrieve from balance sheet)
• rD draft estimate based on calculation of InterestExpenseOverYear0 / BookDebtOfYear-1
• rD draft estimate using alternative method such as traded corporate bond yields available from Reserve Bank of Australia, or comparable firm's IntExp/BookDebt, or whatever you think sensible
• rD final estimate, used as an input into WACC after tax
• Equity (traded market value, retrieve from stock exchange)
• Equity (book value, retrieve from balance sheet)
• D / 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.
• WACC after tax. Calculate using corporate tax rate, rD final estimate, rE, D/V ratio, and other data stated above using a formula.
• Please '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 Q7 will not work. 

 Question 4

• PV of OFCF for each year 1 to 4. Calculate using above data, ensure WACC after tax from Q3i is used.
• Terminal value as at year 4 based on perpetuity of year 5 OFCF growing at year 5 revenue growth rate forever. Calculate using above data.
• PV of the Terminal Value based on perpetuity. Calculate using above data
• Assets (DCF model estimated value. Calculate using above data)
• Equity (DCF model estimated value. Calculate using above data)
• Units of all above cash flows. Retrieve from financial statements. For example, if in millions, then type 1,000,000.
• Number 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.
• Share price in dollars per one share (DCF model estimated share price. Calculate based on above data
• Share 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)
• NPV in dollars of buying one share assuming DCF model is correct and market price is not correct. Calculate based on above data

 Question 5

• Estimate the Terminal Value (TV) at year 5 if it was based on the 'arithmetic average price-to-sales ratio' found above rather than the perpetuity formula. 
• Estimate the share price of your firm based on this multiples-based valuation method.
• Remember to include the 5th year OFCF in the valuation as well, since the multiples valuation would normally be assumed to be the price just a moment after the cash flow (OFCF) at that time is paid

 Question 6

• Find the WACC after tax that makes the market and DCF model-estimated share prices equal. 
• In other words, find the IRR. The model-estimated share price to be made equal to the market share price should be the one using DCF formula from Q4h, not the price-to-sales multiples valuation method from Q5f.
• 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. 
• If using Goal Seek, the 'by changing cell' should be your hard-coded WACC after tax from Q3i, not the formula from Q3h.
• Once you've found your answer to this question using Goal Seek, copy this hard-coded 'WACC after tax' from Q3i into the yellow cell provided in this question, then 
• overwrite Q3i's yellow cell back to its original hard-coded value that matches your answer in Q3h, using 'copy and paste by value'.

 Question 7

Conduct a 2-dimensional sensitity analysis of your estimated share price (based on Question 4's DCF model) by varying the WACC after tax (in the table's left column) and the year 5 revenue 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. 

Question 8

On the same graph, show 3 lines for revenue and net income from time -3 to 5, and OFCF from time 1 to 5. 

Ensure that net income and OFCF are on the primary (left hand side) axis, while revenue is on the secondary (right hand side) axis.

Place your graph into the yellow area below.

This Accounting and Finance has been solved by our PhD Experts at My Uni Paper.