Example 1-7: Net Cash Flow in a Simplified Production Sharing Contract

Anthony Jimenez
16 August 2021
http://www.fa-jimenez.com/

Assumptions:

• Production, price, and cost data from Example 1-1
• 5% royalty
• 80% limit on cost recovery
• 4-year straight-line depreciation on investment
• 50% contractor's equity
• 35% tax rate

Pandas Dataframe Computations for Cash Flow Analysis

Import necessary libraries

import pandas as pd
import numpy as np
import plotly.express as px
from plotly.subplots import make_subplots
import plotly.graph_objects as go

Define input streams (from text)

input_revenue = [1277.5, 1929.625, 1319.5, 901.75, 616.75, 421, 288, 197.25]
input_operating_cost = [207.6, 345.9, 278.2, 231.9, 200.4, 178.7, 163.9, 153.9]
input_investment_stream = [2000, 0, 0, 0, 0, 0, 0, 0]
initial_investment = 1000
royalty_percentage = 5
cost_recov_percentage = 80
depreciation_life = 4
depreciation_method = 'Straight-Line'
contractor_equity_percentage = 50
tax_rate_percentage = 35

Main function for computing net cash flow (NCF)

# Main function for computing all columns required for a cash flow analysis
def net_cash_flow(revenue_stream, oper_cost_stream, investment_stream, initial_investment, royalty, cost_recovery, contractor_equity, tax_rate, depreciation_life, depreciation_method):
    # Create initial pandas dataframe
    df_data = np.array([revenue_stream, oper_cost_stream, investment_stream]).T
    df = pd.DataFrame(data=df_data, columns=['Gross Revenue ($M)', 'Operating Costs ($M)', 'Investment ($M)'])
    
    # Compute cash flow columns
    df['Royalty ($M)'] = (royalty / 100) * df['Gross Revenue ($M)']
    df['Cost Recovery Limit ($M)'] = (cost_recovery / 100) * df['Gross Revenue ($M)']
    
    # Need a loop to compute the cost recovery
    unrecovered_arr = np.zeros(len(df))
    depreciation_arr = np.zeros(len(df))
    cost_recovery_total = np.zeros(len(df))
    cost_recovery_arr = np.zeros(len(df))
    for i in range(len(df)):
        # Define the depreciation streams throughout life of project
        if i == 0:
            investment = df['Investment ($M)'].iloc[i] + initial_investment
        else:
            investment = df['Investment ($M)'].iloc[i]
        
        # Currently supports only Straight-Line depreciation method
        if depreciation_method == 'Straight-Line':
            depreciation_arr[i:(i+depreciation_life)] += investment / depreciation_life
        
        # Compute total recoverable cost (idealized, 100% recovery case)
        if i == 0:
            cost_recovery_total[i] = 0 + df['Operating Costs ($M)'].iloc[i] + depreciation_arr[i]
        else:
            cost_recovery_total[i] = unrecovered_arr[i-1] + df['Operating Costs ($M)'].iloc[i] + depreciation_arr[i]
        
        # Determine the recovered cost amount per year
        cost_recovery_arr[i] = min(cost_recovery_total[i], df['Cost Recovery Limit ($M)'].iloc[i])
        unrecovered_arr[i] = cost_recovery_total[i] - cost_recovery_arr[i]
    
    # Append computed array columns to main pandas dataframe
    unrecovered_prior_arr = np.insert(unrecovered_arr[0:len(unrecovered_arr)-1], 0, 0)
    df['Unrecovered Costs ($M)'] = unrecovered_arr
    df['Unrecovered Prior Costs ($M)'] = unrecovered_prior_arr
    df['Depreciation ($M)'] = depreciation_arr
    df['Cost Recovery Before Limit ($M)'] = cost_recovery_total
    df['Cost Recovery ($M)'] = cost_recovery_arr
    
    # Compute equity columns
    df['Total Equity ($M)'] = df['Gross Revenue ($M)'] - df['Royalty ($M)'] - df['Cost Recovery ($M)']
    df['Contractor\'s Equity ($M)'] = (contractor_equity / 100) * df['Total Equity ($M)']
    
    # Compute taxable income and annual tax owed
    df['Contractor\'s Income ($M)'] = df['Cost Recovery ($M)'] + df['Contractor\'s Equity ($M)']
    df['Taxable Income ($M)'] = df['Contractor\'s Income ($M)'] - df['Operating Costs ($M)'] - df['Depreciation ($M)']
    df['Income Tax ($M)'] = (tax_rate / 100) * df['Taxable Income ($M)']
    
    # Compute NOI and NCF
    df['Contractor Net Operating Income ($M)'] = df['Contractor\'s Income ($M)'] - df['Operating Costs ($M)'] - df['Income Tax ($M)']
    df['Net Cash Flow ($M)'] = df['Contractor Net Operating Income ($M)'] - df['Investment ($M)']
    return df

