you have taken a job with a local manufacturing company. Your boss has asked you to analyze a potential new product, and

[answerhappygod]

you have taken a job with a local manufacturing company. Your
boss has asked you to analyze a potential new product, and to
recommend if the company should produce and sell the product.
Specifically, your boss wants you to prepare a spreadsheet in excel
that shows the free cash flows the product would generate, and
shows what the product’s net present value and internal rate of
return are and what your recommendation is.
Marketing information
Your company already has spent $150,000 to conduct market
research about the demand for the product, which indicates the
optimal wholesale price for the product would be $20.00 per unit,
based on the prices of similar products that competitors sell. The
market research also indicates that demand for the product would
last for five years. At a price of $20.00 per unit, the market
research suggests that sales would be 175,000 units in the first
year, and unit sales would increase 9% per year over the remaining
four years of the product’s life.
Production information
Your company’s production manager estimates manufacturing the
product would require a machine that costs $850,000 and falls in
the 3-year MACRS depreciation class. The machine’s expected salvage
value in five years is expected to be $150,000. The production
manager also estimates the product’s variable costs, consisting of
raw materials and labor, would be $15.00 per unit, and the annual
fixed costs excluding depreciation would be $400,000. He states the
product could be manufactured in a building your company owns,
which has no other use and cannot be sold.
Financial information
Your company’s stock price is $51.61 per share, the last annual
dividend was $1.16 per share, and market analysts who follow your
company’s stock expect the dividends to grow forever at a rate of
5% per year. The company’s beta is 1.5 and Treasury bills are
paying 1.97% per year. The company’s bonds have a par value of
$1,000, pay a coupon of 4.25% per year, semiannually, have 20 years
to maturity, and are trading at $988. The company’s treasurer
estimates that the new product would require a $350,000 increase in
net working capital. She also has told you the company’s target
capital structure is 40% debt and 60% equity, the company’s tax
rate is 25%, and she expects the stock market return over the next
few years will be 5.5% per year.