Need Help. please help me with this

B 1 Problem 112 4 --- Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. The cash flows for three independent projects are as follows: Given Data: Project A Project B Project C O initial ($50,000.00) ($100,000.00) (S450,000.00) $10,000.00 $25,000.00 $200,000.00 2 $15,000.00 $25,000.00 $200,000.00 $20,000.00 $25,000.00 $200,000.00 $25,000.00 $25,000.00 $0.00 5 $30,000.00 $25,000.00 $0.00 5 7 B 1 9 10 3 4 11 12 13 14 a. Calculate the IRR for each of the projects 15 16 Project A Project B Project C 17 IRR 10 19 2888 20 21 22 23 b. If the discount rate for all three projects is 10 percent, which project or projects would you want to undertake? Project A Project B Project C Decision c. What is the NPV of each of the projects where the appropriate discount rate is 10 percent? 20 percent? 24 Discount rate 1096 Project A Project B Project C 25 26 27 28 29 20 31 32 33 NPV 8888888 Discount rate 2096 Project A Project B Project 34 NPV 35 07 38 39 Requirements 1 Start Excel In cell D17, by using cell references and the Excel IRR function, calculate the IRR value for project 2 A Copy the formula from cell D17 across columns E and E Note: Do not enter any value for the Guess argument of the Excel IRR function. (I pt.) In cells D22 F22, interpret the IRR values and determine whether projects A, B, and C should be accepted or rejected (1 pt.) In cell D29, by using relative and absolute celi references and the Excel NPV function, calculate the project's NPV. Copy the formula from cell D29 across columns E and F. (1 pt.) In cell D34, by using relative and absolute cell references and the Excel NPV function, calculate the project's NPV. Copy the formula from cell D34 across columns E and F. (1 pt.) 3 40 4 41 5 42 In cell D17, by using cell references and the Excel IRR function, calculate the IRR value for project A Copy the formula from cell D17 across columns E and F. Note: Do not enter any value for the Guess argument of the Excel IRR function In cells D22-F22, interpret the IRR values and determine whether projects A, B, and C should be accepted or rejected. In cell D29, by using relative and absolute cell references and the Excel NPV function, calculate the project's NPV. Copy the formula from cell D29 across columns E and F. In cell D34, by using relative and absolute cell references and the Excel NPV function, calculate the project's NPV. Copy the formula from cell D34 across columns E and F. Plato Energy is an oil and gas exploration and development company located in Farmington, New Mexico. The company drills shallow wells in hopes of finding significant oil and gas deposits. The firm is considering two different drilling opportunities that have very different production potentials. One is in the Barnett Shale region of central Texas, and the other is on the Gulf Coast. The Barnett Shale project requires a much larger initial investment but provides cash flows (if successful) over a much longer period of time than the Gulf Coast opportunity. In addition, the longer life of the Barnett Shale project results in additional expenditures in Year 3 of the project to enhance production throughout the project's 10-year expected life. This expenditure involves pumping either water or CO2 down into the wells in order to increase the flow of oil and gas. The expected cash flows for the two projects are as follows: Given Data: Cash Flows (Barnett Shale) Cash Flows (Gulf Coast) Year Annual Cumulative Annual Cumulative ($5.000.000 ($1.500.000 $2,000,000 $800,000 2 $2.000.000 $800.000 ($1.000.000) $400.000 $2,000,000 $100,000 $1,500,000 6 $1,500,000 7 $1,500,000 $800.000 9 $500.000 10 $100,000 7 0 1 9 10 11 12 3 4 S 13 14 15 16 8 17 18 a. What is the payback period for each of the two projects? 19 20 21 22 23 24 Barnett Shale Gulf Coast Payback Period b. Based on the calculated payback periods, which of the two projects appears to be the better alternative? 25 26 27 2$ appears to be the best alternative. c. If Plato's management uses a 20 percent discount rate to evaluate the present values of its energy investment projects, what are the NPVs of the two proposed investments? 29 Discount Rate 20% 30 31 32 33 34 35 36 Barnett Shale Gulf Coast NPY 38 1 Start Excel In cells E8-E18, do the following In cell E8 by a using cell reference, determine the cumulative cash flow for year 0 2 In cell E9 by using cell references, calculate the cumulative cash flow for year 1. Copy the formula from cell E9 down the column to cell E18. 39 Note: The outputs of the expressions or function you typed in these cells are expected as negative numbers in case of cash outflows (1 pt.) In cells G8-G12, do the following In cell G8, by using cell reference, determine the cumulative cash flow for year 0. 3 In cell G9 by using cell references, calculate the cumulative cash flow for year 1. Copy the formula from cell G9 down the column to cell G12 40 41 5 Note: The outputs of the expressions or function you typed in these cells are expected as negative numbers in case of cash outflows. (I pt.) In cells D23 and E23, by using cell references, calculate the payback period values. Use relevant cells among C8 C18 as well as cells from the cash flows columns Note: Do not type a numeric value the payback period must be calculated. (1 pt.) In cell C27, determine which project appears to be the best alternative Note: Select from the drop-down menu in cell C27 (1 pt.) In cell D34, by using cell references and the Excel NPV function, calculate the NPV of the Barnett Shale project Note: Enter the appropriate array into the Valuel argument of the Excel NPV function (1 pt.) In cell E34, by using cell references and the Excel NPV function, calculate the NPV of the Gulf Coast project 42 6 43 7 14 Note: Enter the appropriate way into the Valuel argument of the Excel NPV function. (I pt.) In cell E8, by using a cell reference, determine the cumulative cash flow for year 0. In cell E9, by using cell references, calculate the cumulative cash flow for year 1. Copy the formula from cell E9 down the column to cell E18. Note: The outputs of the expressions or function you typed in these cells are expected as negative numbers in case of cash outflows. In cells G8-G12, do the following: In cell G8, by using a cell reference, determine the cumulative cash flow for year 0. In cell G9, by using cell references, calculate the cumulative cash flow for year 1. Copy the formula from cell G9 down the column to cell G12 Note: The outputs of the expressions or function you typed in these cells are expected as negative numbers in case of cash outflows. In cells D23 and E23, by using cell references, calculate the payback period values. Use relevant cells among C8-C18 as well as cells from the cash flows columns Note: Do not type a numeric value, the payback period must be calculated. In cell C27, determine which project appears to be the best alternative. Note: Select from the drop-down menu in cell C27. In cell D34, by using cell references and the Excel NPV function, calculate the NPV of the Barnett Shale project. Note: Enter the appropriate array into the Value 1 argument of the Excel NPV function In cell E34, by using cell references and the Excel NPV function, calculate the NPV of the Gulf Coast project Note: Enter the appropriate array into the Value 1 argument of the Excel NPV function