Question

New Product Analysis You have recently graduated from the University of North Georgia with a BBA degree, and you have taken a job with a local manufacturing company. Your boss has asked you to analyze n potential new product, and to recommend if the company should produce and sell the product. Specifically, your boss wants you to prepare a spreadsheet 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 $225,000 to conduct market research about the demand for the product, which indicates the optimal wholesale price for the product would be $15.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 $15.00 per unit, the market research suggests that sales would be 200,000 units in the first year, and unit sales would increase 6.5% 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 $975,000 and falls in the 3-year MACRS depreciation class. The machine's expected salvage value in five years is expected to be $250,000. The production manager also estimates the product's variable costs, consisting of raw materials and labor, would be $12.50 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. Financial information Your company's stock price is $40.23 per share, the last annual dividend was $1.76 per share, and market analysts who follow your company's stock expect the dividends to grow forever at a rate of 4.0% per year. The company's beta is 1.4 and Treasury bills are paying 1.87% per year. The company's bonds have a par value of $1,000, pay a coupon of 5.5% per year, semiannually, have 15 years to maturity, and are trading at $1,048. The company's treasurer estimates that the new product would require a $300,000 increase in net working capital. She also has told you the company's target capital structure is 60% debt and 40% equity, the company's tax rate is 28%, and she expects the stock market return over the next year will be 6.5%

Answer 1

Answer:

I would recommend not to produce and sell new product because this has negative NPV and IRR.

  

solution 3 - Excel (Product Activation Failed) Sign in Х File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do & Share ΣAutoSurm - Calibri 16 VAR ab Wrap Text Ie> H General % Cut TEE Copy Format Painter Ay Fill - Paste BI U και - > A- EE 3 Merge & Center - T,% Fri 08 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter - Select- Editing Clipboard Font Alignment Number Cells C9 X f B D E F G Н. 1 K L M 15 4 Sales price per unit 5 Variable Cost (Raw Material and labor) 6 Contribution margin per unit 12.5 2.5 7 8 Depreciation is a non-cash expense so it is not considered in the calculation below. But if tax rate is given we need to subtract depreciation to find EBT 9 and after EAT is calculated add back the depreciation 10 Time line 0 1 2 3 4 5 11 Initial Investment -975000 12 Sales in unit (prev. year sale * (1+6.5%) 200000 213000 226845 241590 257294 13 Total Contribution margin (2.5*Sales in unit) 500000.00 532500.00 567112.50 603975.00 643235.00 14 Fixed Cost excluding Depreciation 400000 400000 400000 400000 400000 15 Net cash flow -975000 100000.00 132 500.00 167112.50 203975.00 243235.00 16 PV @ 6.5% 685147.54 =NPV(6.5%,D15:H15) 17 NPV (-975000+685147.54) -289852.46 18 19 IRR -3.99% =IRR(C15:H15) Activate Windows Go to Settings to activate Windows, baloon payment breakeven withdrawal sum reported cash Sheet1 new machine npv + W 100% Type here to search j a ) ENG 13:08 01-08-2020