hi this an excel assignment and i am not able to get question 6
RI AFF 210 - Assignment TWL X RI [r "' - r": ' - . 9 ._ g ,1 - :2 ~ :~ - e a u i w twain 6 9 C' & courses.ryerson.ca Low Battery Your Mac WiH sleep soon uniess piugged Apps RI Homepages/er.\" Netix 9 New Tab k ' aRgion du... Connecteclasszn C3 NeisonBrainrMyu imoavcweromlel- AFF 210 - Assignment Two - Fall 2019, nov 13, 2019 v [1 X Problem Six [15 marks) 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 3 years. The expected salvage of the machine at the end of the project is $300,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 $22 per widget. The production department, has indicated that the Variable cost per widget will be $14, The company has forecasted that the incremental xed costs associated with the project are $180,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 networking capital will he 12% of sales. The CCA rate is 30%, the tax rate is 34%, and the required rate of return is 10%. Note: All of Calculations should be rounded to the nearest dollar. a) Calculate the NPV of the project. Show all your work. b) Should the company purchase the new machine. Why? 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 xed 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. Therefore, you should decrease the number of units sold by 10%, then you should decrease the price per unit by 10%, next you should increase the variable cost per unit by 10%, and finally you should increase the xed cost by 10% 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 $19 $28 RI AFF 210 - Assignment TWL X RI [r "' - r": ' - t u a g ,l - :2 ~ :~ - e a u l in limb. 6 9 C' & courses.ryerson.ca Low Battery Your Mac Will sleep soon unless plugged Apps RI HomepagerRi/er.\" Netflix 9 New Tab k ' aRgion duw Connecteclasszn C3 NelsonBralnrMy', imoavcweromlel- AFF 210 - Assignment Two - Fall 2019, nov 13, 2019 v [1 X c) Your boss has a number 01 concerns regarding the project 'lneretore, 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. Your boss has also asked you to determine which input (units sold, price per unit, variable cost per unit, or xed 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. Therefore, you should decrease the number of units sold by 10%, then you should decrease the price per unit by 10%, next you should increase the variable cost per unit by 10%, and nally you should increase the xed cost by 10% 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 319 $28 Ryerson AFF 210 ASSIGNMENT Two Variable cost per unit $16 $10 Fixed Cost $220,000 $150,000
