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.

We always make sure that writers follow all your instructions precisely. You can choose your academic level or professional level, and we will assign a writer who has a respective degree.

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

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.

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.A 100% refund is guaranteed.

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.

Tutorpro 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.

Tutorpro Homework Help Services

At Tutorpro, we have top rated masters and PhD writers who will help you tacke that homework and score A+ grade. Tutorpro services covers all levels of education : high school, college, university undergraduate, masters and PhD academic level.

Essays

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.

Admissions

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.

Editing

Tutorpro 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.

Revision

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. This is free because we want you to be completely satisfied with the service offered.