Create an excel model to answer the following: Assume there is a company with (all next year’s estimates): EBIT of $2,00

[answerhappygod]

Create an excel model to answer the following:
Assume there is a company with (all next year’s estimates):
EBIT of $2,000,000.
A tax rate of 20%
Depreciation expenses of $150,000.
Capital expenditures of $200,000.
Net Working capital last year of $600,000 and this coming year
of $660,000.
->What is this company’s free cash flow?
Assume the company has capital components:
Debt of $5,000,000. Assume if the company were to
issue debt or take out a loan the interest cost would be 6%.
40,000 preferred share outstanding trading at $25/per share and
a $2.00 annual dividend.
The book value of Common equity is $10,000,000. Their common
shares trade at $50/per share in the market and there are 500,000
shares outstanding. Assume the company has a beta of
1.2. The risk-free rate is 3% (on 10-year US Treasuries)
and the market risk premium is 5.50%.
->What is this company’s WACC?
->What is this company’s intrinsic enterprise value (total
Firm Value) assuming free cash flow is expected to grow at a 4%
rate into perpetuity? (looking for the FCF valuation—not the
current market value of all the capital components!)
->What is this company’s “intrinsic value” per common share
based on your estimate above?
->Based on your analysis, do you believe the common shares
are over or under valued?
EC:
->Create a two variable data table in excel by varying the
growth rate in free cash flow from 2% to 6% in .50% increments and
risk free rate from 1% to 5% in .50% increments to show the impact
on your expected stock price above.
-Using no more than a paragraph, from a macro
perspective (do not worry about this individual company, think
of the market as a whole), describe how the analysis above shows
the classic trade-off between growth versus inflation (as reflected
by higher rates) in valuing equities.