can you answer this question as a excel formula for me please?
Show all work to estimate your answer in the Excel tab for this and all related questions. Bizjak BioTech is evaluating a project to produce a COVID-19 vaccine from existing patents. The equipment needed to manufacture the vaccine will cost $500,000. It is expected to last for 3 years and it will be fully depreciated on a straight-line basis by the end of its life. Managers expect to incur retraining costs of $110,000 after tax, and will sell the equipment to a small generic drug maker at that time for $135,000 The vaccine is expected to average 250,000 units per year and be sold for $22 per unit the first year. Variable costs in the first year are estimated at $18.00 per unit and fixed costs of $185,000 are forecast for all years of the project. $100,000 in additional inventory will need to be purchased immediately; at the project end, it is expected that fifty percent of the inventory will be recovered. This is just one of many projects for the firm, so any losses can be used to offset gains on other firm projects. For projects of this type, the company uses a 13 percent nominal discount rate that includes inflation and faces a tax rate of 28 percent. Inflation in costs and prices is expected to average 3% in the second and third year of the project. In the field specified below provide your answer to the following question: What is the NPV of the project? For any credit, show all work in your Excel worksheet. In a new Excel worksheet tab, a) Answer the questions below and b) Place the company name at the top of the page. c) Use this information above to answer the following five questions. Show all inputs and steps that you used to arrive at your answers. Label each answer according to the question #. ie indicate clearly on your worksheet which section and input answers Question 1, Question 2, etc. Question 1: What is the initial outlay for the project? Question 2: What is the operating cash flow for each year of the project? Question 3: What is the amount of the terminal value (ie not including the operating cash flow) that occurs in the last year of the project life? Question 4: What is the NPV and MIRR of the project? Question 5: Use Goal Seek to evaluate how sensitive NPV is to inflation and one other input you choose. Based upon the results, is this project risky as it relates to either input? Explain. Show all work to estimate your answer in the Excel tab for this and all related questions. Bizjak BioTech is evaluating a project to produce a COVID-19 vaccine from existing patents. The equipment needed to manufacture the vaccine will cost $500,000. It is expected to last for 3 years and it will be fully depreciated on a straight-line basis by the end of its life. Managers expect to incur retraining costs of $110,000 after tax, and will sell the equipment to a small generic drug maker at that time for $135,000 The vaccine is expected to average 250,000 units per year and be sold for $22 per unit the first year. Variable costs in the first year are estimated at $18.00 per unit and fixed costs of $185,000 are forecast for all years of the project. $100,000 in additional inventory will need to be purchased immediately; at the project end, it is expected that fifty percent of the inventory will be recovered. This is just one of many projects for the firm, so any losses can be used to offset gains on other firm projects. For projects of this type, the company uses a 13 percent nominal discount rate that includes inflation and faces a tax rate of 28 percent. Inflation in costs and prices is expected to average 3% in the second and third year of the project. In the field specified below provide your answer to the following question: What is the NPV of the project? For any credit, show all work in your Excel worksheet. In a new Excel worksheet tab, a) Answer the questions below and b) Place the company name at the top of the page. c) Use this information above to answer the following five questions. Show all inputs and steps that you used to arrive at your answers. Label each answer according to the question #. ie indicate clearly on your worksheet which section and input answers Question 1, Question 2, etc. Question 1: What is the initial outlay for the project? Question 2: What is the operating cash flow for each year of the project? Question 3: What is the amount of the terminal value (ie not including the operating cash flow) that occurs in the last year of the project life? Question 4: What is the NPV and MIRR of the project? Question 5: Use Goal Seek to evaluate how sensitive NPV is to inflation and one other input you choose. Based upon the results, is this project risky as it relates to either input? Explain
