Python Group Assignment

# start by loading in our typical set of packages import numpy as np # NumPy import pandas as pd # pandas import seaborn as sns import matplotlib.pyplot as plt import sys print(sys.version)

# use pip to install the package 'xlrd' # need it to read the excel file below !pip install xlrd import pandas as pd df = pd.read_excel

fnamein = 'Company Screening Report 0yr Lag.xls' # use pandas to read the excel file. Open it if you like and check out why the parameters are set the way they are. df_mult0 = pd.read_excel(fnamein,sheet_name='Screening',header=None,na_values=['-','NM'],skiprows=8) # here we are making our own column names instead of the longer ones in the Excel file. i = ['name','ex:tick','comp_type','exchange','industry','sector','marketcap','total_debt','shrout','revenue','gross_profit','sga','advertising','marketing','sales_marketing','gen_admin','rd_expense','int_expense','op_lease_int','ebitda','ebit','net_income','cash','capex','acquisitions','changeNWC','beta1','beta2','beta5','etr','tev','gRev1yr','gRev3yr','ebit_margin','ebitda_margin','gross_margin','roe','roc','altman'] # set column names df_mult0.columns = i # We are going to make the ticker our index - i.e. the unique identifier. So we can't have any NA values, so .dropna() df_mult0.dropna(axis=0, how='any', subset=['ex:tick'],inplace=True) df_mult0.set_index('ex:tick',inplace=True, verify_integrity=True) print(df_mult0.info()) df_mult0.describe()

# fill in the two variables mentioned above to see their summary stats df_mult0.loc[:,['revenue','tev']].describe()

# lets fix that. What should we do? Pick some (not all) of the following # set values that are nonsensical to NaN, keep the rest the same? df_mult0.loc[:,'revenue'] = np.where(df_mult0['revenue']<=0, np.nan, df_mult0['revenue']) df_mult0.loc[:,'tev'] = np.where(df_mult0['tev']<=0, np.nan, df_mult0['tev']) # fill the NaN values with something? #df_mult0['revenue'].fillna(value=???,inplace=True) #df_mult0['???'].fillna(value=???,inplace=True) # drop NaN values? df_mult0.dropna(axis=0, how='any', subset=['revenue','tev'], inplace=True) # see if you solved the problem df_mult0.loc[:,['revenue','tev']].describe()

# let's look at the dataframe, but only where the 'exchange' variable is NaN df_mult0[df_mult0.exchange.isna()]

# this looks a lot like they are just international firms. We should keep these. # Let's fill them with a value that may help us sort later. # Also, later functions won't like NaN in our data. df_mult0['exchange'].fillna(value='NonUS', inplace=True) #all this does is replace the NaN above with the text NonUS for Non-US Exchanges. #check how many NaN are left in the data frame # what function did we use above to see the non-null counts? df_mult0[df_mult0.exchange.isna()]

# working from the top, the next one with missing values is Market Cap. (depending on what you did exactly - it may be ok) # if the firm is missing MarketCap, then that means we don't have stock price or shares outstanding. # Either is a problem, so we have to drop these firms. How do we do that? # drop NaN values df_mult0.dropna(axis=0, how='any', subset=['marketcap'], inplace=True)

# next we see total_debt. # what does it mean if total_debt is missing? It likely means that the firm just has no debt. # filter the Data Frame by only looking at firms where total_debt is NaN. df_mult0.loc[df_mult0.total_debt.isna()]

# Maybe we need to look by sector. Where are these firms with missing total_debt? # Let's look at how many NaN we have in total_debt by sector. # the first part is the same filter as above, then use the groupby() function on the variable 'sector' df_mult0.loc[df_mult0.total_debt.isna()].groupby(['sector']).count()

# since we feel comfortalbe that these are normal companies but that they just have 0 total_debt, let's fill the NaN with 0 df_mult0['total_debt'].fillna(value=0,inplace=True) # make sure you set the correct second parameter to update the DataFrame. df_mult0.info()

