You recently began working for Orange Fizz Company. Management is contemplating the replacement of its existing, three-year-old (Third-year depreciation completed) bottling machines that originally cost $15,000,000 with newer and more efficient machines. The old (existing) machines were placed on the MACRS five-year class life depreciation schedule (assuming half-year convention) three years ago. Total operating costs for the old bottling machines are $5,400,000 per year and Orange Fizz will bottle 48 million (48,000,000) bottles per year each year for the next seven years. The firm expects to realize a $1,000,000 return from salvaging the old machines in 7 years; however, the existing machines may be sold now to another firm in the industry for $2,500,000. If Orange Fizz retains the old machines, they would remain operational for the next 7-years.

The new bottling machines, if purchased, would cost $16,000,000 and would be placed on a MACRS five-year class life depreciation, and will remain in operation for the next 7 years. The new machines are expected to have a salvage value of $2,000,000 in seven years. Total annual savings in operating costs of $0.071 per bottle will be realized if the new bottling machines are installed. The company is in the 26% income tax bracket and it has a 10% WACC.

Determine if the replacement should occur by estimating the replacement project's NPV and IRR.

Complete using an Excel spreadsheet. Use a relative (incremental) cash flow analysis.

*PLEASE PROVIDE A SPREADSHEET WITH THE FORMULAS USED*

*USE THE BELOW ATTACHMENTS AS A REFERENCE FOR WHAT THE COMPLETED PRODUCT SHOULD LOOK LIKE*

?

C D E G K Johnson and Carlton. Relative Cash Flow Initial CFs 5 Cost New Equip -1200000 6 Salvage (old)) 150000 7 Tax Savings 8 (288,000-150,000).26 (E16-E6)*0.26 9 NWC -75000 10 Initial CF SUM(ES:E9) 11 12 Old System Depreciation New System Depre 13 YEAR RATE DEPREC ADJ BASIS YEAR RATE DEPREC 14 1 ADep ADJ BASIS 0.2 C14*1000000 #1000000-D14 15 2 0.32 C15*1000000 E14-D15 16 3 0.192 C16*1000000 E15-D16 17 4 0.1152 -C17*1000000 -E16-D17 0.2 G17*1200000 -H17-D17 =1200000-H17 18 5 0.1152 -C18*1000000 E17-D18 0.32 G18*1200000 H18-D18 -J17-H18 19 6 0.0576 C19*1000000 E18-D19 AWNE 0.192 G19*1200000 H19-D19 =J18-H19 20 0.1152 G20*1200000 -H20-D20 -J19-H20 21 5 0.1152 G21*1200000 =H21-D21 -J20-H21 22 16 0.0576 G22*1200000 -H22-D22 -J21-H22 23 24 Operating CFs Taxable Taxes 25 Year CFBT ADep Income at 26% CFAT E10 26 1 400000 =117 C26-D26 -E26*0.26 C26-G26 =126 27 2 400000 =118 -C27-D27 -E27*0.26 -C27-G27 127 SUN- 28 3 400000 -119 =C28-D28 -E28*0.26 C28-G28 #128 29 4 400000 -120 =C29-D29 -E29*0.26 C29-G29 #125 30 5 400000 =121 C30-D30 E30*0.26 =C30-G30 130+135 32 Terminal CFs 23 Salv-New Taxes-New Salv-Old Taxes-Old Rec NWC CFAT 34 Year (200,000-69,120).26 50,000*.26 35 5 200000 (D35-J21)*0.26 50000 #F35*0.26 75000 -D35-E35-F35+G35+H35 7 NPV =NPV(0.12,L25:130) *(1.12) IRR =IRR(L25:130)B C D E F G Johnson and Carlton. Relative Cash Flows 4 Initial CFs 5 Cost New Equip -1.200,000 6 Salvage (old)) 150,000 7 Tax Savings 8 (288,000-150,000).26 35,880 9 ^ NWC -75.000 10 Initial CF -1,089,120 11 12 Old System Depreciation New System Depreciation 13 YEAR RATE DEPREC ADJ BASIS YEAR RATE DEPREC ADep ADJ BASIS 14 0.20 200,000 800,000 15 0.32 320,000 480.000 16 AWN 0.192 192,000 288,000 17 0.1152 115.200 172,800 0.20 240.000 124,800 960.000 18 0.1152 115.200 57,600 0.32 384.000 268,800 576,000 19 6 0.0576 57,600 0 0.192 230,400 172,800 345,600 20 0.1152 138,240 138,240 207,360 21 0.1152 138,240 138.240 69,120 22 6 0.0576 69,120 69,120 0 23 24 Operating CFs Taxable Taxes 25 Year CFBT ADep Income at 26% CFAT -1,089.120 26 1 400,000 124.800 275.200 71552 328,448 328.448 27 2 400,000 268,800 131.200 34112 365,888 365,888 28 3 400,000 172,800 227.200 59072 340,928 3 340,928 4 400,000 138,240 261,760 68057.6 331,942 4 331,942 30 5 400.000 138.240 261,760 68057.6 331,942 5 535,914 32 Terminal CFs Salv-New Taxes-New Salv-Old Taxes-Old Rec NWC CFAT 34 Year (200,000-69,120).26 50,000*.26 85 5 200,000 34,029 50,000 13,000 75,000 203.971 7 NPV $253,534 18 9 IRR 20.4%