Skip to content

Help your hedge fund manager!

You have two datasets at your disposal: Balance_Sheet.xlsx and Income_Statement.xlsx. Both these datasets have three columns in common:

  • "Company": The company's ticker name.
  • "comp_type" The type of industry the company in question belongs to. It is either "tech" for companies in the technology industry, "fmcg" for companies in the fast-moving consumer goods industry, and "real_est" for companies in the real estate industry.
  • "Year": The year the company's information is from.

The rest of the columns in the datasets contain information from the financial statement of the "Company" in question. Note that the columns in Balance_Sheet.xlsx only contain financial information from the balance sheet. Similarly, the columns in Income_Statement.xlsx only contain financial information from the income statement. The columns are named accordingly. For instance, the column "Total Liab" from Balance_Sheet.xlsx is the total liability.

import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
# Load the datasets
balance_sheet = pd.read_excel('data/Balance_Sheet.xlsx')
income_statement = pd.read_excel('data/Income_Statement.xlsx')
balance_sheet.head()
income_statement.head()
# Merge the datasets on the common columns: 'Company', 'comp_type', and 'Year'
merged_data = pd.merge(balance_sheet, income_statement, on=['company', 'comp_type', 'Year'])
merged_data.head()
# Check the columns of the DataFrame to ensure the correct column names
print(merged_data.columns)
# Calculate the leverage ratio (Debt-to-Equity ratio)
merged_data['leverage_ratio'] = merged_data['Total Liab'] / merged_data['Total Stockholder Equity']
# Calculate the profitability ratio (Gross Margin ratio)
merged_data['profitability_ratio'] = merged_data['Gross Profit'] / merged_data['Total Revenue']
# Save the ratios in a DataFrame called df_ratios
df_ratios = merged_data[['company', 'comp_type', 'Year', 'leverage_ratio', 'profitability_ratio']]
# Determine the company type with the lowest profitability ratio
lowest_profitability = df_ratios.groupby('comp_type')['profitability_ratio'].mean().idxmin()
# Determine the company type with the highest leverage ratio
highest_leverage = df_ratios.groupby('comp_type')['leverage_ratio'].mean().idxmax()
# Analyze the relationship between leverage and profitability in real estate companies
real_estate_data = df_ratios[df_ratios['comp_type'] == 'real_est']
# Calculate the correlation between leverage and profitability
correlation = real_estate_data[['leverage_ratio', 'profitability_ratio']].corr().iloc[0, 1]
# Determine the relationship based on the correlation
if correlation > 0:
    relationship = "positive"
elif correlation < 0:
    relationship = "negative"
else:
    relationship = "no relationship"

# Display results
print("Lowest profitability company type:", lowest_profitability)
print("Highest leverage company type:", highest_leverage)
print("Relationship between leverage and profitability in real estate companies:", relationship)
‌
‌
‌