AFIN1002 Finance: Discounted Cash Flow Model

Questions:

Introduction of Dcf Model

In this assignment, Discounted Cash Flow (DCF) Model for firm valuation assumes that:

  • you can forecast the future 5 years’ cash flows and expect the following cash flows will grow by a perpetuity rate to the forever.
  • So, the timeline for all future cash flows is:

Perpetuity growth rate: g

OFCF5

OFCF4

OFCF3

OFCF2

OFCF1

5

4

3

2

1

0

Forever

  • The formula to get the present value of all future cash flows is:

Estimated Asset value (V0) =

                                         =

  • The method to estimate the share price is:

Estimated Equity value = Estimated Asset value × E/V ratio

Estimated Share price =

So, in this assignment, the DCF valuation model includes:

  • Forecasting the future Operating Free Cash Flow (OFCF) in Question 1.
  • Calculating the expected in Questions 2 and 3.
  • Applying the DCF formula to estimate the share price in Question 4.
  • Using Spreadsheet Skills to do analysis for the DCF model in Questions 6, 7 and 8.

Question 1: OFCF

  • Choose any one listed firm, please refer to lines 6-13 of “instructions” spreadsheet tab.
  • Copy your firm’s past financial data into “questions”, please refer to lines 15-23 of “instructions”.
  • Forecast your firm’s future financial data, please refer to lines 25-31 of “instructions”.
  • Please think more about the limits for the year 5 perpetuity growth rate in Q1a.
  • Reserve Bank of Australia is a very useful website to find some macroeconomic data.
  • Follow the question description and suggestions to do Q1a to Q1i.
  • The relevant formulas are shown in Topic 4 slides.
  • Calculate the OFCF in Q1j, the formula is:

Questions 2 and 3:

  • Get rf, betaE, and MRP from online sources, as suggested in Q2a to Q2c.
  • Use CAPM to calculate equity cost rE in Q2d. The formula is shown in Slide 43 of Topic 7.
  • Follow question description and suggestions to do Q3a to Q3g.
  • Calculate . The formula is shown in Slide 12 of Topic 5.

Question 4: Estimated Share Price

  • Question 4 is the calculation process of the DCF model to estimate the share price, please refer to the “Introduction of DCF Model” on the first page of this document.
  • Follow question description and suggestions to do Q4a to Q4j.

 Question 6: IRR

  • If you’ve got your formulas correct in Question 4, then you can do Question 6.
  • You need to find a new WACC in G29, which makes the estimated share price in G38 and the traded market share price in G39 equal.
  • Click “Data” – “What-If Analysis” – “Goal Seek” setting – “ok”

Note: (To value: xxx) is your firm’s traded market share price value.

  • Copy the new generated WACC in G29, then paste special by value into G52.
  • Copy G28 original WACC then paste special by value into G29, to overwrite the hard-copied G29.

 

Question 7: Sensitivity Analysis

  • If you’ve got your formulas correct in Question 4, then you can do Question 7.
  • First, set the top left corner equal to the estimated share price with the calculation formula, that means set “D62=G38”.
  • Then, set up different perpetuity growth rates in the yellow line E62-L62. Copy your certain perpetuity growth rate value L4 into the middle position (H62), then make the growth rates increase from left to right, maybe by 1% or whatever percent you think sensible.
  • Then, set up different WACC in the yellow column D63-D74. Copy the WACC value G29 into the middle position (D67), then make the WACC increase from top to bottom, maybe by 1% or whatever percent you think sensible.
  • Next, select the whole yellow table area of Question 7, from the left top corner D62 to the right bottom corner L74.
  • Ensure your Excel setting is good, Click “File” – “Options” – “Formulas” – tick “Automatic”
  • Click “Data” – “What-If Analysis” – “Data Table” setting – “ok”
  • Contains nice conditional formatting color shading: select and highlight the whole yellow table area, Click “Home” – “Conditional Formatting” – “Color Scales” – “Green-Yellow-Red Scales”

 

Question 8: Graph Analysis

  • If you’ve got Revenue, Net income, and OFCF in Question 1, then you can do Question 8.
  • Copy the data of Question 1 into the yellow table area of Question 8:

Time: D79-L79, change time to year

Revenue (C80): copy D5-L5, then paste special by value into D80-L80

Net income (C81): copy D6-L6, then paste special by value into D81-L81

OFCF (C82): copy H14-L14, then paste special by value into H82-L82

  • Select the above yellow data area, from the left top corner C79 to the right bottom corner L82
  • Click “Insert” – “Line” – first “2-D Line”
  • Move the above generated chart into the yellow area of Question 8.
  • Drag the chart box to make the whole chart bigger, almost in the whole yellow area.
  • Right click the blue revenue line – go “format data series” – Plot Series On “Secondary Axis”.
  • Put the label on each line. E.g., for the blue revenue line, click “Insert” – “Text” – “Text box”, label “Revenue (RHS)” with the same blue color. Similarly, put the orange “Net income (LHS)” label and the grey “OFCF (LHS)” label on the lines.
  • Delete the bottom legends, which looks ugly.
  • Change the chart title to be something sensible, e.g., “Ynames V.S. Xname”.
  • Add the unit dollars “$k” or “$m” into the top of the left and right axis.
  • Make the font a bit bigger for time axis, left axis, right axis, the line labels, and the chart title.
  • Finally, a nice graph looks like:

Question 5: Multiples Valuation Model

In this assignment, “Price-to-Sales” Multiples Model for firm valuation assumes that:

  • you will sell the stock based on the comparable firms’ average price-to-sales ratio in year 5, and get the terminal equity value TV5 (TV5 = Revenue5 × PS ratio).
  • So, the timeline for the future five years’ equity free cash flow (EFCF) is:

EFCF5 + TV5

EFCF4

EFCF3

EFCF2

EFCF1

5

4

3

2

1

0

  • The formulas to estimate the share price:

EFCF = OFCF −

TV5 = Revenue5 × PS ratio

Estimated Equity Value =

                                   

Estimated Share price =

Question 9: Tesla valuation method

  • Check “Tesla Model” tab and “APK Disclosure” tab to do Question 9.
Read less
QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER

Leave a Reply

Your email address will not be published. Required fields are marked *