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 (Gulf Coast) Annual Cumulative ($1,500,000) $800,000 $800,000 $400,000 $100,000 0 1 -2 13 14 15 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 Year 0 1 2 3 4 5 6 7 8 9 10 16 a. What is the payback period for each of the two projects? 17 18 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 appears to be the best alternative. 28 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 30 Discount Rate 20% 31 32 33 Barnett Shale Gulfast 34 35 NPV Requirements 1 Start Excel 2 3 In cells E8-E18, do the following: In cell E8, by a using 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. (1 pt.) In cells G8-G12, do the following: In cell G8, by using 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. (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 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 7 Note: Enter the appropriate array into the Valuel argument of the Excel NPV function. (1 pt.) 8 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 5 6
