In this note we compute Intangible Intensity using SOI data on business receipts and assets with price to sales valuations using compustat data
from IPython.display import HTML
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
HTML('''<script>
code_show=true;
function code_toggle() {
if (code_show){
$('div.input').hide();
} else {
$('div.input').show();
}
code_show = !code_show
}
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Click here to toggle on/off the raw code."></form>''')
import pandas as pd
import numpy as np
import difflib as dl
from difflib import SequenceMatcher
from heapq import nlargest as _nlargest
pd.options.display.float_format = '{:,.2f}'.format
colnames=pd.read_csv('./data/colnames_soi_1120S.csv')
colnames.columns=[x.strip() for x in colnames.columns]
colnames.SOI1120S = colnames.SOI1120S.astype(int)
colnames.SOI1065 = colnames.SOI1065.astype(int)
colnames.CFC= colnames.CFC.astype(int)
colnames.CCANonCorporate= colnames.CCANonCorporate.astype(int)
colnames.CCACorporate= colnames.CCACorporate.astype(int)
def format_str(string):
string=string.replace('[33]','')
string=string.replace('[26]','')
string=string.replace("[12]",'')
string=string.replace('[34]','')
string=string.replace(':','')
string=string.strip()
return string
def getBSData(soi_link):
#soi_link= "https://www.irs.gov/pub/irs-soi/13co07s.xls"
temp2=pd.read_excel(soi_link,skiprows=6)
BSData=temp2.iloc[:,colnames.SOI1120S.values]
BSData.index=[ format_str(str(x[0])) for x in temp2.iloc[:,[0]].values]
BSData.columns=colnames.name.values
BSData=BSData.iloc[:-1]
return BSData
def get_close_matches_indexes(word, possibilities, n=3, cutoff=0.6):
"""Use SequenceMatcher to return a list of the indexes of the best
"good enough" matches. word is a sequence for which close matches
are desired (typically a string).
possibilities is a list of sequences against which to match word
(typically a list of strings).
Optional arg n (default 3) is the maximum number of close matches to
return. n must be > 0.
Optional arg cutoff (default 0.6) is a float in [0, 1]. Possibilities
that don't score at least that similar to word are ignored.
"""
if not n > 0:
raise ValueError("n must be > 0: %r" % (n,))
if not 0.0 <= cutoff <= 1.0:
raise ValueError("cutoff must be in [0.0, 1.0]: %r" % (cutoff,))
result = []
s = SequenceMatcher()
s.set_seq2(word)
for idx, x in enumerate(possibilities):
s.set_seq1(x)
if s.real_quick_ratio() >= cutoff and \
s.quick_ratio() >= cutoff and \
s.ratio() >= cutoff:
result.append((s.ratio(), idx))
# Move the best scorers to head of list
result = _nlargest(n, result)
# Strip scores for the best n matches
return [x for score, x in result]
years=['2007','2008','2009','2010','2011','2012','2013']
print(years)
fa_section3_link="http://users.cla.umn.edu/~erm/data/sweat/Data/BEA/FA/Section3all_xls.xlsx"
nipa_section6_link="http://users.cla.umn.edu/~erm/data/sweat/Data/BEA/NIPA/Section6all_xls.xlsx"
print(fa_section3_link)
print(nipa_section6_link)
Table 3.4ESI. Current-Cost Depreciation of Private Fixed Assets by Industry
CFC=pd.read_excel(fa_section3_link,sheet_name="FAAt304ESI-A",skiprows=7)
CFC['IndustryName']=CFC['Unnamed: 1']
CFC=CFC.drop(columns=['Unnamed: 1','Unnamed: 2'])
CFC=CFC[:-7]
CFC.Line=CFC.Line.astype(int)
CFC=CFC[CFC['Line'].isin(colnames.CFC.values)]
#CFC.iloc[get_close_matches_indexes('Management of companies and enterprise', CFC.IndustryName)[0],3:]=CFC[CFC.IndustryName==dl.get_close_matches('Administrative and support and waste', CFC.IndustryName)[0]].iloc[0,3:]+\
#CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].iloc[0,3:]
CFC.index=colnames.name
CFC=CFC.drop(CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].index)
CFC[years]
Table 6.13D. Noncorporate Capital Consumption Allowances by Industry
CCA_NonCorporate=pd.read_excel(nipa_section6_link,sheet_name="T61300D-A",skiprows=7)
CCA_NonCorporate['IndustryName']=CCA_NonCorporate['Unnamed: 1']
CCA_NonCorporate=CCA_NonCorporate.drop(columns=['Unnamed: 1','Unnamed: 2'])
CCA_NonCorporate=CCA_NonCorporate[:-4]
CCA_NonCorporate.Line=CCA_NonCorporate.Line.astype(int)
CCA_NonCorporate=CCA_NonCorporate[CCA_NonCorporate['Line'].isin(colnames.CCANonCorporate.values)]
CCA_NonCorporate.index=CFC.index
CCA_NonCorporate[years]/1000.0
Table 6.22D. Corporate Capital Consumption Allowances by Industry
CCA_Corporate=pd.read_excel(nipa_section6_link,sheet_name="T62200D-A",skiprows=7)
CCA_Corporate['IndustryName']=CCA_Corporate['Unnamed: 1']
CCA_Corporate=CCA_Corporate.drop(columns=['Unnamed: 1','Unnamed: 2'])
CCA_Corporate=CCA_Corporate[:-7]
CCA_Corporate.Line=CCA_Corporate.Line.astype(int)
CCA_Corporate.index=CCA_Corporate.Line
CCA_Corporate=CCA_Corporate[CCA_Corporate['Line'].isin(colnames.CCACorporate.values)]
#CCA_Corporate.iloc[get_close_matches_indexes('Management of companies and enterprise', CCA_Corporate.IndustryName)[0],3:]=CCA_Corporate[CCA_Corporate.IndustryName==dl.get_close_matches('Administrative and support and waste', CCA_Corporate.IndustryName)[0]].iloc[0,3:]+\
#CCA_Corporate[CCA_Corporate.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCA_Corporate.IndustryName)[0]].iloc[0,3:]
CCA_Corporate.index=colnames.name
CCA_Corporate=CCA_Corporate.drop(CCA_Corporate[CCA_Corporate.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCA_Corporate.IndustryName)[0]].index)
CCA_Corporate[years]/1000.0
Tax depreciation to economic depreciation using $$ \frac {\text{CFC}} { \text{CCA ( Noncorporate) + CCA ( Corporate)}} $$
IndexDep=CFC[years]/(CCA_NonCorporate[years]/1000.0+CCA_Corporate[years]/1000.0)
display(IndexDep)
Table 3.1ESI. Current-Cost Net Stock of Private Fixed Assets by Industry
CCNFA=pd.read_excel(fa_section3_link,sheet_name="FAAt301ESI-A",skiprows=7)
CCNFA['IndustryName']=CCNFA['Unnamed: 1']
CCNFA=CCNFA.iloc[:-7]
CCNFA.Line=CCNFA.Line.astype(int)
CCNFA.index=CCNFA.Line
CCNFA=CCNFA[CCNFA['Line'].isin(colnames.CCNFA.values)]
#CCNFA.iloc[get_close_matches_indexes('Management of companies and enterprise', CCNFA.IndustryName)[0],3:]=CCNFA[CCNFA.IndustryName==dl.get_close_matches('Administrative and support and waste', CCNFA.IndustryName)[0]].iloc[0,3:]+\
#CCNFA[CCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCNFA.IndustryName)[0]].iloc[0,3:]
CCNFA.index=colnames.name
CCNFA=CCNFA.drop(CCNFA[CCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCNFA.IndustryName)[0]].index)
CCNFA[years]
Table 3.3ESI. Historical-Cost Net Stock of Private Fixed Assets by Industry
HCNFA=pd.read_excel(fa_section3_link,sheet_name="FAAt303ESI-A",skiprows=7)
HCNFA['IndustryName']=HCNFA['Unnamed: 1']
HCNFA=HCNFA.iloc[:-7]
HCNFA.Line=HCNFA.Line.astype(int)
HCNFA.index=HCNFA.Line
HCNFA=HCNFA[HCNFA['Line'].isin(colnames.HCNFA.values)]
#HCNFA.iloc[get_close_matches_indexes('Management of companies and enterprise', HCNFA.IndustryName)[0],3:]=HCNFA[HCNFA.IndustryName==dl.get_close_matches('Administrative and support and waste', HCNFA.IndustryName)[0]].iloc[0,3:]+\
#HCNFA[HCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', HCNFA.IndustryName)[0]].iloc[0,3:]
HCNFA.index=colnames.name
HCNFA=HCNFA.drop(HCNFA[HCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', HCNFA.IndustryName)[0]].index)
HCNFA[years]
Table 3.6ESI. Historical-Cost Depreciation of Private Fixed Assets by Industry
HFC=pd.read_excel(fa_section3_link,sheet_name="FAAt306ESI-A",skiprows=7)
HFC['IndustryName']=HFC['Unnamed: 1']
HFC=HFC.iloc[:-7]
HFC.Line=HFC.Line.astype(int)
HFC.index=HFC.Line
HFC=HFC[HFC['Line'].isin(colnames.HFC.values)]
#HFC.iloc[get_close_matches_indexes('Management of companies and enterprise', HFC.IndustryName)[0],3:]=HFC[HFC.IndustryName==dl.get_close_matches('Administrative and support and waste', HFC.IndustryName)[0]].iloc[0,3:]+\
#HFC[HFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', HFC.IndustryName)[0]].iloc[0,3:]
HFC.index=colnames.name
HFC=HFC.drop(HFC[HFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', HFC.IndustryName)[0]].index)
HFC[years]
Table 3.4ESI. Current-Cost Depreciation of Private Fixed Assets by Industry
CFC=pd.read_excel(fa_section3_link,sheet_name="FAAt304ESI-A",skiprows=7)
CFC['IndustryName']=CFC['Unnamed: 1']
CFC=CFC.drop(columns=['Unnamed: 1','Unnamed: 2'])
CFC=CFC[:-7]
CFC.Line=CFC.Line.astype(int)
CFC=CFC[CFC['Line'].isin(colnames.CFC.values)]
#CFC.iloc[get_close_matches_indexes('Management of companies and enterprise', CFC.IndustryName)[0],3:]=CFC[CFC.IndustryName==dl.get_close_matches('Administrative and support and waste', CFC.IndustryName)[0]].iloc[0,3:]+\
#CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].iloc[0,3:]
CFC.index=colnames.name
CFC=CFC.drop(CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].index)
CFC[years]
HCGFA=HCNFA+HFC
CCGFA=CCNFA+CFC
IndexCCFA=pd.DataFrame(CCGFA[years]/HCGFA[years])
display(IndexCCFA)
IndexCCFA['IndustryName']=CCNFA.IndustryName
IndexCCFA['IndustryName'].iloc[0]='All'
IndexCCFA['IndustryName'].iloc[-1]='Other services'
From the SOI data we extract business receipts and define total tangible assets using
def downloadSCorpData(year):
if int(year) > 2005:
soi_link= "https://www.irs.gov/pub/irs-soi/"+ year[2:] + "co07s.xls"
elif int(year)==2005:
soi_link="https://www.irs.gov/pub/irs-soi/05co1120s07.xls"
elif int(year)==2004:
soi_link="https://www.irs.gov/pub/irs-soi/04co14ccr.xls"
elif int(year)==2003:
soi_link="https://www.irs.gov/pub/irs-soi/03co14bs.xls"
elif int(year)==2002:
soi_link="https://www.irs.gov/pub/irs-soi/02co14bs.xls"
elif int(year)==2001:
soi_link="https://www.irs.gov/pub/irs-soi/01co14bs.xls"
elif int(year)==2000:
soi_link="https://www.irs.gov/pub/irs-soi/00co14bs.xls"
elif int(year)==1999:
soi_link="https://www.irs.gov/pub/irs-soi/99co14bs.xls"
elif int(year)==1998:
soi_link="https://www.irs.gov/pub/irs-soi/98co14bs.xls"
print(soi_link)
ScorpData=getBSData(soi_link)
ScorpData=ScorpData.T
# ScorpData.iloc[get_close_matches_indexes('Management of companies and enterprise', ScorpData.index)[0],7:]=ScorpData.iloc[get_close_matches_indexes('Management of companies and enterprise', ScorpData.index)[0],7:]+ScorpData.iloc[get_close_matches_indexes('Administrative and support and waste', ScorpData.index)[0],7:]
ScorpData=ScorpData.drop(ScorpData[ScorpData.index==dl.get_close_matches('Management of companies and enterprise', ScorpData.index)[0]].index)
return ScorpData
def addHCFA(ScorpData):
CCFASOI=[]
ScorpData['HCFA']=ScorpData[dl.get_close_matches('Depreciable assets', ScorpData.columns)[0]]+\
ScorpData[dl.get_close_matches('Depletable assets', ScorpData.columns)[0]]
return ScorpData
def addCCFA(ScorpData,year):
CCFASOI=[]
for SOIindex in ScorpData.index:
factor=IndexCCFA.iloc[get_close_matches_indexes(SOIindex,IndexCCFA.index)][year].values
HCFASOI=ScorpData['HCFA'][SOIindex]
HCFASOI=float(HCFASOI)
factor=float(factor[0])
CCFASOI.append(HCFASOI*factor)
ScorpData['CCFA']= np.array(CCFASOI)
return ScorpData
def addCCA(ScorpData):
ScorpData['CCA']=ScorpData[dl.get_close_matches('Less: Accumulated depreciation', ScorpData.columns)[0]]+\
ScorpData[dl.get_close_matches('Less: Accumulated depletion', ScorpData.columns)[0]]
return ScorpData
def addCFC(ScorpData,year):
CFCSOI=[]
factorAll=IndexDep.iloc[get_close_matches_indexes('All',IndexDep.index)][year].values
for SOIindex in ScorpData.index:
if len(get_close_matches_indexes(SOIindex,IndexDep.index))>0:
factor=IndexDep.iloc[get_close_matches_indexes(SOIindex,IndexDep.index)][year].values
else:
factor=factorAll
if factor[0]>3.0:
factor=factorAll
CCASOI=ScorpData['CCA'][SOIindex]
CCASOI=float(CCASOI)
factor=float(factor[0])
CFCSOI.append(CCASOI*factor)
ScorpData['CFC']= np.array(CFCSOI)
return ScorpData
def addTotalTangibleAssets(ScorpData):
ScorpData['CurrentAssets']= ScorpData[dl.get_close_matches('Notes and accounts receivable', ScorpData.columns)[0]]\
- ScorpData[dl.get_close_matches('Less: Allowance for bad debts..', ScorpData.columns)[0]] \
+ ScorpData[dl.get_close_matches('other assets', ScorpData.columns)[0]]
ScorpData['Inventories']= ScorpData[dl.get_close_matches('Inventories', ScorpData.columns)[0]]
ScorpData['FixedAssetsLessDep']= ScorpData[dl.get_close_matches('CCFA', ScorpData.columns)[0]] \
- ScorpData[dl.get_close_matches('CFC', ScorpData.columns)[0]] \
ScorpData['Land']= ScorpData[dl.get_close_matches('Land', ScorpData.columns)[0]]
ScorpData['CurrentLiabilities']= ScorpData[dl.get_close_matches('Accounts payable..', ScorpData.columns)[0]] \
+ ScorpData[dl.get_close_matches('Other current liabilities..', ScorpData.columns)[0]]
ScorpData['TotalTangibleAssets']= ScorpData[dl.get_close_matches('Notes and accounts receivable', ScorpData.columns)[0]]\
- ScorpData[dl.get_close_matches('Less: Allowance for bad debts..', ScorpData.columns)[0]] \
+ ScorpData[dl.get_close_matches('Inventories', ScorpData.columns)[0]] \
+ ScorpData[dl.get_close_matches('other current assets', ScorpData.columns)[0]] \
+ ScorpData[dl.get_close_matches('CCFA', ScorpData.columns)[0]] \
- ScorpData[dl.get_close_matches('CFC', ScorpData.columns)[0]] \
+ ScorpData[dl.get_close_matches('Land', ScorpData.columns)[0]] \
+ ScorpData[dl.get_close_matches('other assets', ScorpData.columns)[0]]\
ScorpData['TotalTangibleAssets']= ScorpData['TotalTangibleAssets']+\
- ScorpData[dl.get_close_matches('Accounts payable..', ScorpData.columns)[0]] \
- ScorpData[dl.get_close_matches('Other current liabilities..', ScorpData.columns)[0]] \
ScorpData['TotalTangibleAssetsMrtg'] = ScorpData['TotalTangibleAssets']+ \
+ ScorpData[dl.get_close_matches('Mortgage and real estate loans..', ScorpData.columns)[0]]\
- ScorpData[dl.get_close_matches('Mortgages, notes, bonds, one year or more', ScorpData.columns)[0]]
ScorpData['BusinessReceipts'] = ScorpData[dl.get_close_matches('Business receipts', ScorpData.columns)[0]]
return ScorpData
def getSCropData(year):
ScorpData = downloadSCorpData(year)
ScorpData = addHCFA (ScorpData )
ScorpData = addCCFA (ScorpData,year)
ScorpData = addCCA (ScorpData )
ScorpData = addCFC (ScorpData,year )
ScorpData = addTotalTangibleAssets (ScorpData)
return ScorpData
def getPriceSalesMultiples():
price_sales_multiple=pd.read_csv('price_sales_mean.csv')
price_sales_multiple.columns=['IndustryName','PSMultiple']
price_sales_multiple['IndustryName'].iloc[1]='Mining'
price_sales_multiple['IndustryName'].iloc[12]='Administrative and support and waste management and remediation services'
return price_sales_multiple
def getIntanIntensity(ScorpData,price_sales_multiple,liquidity_factor):
IITaxdata=pd.DataFrame( ScorpData['BusinessReceipts']/ScorpData['TotalTangibleAssets'],columns=['Sales_TangibleAssets'])
price_sales_multiple = getPriceSalesMultiples()
Value_Sales=[]
factorAll=IndexDep.iloc[get_close_matches_indexes('All',price_sales_multiple.IndustryName)].values
for IITaxdataindex in IITaxdata.index:
if len(get_close_matches_indexes(IITaxdataindex,price_sales_multiple.IndustryName))>0:
factor=price_sales_multiple.iloc[get_close_matches_indexes(IITaxdataindex,price_sales_multiple.IndustryName)].PSMultiple.values
else:
factor=factorAll
Value_Sales.append(factor[0])
IITaxdata['Value_Sales']= np.array(Value_Sales)
IITaxdata['Value_Sales_alt']= np.array(Value_Sales)*liquidity_factor
IITaxdata['Value_TangibleAssets']=IITaxdata['Sales_TangibleAssets']*IITaxdata['Value_Sales']
IITaxdata['Intan_TangibleAssets']=IITaxdata['Value_TangibleAssets']-1.0
IITaxdata['IntanIntensity']=IITaxdata['Intan_TangibleAssets']/IITaxdata['Value_TangibleAssets']
IITaxdata['Value_TangibleAssets_alt']=IITaxdata['Sales_TangibleAssets']*IITaxdata['Value_Sales_alt']
IITaxdata['Intan_TangibleAssets_alt']=IITaxdata['Value_TangibleAssets_alt']-1.0
IITaxdata['IntanIntensity_alt']=IITaxdata['Intan_TangibleAssets_alt']/IITaxdata['Value_TangibleAssets_alt']
IITaxdata['IntanIntensityMrtg']= 1- (ScorpData['TotalTangibleAssetsMrtg']/ScorpData['BusinessReceipts'])/IITaxdata['Value_Sales']
return IITaxdata[['IntanIntensity','IntanIntensity_alt','Sales_TangibleAssets','Value_Sales','IntanIntensityMrtg']]
IITaxdataList=[]
IITaxdata_altList=[]
IITaxdataMrtgList=[]
MktCapToSalesList=[]
BusReceiptsToTangibleAssetsList=[]
for year in years:
ScorpData =getSCropData (year)
price_sales_multiple = getPriceSalesMultiples()
liquidity_factor =0.75
temp=[]
temp = getIntanIntensity(ScorpData,price_sales_multiple,liquidity_factor)
IITaxdataList.append(temp.IntanIntensity.values )
IITaxdata_altList.append(temp.IntanIntensity_alt.values)
MktCapToSalesList.append(temp.Value_Sales.values)
BusReceiptsToTangibleAssetsList.append(temp.Sales_TangibleAssets.values)
IITaxdataMrtgList.append(temp.IntanIntensityMrtg.values)
IITaxdata = pd.DataFrame(IITaxdataList,index=years,columns=temp.index).T
IITaxdataAlt = pd.DataFrame(IITaxdata_altList,index=years,columns=temp.index).T
MktCapToSales = pd.DataFrame(MktCapToSalesList,index=years,columns=temp.index).T
BusReceiptsToTangibleAssets = pd.DataFrame(BusReceiptsToTangibleAssetsList,index=years,columns=temp.index).T
IITaxdataMrtg = pd.DataFrame(IITaxdataMrtgList,index=years,columns=temp.index).T
# IITaxdataList.append(IITaxdata)
# IITaxdata_altList.append(IITaxdata_alt)
#IITaxdata = pd.DataFrame(IITaxdataList,index=years).T
#IITaxdataAlt = pd.DataFrame(IITaxdata_altList,index=years).T
#ScorpData[['BusinessReceipts','TotalTangibleAssets']]/1000.0
display(1/BusReceiptsToTangibleAssets)
display(1/pd.DataFrame(MktCapToSales.iloc[:,0].values,index=MktCapToSales.index,columns=['Avg: 1994-2018']))
Define $$ii = 1-\frac{K}{V} =1- \frac{K}{S} \times \frac{S}{V} $$ where $$ \frac{K}{S} = \frac{ \text{TotalTangibleAssets (SOI) } } {\text{Business Receipts (SOI) } } $$ and $$ \frac{S}{V} = \frac{\text{ sales (COMPUSTAT) }} {\text{market cap (COMPUSTAT)} } $$
display(IITaxdata)
display(IITaxdataAlt )
In the SOI tax data, the real estate sector has a lot of liabilities.These are mainly mortgages/loans on the real estate that is used in the business. We compute the intangible intensity after netting out the mortgage liabilities. This is not the case with Pratts.
display(IITaxdataMrtg)