# removing companies with ebit less than or equal to zero df_mult0.loc[:,'ebit'] = np.where(df_mult0['ebit']<=0, np.nan, df_mult0['ebit']) df_mult0.dropna(axis=0, how='any', subset=['ebit'], inplace=True) # filling ebitda with the ebit value if NaN df_mult0.loc[:,'ebitda'] = np.where(df_mult0['ebitda']<=0, np.nan, df_mult0['ebitda']) df_mult0['ebitda'].fillna(value=df_mult0['ebit'], inplace=True) # see if there are any ebitda left NaN df_mult0.loc[df_mult0.ebitda.isna()].count()

You are now a lot of the way there. The goal is to do this kind of analysis all the way down for all of your variables, finding why there is any odd data - negatives, NaN, zeros, etc. You can use filters, look at the data, summary stats, etc.

# fill ebit margin with calculation of ebit/revenue df_mult0['ebit_margin'].fillna(value=(df_mult0['ebit']/df_mult0['revenue']), inplace=True) # make sure that values for ebit_margin look reasonable df_mult0.loc[:,'ebit_margin'].describe()

# fill ebitda margin with calculation of ebitda/revenue df_mult0['ebitda_margin'].fillna(value=(df_mult0['ebitda']/df_mult0['revenue']), inplace=True) # make sure that values for ebitda_margin look reasonable df_mult0.loc[:,'ebitda_margin'].describe()

# fill gross margin with calculation of gross profit/revenue df_mult0['gross_margin'].fillna(value=(df_mult0['gross_profit']/df_mult0['revenue']), inplace=True) # make sure that values for gross_margin look reasonable df_mult0.loc[:,'gross_margin'].describe()

#drop companies without beta 2 since it is necessary to valuation df_mult0.dropna(axis=0, how='any', subset=['beta2'], inplace=True)

# change etr to effective tax rate of 22% if NaN df_mult0['etr'].fillna(value=0.22, inplace=True)

# drop companies with no 1 yr growth rate since there are only 4 and we need this value for valuation df_mult0.dropna(axis=0, how='any', subset=['gRev1yr'], inplace=True)

# drop roc with NaN values since we don't have data to calculate it df_mult0.dropna(axis=0, how='any', subset=['roc'], inplace=True) df_mult0.info()

# lets put all of our conditions into a single index object, then use that to filter our dataframe # do an internet search for "pandas dataframe slice by multiple conditions" if you want more info on what I'm doing here # each of these create a boolean series using specific criteria. idx_margin = (df_mult0.ebit_margin > 10) & (df_mult0.ebit_margin < 20) idx_roc = (df_mult0.roc > 10) & (df_mult0.roc < 20) idx_growth = (df_mult0.gRev1yr > 0) & (df_mult0.gRev1yr < 20) idx_beta = (df_mult0.beta2 > 0.8) & (df_mult0.beta2 < 1.2) # these are all boolean conditions, so we just use AND to combine them idx = idx_margin & idx_roc & idx_growth & idx_beta # now we pass idx for the row portion, and the list of columns we want to see df_mult0.loc[idx,['ebit_margin','roc','gRev1yr','beta2']].describe()

# now, compute the EV/EBITDA multiple. Or EV/EBIT. Or perhaps Price/Sales (MarketCap/Revenue). # note: you may want to use np.where() if your denominator is zero. You'll see inf or NaN for your multiple in that case. df_mult0['ev_ebitda'] = df_mult0['tev']/df_mult0['ebitda'] # check out your multiple df_mult0[idx]['ev_ebitda'].describe()

df_mult0[idx].sort_values(by='ev_ebitda')

# want to do a histogram? df_mult0[idx]['ev_ebitda'].hist();

# identify poor performing companies by using the 25th percentile of gross profit,profit margin, # return on capital, ebitda margin, and ebitda df_mult0['ebitda'].describe()

# find percentiles for gross profit df_mult0['gross_profit'].describe()

# find percentiles for gross margin df_mult0['gross_margin'].describe()

# find percentiles for roc df_mult0['roc'].describe()

# find percentiles for ebitda margin df_mult0['ebitda_margin'].describe()

