The instructor set up an excel spreadsheet to use. The spreadsheet is confusing me based on previous "helps" to the same question. Can you help me understand the excel layout?

A B D E F G H K M N Problem 10-16B Using present value techniques to evaluate alternative investment opportunities 1 Alternative A 2 Year o 3 Yr 1 4 Yr 2 5 Yr 3 6 Yr 4 7 Yr 5 8 PV of cash flows 9 NPV 10 NPV Index 11 IRR "="NPV(12%,B3:37) "-"B2+B8 "="B8/-B2 "="IRR(B2:37) Perry Automobile Repair Inc. currently has three repair shops in Boston. Jerry Perry, the president and chief executive officer, is facing a pleasant dilemma: the business has continued to grow rapidly and major shareholders are arguing about different ways to capture more business opportunities. The com- pany requires a 12 percent rate of return for its investment projects and uses the straight-line method of depreciation for all fixed assets. One group of shareholders wants to open another shop in a newly developed suburban community. This project would require an initial investment of $600,000 to acquire all the necessary equipment, which has a useful life of five years with a salvage value of $200,000. Once the shop begins to operate, another $150,000 of working capital would be required; it would be recovered at the end of the fifth year. The expected net cash inflow from the new shop follows. Year 1 Year 2 Year 3 Year 4 Year 5 12 13 Alternative B 14 Year 0 15 Yr 1 16 Yr 2 17 Yr 3 18 Yr 4 19 Yr 5 20 PV of cash flows 21 NPV $75,000 $125,000 $190,000 $240,000 $300,000 "="NPV(12%,B15:319) "-"B14+B20 "="B20/-B14 "="IRR(B14:B19) 22 NPV Index 23 IRR 24 25 26 A second group of shareholders prefers to invest $500,000 to acquire new computerized diagnostic equipment for the existing shops. The equipment is expected to have a useful life of five years with a salvage value of $100,000. Using this state-of-the-art equipment, mechanics would be able to pinpoint automobile problems more quickly and accurately. Consequently, it would allow the existing shops to increase their service capacity and revenue by $156,250 per year. The company would need to train mechanics to use the equipment, which would cost $56,250 at the beginning of the first year. Required Round your computations to two decimal points. a. Determine the net present value of the two investment alternatives. b. Calculate the present value index for each alternative. c. Indicate which investment alternative you would recommend. Explain your choice. 27 28 29 30 31 32