Past Past Past Past Forecast Forecast Forecast Forecast Forecast Please fill the yellow cells with your answers. Answers elsewhere will not be marked.
Question Marks Year index -3 -2 -1 0 1 2 3 4 5 This cell should be N2. 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 from ilearn and refill your answers carefully.
Year (replace with your firm's year end dates) 30-Jun-17 30-Jun-18 30-Jun-19 30-Jun-20 30-Jun-21 30-Jun-22 30-Jun-23 30-Jun-24 30-Jun-25 Please keep this grey column clear, it's for the machine marking formulas.
Q1a 5 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)
Q1b 2 Revenue (For past: retrieve from P&L. For forecast: calculate based on growth rates)
Q1c 1 Net Income (For past: retrieve from P&L. For forecast: estimate based on forecast P&L done somewhere below line 100)
Q1d 1 Depr & amortisation expense (estimate based on forecast P&L done somewhere below line 100)
Q1e 1 Tangible and intangible capital assets, carrying amount, net of depreciation and amortisation (estimate based on forecast balance sheet done somewhere below line 100)
Q1f 4 CapEx (calculate based on above net capital assets and other items)
Q1g 1 Operating net working capital (estimate based on forecast balance sheet done somewhere below line 100)
Q1h 2 DeltaNOWC (calculate based on above operating working capital and other items)
Q1i 1 Interest expense (estimate based on forecast P&L done somewhere below line 100)
tc, corporate tax rate 0.3 0.3 0.3 0.3 0.3 0.3
Q1j 4 OFCF (calculate based on above items)
Q2a 2 rf (retrieve from online source of 10 year government bond yields)
Q2b 2 betaE (retrieve from reputable online source such as Reuters)
Q2c 2 MRP (retrieve from reputable online source such as Fernandez, de Apellániz and Acín (2020) https://ssrn.com/abstract=3560869
Q2d 2 rE (calculate based on CAPM)
Q3a 1 Debt (book value. Retrieve from balance sheet)
Q3b 1 rD draft estimate based on calculation of InterestExpenseOverPriorYear / BookDebtAtStartOfPriorYear
Q3c 1 rD draft estimate using alternative method such as comparable firm's IntExp/BookDebt, credit ratings or traded bond yields
Q3d 5 rD final estimate, used as an input into WACC after tax
Q3e 1 Equity (book value, retrieve from balance sheet)
Q3f 1 Equity (traded market value, retrieve from stock exchange)
Q3g 4 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.
Q3h 1 WACC after tax. Calculate using corporate tax rate, rD final estimate, and other data stated above using a formula.
Q3i 1 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.
Q4a 2 PV of OFCF for each year 1 to 4. Calculate using above data, ensure WACC after tax from Q3i is used.
Q4b 2 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.
Q4c 2 PV of the Terminal Value based on perpetuity. Calculate using above data
Q4d 2 Assets (model estimated value. Calculate using above data)
Q4e 2 Equity (model estimated value. Calculate using above data)
Q4f 1 Units of all above cash flows. Retrieve from financial statements. For example, if in millions, then type 1,000,000.
Q4g 1 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.
Q4h 2 Share price in dollars per one share (model estimated value based on perpetuity TV. Calculate based on above data)
Q4i 2 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)
Q4j 2 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: Three Similar Listed Firms' Price-to-Sales Ratios
Firm name and ticker code Annual sales (last reported, in same units as other items listed above) Equity market capitalisation (in same units as other items listed above) Price-to-sales ratio Data source or link
Q5a 1
Q5b 1
Q5c 1
Q5d 1 Arithmetic average price-to-sales ratio:
Q5e 3 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.
Q5f 3 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.
Q6 6 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'.
Q7 10 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.
Year 5 revenue growth rate
WACC after tax
Q8 10 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.
100
This cell should be C100. 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 from ilearn and refill your answers carefully. This cell should be N100. 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 from ilearn and refill your answers carefully.