these must be calculated by using cell references, not numerical inputs, please help me!

G H Complete the steps below using cell references to given data or 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 celll 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. previous calculations. In some cases, 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 Bamett 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 (Gulf Coast) Cumalative Cash Flows (Barnett Shale) Annual Cumulative Ansual Year (S1,500,000) S800,000 ($5,000,000) $2,000,000 $2,000,000 (S1,000,000) 1 S800,000 $400,000 S100.000 2 10 $2,000,000 S1,500,000 $1.500,000 6 4 S1.500,000 15 S800,000 s500,000 S100,000 10 a. What is the payback period for each of the two projects 11-23 fr G C the payback period for each of the two projects? Gulf Coast Barnett Shale Payback Peri b. Based on the calculated payback periods, which of the two projects appears to be the better altenative? appears to be the best altemative. 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? Discount Rate 20% Barnett Shale Gulf Coast NPV Requirements 1 Start Excel. In cells E8-E18, do the following In cell E8, by a using cell reference, determine the cumulative cash flow for vear 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, Note: The outputs of the expressions or function you typed in these cellls are expected as negative mumbers 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 11-23 Bead W lynne_Titman Problem 11-23 Sta OSearch Help Review View Data Page Layout Formulas Draw Insert areful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View CTED VIE H G C 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. Note: The outputs of the expressions or function you typed in these cells are expected as negative Tumbers in case of cash outflows. (1 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 mumeric value, the payback period In cell C27, determine which project appears to be the best alternative. must be calculated. (1 pt.) 5 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 Barmett Shale project. 6 Note: Enter the appropriate array into the Valuel argument of the Excel NPV function. (1 pt.) 43 In cell E34, by using cell references and the Excel NPV fiunction, calculate the NPV of the Gulf Coast project. Note: Enter the appropriate array into the Valucl argument of the Excel NPV function, (1 pt.) 8 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 44 45 44 41 AP an 50 11-23