Please answer 1-3 in excel, showing all answers and all formulas. Please use the chart in 2nd piture shown below to set up the formatting and answers.
Part A: The Long-Life Company has a new vaccine. The company estimates that it has a 10-year monopoly on 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, and zero salvage value. The machines are depreciated using the 7-year MACRS depreciation schedule. Each machine is capable of producing 75,000 vaccines annually. Annual fixed costs for producing the vaccine are $35 million, and the variable cost per vaccine is $1,000. Long-life plans to sell the vaccine at a price of $1,500 per vaccine. The company's discount rate for this type of vaccine is 15%, and its corporate tax rate is 30%. Use the model template and MACRS data in "HW Ch4_Template_FIN 360 to answer the questions below. Along with your completed model, submit a write-up that answers parts 4-6. 1. Write a formula in cell B15 to calculate the number of machines Long-Life will need to purchase to meet expected demand. Hint: The ROUNDUP formula may be helpful. 2. If Long-Life expects to sell 250,000 vaccines each year, what are the NPV and IRR of the product over its 10-year life? 3. Create a two-way data table to analyze the sensitivity of NPV to the number of vaccines sold annually and the discount rate. Assume the discount rate ranges between 10% and 25%, by increments of 3%, and annual sales takes on one of the following values: 100300K in increments of 40k 4. Using the results from part 3, make an argument for the optimal number of vaccines to produce. 5. Set annual vaccines sold to a value of 200,000 and use GOAL SEEK to calculate the number of breakeven units, i.e., number of units sold that makes the NPV =0. 6. Repeat part (e), but first set annual vaccines sold to a value of 100,000. Explain your results. \begin{tabular}{|l|c|c|c|} \hline Year & 3 -Year & 5-Year & 7 -Year \\ \hline 1 & 33.33% & 20.00% & 14.29% \\ \hline 2 & 44.44% & 32.00% & 24.49% \\ \hline 3 & 14.81% & 19.20% & 17.49% \\ \hline 4 & 7.41% & 11.52% & 12.49% \\ \hline 5 & & 11.52% & 8.93% \\ \hline 6 & & 5.76% & 8.92% \\ \hline 7 & & & 8.93% \\ \hline 8 & & & 4.46% \\ \hline \end{tabular} Part A: The Long-Life Company has a new vaccine. The company estimates that it has a 10-year monopoly on 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, and zero salvage value. The machines are depreciated using the 7-year MACRS depreciation schedule. Each machine is capable of producing 75,000 vaccines annually. Annual fixed costs for producing the vaccine are $35 million, and the variable cost per vaccine is $1,000. Long-life plans to sell the vaccine at a price of $1,500 per vaccine. The company's discount rate for this type of vaccine is 15%, and its corporate tax rate is 30%. Use the model template and MACRS data in "HW Ch4_Template_FIN 360 to answer the questions below. Along with your completed model, submit a write-up that answers parts 4-6. 1. Write a formula in cell B15 to calculate the number of machines Long-Life will need to purchase to meet expected demand. Hint: The ROUNDUP formula may be helpful. 2. If Long-Life expects to sell 250,000 vaccines each year, what are the NPV and IRR of the product over its 10-year life? 3. Create a two-way data table to analyze the sensitivity of NPV to the number of vaccines sold annually and the discount rate. Assume the discount rate ranges between 10% and 25%, by increments of 3%, and annual sales takes on one of the following values: 100300K in increments of 40k 4. Using the results from part 3, make an argument for the optimal number of vaccines to produce. 5. Set annual vaccines sold to a value of 200,000 and use GOAL SEEK to calculate the number of breakeven units, i.e., number of units sold that makes the NPV =0. 6. Repeat part (e), but first set annual vaccines sold to a value of 100,000. Explain your results. \begin{tabular}{|l|c|c|c|} \hline Year & 3 -Year & 5-Year & 7 -Year \\ \hline 1 & 33.33% & 20.00% & 14.29% \\ \hline 2 & 44.44% & 32.00% & 24.49% \\ \hline 3 & 14.81% & 19.20% & 17.49% \\ \hline 4 & 7.41% & 11.52% & 12.49% \\ \hline 5 & & 11.52% & 8.93% \\ \hline 6 & & 5.76% & 8.92% \\ \hline 7 & & & 8.93% \\ \hline 8 & & & 4.46% \\ \hline \end{tabular}
