Question

6. Financial Returns Section: Present value of the project (PW) , Cost/Benefit analysis, and sensitivity analysis. (See below)

7. Cash flow diagrams and references

Note the following: 1. For the initial cost estimate, assume that five robotic welders will save about $1 million per year which is equivalent to about 7 full time equivalent (FTE) highly skilled welders. 2. The purpose of the investment is to transition the welders to custom projects that provide high margins to the firm that require specialized custom welding capabilities. 3. Show a high level diagram of the work flow breakdown for implementing robots of this type. For this section, you can make some assumptions regarding how long each step would take such as design, vendor analysis, procuring and turning up the system. You can also take timelines that you find on the robot vendors or systems integrators web sites. 4. Each of the 5 robotic welders will cost $250,000 each which includes implementation and set-up costs and each have a 5-year life. However, you can support these assumptions from data provided on the vendor's sites. In general, a typical single arm robotic welder cost between $100 and $250k for a simple work cell (single arm robotic welder) 5. You don't have to use the costs listed in #4 if you have accurate pricing data from a robotic vendor. It is up to you and your team to decide. 6. Salvage value of each robot is 20% of its purchase cost 7. Assume the discount rate for the company is 15% 8. Run a sensitivity analysis to calculate the Net Present Value for $1 million saved per year, $800,000 saved per year, and for $500,000 saved per year. We are assuming that the savings is $1 million per year, but the management team wants to understand how the investment returns are linked to the savings targets. 9. Maintenance costs are 15% of the robotic purchase price per year

Answer 1

Sensitivity Analysis Scenario 1 Scenario 2 1,000,000 $ 1,000,000 $ 800,000 Scenario 3 $ 500,000 Workings 1 Cost Saving $ Cost per Robotic Welder Cost for 5 Robotic Welders Salvage Value - 20% Net Value $ $ $ 250,000 1,250,000 250,000 1,000,000 BEA*5 C=B*20% D=B-C Depreciation (5 years; Assumtion Straight line) $ Maintenance Cost - 15% of Purchase cost $ Discount rate 200,000 187,500 15% $ $ (200,000) $ (187,500) $ (200,000) $ (187,500) $ (200,000) EED/5 (187,500) F=B*15% Net saving for 1 year Tax - Assumption 30% Net savings after tax $ $ $ 612,500 183,750 428,750 $ $ $ 412,500 123,750 288,750 $ $ $ 112,500 33,750 78,750 G=1-E-F H=G*30% IG-H Cashflow: Initial investment Year 0 Net saving after tax FOR 5 YEARS Add: Depreciation FOR 5 YEARS Salvage value 5th year (net of tax @ 30%) Net cashflow $ $ $ $ $ (1,250,000) $ (1,250,000) $ (1,250,000) =B 2,143,750 $ 1,443,750 $ 393,750 K=1*5 1,000,000 $ 1,000,000 $ 1,000,000 L=E*5 175,000 $ 175,000 $ 175,000 M=C*(1-30%) 2,068,750 $ 1,368,750 $ 318,750 =SUM(j:M) Present Value of the Project: Initial investment Year O Net saving after tax for 5 years Add: Depreciation for 5 years Salvage value 5th year (net of tax @ 30%) Present Value Disc. factor @ 15% 1 $ 3.3522 $ 3.3522 $ 0.4972 $ $ (1,250,000) $ (1,250,000) $ (1,250,000) N=2*) 1,437,236 $ 967,935 $ 263,982 0=2*I 670,431 $ 670,431 $ 670,431 P=2*(-E) 87,006 $ 87,006 $ 87,006 Q=2*M 944,673 $ 475,372 $ (228,581) RESUM(N:Q)

Y1 Y2 Disc. Factor @ 15% 0.8696 0.7561 0.6575 0.5718 0.4972 3.3522 Y4 Total - Y Casflow diagram with sensitivity Net cashflow Present Value of Net cashflow Scenario 1 Scenario 2 Scenario 3 $ 2,068,750 $ 1,368,750 $ 318,750 $ 944,673 $ 475,372 $ (228,581) Casflow diagram with sensitivity $2,500,000 $2,000,000 $2,068,750 $1,500,000 $1,368,750 $1,000,000 $944,673 $500,000 $475,272 $318,750 $(500,000) Scenario 1 $2,068,750 Scenario 2 $1,368,750 $(228,581) Scenario 3 $318,750 -Net cashflow -Present Value of Net cashflow $944,673 $475,372 $(228,581)