need sensitivity chart and tornado chart

PROBLEM 3-9: ConocoPhillips Natural Gas Wellhead Project Given ConocoPhillips's Cost of Capital for project Project life 15.00% 10 Solution Solution Legend 1. 0 ($3,086,309) (145,000) 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) 1 2 3 6 7 8 9 10 0.1429 $3.68 3,943 365 $1.84 $0.65 $(3,231,309) NPV IRR 0.2449 $4.00 3,154 0.1749 $2.95 2,524 0.1249 $4.88 2,019 0.0893 $5.46 1,615 0.0893 $7.33 1,292 0.0893 $6.39 1,034 0.0445 $6.25 827 $7.97 662 $3.67 529 $1.84 $0.65 $1.84 $0.65 $1.84 $0.65 $1.84 $0.65 $1.84 $0.65 $1.84 $0.65 $1.84 $0.65 $1.84 $0.65 $1.84 $0.65 $5,296,238 2,648,119 935,477 441,034 $1,271,608 (508,643) $762,965 441,034 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 $4,605,424 2,118,495 748,381 755,837 $982,710 (393,084) $589,626 755,837 $2,717,200 1,694,796 598,705 539,795 $(116,096) 46,439 $(69,658) 539,795 $3,595,915 1,355,837 478,964 385,480 $1,375,634 (550,254) $825,380 385,480 $3,218,639 1,084,669 383,171 275,607 $1,475,191 (590,076) $885,114 275,607 $3,456,794 867,736 306,537 275,607 $2,006,914 (802,766) $1,204,149 275,607 $2,410,796 694,188 245,230 275,607 $1,195,770 (478,308) $717,462 275,607 $1,886,382 555,351 196,184 137,341 $997,506 (399,003) $598,504 137,341 $1,924,411 444,281 156,947 $1,323,184 (529,273) $793,910 - $1,203,999 $1,345,463 $470,138 $1,210,860 $1,160,722 $1,479,756 $993,070 $735,845 $793,910 $708,917 355,424 125,558 $227,935 (91,174) $136,761 145,000 $281,761 $1,960,476 31.12% 2a-c. Scenario Summary Most Likely Current Values Best Case Case Worst Case Changing Cells NG Price $3.68 $8.00 $6.00 $3.00 Production Rate 3,943 1,200 800 700 Result Cells NPV $1,960,476 $27,159 $(1,276,330) $(2,059,033) IRR 31.12% 15.31% -0.11% -10.34% 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 $8.04 b. Breakeven natural gas volume in Year 1 for an NPV = 0 c. Breakeven investment for an NPV = 0 Years 5 4 2179 $(5,703,034) 4. Analysis The NPV and IRR indicates that the projected cash flows for this proposed project are significantly above the required rate of return. The breakeven natural gas price is slightly higher than the worst case projected price and well below the most likely outcome. The breakeven gas volume at current prices is less than half of current rate, but there is some concern when comparing them to the values of the different projected scenarios. The breakeven point is above the best case scenario, so if the actual production when the project starts is at the scenario levels versus the projected the project will end up producing at a loss. There seems to be a lot of risk in the variability of the future numbers that Chris should be hesitant to promote this project without additional research. = 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-9: ConocoPhillips Natural Gas Wellhead Project Given Price ConocoPhillips's Cost of Capital for project Project life 15.00% 10 Solution Solution Legend 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 $3,086,309 145,000 1 2 3 4 Years 5 6 7 8 9 0.1429 $3.68 3943 365 $1.840 0.65 $(3,231,309) NPV IRR $5,163,164.59 31.01% 0.2449 $4.00 3154 365 $1.840 0.65 0.1749 $2.95 2524 365 $1.840 0.65 0.1249 $4.88 2019 365 $1.840 0.65 0.0893 $5.46 1615 365 $1.840 0.65 0.0893 $7.33 1292 365 $1.840 0.65 0.0893 $6.39 1034 365 $1.840 0.65 0.0445 $6.25 827 365 $1.840 0.65 $7.27 662 365 $1.840 0.65 $3.67 529 365 $1.840 0.65 $5,296,238 2,648,119 935,477 441,034 $1,271,608 508,643 $762,965 441,034 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 = 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 10 $4,604,840 2,118,226 748,287 755,837 $982,490 392,996 $589,494 755,837 $2,717,717 1,695,118 598,819 539,795 $(116,016) (46,406) $(69,610) 539,795 $3,596,243 1,355,960 479,008 385,480 $1,375,795 550,318 $825,477 385,480 $3,218,534 1,084,634 383,159 275,607 $1,475,133 590,053 $885,080 275,607 $3,456,681 867,707 306,527 275,299 $2,007,148 802,859 $1,204,289 275,299 $2,411,650 694,434 245,317 275,607 $1,196,292 478,517 $717,775 275,607 $1,886,594 555,413 196,206 137,649 $997,325 398,930 $598,395 137,649 $1,756,650 444,599 157,060 $1,154,991 461,997 $692,995 - $1,203,999 $1,345,331 $470,186 $1,210,957 $1,160,687 $1,479,588 $993,382 $736,045 $692,995 $708,622 355,276 125,505 $227,840 91,136 $136,704 145,000 $281,704 ### Best Case 1200000 8 1200 Page 3 Most Likely Case 1200000 6 900 Page 4 Worst Case 1200000 3 700 Page 5 Breakeven - Natural Gas Price 1200000 4.978281356 900 Page 6 Breakeben - Natural Gas Volume 1200000 6 704.351749 Page 7 Breakeven - Initial Investment 1573795 6 900 Page 8 Variable Investment WACC Compression and Processing Costs Price 1 Price 2 Volume 1 NWC Volume 2 Volume 3 Price 9 Price 10 Volume 9 Volume 10 Downside $(419,446) $(471,471) $(473,719) $(536,089) $(527,951) $(524,948) $(498,436) $(520,201) $(516,899) $(510,818) $(510,372) $(510,207) $(509,947) NPV Upside $(599,257) $(544,311) $(544,984) $(482,614) $(490,752) $(493,755) $(520,267) $(498,502) $(501,804) $(507,885) $(508,331) $(508,496) $(508,756) Range $179,810 $72,839 $71,266 $53,474 $37,199 $31,193 $21,832 $21,700 $15,095 $2,933 $2,040 $1,711 $1,190 Downside $1,080,000 13.50% 0.59 $2.81 $2.81 810 130,500 648 518 $2.81 $2.81 136 109 Input Upside Base Case $1,320,000 $1,200,000 16.50% 15.00% 0.72 0.65 $3.43 $3.12 $3.43 $3.12 990 900 159,500 145,000 792 720 634 576 $3.43 $3.12 $3.43 $3.12 166 151 133 121