Please show the work in excel. Thanks!

please ignore the second and the third photos becaus they are blurred

The Doctor is In 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 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 macine. 3) What is the NPV of this project? Clearly label your answer. Note that all of my inputsassumptions are up in the left hand corner. In the cakculations below, there are nothing but cell references. You want to get comfortable doing this I tried to make ny income statement PU simple Sales Cash Costs - Depreciation - Net. Pay some taxes and you have net after thes. Add back depreciation and you have cash flow 2 3 Machine cost 4 Useful Life 5 Machine Sold in Yeh 6 Aldepreciation 7 Salvage value 8 9 Annual production headphones) 10 Coupe leadphone 11 selling preget headphone 12 Other costs 13 14 tax rate 15 Cout of capital 16 17 Year Cash flow 18 Mach 19 0 20 1 21 2 23 1 Note the tax shield from the loss. As an accounting professor once told me as an undergrad, taxes make the good times worse and the bad times better Annual Profit/lous and cash flow Year 16 Sale Co dation O Drition 5 before 24 25 26 tas NPV DO 28 20 30 31 back Cash flow Year of maine See 34 35 36 lo Taxes Total from of mechi 87 Machine cont utile Machine Sold in Year Annual depreciation Salvage value Note that all of my input/assumptions are up in the left hand corner. In the calculations below, there are nothing but cell references. You want to get comfortable doing this Anal production headphones Coheadphone Selling Price per headphone Othco I tried to make my income statement simple. Sales - Cash Costs - Depreciation - Net. Pay some taxes and you have net atter tees, add back depreciation and you have cash flow. Note the tice shield from the loss. As an accounting professor once told me as an undergrad, taves make the good times worse and the bad times better Contact Annual Profit/ton and cash flow 7 Yew 3 ca Machine O Year 16 2 3 0 1 2 3 4 5 6 Costs die O Depreciation 5 Nettet NPV 0.00 Cashew 8 9 30 31 33 34 Year sale of machine Sale of machine Boote Les sale who Taxshield en los Total cash flow from of machine 86 37 B A 1 2 3 Machine cost 4 Useful Life 5 Machine Sold in Year 6 Annual depreciation 7 Salvage value 8 9 Annual production (headphones) 10 Cost per headphone 11 Selling price per headphone 12 Other costs 13 14 Tax rate 15 Cost of capital 16 17 Year Cash flow 18 Machine 19 0 20 1 21 2 22 3 23 4 24 5 25 6 26 27 NPV 198 0.00 Note that all of my inputs/assumptions are up in the left-hand corner. In the calculations below, there are nothing but cell references. You want to get comfortable doing this. I tried to make my income statement (P/L) simple. Sales - Cash Costs - Depreciation = Net. Pay some taxes and you have net after taxes. Add back depreciation and you have cash flow. Note the tax shield from the loss. As an accounting professor once told me as an undergrad, taxes make the good times worse and the bad times better. Annual Profit/Loss and cash flow Years 1-6 Sales Costs production Other Depreciation Net taxes Net after taxes Earnings before taxes Earnings Add back depreciation Cash flow Year 6 sale of machine Sale of machine Hook value at time of sale Loss on sale over book Tax shield on loss Total cash flow from sale of machine The Doctor is In 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 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 macine. 3) What is the NPV of this project? Clearly label your answer. Note that all of my inputsassumptions are up in the left hand corner. In the cakculations below, there are nothing but cell references. You want to get comfortable doing this I tried to make ny income statement PU simple Sales Cash Costs - Depreciation - Net. Pay some taxes and you have net after thes. Add back depreciation and you have cash flow 2 3 Machine cost 4 Useful Life 5 Machine Sold in Yeh 6 Aldepreciation 7 Salvage value 8 9 Annual production headphones) 10 Coupe leadphone 11 selling preget headphone 12 Other costs 13 14 tax rate 15 Cout of capital 16 17 Year Cash flow 18 Mach 19 0 20 1 21 2 23 1 Note the tax shield from the loss. As an accounting professor once told me as an undergrad, taxes make the good times worse and the bad times better Annual Profit/lous and cash flow Year 16 Sale Co dation O Drition 5 before 24 25 26 tas NPV DO 28 20 30 31 back Cash flow Year of maine See 34 35 36 lo Taxes Total from of mechi 87 Machine cont utile Machine Sold in Year Annual depreciation Salvage value Note that all of my input/assumptions are up in the left hand corner. In the calculations below, there are nothing but cell references. You want to get comfortable doing this Anal production headphones Coheadphone Selling Price per headphone Othco I tried to make my income statement simple. Sales - Cash Costs - Depreciation - Net. Pay some taxes and you have net atter tees, add back depreciation and you have cash flow. Note the tice shield from the loss. As an accounting professor once told me as an undergrad, taves make the good times worse and the bad times better Contact Annual Profit/ton and cash flow 7 Yew 3 ca Machine O Year 16 2 3 0 1 2 3 4 5 6 Costs die O Depreciation 5 Nettet NPV 0.00 Cashew 8 9 30 31 33 34 Year sale of machine Sale of machine Boote Les sale who Taxshield en los Total cash flow from of machine 86 37 B A 1 2 3 Machine cost 4 Useful Life 5 Machine Sold in Year 6 Annual depreciation 7 Salvage value 8 9 Annual production (headphones) 10 Cost per headphone 11 Selling price per headphone 12 Other costs 13 14 Tax rate 15 Cost of capital 16 17 Year Cash flow 18 Machine 19 0 20 1 21 2 22 3 23 4 24 5 25 6 26 27 NPV 198 0.00 Note that all of my inputs/assumptions are up in the left-hand corner. In the calculations below, there are nothing but cell references. You want to get comfortable doing this. I tried to make my income statement (P/L) simple. Sales - Cash Costs - Depreciation = Net. Pay some taxes and you have net after taxes. Add back depreciation and you have cash flow. Note the tax shield from the loss. As an accounting professor once told me as an undergrad, taxes make the good times worse and the bad times better. Annual Profit/Loss and cash flow Years 1-6 Sales Costs production Other Depreciation Net taxes Net after taxes Earnings before taxes Earnings Add back depreciation Cash flow Year 6 sale of machine Sale of machine Hook value at time of sale Loss on sale over book Tax shield on loss Total cash flow from sale of machine