Main function for generating the output summary tables

# Allows negative values to be displayed as "red" text in pandas dataframe
def color_negative_red(val):
    color = 'red' if val < 0 else 'black'
    return 'color: %s' % color

# Main function for generating output cash flow tables
def net_cash_flow_tables(df):
    # Create the Net Cash Flow Table
    df_ncf = df[['Gross Revenue ($M)', 'Royalty ($M)', 'Cost Recovery ($M)', 'Total Equity ($M)',
                'Contractor\'s Equity ($M)', 'Contractor\'s Income ($M)', 'Operating Costs ($M)',
                'Income Tax ($M)', 'Contractor Net Operating Income ($M)', 'Investment ($M)',
                'Net Cash Flow ($M)']]
    
    # Create the first row of the NCF table
    df_ncf_row0 = np.empty(len(df_ncf.columns))
    df_ncf_row0[:] = np.NaN
    df_ncf_row0[9] = initial_investment
    df_ncf_row0[10] = -initial_investment
    df_ncf_row0 = pd.DataFrame(df_ncf_row0).T
    df_ncf_row0.columns = df_ncf.columns
    df_ncf = pd.concat([df_ncf_row0, df_ncf]).reset_index(drop=True)
    df_ncf.index.name = 'Year'
    df_ncf.loc['Total'] = df_ncf.sum()
    #df_ncf = df_ncf.style.applymap(color_negative_red)
    
    # Create the Cost Recovered Table
    df_cost = df[['Unrecovered Prior Costs ($M)', 'Operating Costs ($M)', 'Depreciation ($M)',
                 'Cost Recovery Before Limit ($M)', 'Cost Recovery Limit ($M)', 'Cost Recovery ($M)',
                 'Unrecovered Costs ($M)']]
    df_cost.index = np.arange(1, len(df_cost)+1)
    df_cost.index.name = 'Year'
    df_cost = df_cost.style.applymap(color_negative_red)
    
    # Create the PSC Income Tax Table
    df_tax = df[['Contractor\'s Income ($M)', 'Operating Costs ($M)', 'Depreciation ($M)', 
                 'Taxable Income ($M)', 'Income Tax ($M)']]
    df_tax.index = np.arange(1, len(df_tax)+1)
    df_tax.index.name = 'Year'
    df_tax.loc['Total'] = df_tax.sum()
    df_tax = df_tax.style.applymap(color_negative_red)
    return df_ncf, df_cost, df_tax

# Eggholder function for net cash flow
def net_cash_flow_function():
    # Compute all NCF functions
    df = net_cash_flow(input_revenue, input_operating_cost, input_investment_stream, initial_investment, 
                   royalty_percentage, cost_recov_percentage, contractor_equity_percentage, tax_rate_percentage, 
                  depreciation_life, depreciation_method)
    
    # Create the summarized tables for output (i.e., NCF, Cost Recovery, Income Tax Report)
    df_ncf, df_cost, df_tax = net_cash_flow_tables(df)
    return df_ncf, df_cost, df_tax, df

df_ncf, df_cost, df_tax, df_raw = net_cash_flow_function()

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\indexing.py:723: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  iloc._setitem_with_indexer(indexer, value, self.name)

Net Cash Flow Table (NCF)

df_ncf

	Gross Revenue ($M)	Royalty ($M)	Cost Recovery ($M)	Total Equity ($M)	Contractor's Equity ($M)	Contractor's Income ($M)	Operating Costs ($M)	Income Tax ($M)	Contractor Net Operating Income ($M)	Investment ($M)	Net Cash Flow ($M)
Year
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	1000.0	-1000.000000
1	1277.500	63.87500	957.6	256.02500	128.012500	1085.612500	207.6	44.804375	833.208125	2000.0	-1166.791875
2	1929.625	96.48125	1095.9	737.24375	368.621875	1464.521875	345.9	129.017656	989.604219	0.0	989.604219
3	1319.500	65.97500	1028.2	225.32500	112.662500	1140.862500	278.2	39.431875	823.230625	0.0	823.230625
4	901.750	45.08750	721.4	135.26250	67.631250	789.031250	231.9	-67.504062	624.635313	0.0	624.635313
5	616.750	30.83750	460.9	125.01250	62.506250	523.406250	200.4	113.052188	209.954063	0.0	209.954063
6	421.000	21.05000	178.7	221.25000	110.625000	289.325000	178.7	38.718750	71.906250	0.0	71.906250
7	288.000	14.40000	163.9	109.70000	54.850000	218.750000	163.9	19.197500	35.652500	0.0	35.652500
8	197.250	9.86250	153.9	33.48750	16.743750	170.643750	153.9	5.860313	10.883438	0.0	10.883438
Total	6951.375	347.56875	4760.5	1843.30625	921.653125	5682.153125	1760.5	322.578594	3599.074531	3000.0	599.074531

