Cash Flow Table: 24-Pet-B3 Petroleum Geology, May 2016, Q22

Question 22 — Cash Flow Table (20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. 100%-interest well cost $6,000,000 (all spent at start of Year 1); Working Interest (WI) = 75%; Royalty = 10%; Year-1 gross rate 500 BOPD declining 30%/yr; oil price $30/bbl in Year 1, rising $10/yr; OPEX = 15% of the 100%-interest capital in Year 1, escalating 10%/yr; Hurdle Rate = 10%; 365 days/yr; values shown to the nearest k$.

Find. The full 4-year cash-flow table for the WI owner, then (1) whether the hurdle rate is met, (2) ROI on the initial capital, and (3) the approximate payout.

Approach. Build the table left to right by the exam's own column definitions: gross production → WI share → royalty → net production → revenue (× price) → less the WI's share of CAPEX and OPEX → net production revenue → cumulative → discount each year's net revenue at the hurdle rate for NPV → compare cumulative NPV against total CAPEX.

Annual gross & net production. Gross production declines 30%/yr from 500 BOPD; the WI owner's net (after royalty) share is $Net = 500(0.70)^{n-1}\times365\times0.75\times(1-0.10)$ bbl. Year 1: $500\times365\times0.75\times0.90 = 123{,}187.5$ bbl; Year 4: $500(0.70)^3\times365\times0.75\times0.90 = 42{,}253.3$ bbl.
Production revenue. Net production × the year's oil price ($30, $40, $50, $60/bbl). Year 1: $123{,}187.5\times\$30/1000 = \boxed{3{,}695.63\ \text{k\$}}$; Year 4: $42{,}253.3\times\$60/1000 = 2{,}535.20\ \text{k\$}$.
Capital and operating costs (WI share). Year-1 CAPEX = $\$6{,}000{,}000\times0.75/1000 = 4{,}500\ \text{k\$}$ (zero thereafter). Year-1 OPEX = $15\%$ of the 100%-interest capital, WI's share: $0.15\times\$6{,}000{,}000\times0.75/1000 = 675\ \text{k\$}$, escalating 10%/yr to 742.50, 816.75 and 898.43 k$ in Years 2–4.
Net production revenue and cumulative. Net Production Revenue_n = Revenue_n − CAPEX_n − OPEX_n. Year 1: $3{,}695.63-4{,}500.00-675.00=\boxed{-1{,}479.38\ \text{k\$}}$ (negative — capital cost exceeds first-year revenue); Years 2–4: $+2{,}706.75$, $+2{,}201.34$, $+1{,}636.77\ \text{k\$}$. Cumulative reaches $+1{,}227.38$ k$ by Year 2 and $+5{,}065.49$ k$ by Year 4.
NPV @ 10% and hurdle-rate test. Discounting each year's Net Production Revenue at 10% using $P=F\times\dfrac{1}{(1+i)^{n-1}}$ (Year 1 undiscounted, per the “start of year = time zero” convention) and summing gives total NPV @ 10% $= \boxed{4{,}030.33\ \text{k\$}}$. The exam's own pass criterion is cumulative NPV (column 12) > total CAPEX (column 8) on the totals row: $4{,}030.33 < 4{,}500.00$, so $$\boxed{\text{Hurdle Rate is NOT met (No)}}$$
ROI and payout. $ROI = \dfrac{\text{cumulative net income}}{\text{total investment}} = \dfrac{5{,}065.49}{4{,}500.00} = \boxed{1.126\ (112.6\%)}$. Cumulative net revenue is still negative at the end of Year 1 ($-1{,}479.38$ k$) and turns positive during Year 2 (+2,706.75 k$ that year); interpolating within Year 2: $$Payout \approx 1 + \frac{1{,}479.38}{2{,}706.75} = \boxed{1.55\ \text{years}\ (\approx\ 1\ \text{yr}\ 7\ \text{months})}$$

Year	Gross Prod. (bbl)	Net Prod. (bbl)	Oil Price ($/bbl)	Revenue (k$)	CAPEX (k$)	OPEX (k$)	Net Income (k$)	Cum. Net Income (k$)	NPV@10% (k$)
1	182,500.0	123,187.5	30	3,695.63	4,500.00	675.00	-1,479.38	-1,479.38	-1,479.38
2	127,750.0	86,231.3	40	3,449.25	0.00	742.50	2,706.75	1,227.38	2,460.68
3	89,425.0	60,361.9	50	3,018.09	0.00	816.75	2,201.34	3,428.72	1,819.29
4	62,597.5	42,253.3	60	2,535.20	0.00	898.43	1,636.77	5,065.49	1,229.73
Totals				12,698.17	4,500.00	3,132.68	5,065.49		4,030.33

Question	Answer
Does the investment meet the Hurdle Rate?	No — NPV@10% (4,030.33 k$) < total CAPEX (4,500.00 k$)
ROI on initial capital investment	112.6% (cumulative 4-yr net income ÷ total WI capital)
Approximate payout	≈ 1.55 years (about 1 year, 7 months)

Topic: Working-interest cash-flow / hurdle-rate / ROI / payout screening

Key relations: Net Production Revenue = Revenue − CAPEX − OPEX (each at the WI owner's share); $NPV=\sum F_n/(1+i)^{n-1}$; $ROI=$ cumulative net income ÷ total investment; Payout = time for cumulative net income to reach zero.

Why this works: All four metrics are read off the same underlying year-by-year net-cash-flow series; the hurdle-rate test asks whether the TIME VALUE of that series is positive at the required return, while ROI and payout are simpler, non-time-value screens on the same numbers — a project can fail one test and pass another, which is exactly what happens here (ROI is a strongly positive 112.6% and payout is fast at 1.55 years, yet the time-discounted NPV still falls just short of the 10% hurdle because Year 1's large negative cash flow is not discounted at all under the “time zero” convention while it is the deepest negative number in the series).

Common pitfall: Discounting Year 1 by one full period (dividing by $(1+i)^1$) — under this exam's explicit “start of year = time zero” convention, Year 1 cash flow is undiscounted ($n-1=0$ periods), and using the wrong exponent shifts every later year's NPV and can flip the hurdle-rate conclusion.

Source: Thompson & Wright, Oil Property Evaluation, Ch. on working-interest cash flow and hurdle-rate screening; COGEH Vol. 1.

Question 22 of 22: Cash Flow Table

Question 22 — Cash Flow Table (20 marks)