### 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 |

OFCF |

OFCF |

OFCF |

OFCF |

OFCF |

5 |

4 |

3 |

2 |

1 |

0 |

Forever |

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

Estimated Asset value (V_{0}) =

=

- 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
_{5}(TV_{5}= Revenue_{5}× PS ratio). - So, the timeline for the future five years’ equity free cash flow (EFCF) is:

EFCF |

EFCF |

EFCF |

EFCF |

EFCF |

5 |

4 |

3 |

2 |

1 |

0 |

- The formulas to estimate the share price:

EFCF = OFCF −

TV_{5} = Revenue_{5} × 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.

- Assignment status: Already Solved By Our Experts
*(USA, AUS, UK & CA PhD. Writers)***CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS**

**NO PLAGIARISM**– CUSTOM PAPER