Based on the value driver assumptions provided, create pro-forma income statement (Cells Rows 25 - 39) and balance sheet (Rows 40 -57) for years 2xx1 through 2xx5. Assume cash and revolving credit as plugs.
- Calculate cash flow provided by operating activities (CFO) (Rows 65 - 73)
- Calculate free cash flow using CFO. (Row 79)
- Calculate free cash flow using EBIT (Rows 81 - 94)
- What is WACC? (B96)
- What is the terminal value for FCFs for the period after 2xx5? (B97)
- Calculate the value of the firm (enterprise value) (B99)
- Calculate the share holder equity (B100)
- Calculate the value of one share of equity (B101)
- Conduct a sensitivity analysis on the effects of sales growth rate on stock value (B120:C130). How would you describe the effect? (Double click this area and answer below)
- Conduct a Sensitivity analysis on the effects of Beta and LT growth rate of FCF on Enterprise value (B135 - G146). How would you describe the effects?
Monte Carlo simulation given the probability assumptions (use WorkSheet "Simulation")
- Copy the entire worksheet to a separate worksheet, "Simulation"
- Set the simulation setting: 5,000 iterations
- Use the following distributions of the input variables
Sales growth rate: Uniform (7%,9%)
Capital expenditure to sales ratio: Uniform (8%,12%)
Long-term growth rate of FCFs: Uniform (0%, 4%)
Dividend payout ratio: Triang(25%,30%,35%)
Company equity beta: Triang(1.2, 1.5,1.8
- Run the simulation. Using the tornado graph, identify the value drivers with the most impact and the least impact on the stock price.
ANSWER: (Write your answer in the space below)
- What is the mean value of the stock price?
ANSWER: (Write your answer in the space below)
- Would you buy the stock at the current price of $70.00? Why?
ANSWER: (Write your answer in the space below)
- Explain the significance of the terminal value in your analysis and recommendation above.
ANSWER: (Write your answer in the space below)
Download:- MonteCarlo.xls