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. 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: Year 0 1 Cash Flows (Gulf Coast) Annual Cumulative ($1,500,000) $800,000 $800,000 $400,000 $100,000 2 3 Cash Flows (Barnett Shale) Annual Cumulative ($5,000,000) $2,000,000 $2,000,000 ($1,000,000) $2,000,000 $1,500,000 $1,500,000 $1,500,000 $800,000 $500,000 $100,000 4 5 6 7 8 9 10 a. What is the payback period for each of the two projects? Barnett Shale Gulf Coast Payback Period b. Based on the calculated payback periods, which of the two projects appears to be the better alternative? 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? Discount Rate 20% Barnett Shale Gulf Coast NPV 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. 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: Year 0 1 Cash Flows (Gulf Coast) Annual Cumulative ($1,500,000) $800,000 $800,000 $400,000 $100,000 2 3 Cash Flows (Barnett Shale) Annual Cumulative ($5,000,000) $2,000,000 $2,000,000 ($1,000,000) $2,000,000 $1,500,000 $1,500,000 $1,500,000 $800,000 $500,000 $100,000 4 5 6 7 8 9 10 a. What is the payback period for each of the two projects? Barnett Shale Gulf Coast Payback Period b. Based on the calculated payback periods, which of the two projects appears to be the better alternative? 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? Discount Rate 20% Barnett Shale Gulf Coast NPV