For this project, I completed a Financial Reporting case study that analyzed company performance across industries (Tech, FMGC, Real Estate) using balance sheet and income statements. I cleaned, merged, and transformed raw fianncial data in actionable insights.
I worked with the two data sets partially shown below: 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.
import numpy as np
import pandas as pd
import seaborn as sns
balance_sheet = pd.read_excel("data/Balance_Sheet.xlsx")
print(balance_sheet.head())import pandas as pd
income_statement = pd.read_excel("data/Income_Statement.xlsx")
print(income_statement.head())import pandas as pd
import numpy as np
import seaborn as sns
df_ratios = pd.merge(income_statement, balance_sheet, on = ["Year", "company", "comp_type"])
df_ratios["profitability_ratio"] = (df_ratios["Total Revenue"] - df_ratios["Cost Of Goods Sold"]) / df_ratios["Total Revenue"]
df_ratios["leverage_ratio"] = df_ratios["Total Liab"] / df_ratios["Total Stockholder Equity"]
print(df_ratios.pivot_table(index = "comp_type", values = "profitability_ratio"))
lowest_profitability = "fmcg"
print(df_ratios.pivot_table(index = "comp_type", values = "leverage_ratio"))
highest_leverage = "real_est"
df_real_est = df_ratios.loc[df_ratios["comp_type"]== "real_est"]
plot = sns.regplot(data=df_real_est, x="leverage_ratio", y="profitability_ratio")
relationship = "positive"
I used an sns plot to reveal a positive relationship between leverage and profitability in real estate. Higher debt correlated with higher margins, which is likely due to asset appreciation. Real estate offers high returns but also carries higher risk.
Key findings from my Analysis
Real Estate: Revealed a positive relationship betwen debt and profitability in this sector. Highest risk. Tech: Highest absolute profitability ratios despite lower leverage. Favored industry for an aggresive investing strategy Fast-Moving Consumer Goods: Lowest leverage and profitability. Favored industry for a conservative investing strategy