EXCEL SPREADSHEET

New Product Analysis

You have recently graduated with a BBA degree, and you havetaken a job with a local manufacturing company. Your boss has askedyou to analyze a potential new product, and to recommend if thecompany should produce and sell the product.Specifically, your boss wants you to prepare aspreadsheet that shows the free cash flows the product wouldgenerate, and shows what the product’s net present value andinternal rate of return are and what your recommendationis.

Marketing information

Your company already has spent $125,000 to conduct marketresearch about the demand for the product, which indicates theoptimal wholesale price for the product would be $14.00 per unit,based on the prices of similar products that competitors sell. Themarket research also indicates that demand for the product wouldlast for five years. At a price of $14.00 per unit, the marketresearch suggests that sales would be 300,000 units in the firstyear, and unit sales would increase 5% per year over the remainingfour years of the product’s life.

Production information

Your company’s production manager estimates manufacturing theproduct would require a machine that costs $900,000 and falls inthe 3-year MACRS depreciation class. The machine’s expected salvagevalue in five years is expected to be $200,000. The productionmanager also estimates the product’s variable costs, consisting ofraw materials and labor, would be $12.00 per unit, and the annualfixed costs excluding depreciation would be $300,000. He states theproduct could be manufactured in a building your company owns,which has no other use.

Financial information

Your company’s stock price is $41.09 per share, the last annualdividend was $2.00 per share, and market analysts who follow yourcompany’s stock expect the dividends to grow forever at a rate of5.0% per year. The company’s beta is 1.6 and Treasury bills arepaying 2.4% per year. The company’s bonds have a par value of$1,000, pay a coupon of 6% per year, semiannually, have 10 years tomaturity, and are trading at $903. The company’s treasurerestimates that the new product would require a $350,000 increase innet working capital. She also has told you the company’s targetcapital structure is 40% debt and 60% equity, the company’s taxrate is 30%, and she expects the stock market return over the nextyear will be 8.0%.