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. Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell C21 enter the formula = 810". Note: The present value factors could be computed using the built-in Excel function PV, but we recommend using the formulas in Appendix 148. Verify that your worksheet matches the example in the text." Check your worksheet by changing the discount rate to 10%. The net prosent value should now be between $56,400 and $56,535-depending on the precision of the calculations. If you do not get an answer in this range, find the errors in your worksheet and correct them. If you did not use formules to calculate the Discount Factors, be sure that those values are updated appropriately using the Appendix 148 formulas that are also noted at the bottom of the spreadsheet instead of using the Discount Factor tables. Click here to view Exhibit 148-1 and Exhibit 148-2. to determine the appropriate discount factor(s) using tables, Save your completed Applying Excel form to your computer and then upload it here by clicking "Browse." Next, click "Save." You will use this worksheet to answer the questions in Part 2. DHIST 1491 recent Value of the UN Periods 1 2 3 4 5 10 0 0 - 3 SN 65 9 10% 111 0.962 0952 0943 0.935 0.926 0907 15 SON 0.909 0.901 0.893 0.385 0877 0.70 0.362 0.855 0.347 0340 0.00 0.920 0820 000306 000 17 199 200 21 22 22 20 25 0.925 0.907 0.90 0.873 0.657 0.542 0.826 0812 0.797 0783 0709 09.99 0.864 0.940 0.16 0.794 0772 0756 0730731 0.751 0731 0712 0.7180.06 0.694 063 0672 0.001 00500600 0955 0.8230792 0763 07350.708 0.03 0.659 0.636 0013 0:592 0.572 0552 0534 0510 0.499 0.32 0.467 0.51 0.437 04230410 0.693 06750650 0541 0624 0609 0.593 0.579 0.5840551 0524050 0522 0.784 0747 0.71306810650 0621 0.593 0.567 050 0.519 0497 0.476 0456 0.437 0.4100402 0.386 0.3700355 0.34 0.328 07900746 0705 0666 0.630 0.596 0564 0.535 0.507 0.430 0456 0432 0.410 0.30 0370 0.52 0.335 0.39 0.303 0.209 02750202 0.700 71 0.665 0.623 0.58305470530492 0.452 0.425 0.400 0.375 0.354 0.333 0.31 0.296 02790 200 0.2490235 0222 0210 0.751 0.677 0.677 0.5820540 0.502 0.467 0034 0.404 0376 0.351 0.327 0.305 0.265 0.206 0249 023 024 0.2040191 0.703 06450.592 0.544 0.500 0.400 0.4240391 0.361 0.333 0.308 0254 0263 0.23 0.225 0.209 0:194 090168 067506140558 0.508 0463 0422 0.386 0352 0.322 0.295 0.270 0247 0.227 0.208 0191 0.150017 015501610134 017601620149 037 012600 0.02 0.65005850527 0.4750429 0388 0350 0.31 0.207 0.261 0237 02150195 017 0.952 0148 035 0123 0112 0.625 0.557 04970444 0.397 0356 03190286 0.257 0.231 0208 0187 01000054 0056 0.1500152 0037 0.124 0112 0102 0092 0.003 00700000 0.601 0530 041004150368 0.326 0290 0.250 0.229 0204 0.192 01635450130 0:16 03040091 0034, 0075 0.000 0.001 0.055 0.577 0.505 0.442 038 0340 0.299 0263 0.232 0.205 0.101 0.160 041 0.125 0.1 0.099 0.00 0.078 0000000055 0.049000 0555 0.41 0.417 0.362 0.315 0.275 0.29 0.209 0183 0160 01001230108 0095 0004 0.074 0.005 0.057 0.051 0045 004 0035 0.534 045 0.394 0.339 0.292 0252 0.21 0.188 0163 01410123 0807 0.093 0.001 0.07 0.062 0054 0.0470012 0.036 0012 0.023 05130435 0371 0317 0270 0221 0198 010 0146 0.125 0100 0.093 00800069 0.060 0052 0045 0.039 6034 0.000 0.026 0.023 0491 0.416 0.350 0.296 0.250 0.212 0.1300153 01300m 0.095 0.001 0.009 0.055 0.051 0044 0.03 0.002 0.0200.024 0.021 0:00 0.475 0.396 0.731 0 277 0.232 0.194 0164 0130 0.11 0.098 003 007000600051 0043 0037 0031 0.027 0023 00200017001 0456 0377 0.312 0258 02150178 0.109 0124 0304 087 0073 0.0610051 000 0.037 0031 0.025 0.022 0.019 0.00 0.00 0.00 0409 0.359 0.21 0242 0199 0164 0035 0.112 0.093 0.0770064 0.053 0044 0097 0.0310026 0.022 0.010001000001 0.009 042203420278 0.226 0194 015001230101 0.083 0.008 0.056 0.046 0030 0032 0.025 0.022 0.0080015000 0.01 0000 0.007 0406 0326 0.262 0211 0.170 000 0112 0.001 0.074 0000 0000 000 0033 0027 0022 0% 0.015 0012 000 0.000 0.007 0.006 0.390 0310 0.2470197 0156 0126 0102 0082 0.006 0.053 000 0.035 0020 021 0099001500000100000 0.007 0000 000 075 0295 02330184 0.116 0092 0.074 0050001700800300024 0.020000000000000000002 0.0060005 000 0.361 0.21 0.2200172 0135 0100 0.00 0.00 0.00 0.00 0.0040003 0.014 0.006 0053 0042 0.03 0.026 0.021 00 00 00 0:47 02080207 0101 0125 0.0980026 0.000 0.007 0.03700200023 0.01 0.01 001 0000 0.00 0.00 0.00 0.00 0.0000002 030 0255 0.196 0150 011 0.012 0010 0000 0000 0005 0004 000000000002 0.00000690054 00420.033 0.02000200016 0.321 02430185 0143 0107 008200630080037 0029002200110010011 0000 0000 0005 0.001 0000 0002 0.002 0.002 0.308 0210114 001 0.099 0.075 005 004 003 0.020002000150012 0.000 0.00700050004 0000 0000 000 000 000 0208 0300097 0.067 0.046 0.0320022 0.01 0.01 0.000 0.005 0004 0.003 0002 0.001 0.001 0.001 0000 0000 0.000 0.000 0.000 20 22 0140 24 25 26 27 28 29 30 40 EXHIBIT 140-2 Pretant eve of an Annuity of 51 Arreart 25 8.244 Periods 53 5% 95 101 1 0.362 0952 0.04 0935 19 15 102 1 0.926 0917 10% 205 27 090909010990 0.065 087 0.870 0.162 0.8550047 0.540 033 0325 0.020 000 000 000 20 2 1996 1SSO 1830 1300 13 1759 1756 121 1600 1668 1647 2 2775 2723 2.673 2624 2.577 2531 1626 16051585156615471528150914921457140 247 2444 2402 2261 2222 223 2246 2.200 2174 3.630 3545 3465 3387 3.32 3.220 2.300 2.106 2074 2042 2011 3.170 1901 1952 3.102 2007 2.974 2.954 2055 2.7982743 2090 2600 2500 2500 2054 24 2404 2352 5 3452 4320 1212 4900 2.993 2990 3.791 3.696 3805 3517 3433 352 3274 3199 22 1050 2991 2.926 2064 2303 2745 2.600 . 52423.076 4917 4767 4623 4416 43554231 4.1 3990 380 3.78436052509 3.498 3410 1326 1.245 1957 7 8.002 51786 5582 5389 5.200 2092 2090 2.951 5030 485 712 45644423 42804160 B 4.039 3.922 1012 370635053508 2416 2712421189 673 6.463 5210 5971 5747 5.535 5.335 50 4.968 4790 4639 4.487 434442074078 354 38373726361051421129 9 7435 2900 6.802 6515 61247 5995 57595537 532850249464772 4507 4451 43034563 4031 2005 206 2073 3565 10 8.111 7722 7350 7024 6710 6545 5.889 5650 5.426 5.216 5.019 4833 4650 4.494 43394192 4054 1923 3.7992682 11 8700 300 7587 2499 7539 0.805 6.495 6.202.939 5.6875453 5.234 5.029 4836 46564436 43274 LOS 300237753650 12 91285 8.863 304 7943 7536 7181 6 814 6.492 614 5.9185660 5421 5197 4950 4793461 4.439 4.278 427 3.9853051725 9.990 9.394 8.8538.358 7904 7487 7103 6.750 6.424 6122 342 5.503 5342 5118 4.910 471 4533 43420140532901780 14 10.553 9.599 9.295 8.745 7786 7.3676.002 0.620 602 0.002 57245.000 5229 5.000 4802 4611 1432203450839621124 TE 10.3809712 9100 3550 0.001 75007191 00116462 6342 5.047 5.575 5.324 5.092 416 4675 4400 4.15 415340011050 1652 10.838 10100 9.447 851 03 7324 7329 6.974.008 6265 5.954 5000 5.00 5.1624900 47304596 4104031007 12156 11274 10.4779.703 9122 8544 3.02275497120 67296373 6047 579 5475 5.222 4990 47734574391 4.219 4059 190 12650 11000 10.828 10.0599372 3.750 201 7702 7.250 6.540 6.057 120 500 5534 5.2735033482 4.600 410 424 4000 1920 13.13 12.085 1158 10.336 9004 3950 336528397300 6.938 65506198 58715.50 5.4 5070 480 4635 442 4200 4097 3242 20 13.500 2.40211470 10.594 0.010 9129 8.51796324097025 6623 6.250 5.920 S628 5353 5101 4370 4657 4450 4270 4110 1554 21 1020 2021 1170 10836 2017 9292 3649 11.0757562730266376312 50735005 5334 50 49 4675 4.476 4203412119 22 St120421001 10.201 942 1772 0.120 7645 2170 6.70 6.350 60 5.000 410540 4.909 4090 44430041303970 23 457 409 12.30311272 10371 9.500 8.8832002710 223067926604572) 5412 55674925 47034490 401 1970 24 15.247 13.790 12.550 1.409 10.529 9.707 3905 340770472010005 0.434 607 576 5451 532 433 470 450 450 1501 25 15.622 1004 2700 1654 106759023 90778422700730676.464 0.07 5.700 467 5.0644214514 323 407 1905 26 150314375 003 1026 0.8109929 9101 1148420967.372 6.900 6491 6110 5705.000 5.20049647245204284151 1990 27 56.330 160 211 1907 10.035 1002792371541 7943 7409 6005 661 016 7995492521549644734524 433 4154 1992 28 16031409013406 12.137 11051 10.116 6.9616534 6.152 5.810 5.502 522349704.739452143154152 9.307 1.602 79047441 433741503.994 29 5.820 5510 5229 4975 4743 16.904 15:41 2591 10.198 12.27871150 9370 8.50 8022 7470 690 6551 6.166 5829 551 5215 49747404554 433941001995 30 17.292 15172705 2.400 112581027494278.001 0.055749670036.566677 8.642 6235871548 5.258 4.99747005443474106990 40 1979312150 15.0461333210259075707798951324476347105 SENSOS&S D E F G B 1 Chapter 14: Applying Excel 2 3 Data 4 Example E 5 Cost of equipment needed $60,000 6 Working capital needed $100,000 7 Overhaul of equipment in four years $5,000 8 Salvage value of the equipment in five years $10,000 9 Annual revenues and costs: 10 Sales revenues $200,000 11 Cost of goods sold $125,000 12 Out-of-pocket operating costs $35,000 13 Discount rate 14% 14 15 Enter a formula into each of the cells marked with a bolow 16 Exhibit 14-8 17 18 Now 19 Purchase of equipment 2 20 Investment in working capital 2 21 Sales 22 Cost of goods sold 23 Out-of-pocket operating costs 24 Overhaul of equipment 25 Salvage value of the equipment 26 Working capital released 27 Total cash flows (a) 2 28 Discount factor (14%) (b) 2 29 Present value of cash flows (a) (b) 2 30 Net present value 7 31 32 "Use the formulas from Appendix 138: 33 Present value of $1 = 1/(1+ryn 34 Present value of an annuity of $1 = (1/0)*(1-(1/(1+rn)) 35 where n is the number of years and is the discount rate 36 37 1 Years 3 2 4 ON 5 ? 2 2 2 ? 2 ? ? ? ? 2 ? ? 2 2 2 ? 2 ? ? ? ? 2 ? ? ? ? 2 2 2 2 2