Please do it on excel and show the formulas!

AFF 210 ASSIGNMENT TwoO WINTER 2019 Problem Six (15 marks) 123 Inc. is considering purchasing a new machinc The machinc will cost $2,500,000. The machine will bc used for a projcct that lasts 4 ycars. Thc cxpected 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 180,000 widgets per year. The marketing department believes that the company will be able to charge $12 per widget. The production department, ha:s indicated that the variable cost per widget will be S5. The company has forecasted that the incremental fixed costs associated with the project arc $80,000. The company bclicves that the project will rcquirc an initial investment in operating nct working capital of $60,000. Thercaftcr, thc investment in operating net working capital will be 10% of sales The CCA rate is 30%, the tax rate is 26%, and the required rate of return is 10%. a) UUsing net present value (NPV) calculation, determine if the company should purchase the new machine. Show all work. b) 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 cach ycar for the NPV to be greater than zero c) 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 lorecasting risk d) Finally, your boss has asked you to perform a scenario analysis. Therelore, 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 Units sold Pricc per unit Variable cost per unit Fixed Cost Pessimistic 125,000 $10 $7 $120,000 Optimistic 200.000 $15 $4 $60,000 Ryerson Page 7 of7 AFF 210 ASSIGNMENT TwoO WINTER 2019 Problem Six (15 marks) 123 Inc. is considering purchasing a new machinc The machinc will cost $2,500,000. The machine will bc used for a projcct that lasts 4 ycars. Thc cxpected 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 180,000 widgets per year. The marketing department believes that the company will be able to charge $12 per widget. The production department, ha:s indicated that the variable cost per widget will be S5. The company has forecasted that the incremental fixed costs associated with the project arc $80,000. The company bclicves that the project will rcquirc an initial investment in operating nct working capital of $60,000. Thercaftcr, thc investment in operating net working capital will be 10% of sales The CCA rate is 30%, the tax rate is 26%, and the required rate of return is 10%. a) UUsing net present value (NPV) calculation, determine if the company should purchase the new machine. Show all work. b) 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 cach ycar for the NPV to be greater than zero c) 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 lorecasting risk d) Finally, your boss has asked you to perform a scenario analysis. Therelore, 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 Units sold Pricc per unit Variable cost per unit Fixed Cost Pessimistic 125,000 $10 $7 $120,000 Optimistic 200.000 $15 $4 $60,000 Ryerson Page 7 of7