The large computer company you work for just purchased a leading headphone manufacturer. As part of the merger, you have been put in charge of evaluating their manufacturing process. At the time of the merger, the headphone company was considering purchasing a new plastic mold for $5,000,000. Accounting rules say that this machine should be depreciated over 10 years to a zero salvage value. However, you anticipate selling the machine in Year 6 for $100,000 to make room for newer headphone models. This new machine can produce up to 25,000 headphones per year. Your marketing team believes that the new headphones should sell for $199.00 a piece, while your engineers tell you that they will cost $115.00 each to produce. You anticipate spending $50,000 per year on advertising the new headphones. Your firm has a tax rate of 35% and a cost of capital of 14%
1) Put together a mini-income statement for Years 1-6. In other words, start with sales and end up with net profit after taxes for each year.
2) Determine the appropriate cash flows each year, taking into account any adjustments for depreciation and the Year 6 sale of the machine. Include any tax shield generated when selling the machine.
3) What is the NPV of this project? Clearly label your answer.
4) Use goal seek to find the minimum price that allows you to break even. Assume that you sell the same 25,000 headphones at that price.
5) Create a data table showing how NPV changes with headphone price and quantity sold. let headphone price vary in a column from $179 to $239 in $10 increments. Let quantity vary in a row from 15,000 to 35,000 in 5,000 increments. Try using conditional formatting (on the home tab) to highlight positive NPV combinations in green.