Required information The Chapter 14 Form worksheet is to be used to create your own worksheet version of Example E and Exhibit 14.8 in the text. 2. The company is considering a project involving the purchase of new equipment Change the data area of your worksheet to match the following: Use Exhibit 14B-1 and Exhibit 14B-2. (Use appropriate factor(s) from the tables provided.) B C 1 Chapter 14: Applying Excel 2 3 Data 4 Example E 5 Cost of equipment needed 6 Working capital needed 7 Overhaul of equipment in four years 8 Salvage value of the equipment in five years 9 Annual revenues and costs 10 Sales revenues 11 Cost of goods sold 12 Out-of-pocket operating costs 13 Discount rate $ $ $ $ 160,000 50,000 10,000 25,000 $ $ $ 365.000 230.000 90.000 15% a. What is the net present value of the project? (Negative amount should be indicated by a minus sign. Round your present value factor to 3 decimals and round all other intermediate calculations to nearest whole dollar.) Not present value c. The internal rate of return is between what two whole discount rates (eg, between 10% and 11%, between 11% and 12%, between 12% and 13%, between 13% and 14%, etc.)? and % The internal rate of return is between d. Reset the discount rate to 15%. Suppose the salvage value is uncertain. How large would the salvage value have to be to result in a positive net present value? Minimum salvage value required to generate a positive present value EXHIBIT 14-8 The Net Present Value Method-An Extended Example D 1 Year 2 Now 2 3 4 5 3. Purchase of equipment $ (60.000) 4 Investment in working capital $ 100,000) 5 Sales $ 200,000 $ 200,000 $200,000 $200,000 $200,000 6 Cost of goods sold $ (125,000) S (125,000) S (125,000) S (125,000) S (125,000) 7 Out-of-pocket costs for salaries, advertising, etc. $ (35.000) $ (35,000) $ (35,000) $ (35,000) 5 (35,000) 8 Overhaul of equipment $5,000) 9 Salvage value of the equipment $10,000 10 Working capital released $ 100,000 11 Total cash flows (a) $ (160,000) $ 40,000 $ 40,000 $40,000 35,000 $ 150,000 1.000 12 Discount factor (14%) (b) 0.877 0.709 0.675 0.3925 0.519 13 Present value of cash flows (a) (b) $ (160,000) $ 35,000$ 30,760 $ 27,000 $20,720 5 77,850 14 Net present value (SUM 813:613) $ 31.410 15 16 Note: The discount factors come from Exhibit 148-1 in Appendix 148. 17 Exhibit 14-8 Eb 140 Exhibit 14-10 Eshot 14:11 Eshte Source Mic BOLT 140-1 Present of State . . 2 OSNO O TO EGIONO COMO ZOO O go to 16 Peno 45 55 94 19 1 0.9520.9520000950920 0.00 0.00 0.00193 0.15 0.77 0.8700002 0155 0347 0000 0026 0230 0101000.000 101 20 21 22 23 24 25 2 0925 0.007 0.000373 0857 0100120 0791 073 0756 07072 2 80 0164 0.000 0.16 079 072 071 0721 0707060614 0.000672 0.61 0.650 000 0712 0.0000675 06410024 0600 0.00 059 054 051 051 0536 090 0.855 03230792 0.753 0735 0708 0888 065 0.035 060 0592 6572 052 0534 039 0499 042 047 048 007 00 5 0.822 071407073 0.001 0.650 0.021 0.593 0.507 0.54 0.55 490 0.00 0.250 0.00 0.0002 0.3 0.30 0.55 0.3410121 070 075 0705 066 060 059 036 055 0507 0 450 456 0032 0.21 000 020 692 025 6301 02 075 020 7 0.00071 0.665 06 0.583 050 051 0AB20452 01350.400 0.3 0.354 0:3 026 027 02 03 023 0222020 0731 06770627 052 05:40 0502 046704160404 037605510327 0905 205 206 024 023 024 0204 0 009 07030645 6592 0544 0.500 0.000 0.24 0.01 0.01 0.333 0300 0254 0203 023 0225 0200 01010011550334 10 0.676 0150508 01 0422 0:36 0352 022 0295 0.7100200227 0.200 MM 0000100 0.650005270475 04290 0.750 03 0207020102202150950172 093 002010 09030040000 06750551049704440.397 0.56 0.319 02060257 02310200 0700152013 01340 0.9020002000000000 0 0.00105300.00.41 0.368 0.2360290 07580229 0206 02 000000 0304 0000 0004 0.00 0.00 001 005 050505 04420300340 029 020 0212 03050 OYO 0.05 0:00 0000 0000 0002 05 004 00 15 05550.60402032015 0275 026200 OO 0500123 000 000 000 000 000 0057 00 0045 004 00 0534 0500394030 02920292029 ON Oon 000 000 0000 054 000 000 000 000.000 027700200 011000125 000 0.000 0000 0000 0000 0000 004 000 000 000 0026 00 0.00 0.00 00 00 00 00 00 002 0001 0.01 0.01 0.475 026 021 027701204 008 GO00000000000000100000 000 000 000 000 000 20 0456 0:377030 0.20 02150 OM 04 04 00 00 00 00 00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.250 0.24 000 0005 0000 0000140053 004 000010000920000000000 0.4020300210226 0904 0150 ON 000 000 000 000 000 000 0026 0.002 00000000000000000 23 0401032602001 0110000010 OO 000 0000 0000400003 0022 0.002 000000000000000000000 24 0.0 0.100247 02600) 0.000006 0053 000 000 0000 000 000 000 000 000 000 000 000 000 0.375 00201140 011 0.002 003 000 000 000 0000 0.004 0030 0.0000 000 000 000 000 000 0004 20 0302010220002 ous 000064 0.006 0.053 000 000 000 0000 0000 0000 000 0000 000 000 m oto 0.001 000 000 0000 0000 0000 20 03470200 2010 05 0000000000000000029 0.025 004 0 on 20 03030255043015001 0000 000 0054 000 000 000 0000 0000 000 0000 0000 0000 000 0004 000000000002 31020050 01070002 003 004 0037 0.03 002 00000 con 0000 0000 0005 000 000 0.00 0.00 0.002 0300021 091009 0.0 005 004 con 0.025 0000 000 000 0000 0.00 0.00 0.004 0000 0000 0000 0000 0.00 020000 000 0062 0006 0.002 0.022 000 0.00 0.00 0.00 0.00 0.00 0.00 0.00 000 0000 0000 0000 0.000 0.000 CO MCO 970 90 GO HD0010 OOOOOOOOOOOOO 00000000000000000000000000 VO | 610 COLO HEERESRR 8 SLO OOO OSLO EXHIBIT 448-2 Present Value of an Annulty of Stle Area [-] 13 150 3240 5033 Periods 49 5 7 94 108 15 1 12% 0952 0952093 0.935 15% 16 0.926 0917 17 101 0.909 0.901 0.093 0385 0.377 0.870 0.362 0.855 0.347 0140 0.883 0820002000006 0.000 20% 27322 23 24 2 1866 1859 1833 1800 1759 1736 3 1690 1668 2775 2723 2873 2624 1626 1605 155 156 157 158 159 1492 14 1657 400 2572 2571 2.487 2464 2.4022361 2322 2.283 224 2.250 2174 4 3630 3.546 3.455 3307 3.312 3.170 3102 2.500 2100 2014 2012 2011 3.037 2.9742.914 2.955 27902743 2,690 26592509 2540 2494 2.4 2.404 242 1901 195 4452 4329 4212 4100 29933.890 3.791 3696 3.605 3.517 1433 33523274 1199 3.122 6 3.058 2.991 2.920 2064 2800 275 2.0 5:242 5078 490 4767 4.623 4,436 4.355 4231 4.111 3.990 3.889 3.784 365 3589 3.498 3410 326 3245 150 2092 2020 2.991 7 60025786 5582 5389 5.200 4568 4702 4.564 4423 4253 41604039 3.922 3.012.3.706 3.605 350 34963327 3242301 B 6.733 6263 5210 5.971 5.76 5.535 5.335 5.164.968 479946394487 4.344 9 7435 4.2014.078 3.954 3.031 3726 1610251422329 2108 6.802 6515 6247 5.995 5.7595.517 5320 5132 4946477246074451 4.303 4.16 4031 3.905 1786 3573 3706 2003 10 B. 2722 7360 7024 6.710 6.418 6105 5.889 5650 5.420 5216 5.099 4833 4659 4494 4309 4192 4054 1922 3.799 3002375 3780 3306 7887 7499 7009 6.805 6.495 6.207 5931 56875453 5234 5029 48364656 4.486 4327 47 4.035 2.902 37761656 12 9385 8.800 8.394 7943 7536 2461 6.834 6.492 694 5.918 5.680 54215.097 4.988 4793461 4.439 4278 427 3985 29513725 9.900 9.394 8.853 3.350 7900 7457 7103 6.750 6.424 6.1225842 5.583 5342 5.118 4910 4715 4533 43024.203 40533902 3730 14 10.563 9.899 9.295 8.745 8.244 7786 7367 0982. 6.628 6.302 6002 5724 5,468 5229 5.000 4802 4011 432 4265.00 3.962 12 15 71113 10 3809712 9308 8559 76002190 6811 6.4626142 5.847 5.575 5.24 5092 4876 46754489 4.3154153 4.0013859 10 1852 10.638 10.306 9.40 3851 830 7824 7370 6.9746,604 6.265 5954 8.668 5.405 55249384730 4.5364357 4100 4033 2002 17 12166 11274 10.47 9.753 9.122 8544 8,022 75497120 67296.37360075749 5.475 5.222 4.990 4775 4576 4191 42090059 1910 18 12 659 T1090 1032810059 9.372 3.756 8.201 7702 7.250 6.840 6.457 6125 5.818 5534 5.2735033 48124603 4419 42434050 2900 304 12.005 94158 10.336 9.004 3950 8.365 783973666.908 6.550 6.198 5.8775.584 5.28 5.070 48434635 4442420) 4007 1942 20 13.590 12.462 11470 10.5949.318 9.129 8.5147963 7469 7025 6.623 6.259 59295.628 5353 5101 4.870 4657 4.450427940 1954 21 14.029 2020 11.754 10.836 10.017 9.292 8549 3.075 75627802 6 687 6.312 5973 5665 5.384 51274891 40754.476 4292421 3960 22 1451 303 12.042 1061 10.2019.442 3.772 170 7645 2170 6.7436.750 6.01 5.695 5.4105149 4909690440024003970 4137 49254701 449941 7230 679263996045721 5.432 5867 886)266 7719 14857 134099230311272 10371 9.580 1910 49074710 3507 1 413001 24 3.858.3487734 728) 6.835 604 607357454515182 15.247 1799 12.550 11469 10.529 9707 25 15622 14.094 2.783 11654 10.675 9.823 9.0778422784373306073 6.464 6.097 5.700 467 5905498 4721 451 4323 4547 3.905 26 15.903 14.375 73.003 1826 10.810 0929 5700 5.00 5.20049564728 4520 4320 4151 1958 9161 3.488 2896 7372 6.906 64916118 6.136 57905492 5 215 4.964 4714 524 44154 1990 22 10.330 1.643211 7409 6935 6514 11987 10,935 10.027 92378549793 28 16.663 14.8983406 2007 6901 6534 6.152 5810 5502 52234970 4719452843354157 992 11051 10.116 9.307860279047441 29 16.904 15.101 501 12.27811150 10:190 9.370 8650 022 7470 6.903 6.5516166 5.820 5.510 52294975470 451 437 41593991 30 17.292 15,372 3765 12.409 11258102749.427 8.6948055 7495 70036.566 6.77 56295517 525 47946453443941603995 7105 40 6.642 6233 5.6715.543 5258 49974700 45414307496 3999 19.790 17159 15.046 2 1925 10757 9.779.951 1244 7634 RENRXONRA