Based on the value driver assumptions provided, create pro-forma income statement (Cells Rows 25 - 39) and balance sheet for years 2xx1 through 2xx5. Assume cash and revolving credit as plugs.
Calculate cash flow provided by operating activities (CFO)
Calculate free cash flow using CFO.
Calculate free cash flow using EBIT
What is WACC?
What is the terminal value for FCFs for the period after 2xx5?
Calculate the value of the firm (enterprise value)
Calculate the share holder equity
Calculate the value of one share of equity
Conduct a sensitivity analysis on the effects of sales growth rate on stock value (B120:C130). How would you describe the effect?
ANSWER:
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?
ANSWER:
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.
What is the mean value of the stock price?
Would you buy the stock at the current price of $70.00? Why?
Explain the significance of the terminal value in your analysis and recommendation above.
Download:- MonteCarlo.xls