Please solve this question to use the Excel spreadsheet, and list how to arrive to each answer(FORMULATEXT). Thanks.

After graduating, you have developed a brilliant new widget. You sell your widgets for $6. You need to buy a machine to produce your widgets. You are considering two machines:

Machine A:

• Cost $2,000,000
• Annual fixed cost per machine: $250,000; Variable cost per unit $1.40
• Annual production capacity: 300,00 widgets (i.e., you cannot produce more than 300,000 widgets)
• Market life = 5 years; terminal value =0

Machine B:

• Cost $6,000,000
• Annual fixed cost per machine: $25,000; Variable cost per unit $0.40
• Annual production capacity: 600,00 widgets
• Market life = 5 years; terminal value = $200,000

Additional Details:

• You have already had an inspector evaluate Machine A for $2,000 and Machine B for $6,000.
• Both machines can be depreciated using straight-line depreciation over a 5-year period. Book value should =0 at the end of 5 years.
• The tax rate is 30%
  • If Net Income is negative, assume a tax of 0 (i.e. the government will not pay you if you lose money, and there are no tax-loss carry forwards)
• The discount rate is 6%
• You expect to sell 400,000 widgets (if you are able to produce that many)
  • Assume you can only buy one machine

Question:

1. Compute the NPV and IRR of both projects.
2. Which project should you take according to the NPV rule?
3. Which project should you take according to the IRR rule?
4. Based on your answer to question B and C, explain which project you should take. If your NPV and IRR rule give different rankings, explain why they give different rankings (you answer should relate specifically to this example), and why you chose one criteria over the other.
5. Compare the differential NPV of Machine B and Machine A as the number of widgets produced varies from 200,000 to 700,000 in increments of 100,000.
6. Intuitively explain why the differential is so sensitive to level of widget production. Your explanation should include two factors.