Please answer parts d and e using excel and show formulas.

123 Inc. is considering purchasing a new machine. The machine will cost $3,500,000. The machine

will be used for a project that lasts 5 years. The expected salvage of the machine at the end of the

project is $200,000. The machine will be used to produce widgets. The marketing department has

forecasted that the company will be able to sell 280,000 widgets per year. The marketing

department believes that the company will be able to charge $25 per widget. The production

department, has indicated that the variable cost per widget will be $10. The company has forecasted

that the incremental fixed costs associated with the project are $280,000. The company believes that

the project will require an initial investment in operating net working capital of $90,000. Thereafter,

the investment in operating net working capital will be 10% of sales.

The CCA rate is 30%, the tax rate is 25%, and the required rate of return is 10%.

a) Calculate the NPV of the project.

b) Using net present value (NPV) calculation, determine if the company should purchase the

new machine. Show all work.

c) 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.

d) 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 10% 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.

e) 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	225,000	350,000
Price Per Unit	$18	$30
Variable Cost per Unit	$12	$7
Fixed Cost	$320,000	$200,000