AFIN1002 Finance: Discounted Cash Flow Model

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

Estimated Asset value (V₀) =

                                         =

• 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 TV₅ (TV₅ = Revenue₅ × PS ratio).
• So, the timeline for the future five years’ equity free cash flow (EFCF) is:

• The formulas to estimate the share price:

EFCF = OFCF −

TV₅ = Revenue₅ × 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.