Please Answer in Excel

The Long-Life Company has a new vaccine. The company estimates that it has a 10-year monopoly for the production of the vaccine, and it is trying to estimate how many vaccines it should try to sell annually. Machines to produce the new vaccine cost $70 million, have a 5-year life, straight-line depreciation, and a zero salvage value. Each machine is capable of producing 75,000 doses annually. Annual fixed costs for producing the vaccine are $50 million, and the variable costs per dose is $1,000. The company's discount rate for this type of vaccine is 15%, and its corporate tax rate is 30%. The total market for the vaccine could be as high as 1,000,000 annually. (Hint: since the machine has a 5-year life. Consider purchasing new machines at the end of year 5.) 1) If the annual sales are 200,000, what will be the NPV and IRR of the product over its 10-year life? (10 points) 2) Build a data table to show the relation between annual vaccine sales and the NPV. Set up the discount rate in the column from 10% to 30% in increments of 2%. Set up the annual vaccine sales in the row from 100000 to 900000 in increments of 100000. (5 points) The Long-Life Company has a new vaccine. The company estimates that it has a 10-year monopoly for the production of the vaccine, and it is trying to estimate how many vaccines it should try to sell annually. Machines to produce the new vaccine cost $70 million, have a 5-year life, straight-line depreciation, and a zero salvage value. Each machine is capable of producing 75,000 doses annually. Annual fixed costs for producing the vaccine are $50 million, and the variable costs per dose is $1,000. The company's discount rate for this type of vaccine is 15%, and its corporate tax rate is 30%. The total market for the vaccine could be as high as 1,000,000 annually. (Hint: since the machine has a 5-year life. Consider purchasing new machines at the end of year 5.) 1) If the annual sales are 200,000, what will be the NPV and IRR of the product over its 10-year life? (10 points) 2) Build a data table to show the relation between annual vaccine sales and the NPV. Set up the discount rate in the column from 10% to 30% in increments of 2%. Set up the annual vaccine sales in the row from 100000 to 900000 in increments of 100000. (5 points)