Problem Four (20 marks) please show excel formulas!

ABC Inc. is considering purchasing a new machine. The machine will cost $15,500,000. The machine will be used for a project that lasts 4 years. The expected salvage of the machine at the end of the project is $1,600,000. The machine will be used to produce widgets. The manufacturing department has forecasted that the company will be able to sell 480,000 widgets per year. The manufacturing department believes that the company will be able to charge $38 per widget. The variable costs per widget are expected to be $24. The incremental fixed costs per year are expected to be $150,000. The company believes that the project will require an initial investment in operating net working capital of $240,000. Thereafter, operating net working capital will increase by $20,000 per year to the end of the project. Assume the asset class remains open.

The CCA rate is 24%, the tax rate is 34%, and the required rate of return is 8%.

1. Using net present value (NPV) calculation, determine if the company should purchase the new machine. Show all work.
2. Your boss has a number of concerns regarding the project. Therefore, she has asked you to determine the number of units that the company must sell each year for the NPV to be greater than zero.
3. Your boss has also asked you to determine which input (units sold, price per unit, variable cost per unit, or fixed cost) has the greatest forecasting risk. Therefore, your boss has asked you to do a sensitivity analysis. Your boss wants you to vary the input forecast by 5% and determine the impact on the NPV of the project. Based on this analysis you are to determine which input has the greatest forecasting risk.
4. Finally, your boss has asked you to perform a scenario analysis. Therefore, your boss has asked to include two new scenarios. A pessimistic scenario and an optimistic scenario. Your boss wishes to know the NPV of the project under these two additional scenarios. The value of the inputs for each scenario are shown in the table below.

	Pessimistic	Optimistic
Units sold	380,000	700,000
Price per unit	$32	$48
Variable cost per unit	$28	$18
Fixed Cost	$250,000	$100,000