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 puchasing 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 $35,000$ headphones per year, and we are assuming $25,000$ units initially. Your marketing team believes that the new headphones should sell for $$199\mathrm{.}00$ a piece, while your engineers tell you that they will cost $$115\mathrm{.}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. $$137,$ please see the right section.

$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.

$6)$ How would you summarize the results of this analysis to someone higher up in the corporate hierarchy?