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: estimate based on forecast P&L done somewhere below line 100)
Depr & amortisation expense (estimate based on forecast P&L done somewhere below line 100)
Tangible and intangible capital assets, carrying amount, net of depreciation and amortisation (estimate based on forecast balance sheet done somewhere below line 100)
CapEx (calculate based on above net capital assets and other items)
Operating net working capital (estimate based on forecast balance sheet done somewhere below line 100)
DeltaNOWC (calculate based on above operating working capital and other items)
Interest expense (estimate based on forecast P&L done somewhere below line 100)
tc, corporate tax rate
OFCF (calculate based on above items)
rf (retrieve from online source of 10 year government bond yields)
betaE (retrieve from reputable online source such as Reuters)
MRP (retrieve from reputable online source such as Fernandez, de Apellániz and Acín (2020) https://ssrn.com/abstract=3560869
rE (calculate based on CAPM)
Debt (book value. Retrieve from balance sheet)
rD publish estimate based on calculation of InterestExpenseOverPriorYear / BookDebtAtStartOfPriorYear
rD publish estimate using alternative method such as comparable firm’s IntExp/BookDebt, credit ratings or traded bond yields
rD final estimate, used as an input into WACC after tax
Equity (book value, retrieve from balance sheet)
Equity (traded market value, retrieve from stock exchange)
D / V final estimate. Calculate using the above data you think most appropriate. This debt-to-assets ratio will be used as input into WACC after tax.
WACC after tax. Calculate using corporate tax rate, rD final estimate, 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 for Q7 will not work.
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 (model estimated value. Calculate using above data)
Equity (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 (model estimated value based on perpetuity TV. Calculate based on above data)
Share price in dollars per one share (traded market value. 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 model is correct and market price is not correct. Calculate based on above data
Undertake a multiples valuation using this table:
Estimate the terminal value (TV) at year 5 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 TV.
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.
Find the WACC after tax that makes the market and 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 the perpetuity formula Terminal Value (TV) from Q4h, not the price-to-sales ratio TV share price from Q5f.
Note that you will have to use Goal Seek or Solver 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’.
Conduct a 2-dimensional sensitity analysis of your share price (model estimated based on perpetuity TV) 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).
Ensure that the table shows your base case share price bolded in the middle somewhere.
WACC after tax
On the same graph, show 3 lines for revenue and net income from time -4 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.
Why Homework Market
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account.
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. We will always strive to deliver on time.
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text.
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on essay at night. Sleep tight, we will cover your back. We offer all kinds of essay writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied.