Question

Replacement analysis The Dauten Toy Corporation currently uses an injection molding machine that was purchased 2 years ago. This machine isbeing depreciated on a straight-line basis, and it has 6 years of remaining life. Its current book value is $2,100, and it can be sold for $2,500 at thistime. Thus, the annual depreciation expense is $2,100/6 at $350 per year. If the old machine is not replaced, it can be sold for $500 at the end of itsuseful life.Dauten is offered a replacement machine which has a cost of $8,000, an estimated useful life of 6 years, and an estimated salvage value of $800.This machine falls into the MACRS 5-year class so the applicable depreciation rates are 20, 32, 19, 12, 11, and 6 percent. The replacement machine wouldpermit an output expansion, so sales would rise by $1,000 per year; even so, the new machine’s much greater efficiency would still cause operatingexpenses to decline by $1,500 per year. The new machine would require that inventories be increased by $2,000, but accounts payable would simultaneouslyincrease by $500. Dauten’s marginal federal-plus-state tax rate is 40 percent, and its WACC is 15 percent. Should it replace the old machine?
I have attempted the excel sheet and cannot figure it out in full.I know the answer is yes, and NPV =$921.36.
Can someone please help me with the excel setup?

Answer 1

0 ($8,000.00) ($1,500.00) ($9,500.00) 4 6 2 Cost of machine 3 Working capital 4 Total 6 Sales increased 1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 Add: Operating expenses $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $2,500.00 $2,500.00 $2,500.00 $2,500.00 $2,500.00 $2,500.00 1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,000.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,500.00 $1,440.00 $2,304.00 $1,368.00 $864.00$792.00 $432.00 7 reduced 8 Net Income 9 Less: Tax at 40% 10 After tax net income 11 Add: Depreciation expense 12 Add: Inventory 13 Add: Aftertax salvage value 14 15 NPV 16 17 The new machine has positive NPV, hence the old machine should be replaced 18 ($1,500.00) $480.00 2,940.00 $3,804.00 $2,868.00 $2,364.00 2,292.00 $432.00 $496.57

Formulas for the above values are as follows:

0 4 6 2 Cost of machine 3 Working capita(2000-500) 4 Total 8000 B2+B3 6 Sales increased 1000 1000 1000 1000 1000 1000 Add: Operating 1500 -C6+C7 C8*0.4 -C8-C9 1500 1500 1500 1500 G6+G7 -G8*0.4 -G8-G9 1500 /expenses reduced 8 Net Income 9 Less: Tax at 40% 10 After tax net income D6+D7 D8*0.4 D8-D9 E6+E7 E8*0.4 E8-E9 F6+F7 F8*0.4 F8-F9 H6+H7 H8*0.4 H8-H9 Add: Depreciation 11 expense 12 Add: Inventory (8000-800)*20%-(8000-800)*32%-(8000-800)*19%-(8000-800)*12%-(8000-800)*11%-(8000-800)*6% (2000-500) Add: Aftertax 13 salvage value 800 (1-0.4) H10+H11+H12 C10+C11 D10+D11 E10+E11 F10+F11 -G10+G11 NPV(15%,C14:H14)+B4 15 NPV 16 17 The new machine has positive NPV, hence the old machine should be replaced