Question: How to solve this in Excel?
Pick a stock of your choice which you think is likely to be in a high-growth stage and which has publicly-available financial statements for fiscal year 2016. From financial statements, obtain Total Revenue, EBIT, Capital Expenditures, Net Working Capital, Depreciation, and the Debt-to-Equity ratio (use Long-term debt as a measure of total debt) for fiscal year 2016. Make sure the company has positive debt and positive EBIT.
The income statement numbers should be for the entire year, not just for the fourth quarter. (Note: possible sources for financial statements include the company's website, finance.yahoo.com, or the Securities and Exchange Commission (www.sec.gov).)
Assume that the stock will be in a high growth stage over the next 8 years (2017-2024), after which it will reach a stable, lower-growth phase in year 9 (year 2025), which will last into the indefinite future. In addition, assume the following parameters:
The tax rate = 34%.
T-bill rate = 6.5 percent
Market risk premium = 5.5 percent.
High-growth phase: Expected growth rate in EBIT, Capital Expenditures, Revenues, NWC, and Depreciation: 7.5%.
Equity Beta during the high growth period: 1.34.
Cost of debt = 8.75%
Debt-to-equity stays constant over the high-growth phase.
Stable growth phase: Expected growth rate in FCFF = 4.5%
Equity Beta during stable growth phase: 1.12
Cost of debt = 7.5%
Debt-to-equity ratio in the stable growth phase will fall to 60% of what it was in the high-growth phase
Capital expenditures are offset by depreciation.
Use the FCFF approach to calculate the enterprise value of the firm, the equity value of the firm, and the intrinsic share value as of the end of 2016.