Hi-

Having difficulty on the below question, which can be found in valuation book P 3-12. Can you please provide excel formula equations for everything highlighted in green & orange?

ConocoPhillipss (COP) Natural Gas and Gas Products Department (NG&GP) manages all of the companys activities relating to the gathering, purchasing, 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 Chriss first assignments was to review the projections for a proposed gas purchase project that were made by one of the firms field engineers. The cash flow projections for the ten-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 ten-year life and is depreciated over seven years using a seven-year modified accelerated cost recovery system (MACRS).20 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.

20 Modified accelerated cost recovery system (MACRS) uses a shorter depreciable life for assets, thus giving businesses larger tax deductions and cash flows in the earlier years of the project life relative to those of straight-line depreciation.

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%.

Answer the following questions.

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?

Book Exibit:

Given Information
ConocoPhillips's Cost of Capital for project	$ 1,200,000.00
Project life	10
Solution
1.	Years
	0	1	2	3	4	5	6	7	8	9	10
Investment	$ (1,200,000)
Increase in NWC	(145,000)
MACRS Depr Rate (7 year)
Natural Gas Wellhead Price (per MCF)
Volume (MCF/day)
Days per year		365	365	365	365	365	365	365	365	365	365
Fee to Producer of Natural Gas
Compression & processing costs (per MCF)
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

\begin{tabular}{|c|c|c|c|c|c|c|c|} \hline \multicolumn{2}{|c|}{4} & 5 & 6 & 7 & 8 & 9 & 10 \\ \hline & 0.1249 & 0.0893 & 0.0893 & 0.0893 & 0.0445 & & \\ \hline \multirow[t]{2}{*}{$} & 6.00 & 6.00 & 6.00 & 6.00 & 6.00 & 6.00 & 6.00 \\ \hline & 461 & 369 & 295 & 236 & 189 & 151 & 121 \\ \hline \multirow[t]{2}{*}{$} & 3.00 & 3.00 & 3.00 & 3.00 & 3.00 & 3.00 & 3.00 \\ \hline & 0.65 & 0.65 & 0.65 & 0.65 & 0.65 & 0.65 & 0.65 \\ \hline \multicolumn{2}{|c|}{$1,009,152} & $807,322 & $645,857 & $516,686 & $413,349 & $330,679 & $264,543 \\ \hline \multicolumn{2}{|r|}{504,576} & 403,661 & 322,929 & 258,343 & 206,674 & 165,339 & 132,272 \\ \hline \multicolumn{2}{|r|}{109,325} & 87,460 & 69,968 & 55,974 & 44,779 & 35,824 & 28,659 \\ \hline \multicolumn{2}{|r|}{149,880} & 107,160 & 107,160 & 107,160 & 53,400 & - & - \\ \hline & 245,371 & 209,041 & 145,801 & 95,209 & 108,495 & 129,516 & 103,613 \\ \hline & (98,148) & & (58,320) & (38,083) & (43,398) & (51,806) & \\ \hline & 147,223 & 125,425 & 87,480 & 57,125 & 65,097 & 77,710 & 62,168 \\ \hline & 149,880 & 107,160 & 107,160 & 107,160 & 53,400 & - & - \\ \hline & & & & & & & 145,000 \\ \hline$ & 297,103 & $232,585 & $194,640 & $164,285 & $118,497 & $77,710 & $207,168 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|} \hline Year & 0 & \multicolumn{2}{|r|}{1} & \multicolumn{2}{|r|}{2} & \multicolumn{2}{|r|}{3} \\ \hline Investment & $1,200,000 & & & & & & \\ \hline Increase in NWC & 145,000 & & & & & & \\ \hline \begin{tabular}{l} MACRS depreciation rate \\ (7 years) \end{tabular} & & & 0.1429 & & 0.2449 & & 0.1749 \\ \hline \begin{tabular}{l} Natural gas wellhead price \\ (per MCF) \end{tabular} & & $ & 6.00 & $ & 6.00 & $ & 6.00 \\ \hline Volume (MCF/day) & & & 900 & & 720 & & 576 \\ \hline Days per year & & & 365 & & & & \\ \hline Fee to producer of natural gas (per M & MCF) & $ & 3.00 & $ & 3.00 & $ & 3.00 \\ \hline \begin{tabular}{l} Compression and processing \\ costs (per MCF) \end{tabular} & & & 0.65 & & 0.65 & & 0.65 \\ \hline Cash Flow Calculations & & & & & & & \\ \hline Natural gas wellhead price revenue & & & 1,971,000 & & 1,576,800 & & ,261,400 \\ \hline Lease fee expense & & & 985,500 & & 788,400 & & 630,720 \\ \hline Compression and processing costs & & & 213,525 & & 170,820 & & 136,656 \\ \hline Depreciation expenses & & & 171,480 & & 293,880 & & 209,880 \\ \hline Net operating profit & & & 600,495 & & 323,700 & & 284,184 \\ \hline Less taxes (40%) & & & (240,198) & & (129,480) & & (113,674) \\ \hline Net operating profit after tax (NOPA & AT) & & 360,297 & & 194,220 & & 170,510 \\ \hline Plus depreciation & & & 171,480 & & 293,880 & & 209,880 \\ \hline Return of net working capital & & & & & & & \\ \hline Project free cash flow & $(1,345,000) & $ & 531,777 & $ & 488,100 & $ & 380,390 \\ \hline \end{tabular}