{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# On Profitability Measures of Businesses " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Import libraries" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "import datetime\n", "import pandas as pd\n", "import warnings\n", "pd.set_option('display.max_rows', 500)\n", "import seaborn as sns\n", "sns.set(color_codes=True)\n", "\n", "warnings.filterwarnings('ignore')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Import PrattStats Data" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "data=pd.read_csv('prattsdatabase.csv',encoding='latin-1') \n", "\n", "data['IntanglibleIntensityPPA']=data.TotalIntangiblesPPA/data.TotalAssetsPPA\n", "data['NonCompete'][data['NonCompete']=='Yes']=1.0\n", "data['NonCompete'][data['NonCompete']=='No']=0.0\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### The baseline measure of Business Rate of Return\n", "The measure of rate of returns is called \"NetIncomeOverSalePrice\". It is the netincome (after depreciation, interest, taxes) from business in the last full year before the sale divided by market value of invested capital or MVIC. MVIC is the total consideration paid to the seller and includes any cash, notes and/or securities that were used as a form of payment plus any interest-bearing liabilities assumed by the buyer. The MVIC price includes the noncompete value and the assumption of interest-bearing liabilities and excludes (1) the real estate value and (2) any earnouts (because they have not yet been earned, and they may not be earned) and (3) the employment/consulting agreement values." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "data['NetIncomeOverSalePrice']=(data.EarningsBeforeTaxes/data.MVICPrice)*100\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Add labels for Legal type and NAICS " ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "naics_labels=['Agriculture, Forestry, Fishing and Hunting',\\\n", " 'Mining, Quarrying, and Oil and Gas Extraction',\\\n", " 'Utilities',\\\n", " 'Construction',\\\n", " 'NA',\\\n", " 'Manufacturing',\\\n", " 'NA',\\\n", " 'Wholesale Trade',\\\n", " 'Retail Trade',\\\n", " 'NA',\\\n", " 'Transportation and Warehousing',\\\n", " 'NA',\\\n", " 'Information',\\\n", " 'Finance and Insurance',\\\n", " 'Real Estate and Rental and Leasing',\\\n", " 'Professional, Scientific, and Technical Services',\\\n", " 'Management of Companies and Enterprises',\\\n", " 'Administrative and Support and Waste Management and Remediation Services'\\\n", " 'NA',\\\n", " 'Educational Services',\\\n", " 'Health Care and Social Assistance',\\\n", " 'Arts, Entertainment, and Recreation',\\\n", " 'Accommodation and Food Service',\\\n", " 'Other Services']\n", "\n", "len(naics_labels)\n", "\n", "naics_code=[11,21,22,23,24,31,34,42,44,46,48,50,51,52,53,54,55,56,61,62,71,72,81,92]\n", "n=23\n", "\n", "\n", "naics_labels=['Ag. Mining and Utilities',\\\n", " 'Consts and Mfg',\\\n", " 'Trade and warehousing',\\\n", " 'Information and FIRE',\\\n", " 'Professional services',\\\n", " 'Nonprof services']\n", "\n", "naics_code=[11,23,42,51,54,57,92]\n", "\n", "data['TargetStructureAlt']=data['TargetStructure'].values\n", "sel=data['TargetStructure'].isin(['Partnership', 'Limited Partnership', 'LLP'])\n", "data.loc[sel,'TargetStructureAlt']='Partnership'\n", "sel=data['TargetStructure'].isin(['Consolidated', 'UK Corporation', 'PC','Non-Profit','Ltd','Limited Corporation'])\n", "data.loc[sel,'TargetStructureAlt']='Others'\n", "sel=data['TargetStructure'].isin(['C corporation'])\n", "data.loc[sel,'TargetStructureAlt']='C Corporation'\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Rates of Returns" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Sample selection\n", "Taking away outliers top and bottom 0.1%" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "upper_cutoff=0.999\n", "lower_cutoff=0.001\n", "cutoffH=data.MVICPrice.quantile(upper_cutoff)\n", "cutoffL=data.MVICPrice.quantile(lower_cutoff)\n", "cutoffHNI=data.EarningsBeforeTaxes.quantile(upper_cutoff)\n", "cutoffLNI=data.EarningsBeforeTaxes.quantile(lower_cutoff)\n", "cutoffTAH=data.TotalAssetsPPA.quantile(upper_cutoff)\n", "cutoffTAL=data.TotalAssetsPPA.quantile(lower_cutoff)\n", "selection=(data.MVICPrice.notnull())&(data.MVICPrice0))&((data.MVICPrice>cutoffL)) &(data.EarningsBeforeTaxescutoffLNI)&(data.TotalAssetsPPAcutoffTAL)\n", "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership'])\n", "selection=selection&sel\n", "selPassThru=data['TargetStructureAlt'].isin(['S Corporation', 'Sole Proprietorship', 'Partnership'])\n", "\n", "data['TargetStructurePassThru']=data['TargetStructure'].values\n", "data.loc[selPassThru,'TargetStructurePassThru']=True\n", "data.loc[~selPassThru,'TargetStructurePassThru']=False\n", "\n", "\n", "data['TargetStructureBus']=data['TargetStructure'].values\n", "data.loc[sel,'TargetStructureBus']='all_bus'\n", "\n", "\n", "#data['TargetStructure']='bus'\n", "#data['TargetStructureAlt']='bus'\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Define a helper function" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "def f(x):\n", " var_name='NetIncomeOverSalePrice'\n", " d = {}\n", " d['count']=x[var_name].count()\n", " d['eq-wgt mean'] = x[var_name].mean()\n", " d['value-wgt mean']=100*x.EarningsBeforeTaxes.sum()/x.MVICPrice.sum()\n", " d['std dev'] = x[var_name].std()\n", " d['p10'] = x[var_name].quantile(.1)\n", " d['p25'] = x[var_name].quantile(.25)\n", "\n", " d['p50'] = x[var_name].quantile(.5)\n", " d['p75'] = x[var_name].quantile(.75)\n", " d['p90'] = x[var_name].quantile(.90)\n", " return pd.Series(d, index=['count','eq-wgt mean', 'value-wgt mean','std dev', 'p10', 'p25', 'p50','p75','p90'])\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Summary statistics for Net Income/ Sale Price" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### all firms" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
counteq-wgt meanvalue-wgt meanstd devp10p25p50p75p90
TargetStructureBus
all_bus8986.027.7045891.1446474.950375-9.6438193.64510621.45312846.96278279.19524
\n", "
" ], "text/plain": [ " count eq-wgt mean value-wgt mean std dev p10 \\\n", "TargetStructureBus \n", "all_bus 8986.0 27.704589 1.14464 74.950375 -9.643819 \n", "\n", " p25 p50 p75 p90 \n", "TargetStructureBus \n", "all_bus 3.645106 21.453128 46.962782 79.19524 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data[selection][['EarningsBeforeTaxes','NetIncomeOverSalePrice','MVICPrice']].groupby(data.TargetStructureBus).apply(f)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### by pass-thru status" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
counteq-wgt meanvalue-wgt meanstd devp10p25p50p75p90
TargetStructurePassThru
False1988.06.9948100.33274083.463948-24.587270-2.1168696.21702128.44382657.580734
True6998.033.5878477.02172671.266340-5.2426857.31514825.37405851.03145284.457674
\n", "
" ], "text/plain": [ " count eq-wgt mean value-wgt mean std dev \\\n", "TargetStructurePassThru \n", "False 1988.0 6.994810 0.332740 83.463948 \n", "True 6998.0 33.587847 7.021726 71.266340 \n", "\n", " p10 p25 p50 p75 p90 \n", "TargetStructurePassThru \n", "False -24.587270 -2.116869 6.217021 28.443826 57.580734 \n", "True -5.242685 7.315148 25.374058 51.031452 84.457674 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data[selection][['EarningsBeforeTaxes','NetIncomeOverSalePrice','MVICPrice']].groupby(data.TargetStructurePassThru).apply(f)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### by legal form of organization" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
counteq-wgt meanvalue-wgt meanstd devp10p25p50p75p90
TargetStructureAlt
C Corporation1988.06.9948100.33274083.463948-24.587270-2.1168696.21702128.44382657.580734
Partnership205.027.9252935.78504956.658561-19.3061212.73373720.43691048.82260490.344211
S Corporation5632.031.6686886.85973674.637629-5.9160706.50664223.48965148.23443479.711738
Sole Proprietorship1161.043.89751831.90538053.8385690.00000013.92577337.76261163.762973105.000000
\n", "
" ], "text/plain": [ " count eq-wgt mean value-wgt mean std dev \\\n", "TargetStructureAlt \n", "C Corporation 1988.0 6.994810 0.332740 83.463948 \n", "Partnership 205.0 27.925293 5.785049 56.658561 \n", "S Corporation 5632.0 31.668688 6.859736 74.637629 \n", "Sole Proprietorship 1161.0 43.897518 31.905380 53.838569 \n", "\n", " p10 p25 p50 p75 p90 \n", "TargetStructureAlt \n", "C Corporation -24.587270 -2.116869 6.217021 28.443826 57.580734 \n", "Partnership -19.306121 2.733737 20.436910 48.822604 90.344211 \n", "S Corporation -5.916070 6.506642 23.489651 48.234434 79.711738 \n", "Sole Proprietorship 0.000000 13.925773 37.762611 63.762973 105.000000 " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data[selection][['EarningsBeforeTaxes','NetIncomeOverSalePrice','MVICPrice']].groupby(data.TargetStructureAlt).apply(f)\n", "\n", "#data[selection][['EarningsBeforeTaxes','NetIncomeOverSalePrice','MVICPrice','NonCompete']].groupby(data.TargetStructureAlt).apply(f).to_csv('PrattReturns.csv')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### nontech/nondistressed" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [], "source": [ "data['Reason']='NoReason'\n", "data.loc[data.Notes.str.contains('retirement',case=False)==1,'Reason']='retirement'\n", "data.loc[data.Notes.str.contains('health',case='False')==1,'Reason']='health'\n", "data.loc[data.Notes.str.contains('relocation',case='False')==1,'Reason']='relocation'\n", "data.loc[data.Notes.str.contains('burnout',case='False')==1,'Reason']='burnout'" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
counteq-wgt meanvalue-wgt meanstd devp10p25p50p75p90
TargetStructureBus
all_bus8223.029.6255582.01973669.3952-7.2821544.62634422.73028648.3270480.584188
\n", "
" ], "text/plain": [ " count eq-wgt mean value-wgt mean std dev p10 \\\n", "TargetStructureBus \n", "all_bus 8223.0 29.625558 2.019736 69.3952 -7.282154 \n", "\n", " p25 p50 p75 p90 \n", "TargetStructureBus \n", "all_bus 4.626344 22.730286 48.32704 80.584188 " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "selection=(data.MVICPrice.notnull())&(data.MVICPrice0))&((data.MVICPrice>cutoffL)) &(data.EarningsBeforeTaxescutoffLNI)&(data.TotalAssetsPPAcutoffTAL)\n", "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership'])\n", "\n", "selection=selection&sel\n", "\n", "sel51=( (data.TargetNAICS1/(10**10))>=51 ) &( (data.TargetNAICS1/(10**10))<52 ) \n", "sel5415=( (data.TargetNAICS1/(10**10))>=54.15 ) &( (data.TargetNAICS1/(10**10))<54.16 )\n", "sel5417=( (data.TargetNAICS1/(10**10))>=54.17 ) &( (data.TargetNAICS1/(10**10))<54.18 )\n", "\n", "selnaics=(~sel51&~sel5415 & ~sel5417)\n", "\n", "selreason=(data.Reason=='NoReason')\n", "selection=selection&selnaics&selreason\n", "\n", "\n", "naics_code=[11,23,42,50,51,52,53,54,54.15,54.16,54.17,54.18,55,56,57,92]\n", "data[selection][['EarningsBeforeTaxes','NetIncomeOverSalePrice','MVICPrice']].groupby(data.TargetStructureBus).apply(f)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.3" } }, "nbformat": 4, "nbformat_minor": 2 }