Muskogee Hospital is investigating the possibility of investing in new dialysis equipment. Two local manufacturers of...

Transcribed Image Text:

Muskogee Hospital is investigating the possibility of investing in new dialysis equipment. Two local manufacturers of this equipment are being considered as sources of the equipment. After-tax cash inflows for the two competing projects are as follows: Year Puro Equipment Briggs Equipment 1 $320,000 $120,000 2 280,000 120,000 3 240,000 320,000 4 160,000 5 120,000 400,000 440,000 Both projects require an initial investment of $560,000. In both cases, assume that the equipment has a life of 5 years with no salvage value. Required: Download Excel spreadsheet Round present value calculations and your final answers to the nearest dollar. 1. Assuming a discount rate of 12%, compute the net present value of each competing project. Puro equipment: 289,530 Briggs equipment: 374,453 Since the NPV of the Briggs equipment is than that of the Puro equipment, Briggs should chosen. The data analytics types are 2. A third option has surfaced for equipment purchased from an out-of-state supplier. The cost is also $560,000, but this equipment will produce even cash flows over its 5-year life. What must the annual cash flow be for this equipment to be selected over the other two? Assume a 12% discount rate. 155,349 per year 3. The following information is provided for Year 3 for the Puro equipment. The cell row is for an Excel spreadsheet. Cell: B5 C5 D5 E5 F5 Data Item Data Value Year 3 Cash flow Discount Factor Present Value 240,000 ? ? Discount rate 0.12 a. Using the cell notation given, prepare and show the written versions of the Excel equations that you used to provide the derived data for the question marks, where the discount factor (D5) is rounded to five decimal places and the present value (E5) is the product of cash flow and the discount factor is rounded to the nearest dollar. Discount Factor: Present Value: b. In Excel, following the pattern of Part 3a, create similar rows that run consecutively for all project years (Years 0 through 5). For the Puro project with the Year 0 row starting at Cell B2, Year 1 at Cell B3, Year 2 at Cell B4, etc. However, use F5 (the discount rate) as a data source for all rows. Finally, calculate the NPV using a summation equation for the Present Value column. c. Create a similar set of Excel rows, columns, and equations for the Briggs equipment project. d. The finance manager believes that cash flows of $260,000 per year are a better estimate for the Puro equipment than the original forecast. They also believes that the required rate of return should be 14%. Using the Excel NPV program from Parts 3b and 3c, calculate the NPV for each competing investment using the new cash forecast (for Puro) and a discount rate of 14%. Puro: Briggs: Muskogee Hospital is investigating the possibility of investing in new dialysis equipment. Two local manufacturers of this equipment are being considered as sources of the equipment. After-tax cash inflows for the two competing projects are as follows: Year Puro Equipment Briggs Equipment 1 $320,000 $120,000 2 280,000 120,000 3 240,000 320,000 4 160,000 5 120,000 400,000 440,000 Both projects require an initial investment of $560,000. In both cases, assume that the equipment has a life of 5 years with no salvage value. Required: Download Excel spreadsheet Round present value calculations and your final answers to the nearest dollar. 1. Assuming a discount rate of 12%, compute the net present value of each competing project. Puro equipment: 289,530 Briggs equipment: 374,453 Since the NPV of the Briggs equipment is than that of the Puro equipment, Briggs should chosen. The data analytics types are 2. A third option has surfaced for equipment purchased from an out-of-state supplier. The cost is also $560,000, but this equipment will produce even cash flows over its 5-year life. What must the annual cash flow be for this equipment to be selected over the other two? Assume a 12% discount rate. 155,349 per year 3. The following information is provided for Year 3 for the Puro equipment. The cell row is for an Excel spreadsheet. Cell: B5 C5 D5 E5 F5 Data Item Data Value Year 3 Cash flow Discount Factor Present Value 240,000 ? ? Discount rate 0.12 a. Using the cell notation given, prepare and show the written versions of the Excel equations that you used to provide the derived data for the question marks, where the discount factor (D5) is rounded to five decimal places and the present value (E5) is the product of cash flow and the discount factor is rounded to the nearest dollar. Discount Factor: Present Value: b. In Excel, following the pattern of Part 3a, create similar rows that run consecutively for all project years (Years 0 through 5). For the Puro project with the Year 0 row starting at Cell B2, Year 1 at Cell B3, Year 2 at Cell B4, etc. However, use F5 (the discount rate) as a data source for all rows. Finally, calculate the NPV using a summation equation for the Present Value column. c. Create a similar set of Excel rows, columns, and equations for the Briggs equipment project. d. The finance manager believes that cash flows of $260,000 per year are a better estimate for the Puro equipment than the original forecast. They also believes that the required rate of return should be 14%. Using the Excel NPV program from Parts 3b and 3c, calculate the NPV for each competing investment using the new cash forecast (for Puro) and a discount rate of 14%. Puro: Briggs: