Check Required information The Chapter 12 Form worksheet is to be used to create your own woricsheet version of Example E and Exhibe 12-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 128-1 and Exhibit 12B-2. (Use appropriate factor(s) from the tables provided) B A Chapter 12: Applying Excel 1 2 3 5 Data Example E Cost of equipment needed Working captal needed Overhaul of equipment in four years Salvage value of the equipment in fe years Annual revenues and costs S $ $ 6 250 000 25 000 25 000 25 000 8 9 Muut CH 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 123.1 and Exhibit 120 2. (Use appropriate factor(s) from the tables provided.) B 5 $ $ Chapter 12: Applying Excel 2 3 Data 4 Example 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 250,000 25 000 25,000 25.000 $ 5 $ 5 370.000 230,000 TO 000 13 EXHIBIT 12-8 The Net Present Value Method-An Extended Example B C D 2 Now 1 2 3 4 3 Purchase of equipment S (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) $(125,000 $1125,000) $ (125,000) $(125,000) 7 Out-of-pocket costs for salaries, advertising, etc. $ 35,0001 S 135,000S 35,000) $ (35,000) S (35,000) 8 Overhaul of equipment S (5,000 9 Salvage value of the equipment $10,000 $100,000 10 Working capital released 11 Total cash flows (a) S (160,000) S40,000 40,000 $40.000 S 35,000 $150,000 1.000 0.87 0.769 0675 0.592 s 12 Discount factor (1496) (b) 13 Present value of cash flows (a) (b) $ 160,000) $35,080 S 30.760 $ 27,000 $ 20,720 $ 77,850 $ 31,410 14 Net present value (SUMB13:13) 16 16 Note: The discount factors come from Exhibit 128-1 in Appendix 128 0519 EMIT 128-1 Present Value of the 22 Periods 4% 5% 6% 7% 8% 94 11% 7 13% 14% 15% 15% 17% 18% 19%, 20% 21% 0.962 0952 0943 0.935 0.926 0917 0909 0.901 0893 0.885 0877 0.870 0862 0.855 08470840 0833 0826 0820 083 0806 0.800 233 24 25 2 0925 0.907 0.890 0.873 0.857 0842 0826 0812 0797 0783 0769 0.756 0743 0731 078 0706 0694 0.683 0672 0661 0650 0640 3 0889 0.864 0840 0.816 0794 072 0.751 0731 0.712 0.69 0.675 0:658 0641 0624 0609 0.593 0.579 0564 0.551 0.537 0524 0512 4 0.855 0823 0.792 0763 0.735 0708 0683 0659 0636 0613 0592 0572 0.552 0534 05160499 0482 04670451 0437 0423 0.410 5 0822 0784 0747 0713 0681 0.650 0621 0.593 0.567 0543 0519 0497 0.426 0.456 0437 0.419 0402 0386 0370 0355 0341 0328 6 0790 0746 0705 0666 0630 0596 0.564 0.535 0.507 0.480 0456 0432 0.40 0.390 0370 0352 0335 0.39 0.303 0289 0275 0262 7 0760 0711 0665 0623 0.583 0547 0513 0.482 0.452 0425 0400 0376 0354 0333 034 0296 0279 0263 0249 0235 0222 0210 8 0731 0677 0627 0582 0540 0502 0467 0.434 04014 0376 0351 0.327 0305 0285 0266 0249 0233 0218 0204 0191 0179 0168 9 0703 0645 0592 0544 0.500 0460 0424 0.391 0361 033 0308 0.284 0263 0243 0225 0.209 0194 0180 0167 0155 014 0134 10 0676 0614 0558 0508 0463 0422 0386 0352 0322 0295 020 0247 0227 0208 0191 0:176 01620149 0137 01260116 0102 11 0650 0585 0.527 0475 0.429 0388 0.350 0.317 0 287 0261 0237 025 0.195 0178 0562 0149 013501230112 003 0094 0086 12 0625 0552 0497 0.444 0 397 0356 0319 0286 0257 0231 0208 010168 0.152 0137 0124 0.112 0102 0092 0083 0076 0069 13 0.601 0530 0469 0.415 0368 0.326 0290 0.258 0229 0204 0182 01630145 01300116 0104 0093 0084 0.075 006 0051 0055 14 0577 0.505 0.442 0388 0.340 0.299 0 263 0.232 0205 0181 0950 0141 0125 011 0 099 088 008 0069 0062 0055 0049 0044 15 0.555 041 04120362 0315 0275 0.239 0209 0183 01600001230108 0095 0084 007 0065 0057 0051 0045 00400035 10 0534 045 0.394 0339 0292 0252 0218 0188 0163 01410123 0907 0.093 008 007 0062 0054 0047 0042 0036 0032 0028 12 0.513 0.436 0371 03170 270 0231 0198 01700146 0125 0100 0.00 0.069 000 00520045 0039 0034 0000 0026 0023 18 0.494 10.416 0 350 0.296 0.250 0.212 01800153 0130 0111 0095 00690059 005 004 003 0032 0028 0024 0.021 0.018 19 0475 0.3960331 0277 0237 0994 05540138 016 0098 000 0000 00151 0.043 000 0031 0027 0023 0020 001 00M 20 0456 037 0312 0.258 02150178 0.149 0124 0504 00 00 00 05 0043 6037 0.03 0026 0022 0079 0016 OM 0012 121 0.4290359 0294 0242 0199016409350112 09064 30026 0.022 0018 0.015 000 001 0.009 22 0422 0342 278 276 0.184 0.15001230101 03 05 02 00260022 0.018 00150013 001 0009 0.007 20 04060.3260 2620 20170302 09 004 000024220018 0015 0012 000 0009 0.007 0.005 24 0390 0310 02420197 0180126 2006 0023 0.019 0015003 000 000 000 000 0005 25 0.375 0.295 283 184 111 024 D20 0016 003 000 0009 0007 0006 0005 0004 936 0281 0220 0122012016-12002001200400 0009 000 000 0005 0.0040003 27 07 026 0207 0.161 0125 2018 0014 dont 0.009 0007 0005 0.0050004 0.003 0.002 0133 25 0196 01501 00200016 0012 001 000 000 000 0004 0003 000 0002 29 0 029 5014 de 0911 000 0006 0.005 004 000 000 0002 0002 020231050050012 0009 007 00050404 0000 0003 0002 0002 0001 40 02020542 0109000500002 on 0001 000 0000 0000 0000 0000 0000 120-2 Present Value of an Annuity of $1 in Arrears! 1759 Z 7024 Periods 4% 5% 63 73 8% 9% 10% 11% 13 23 1% 156 1 0962 0952 0943 0935 0926 0917 0909 0901 0893 0885 0877 087 0852 0855 0.847 0.840 0833 0826 0820 080 0806 OBDO 16% 2011 21 22 23 24 2 1885 1859 1833 1808 1783 1736 1713 1690 1668 1647 1626 1605 1585 156 1547 1528 1509 1492 104 1457 1460 3 2775 2.723 2673 2524 2572 2.531 2.487 2444 2402 236 2327 2283 2246 2210 2174 2140 2106 2074 2042 204 1981 1952 4 3.630 3.546 34653387 33123 240 31703102 3037 2974 294 2955 2798 273 2690 2639 2589 2540 2494 245 2.404 2352 5 4452 4329 4212 4100 3 993 3.890 391 3696 3605 3517 303 3:352 3274 3199 327 3058 2991 2925 2864 2803 2146 2689 6 5242 5076 4917 4757 4623 4 486 4355 4231 411 3.998 3899 7 3685 359 3498 3410 3326 3.245 3157 3092 3020 2951 60025786 5582 5389 5206 5033 4868 472 4564 4423 4288 490 4939 3922 3812 37063605 3508 345 3327 3242 3151 8 6733 6 463 6210 5.971 5747 5535 533 516 4968 4799 4639 487 43444207 4078 3954 3837 3726 369 356 3421 1129 9 2435 7108 6.HOZ 6.515 6 247 5.995 5759 5537 5328 5132 4946 472 4607 4451 303 43 4031 905 3786 367 3566 3.463 10 8111 7722 7360 6710 6.418 6145 5889 5650 5426 5216 5019 4833 4659 4494 439 412 40543923 3799 352 357 11 8760 83067887 7.499 2139 6.80S 64956 207 5.939 5587 5453 5234 5029 48 40566 437 417 4035 3902 3776 3656 12 905 8863 8384 7943 2535 7561 5814 692 6194 59855421 5498 4793461 4439 427 4127 385 385 1725 113 9.986 9394 885383587904 7.487 710 6.750 64246122 5842 53 532 520 475 284203 405) 1937 14 10563 9 899 9295 8.745824421867367692 6628 630 6.002 57 58 529 500 482461 4432 4265 480 7962324 15 11.118 103092 9.108 8559 8061 76067191 61 462 81422 555 456 4575 49 4315451 00 3869 18 1.62 10.898 10106 9447 831 2824279 6.604 2655945535 435 41 40 18 12 126 127 10477 9763 9122 854 02 75492 67295 575 222 16341 422405910 2610828 905932 8157027250802124240397 3134 12085 1158 33369604 907698H 2640973542 120 3590 12402 0918 91248518 257 446042793950 21 140728212 0835017 292 272525164120296 22 14.45 16 1200 201 9.21 T200203970 20 145791203112221097 SH 4499 0312297 22.10.2015 416 4132 25 1562 ALYA 44322 12 2985 59001 HERE THE SHELTER 4520328 0151 17 3621 1447344574 432454 2950 16 14 12 00528 1192 A 15M 1200 097925344160 2 112 HU Check Required information 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) Net present value c. The internal rate of return is between what two whole discount rates leg., between 10% and its between 11 and 12%, between 12% and 13%, between 13 and 14%, etc.)? The internal rate of returns between and d. Reset the discount rate to 13%. Suppose the salvage value is uncertain How large would the salvage value have to be to result in a d. Reset the discount rate to 13%. 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 net present value