Kindly help to solve this mini case and please find attached document for case problems and solution template.

PROBLEM 3-9: ConocoPhillips Natura Given ConocoPhillips's Cost of Capital for project Project life 15.00% 10 years Solution 1. Investment Increase in NWC MACRS Depr Rate (7 year) Natural Gas Wellhead Price (per MCF) Volume (MCF/day) Days per year Fee to Producer of Natural Gas Compression & processing costs (per MCF) $ 0 1,200,000 145,000 1 2 0.1429 6 900 365 $3.00 0.65 0.2449 Cash Flow Calculations Natural Gas Wellhead Price Revenue Lease fee expense Compression & processing costs Depreciation expense Net operating Profit Less: Taxes (40%) Net operating profit after tax (NOPAT) Plus: Depreciation expense Return of net working capital Project Free Cash Flow NPV IRR 2a-c. Scenario Summary Current Values Changing Cells NG Price Production Rate Result Cells NPV IRR 6 900 Best Case Most Likely Case 8 1200 6 900 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. 3. Breakeven Sensitivity Analsyis Students should use Goal Seek in Excel to answer this question. a. Breakeven nautral gas price for an NPV = 0 b. Breakeven natural gas volume in Year 1 for an NPV = 0 c. Breakeven investment for an NPV = 0 4. Student answers will vary but most will probably recommend the project. M 3-9: ConocoPhillips Natural Gas Wellhead Project Solution 3 4 0.1749 Worst Case 3 700 mmary Report was created. n. 0.1249 Years 5 0.0893 6 0.0893 7 0.0893 8 0.0445 9 project. Solution Legend 10 = Value given in problem = Formula/Calculation/Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Problem 3-12 CONCOPHILIPS GAS ACQUISITION PROJECT ConocoPhillips (COP) Natural Gas and Gas Products Department (NG&GP) manages all of the company's activities relating to the gathering, purchase, processing, and sale of natural gas and gas liquids. Chris Simpkins, a recent graduate, was recently hired as a financial analyst to support the NG&GP department. One of Chris's first assignments was to review the projections for a proposed gas purchase project the were made by one of the firm's field engineers. The cash flow projections for the 10-year project are found in Exhibit P3-12.1 and are based on the following assumptions and projections: The investment required for the project consists of two components: First, there is the cost to lay the natural gas pipeline of $1,200,000. The project is expected to have a 10-year life and is depreciated over seven years using a seven-year modified accelerated cost recovery system (MACRS). Second, the project will require a $145,000 increase in net working capital that is assumed to be recovered at the termination of the project. The well is expected to produce 900,000 cubic feet (900 MCF) per day of natural gas during Year 1 and then decline over the remaining nine-year period (365 operating days per year). The natural gas production is expected to decline at a rate of 20% per year after Year 1. In addition to the initial expenditures for the pipeline and additional working capital, two more sets of expenses will be incurred. First, a fee consisting of 50% of the wellhead natural gas market price must be paid to the producer. In other words, if the wellhead market price is $6.00 per MCF, 50% (or $3.00 per MCF) is paid to the producer. Second, gas processing and compression costs of $0.65 per MCF will be incurred. There is no salvage value for the equipment at the end of the natural gas lease. The natural gas price at the wellhead is currently $6.00 per MCF. The cost of capital for this project is 15%. a.) What are the NPV and IRR for the proposed project, based on the forecasts made above? Should Chris recommend that the project be undertaken? Explain your answer. What reservations, if any, should Chris have about recommending the project to his boss? b.) Perform a sensitivity analysis of the proposed project to determine the impact on NPV and IRR for each of the following scenarios: 1.) Best case: a natural gas price of $8.00 and a Year 1 production rate of 1,200 MCF per day that declines by 20% per year after that. 2.) Most likely case: a natural gas price of $6.00 and a Year 1 production rate of 900 MCF per day that declines by 20% per year after that. 3.) Worst case: a natural gas price of $3.00 and a Year 1 production rate of 700 MCF per day that declines by 20% per year after that. c) Do breakeven sensitivity analysis to find each of the following: 1) Breakeven natural gas price for an NPV = 0. 2) Breakeven natural gas volume in Year 1 for an NPV = 0. 3) Breakeven investment for an NPV = 0. d) Given the results of your risk analysis in parts b and c, would you recommend this project? Explain your answer Year Investment 0 $ 1,200,000 1 2 3 Year Increase in NWC 0 1 2 3 145,000 MACRS depreciation rate (7 years) 0.1429 0.2449 0.1749 Natural gas wellhead price (per MCF) $ 6.00 $ 6.00 $ 6.00 Volume (MCF/day) 900 720 576 Days per year 365 Fee to producer of natural gas (per MCF) $ 3.00 $ 3.00 $ 3.00 Compression and processing costs (per MCF) 0.65 0.65 0.65 Natural gas wellhead price revenue $ 1,971,00 0 $ 1,576,80 0 $1,261,4 00 Lease fee expense 985,500 788,400 630,720 Compression and processing costs 213,525 170,820 136,656 Depreciation expenses 171,480 293,880 209,880 Net operating profit 600,495 323,700 284,184 Less taxes (40%) (240,198) (129,480) (113,674 ) Net operating profit after tax (NOPAT) 360,297 194,220 170,510 Plus depreciation 171,480 293,880 209,880 $ 531,777 $ 488,100 $ 380,390 Cash Flow Calculations Return of net working capital Project free cash flow $(1,345,00 0) Exhibit P312.1 Analysis of the ConcoPhilips Gas PurchaseProject Alternate View 4 5 6 7 8 9 10 0.1249 0.0893 0.0893 0.0893 0.0445 $ 6.00 $ 6.00 $ 6.00 $ 6.00 $ 6.00 $ 6.00 $ 6.00 461 369 295 236 189 151 121 $ 3.00 $ 3.00 $ 3.00 $ 3.00 $ 3.00 $ 3.00 $ 3.00 0.65 0.65 0.65 0.65 0.65 0.65 0.65 $1,009,152 $807,322 $645,857 $516,686 $413,349 $330,679 $264,543 504,576 403,661 322,929 258,343 206,674 165,339 132,272 109,325 87,460 69,968 55,974 44,779 35,824 28,659 149,880 107,160 107,160 107,160 53,400 245,371 209,041 145,801 95,209 108,495 129,516 103,613 (98,148) (83,616) (58,320) (38,083) (43,398) (51,806) (41,445) 147,223 125,425 87,480 57,125 65,097 77,710 62,168 149,880 107,160 107,160 107,160 53,400 145,000 $ 297,103 $232,585 $194,640 $164,285 $118,497 $ 77,710 $207,168