Number 3

Required Information Sanders Company has an opportunity to manufacture and sell a new product for a five-year period. The company estimated the following costs and revenues for the new product: Cost of new equipment Initial working capital required Overhaul of the equipment after three years Salvage value of the equipment after five years $420,000 $ 95,000 $ 50,000 $ 30,000 Annual revenues and costs: Sales Variable expenses Pixed out-of-pocket operating costs $850,000 5500,000 $199,000 When the project concludes in five years the working capital will be released for investment elsewhere in the company Click here to download the Excel template, which you will use to answer the questions that follow Click here for a brief tutorial on Goal Seek in Excel 3. In the Excel template, using Goal Seek, calculate this investment's internal rate of return. If the company's hurdle rate is 18% would it be likely to accept or reject the investment? Why? 4. What is the project's net present value when using a discount rate of 18%? 5. If the company wants to achieve an 18% return on this investment, what is the maximum amount that it can spend each year on fixed out-of-pocket operating costs? Use Goal Seek to compute your answer. Note: The fixed out-of-pocket operating costs remain constant for all five years, therefore modifying cell C13 automatically updates cells D13 through G13. 6. If the investment in working capital increased from $95.000 to $115,000 would you expect Internal rate of return to crease decrease, or stay the same? No computations are necessary to answer this question 7. Refer to the original data. Using Goal Seek, calculate the internal rate of return if the investment in working capital increases from 595,000 to $115,000. Note: Be sure to return the fixed out-of-pocket operating costs to the original value of $199,000). Complete this question by entering your answers in the tabs below. Reg 3A Reg 38 Reg 4 Reg 5 Reg 6 Reg 7 Goal Seek: 2 3 5 1. 5 2. 3. Goal Seek is a tool within Microsoft Excel that performs a "what-if analysis" that enables you to learn what input values would be needed to achieve a desired goal or outcome. 1 When utilizing Goal Seek you should proceed as follows: Under the "Data" tab click on the "What-lf Analysis" drop down in the "Forecast" section 7 From that drop down select "Goal Seek" 8 A "Goal Seek" option box will appear with three values to be manipulated 9 LO a. The first input Is "Set cell" which is the cell with linked formulas that you want to change to a specific outcome. Here you will select the specified outcome cell or type in the cell value manually. 11 b. The second input is "To value which is the desired goal or outcome that you want the cell you selected in the first input 12 13 to be changed to For example, if you want to break even, you could set this value to 0 or if you wanted profit of $5,000 you could input 5000 14 c. The third and final input is "By changing cell" is the cell that contains the variable you want to change that is linked to 15 the formulaic output cell that was chosen in the "Set cell" input 16 17 Click OK 18 Now the "Set cell" should have been modified to the "To value you chose as well as the cell value that you designated as the 19 "By changing cella variable. 20 21 22 4. 5. 23 24 25 Goal Soak Template + Spotify Law A13 fx Rondout-of-pocket operating costs D Buon he medie Discontrat DON 1 2 1 4 S 5 $ Now (320,000 DRO DOO $ 150.000 6 Purchase of equipment investment in working capit #Overhaul of equipment 9 Savage value of them 20 Working capitalelse 11 S 12 Variable 13 hed out-of-pocket operating 14 Total flow 15 Discount factor 16 Present value of cash flow all 17 Net present a SUM 1166161 18 19 20 21 22 23 24 25 26 5 5 $ 500.000 1000 500,000 5 210.000 50,005 4500.000 200 000 150.000 1000 150.000 350.0005 15000001 200 000 150.000 1000 150.000 5 350,000 (500.000 200.000 100.000 1000 100.000 5 5 50.000 500.000 200.000 150.000 1000 150.000 $ 30.000 R0.000 350.000 OOOOO 200 000 260,000 1000 260.000 $