poor_ebitda = (df_mult0.ebitda < 67.225) poor_profitmargin = (df_mult0.gross_margin < 24.7) poor_profit = (df_mult0.gross_profit < 170.9) poor_roc = (df_mult0.roc < 3.14) poor_ebitdamargin = (df_mult0.ebitda_margin < 9.5275) # these are all boolean conditions, so we just use AND to combine them poor = poor_ebitda & poor_profitmargin & poor_profit & poor_roc & poor_ebitdamargin # finding the companies that are below the 25th percentile for each criteria df_mult0.loc[poor,['ebitda','gross_margin','gross_profit','roc','ebitda_margin']].describe()

df_mult0[poor]['ev_ebitda'].describe()

df_mult0[poor].sort_values(by='ev_ebitda', ascending = False)

# plot multiples for poor performing companies df_mult0[poor]['ev_ebitda'].hist();

Now, we are going to identify the high performing companies using the same categories for criteria.

# finding the companies performing well # characterized as the companies above the 75th percentile for each criteria good_ebitda = (df_mult0.ebitda > 912.9) good_profitmargin = (df_mult0.gross_margin > 56.975) good_profit = (df_mult0.gross_profit > 1789.825) good_roc = (df_mult0.roc > 9.4675) good_ebitdamargin = (df_mult0.ebitda_margin > 28.1) good = good_ebitda & good_profitmargin & good_profit & good_roc & good_ebitdamargin # finding the companies that are above the 75th percentile for each criteria df_mult0.loc[good,['ebitda','gross_margin','gross_profit','roc','ebitda_margin']].describe()

df_mult0[good].sort_values(by='ev_ebitda', ascending = False)

# look at the distribution of multiples in the high performing category df_mult0[good]['ev_ebitda'].describe()

df_mult0[good]['ev_ebitda'].hist();

# Identify two high-performing firms to buy Merck & Co. Nvidia # Identify two low-performing firms to sell Daily Journal Corporation Cara Therapeutics, Inc.

We recommend selling Daily Journal Corporation (NasdaqCM:DJCO) and Cara Therapeutics, Inc. (NasdaqGM:CARA). We selected these companies based on poor key financial health criteria, coupled with having a high EV/EBITDA multiple, and negative industry outlooks. Poor performing companies were in the bottom quartile of EBITDA, profit margin, profit, roc, and EBITDA margin, within the entire dataset. Daily Journal has an EV/EBITDA multiple of 95.87, the fourth highest in the poor performing quartile – indicating a high valuation with comparatively low EBITDA. Furthermore, as a news publishing company operating on a regional basis, we see strong industry headwinds of full digitization as an obstacle to company growth (CFRA Equity Research: DJCO, 11/20/21). Cara Therapeutics has an EV/EBITDA multiple of 201.06, the second highest in the poor performing quartile. We see the same trend here as we do with Daily Journal Corporation, only more exacerbated. Additionally, recent company news reports revealed abnormalities for patients involved in their clinical trials for Oral KORUSVA (Company Press Release: CARA, 4/29/21). Specifically, we believe that the company faces inconsistent growth, in addition to poor operating and return on investment margins (Argus Research Report: CARA, 11/10/21).

We recommend buying Merck & Co (NYSE: MRK) and Nvidia Corporation (NasdaqGS: NVDA). We selected these companies because they have a relatively lower (hence healthier) EV/EBITDA multiple, while also they are high performing so we would recommend buying those stocks. The division of EV by EBITDA gives a good measure of value. It estimates the number of years in which the business will repay its acquisition cost to the buyer through its earnings. Merck&Co has an EV/EBITDA multiple of 11.35, the 13th from the end in our data frame. This indicates that although it's not at the top of the high performers group, it's still considered to be a buy since it has a good EV/EBITDA compared to the firms in it's industry. Nvidia has a high EV/EBITDA multiple of 60.36, which should be encouraging to a potential investor due to previously discussed positive performance implications. This is the highest multiple in the top performing companies and represents a company with ample room for future growth. Nvidia is an artificial intelligence company with promising future earnings expectations based on our multiple analysis. Following the pandemic chip storage, the demand for Nvidia's chips for self-driving cars sky rocketed. The company is looking to expand chip supply to other industries such as healthcare and entertainment. (Investor's Business Daily: NVDA, 11/18/21).