Question

1 Approved Answer

Posted on Jul 26, 2024

Given the 4 spreadsheets below what is the weighted NPV of the entire project? A B D E F. 1 Part 1: Base Case: 2

Given the 4 spreadsheets below what is the weighted NPV of the entire project?

image text in transcribed

A B D E F. 1 Part 1: Base Case: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000] =3200000-(600000*4) 8 After-tax salvage value 642000=600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 4.50% 11 Growth Year 4 -10% 12 Expense 74% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year 0 1 2 3 4 18 Initial Cost -3000071 19 20 Sales 3000000.00 3135000.00 3276075.00 2948467.50 21 Less : Variable cost -2220000.00-2319900.00 -2424295.50 -2181865.95 22 Less : Depreciation -600000.00 -600000.00 -600000.00 -600000.00 23 EBIT 180000.00 215100.00 251779.50 166601.55 24 Less : tax@21% -37800.00 -45171.00 -52873.70 -34986.33 25 Net earnings 142200.00 169929.00 198905.81 131615.22 26 Add : Depreciation 600000.00 600000.00 600000.00 600000.00 27 Add: Net working capital 80000.00 28 Add : Salvage value 642000.00 29 Operating cash flow -3000071 742200.00 769929.00 798905.81 1453615.22 30 Present value factor@8.5% 1.0000 0.9217 0.8495 0.7829 0.7216 31 Present value -3000071 684055.30 654020.26 625469.82 1048891.37 32 NPV 12365.56 33 IRR 8.67% w E F A D 1 Part 2: Alternate Scenario 1: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000 =3200000-(600000*4) 8 After-tax salvage value 642000=600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 4.30% 11 Growth Year 4 -10% 12 Expense 74.5% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year 0 1 2 18 Initial Cost -3000071 3 4 19 20 Sales 21 Less : Variable cost 22 Less : Depreciation 23 EBIT 24 Less : tax@21% 25 Net earnings 26 Add : Depreciation 27 Add : Net working capital 28 Add : Salvage value 29 Operating cash flow 30 Present value factor@8.5% 31 Present value 32 NPV IRR 3000000.00 3129000.00 3263547.00 2937192.30 -2235000.00 -2331105.00 -2431342.52 -2188208.26 -600000.00 -600000.00 -600000.00 -600000.00 165000.00 197895.00 232204.49 148984.04 -34650.00 -41557.95 -48762.94 -31286.65 130350.00 156337.05 183441.54 117697.391 600000.00 600000.00 600000.00 600000.00 80000.00 642000.00 -3000071 730350.00 756337.05 783441.54 1439697.39 1.0000 0.9217 0.8495 0.7829 0.7216 -3000071 673133.64 642474.51 613362.73 1038848.61 -32251.70 8.06% 33 M A B D E F 1 Part 3: Alternate Scenario 2: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000 =3200000-(600000*4) 8 After-tax salvage value 642000 =600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 5.00% 11 Growth Year 4 -10% 12 Expense 74% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year o 1 2 3 4 18 Initial Cost -3000071 19 20 Sales 3000000.00 3150000.00 3307500.00 2976750.00 21 Less : Variable cost -2220000.00 -2331000.00 -2447550.00 -2202795.00 22 Less : Depreciation -600000.00 -600000.00 -600000.00 -600000.00 23 EBIT 180000.00 219000.00 259950.00 173955.00 24 Less : tax@21% -37800.00 -45990.00 -54589.50 -36530.55 25 Net earnings 142200.00 173010.00 205360.50 137424.45 26 Add : Depreciation 600000.00 600000.00 600000.00 600000.00 27 Add: Net working capital 80000.00 28 Add : Salvage value 642000.00 29 Operating cash flow -3000071 742200.00 773010.00 805360.50 1459424.45 30 Present value factor@8.5% 1.0000 0.9217 0.8495 0.7829 0.7216 31 Present value -3000071 684055.30 656637.43 630523.26 1053083.15 32 NPV 24227.95 33 IRR 8.83% E F A D 1 Part 4: Alternate Scenario 3: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000 =3200000-(600000*4) 8 After-tax salvage value 642000=600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 4.30% 11 Growth Year 4 -5% 12 Expense 74.8% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year 0 1 2 18 Initial Cost -3000071 3 4 19 20 Sales 21 Less : Variable cost 22 Less : Depreciation 23 EBIT 24 Less : tax@21% 25 Net earnings 26 Add : Depreciation 27 Add : Net working capital 28 Add : Salvage value 29 Operating cash flow 30 Present value factor@8.5% 31 Present value 32 NPV IRR 3000000.00 3129000.00 3263547.00 3100369.65 -2244000.00 -2340492.00-2441133.16 -2319076.50 -600000.00 -600000.00 -600000.00 -600000.00 156000.00 188508.00 222413.84 181293.15 -32760.00 -39586.68 -46706.91 -38071.56 123240.00 148921.32 175706.94 143221.59 600000.00 600000.00 600000.00 600000.00 80000.00 642000.00 723240.00 748921.32 775706.94 1465221.59 0.9217 0.8495 0.7829 0.7216) 666580.65 636175.17 607307.24 1057266.22 -3000071 1.0000 -3000071 -32741.91 8.06% 33 M A B D E F. 1 Part 1: Base Case: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000] =3200000-(600000*4) 8 After-tax salvage value 642000=600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 4.50% 11 Growth Year 4 -10% 12 Expense 74% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year 0 1 2 3 4 18 Initial Cost -3000071 19 20 Sales 3000000.00 3135000.00 3276075.00 2948467.50 21 Less : Variable cost -2220000.00-2319900.00 -2424295.50 -2181865.95 22 Less : Depreciation -600000.00 -600000.00 -600000.00 -600000.00 23 EBIT 180000.00 215100.00 251779.50 166601.55 24 Less : tax@21% -37800.00 -45171.00 -52873.70 -34986.33 25 Net earnings 142200.00 169929.00 198905.81 131615.22 26 Add : Depreciation 600000.00 600000.00 600000.00 600000.00 27 Add: Net working capital 80000.00 28 Add : Salvage value 642000.00 29 Operating cash flow -3000071 742200.00 769929.00 798905.81 1453615.22 30 Present value factor@8.5% 1.0000 0.9217 0.8495 0.7829 0.7216 31 Present value -3000071 684055.30 654020.26 625469.82 1048891.37 32 NPV 12365.56 33 IRR 8.67% w E F A D 1 Part 2: Alternate Scenario 1: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000 =3200000-(600000*4) 8 After-tax salvage value 642000=600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 4.30% 11 Growth Year 4 -10% 12 Expense 74.5% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year 0 1 2 18 Initial Cost -3000071 3 4 19 20 Sales 21 Less : Variable cost 22 Less : Depreciation 23 EBIT 24 Less : tax@21% 25 Net earnings 26 Add : Depreciation 27 Add : Net working capital 28 Add : Salvage value 29 Operating cash flow 30 Present value factor@8.5% 31 Present value 32 NPV IRR 3000000.00 3129000.00 3263547.00 2937192.30 -2235000.00 -2331105.00 -2431342.52 -2188208.26 -600000.00 -600000.00 -600000.00 -600000.00 165000.00 197895.00 232204.49 148984.04 -34650.00 -41557.95 -48762.94 -31286.65 130350.00 156337.05 183441.54 117697.391 600000.00 600000.00 600000.00 600000.00 80000.00 642000.00 -3000071 730350.00 756337.05 783441.54 1439697.39 1.0000 0.9217 0.8495 0.7829 0.7216 -3000071 673133.64 642474.51 613362.73 1038848.61 -32251.70 8.06% 33 M A B D E F 1 Part 3: Alternate Scenario 2: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000 =3200000-(600000*4) 8 After-tax salvage value 642000 =600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 5.00% 11 Growth Year 4 -10% 12 Expense 74% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year o 1 2 3 4 18 Initial Cost -3000071 19 20 Sales 3000000.00 3150000.00 3307500.00 2976750.00 21 Less : Variable cost -2220000.00 -2331000.00 -2447550.00 -2202795.00 22 Less : Depreciation -600000.00 -600000.00 -600000.00 -600000.00 23 EBIT 180000.00 219000.00 259950.00 173955.00 24 Less : tax@21% -37800.00 -45990.00 -54589.50 -36530.55 25 Net earnings 142200.00 173010.00 205360.50 137424.45 26 Add : Depreciation 600000.00 600000.00 600000.00 600000.00 27 Add: Net working capital 80000.00 28 Add : Salvage value 642000.00 29 Operating cash flow -3000071 742200.00 773010.00 805360.50 1459424.45 30 Present value factor@8.5% 1.0000 0.9217 0.8495 0.7829 0.7216 31 Present value -3000071 684055.30 656637.43 630523.26 1053083.15 32 NPV 24227.95 33 IRR 8.83% E F A D 1 Part 4: Alternate Scenario 3: 2 New system cost 3200000 3 Salvage value 200000 4 Old system market value 506239 5 New system actual SV 600000 6 Depreciation 600000 =(3200000-200000)/5 7 Book Value 800000 =3200000-(600000*4) 8 After-tax salvage value 642000=600000-21%*(600000-800000) 9 Year 1 Sales 3000000 10 Growth Year2-3 4.30% 11 Growth Year 4 -5% 12 Expense 74.8% of Sales 13 Tax rate 21% 14 Required rate 8.50% 15 Net working capital 200000 16 recovered at end 40% 17 Year 0 1 2 18 Initial Cost -3000071 3 4 19 20 Sales 21 Less : Variable cost 22 Less : Depreciation 23 EBIT 24 Less : tax@21% 25 Net earnings 26 Add : Depreciation 27 Add : Net working capital 28 Add : Salvage value 29 Operating cash flow 30 Present value factor@8.5% 31 Present value 32 NPV IRR 3000000.00 3129000.00 3263547.00 3100369.65 -2244000.00 -2340492.00-2441133.16 -2319076.50 -600000.00 -600000.00 -600000.00 -600000.00 156000.00 188508.00 222413.84 181293.15 -32760.00 -39586.68 -46706.91 -38071.56 123240.00 148921.32 175706.94 143221.59 600000.00 600000.00 600000.00 600000.00 80000.00 642000.00 723240.00 748921.32 775706.94 1465221.59 0.9217 0.8495 0.7829 0.7216) 666580.65 636175.17 607307.24 1057266.22 -3000071 1.0000 -3000071 -32741.91 8.06% 33 M