3 6 Excel Problem 2 Show All Excel Work (32 points) 2 Chesapeake Shipyards is considering replacing an six-year-old welding machine with a new one that will decrease operating costs before taxes by $75,000 per year. The new machine costs $200,000 and its estimated life is 4 years. The salvage value at the end of the life is $15,000. The new welder will be depreciated using the MARCS 7-year factors. The net working capital 5 will increase by $10,000 if the new machine is purchased. The old machine is expected to last 4 years. Its current salvage value is $5,000 and is deprecated to zero at the end of four years using the straight-line approach. Its current book value is $15,000 and the expected salvage value at the end of 6 years is expected to be $1,000 The required return or WACC is 15 percent and the tax rate is 40 percent. The required payback period is 4 years or less. 10 What is the payback period? Is the project acceptable using this model? 11 What is the NPV? Is the project acceptable using this model? 12 What is the IRR? Is the project acceptable using this model? 13 Should Chesapeake replace the welding machine? Explain your decision. 8 9 15 26 Inputs 17 Project Life 18 New Equip Cost 19 New Equip SV = 4) 20 Increase NWC 21 Old Equip SV (0) 22 Old Equip BV (0) 23 Old Equip Tax SV (C = 1) 24 Old Equip Econ SV (4) 25 26 Op. Cost Decrease 27 28 Tax Rate 29 WACC 30 32 Year 32 Dep Factors 4 Years $200.000 $15.000 $10,000 $5,000 $15.000 0 $1.000 Recovery Year 1 2 3 4 5 6 7 8 3. Year 0.3333 04445 0.1481 0.0741 5 Year 0.2 0.32 0.192 0.1152 0.1152 0.0576 7-Year 0.1429 0.2449 0.1749 0.1249 0.0893 0.0693 0.0892 0.0446 $75,000 per year 04 05 34 New Equip 35 Old Equip 36 NWC 37 38 Sales Rev 39 Cost 40 Dep (New) 41 Dep (Old) 42 Change in Dup 43 EBT