Cost Recovery Table

df_cost

	Unrecovered Prior Costs ($M)	Operating Costs ($M)	Depreciation ($M)	Cost Recovery Before Limit ($M)	Cost Recovery Limit ($M)	Cost Recovery ($M)	Unrecovered Costs ($M)
Year
1	0.000000	207.600000	750.000000	957.600000	1022.000000	957.600000	0.000000
2	0.000000	345.900000	750.000000	1095.900000	1543.700000	1095.900000	0.000000
3	0.000000	278.200000	750.000000	1028.200000	1055.600000	1028.200000	0.000000
4	0.000000	231.900000	750.000000	981.900000	721.400000	721.400000	260.500000
5	260.500000	200.400000	0.000000	460.900000	493.400000	460.900000	0.000000
6	0.000000	178.700000	0.000000	178.700000	336.800000	178.700000	0.000000
7	0.000000	163.900000	0.000000	163.900000	230.400000	163.900000	0.000000
8	0.000000	153.900000	0.000000	153.900000	157.800000	153.900000	0.000000

Production Sharing Contract (PSC) Income Tax Table

df_tax

	Contractor's Income ($M)	Operating Costs ($M)	Depreciation ($M)	Taxable Income ($M)	Income Tax ($M)
Year
1	1085.612500	207.600000	750.000000	128.012500	44.804375
2	1464.521875	345.900000	750.000000	368.621875	129.017656
3	1140.862500	278.200000	750.000000	112.662500	39.431875
4	789.031250	231.900000	750.000000	-192.868750	-67.504062
5	523.406250	200.400000	0.000000	323.006250	113.052188
6	289.325000	178.700000	0.000000	110.625000	38.718750
7	218.750000	163.900000	0.000000	54.850000	19.197500
8	170.643750	153.900000	0.000000	16.743750	5.860313
Total	5682.153125	1760.500000	3000.000000	921.653125	322.578594

Visualizing the Outputted Cash Flow Tables

Net Cash Flow Visuals

Utilizing the Plotly plotting library, we can create interactive plots. However, plotly works best with data in long-form, not wide-form (as is a typical cash flow table.

Thus, we should first reformat our data to long-form by using the Pandas function pd.melt().

# Remove the "Total" row from dataframe
df1 = df_ncf.iloc[:len(df_ncf)-1].reset_index()

# Make investments show as a negative cash flow for plotting purposes
df1['Investment ($M)'] = -1 * df1['Investment ($M)']
 
# Convert wide-form data to long-form data
df_ncf = pd.melt(df1, id_vars=['Year'])

df_ncf

	Year	variable	value
0	0	Gross Revenue ($M)	NaN
1	1	Gross Revenue ($M)	1277.500000
2	2	Gross Revenue ($M)	1929.625000
3	3	Gross Revenue ($M)	1319.500000
4	4	Gross Revenue ($M)	901.750000
...	...	...	...
94	4	Net Cash Flow ($M)	624.635313
95	5	Net Cash Flow ($M)	209.954063
96	6	Net Cash Flow ($M)	71.906250
97	7	Net Cash Flow ($M)	35.652500
98	8	Net Cash Flow ($M)	10.883438

99 rows × 3 columns

# Shows that all the same column/information is here in this new format (long vs. wide formatting)
df_ncf.variable.unique()

array(['Gross Revenue ($M)', 'Royalty ($M)', 'Cost Recovery ($M)',
       'Total Equity ($M)', "Contractor's Equity ($M)",
       "Contractor's Income ($M)", 'Operating Costs ($M)',
       'Income Tax ($M)', 'Contractor Net Operating Income ($M)',
       'Investment ($M)', 'Net Cash Flow ($M)'], dtype=object)

Basic line plot illustrating how Net Cash Flow (NCF) is computed

The basic equation for NCF in this case is:

$NCF = NOI - Investment$

Because no additional investments are made after the first year, we see that NOI equals NCF for years 2-8.

# Slice full NCF table to just the columns we want to visualize
col1 = ['Contractor\'s Income ($M)', 'Operating Costs ($M)', 'Income Tax ($M)', 'Contractor Net Operating Income ($M)', 
       'Investment ($M)', 'Net Cash Flow ($M)']
df_plot1 = df_ncf[df_ncf['variable'].isin(col1[-3:])]

fig = px.line(df_plot1, x='Year', y='value', color='variable', 
              title='Illustration of how (NOI - Investments) yields NCF',
             labels={
                 'x': 'Year',
                 'value': 'US Dollars ($M)',
                 'variable': 'Series Name'
             })
fig.update_traces(mode='lines+markers')
fig.show()