Excel 2 Problem Data Solar Power, Inc is one of the largest solar panel manufacturers in the U S It has a proven track record for sustainability manufacturing The company controls the entire manufacturing process from materials sourcing to assembly to delivery of its solar systems The company is investigating the purchase of a robot system for use on the companys assembly line which would improve quality control of the solar cells The use of the robot would ensure that the manufacturing process of the solar cells would be pristine and untouched by human hands until final inspection Data related to the purchase, installation, and cost savings are provided below Cost of the robot system $1,800,000 Installation and software 900,000 Annual savings in labor costs Annual savings in inventory carrying costs 210,000 Monthly increase in power and maintenance costs 2,500 Salvage value in 10 years 70,000 Useful life 10 years The companys Engineering Dept has conducted several studies regarding the possible labor savings that will result from the purchase of the robot system The Engineering Dept believes that the robot system will result in a savings of 50,000 direct labor hours each year at an average rate of $8 00 per hour Company engineers also believe that the work flow will become smoother and more efficient due to the automation and will allow the company to reduce its inventory on hand by $400,000 The inventory reduction will take place at the end of the first year of operation the released funds will be available for use elsewhere in the company Solar Power requires a 20 return on all equipment purchases Shelby Maxwell, Vice President of Operations, has noted that all of Solar Powers competitors are automating their plants However, she is pessimistic, about whether Solar Powers president will allow it to automate In preparing the proposal for the robot, she stated to a colleague, Lets just hope that reduced labor and inventory costs can justify the purchase of this automated equipment Otherwise, well never get it approved by the president You know how the president feels about equipment paying for itself out of reduced costs REQUIRED Determine the net annual cost savings if the robot is purchased (Do not include the $400,000 inventory reduction or the salvage value in this computation These are NOT part of the ANNUAL cost savings ) Include this in your data block page You can use the format provided in the Excel 2 instructions Compute the net present value (NPV) of the proposed investment in the robot using the format in the Excel 2 instructions Based on these data results, would you recommend that the robot be purchased Explain (Worth 2 pts ) Put your answer to Part 2 in your data block page Label your answer Part 2 Do not just say Yes or No Explain why you believe the company should or should not purchase the robot Support your position with the results of your NPV calculation Assume that the robot is purchased At the end of the first year, Shelby Maxwell has found that some items didnt work out as planned Due to unforeseen problems, software and installation costs were $75,000 more than estimated and direct labor has been reduced by only 45,000 hours per year, rather than by 50,000 hours Assume that all other cost data were accurate Save your original file and then open your file and save it again under a new name See the Excel 2 instructions on how to name your files Then just change the data block entry area for the new data above and your spreadsheet should automatically recalculate the NPV using the new data Does it appear that the company made a wise investment Explain (Worth 2 pts ) Put your answer to the question above (underlined) in your data block page of the new file Label your answer Part 3 Explain your answer Again, do not just answer Yes or No Support your position with your NPV calculation Upon seeing your analysis in Part 3 above, Solar Powers president stated, That robot system is the worst investment weve ever made And now well be stuck with it for years Explain to the president what benefits other than the above cost savings might accrue from use of the new robot Label your answer Part 4(a) A minimum of three other benefits must be listed to receive credit (Worth 3 pts ) Compute for the president the dollar amount of cash inflow that would be needed each year from the intangible benefits in (4a) above for the robot to yield a 20 rate of return (Hint NPV factor for 10 yrs at 20 Net Annual Cash Flow Label your answer Part 4(b) (Worth 2 pts ) Type your answers for Part 4(a) and (b) into the data block page of the new file

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 08, 2024

Excel #2 Problem & Data Solar Power, Inc. is one of the largest solar panel manufacturers in the U.S. It has a proven track record

Excel #2 Problem & Data

Solar Power, Inc. is one of the largest solar panel manufacturers in the U.S. It has a proven track record for sustainability manufacturing. The company controls the entire manufacturing process from materials sourcing to assembly to delivery of its solar systems. The company is investigating the purchase of a robot system for use on the companys assembly line which would improve quality control of the solar cells. The use of the robot would ensure that the manufacturing process of the solar cells would be pristine and untouched by human hands until final inspection.

Data related to the purchase, installation, and cost savings are provided below:

Cost of the robot system $1,800,000

Installation and software 900,000

Annual savings in labor costs ?

Annual savings in inventory carrying costs 210,000

Monthly increase in power and maintenance costs 2,500

Salvage value in 10 years 70,000

Useful life 10 years

The companys Engineering Dept. has conducted several studies regarding the possible labor savings that will result from the purchase of the robot system. The Engineering Dept. believes that the robot system will result in a savings of 50,000 direct labor hours each year at an average rate of $8.00 per hour. Company engineers also believe that the work flow will become smoother and more efficient due to the automation and will allow the company to reduce its inventory on hand by $400,000. The inventory reduction will take place at the end of the first year of operation; the released funds will be available for use elsewhere in the company. Solar Power requires a 20% return on all equipment purchases.

Shelby Maxwell, Vice-President of Operations, has noted that all of Solar Powers competitors are automating their plants. However, she is pessimistic, about whether Solar Powers president will allow it to automate. In preparing the proposal for the robot, she stated to a colleague, Lets just hope that reduced labor and inventory costs can justify the purchase of this automated equipment. Otherwise, well never get it approved by the president. You know how the president feels about equipment paying for itself out of reduced costs.

REQUIRED:

Determine the net annual cost savings if the robot is purchased. (Do not include the $400,000 inventory reduction or the salvage value in this computation. These are NOT part of the ANNUAL cost savings.) Include this in your data block page. You can use the format provided in the Excel #2 instructions.

Compute the net present value (NPV) of the proposed investment in the robot using the format in the Excel #2 instructions. Based on these data results, would you recommend that the robot be purchased? Explain. (Worth 2 pts.)

Put your answer to Part 2 in your data block page. Label your answer: Part 2: Do not just say Yes or No. Explain why you believe the company should or should not purchase the robot. Support your position with the results of your NPV calculation.

Assume that the robot is purchased. At the end of the first year, Shelby Maxwell has found that some items didnt work out as planned. Due to unforeseen problems, software and installation costs were $75,000 more than estimated and direct labor has been reduced by only 45,000 hours per year, rather than by 50,000 hours. Assume that all other cost data were accurate.

Save your original file and then open your file and save it again under a new name. See the Excel #2 instructions on how to name your files. Then just change the data block entry area for the new data above and your spreadsheet should automatically recalculate the NPV using the new data.

Does it appear that the company made a wise investment? Explain. (Worth 2 pts.)

Put your answer to the question above (underlined) in your data block page of the new file. Label your answer: Part 3: Explain your answer. Again, do not just answer Yes or No. Support your position with your NPV calculation.

Upon seeing your analysis in Part 3 above, Solar Powers president stated, That robot system is the worst investment weve ever made. And now well be stuck with it for years.
1. Explain to the president what benefits other than the above cost savings might accrue from use of the new robot. Label your answer: Part 4(a). A minimum of three other benefits must be listed to receive credit. (Worth 3 pts.)
2. Compute for the president the dollar amount of cash inflow that would be needed each year from the intangible benefits in (4a) above for the robot to yield a 20% rate of return. (Hint: NPV / factor for 10 yrs. at 20% = Net Annual Cash Flow. Label your answer: Part 4(b). (Worth 2 pts.)

Type your answers for Part 4(a) and (b) into the data block page of the new file.