50.1K

Verified Solution

Link Copied!

SOLVE USING EXCEL. A company is considering estimating its stock price using the Pro Forma method combined with the Discounted Cash Flow $($DCF$)$ model. The following financial projections for the next five years are provided:

Year $1$: Revenue: $$500,000$; EBIT: $$60,000$; Depreciation: $$10,000$; Capital Expenditures: $$20,000$; Change in Working Capital: $$5,000$

Year $2$: Revenue: $$550,000$; EBIT: $$70,000$; Depreciation: $$12,000$; Capital Expenditures: $$25,000$; Change in Working Capital: $$6,000$

Year $3$: Revenue: $$605,000$; EBIT: $$82,000$; Depreciation: $$14,000$; Capital Expenditures: $$30,000$; Change in Working Capital: $$7,000$

Year $4$: Revenue: $$665,500$; EBIT: $$95,000$; Depreciation: $$16,000$; Capital Expenditures: $$35,000$; Change in Working Capital: $$8,000$

Year $5$: Revenue: $$732,050$; EBIT: $$110,000$; Depreciation: $$18,000$; Capital Expenditures: $$40,000$; Change in Working Capital: $$9,000$

Additional information:

Tax rate: $30\%$

WACC: $10\%$

Terminal growth rate after Year $5$: $3\%$

Current debt: $$200,000$

Cash: $$50,000$

Additional information:

Tax rate: $30\%$

WACC: $10\%$

Terminal growth rate after.Year $5$: $3\%$

Current debt: $$200,000$

Cash: $$50,000$

Number of shares outstanding: $100,000$

Calculate the Free Cash Flow $($FCF$)$ for each of the next five years.

Estimate the Terminal Value at the end of Year $5.$

Calculate the present value of the Free Cash Flows and Terminal Value.

Determine the equity value and estimate the stock price.

Answer & Explanation Solved by verified expert

See Answer