Skip to content
New Workbook
Sign up
Building financial reports outline

Data used

I plan on using the same data as in the course Analyzing Financial Statements in Python. The datasets are uploaded in this workspace.

Packages to be used:

I noticed that you wanted to use matplotlib in the course wishlist. However, I feel that it is becoming an outdated method of making plots. seaborn is used more commonly in industry these days, so I would prefer to use that (it is based on matplotlib, so we still need to import it to make small changes to the plots made)

#importing the packages I plan on using
import pandas as pd
import numpy as np
# will be using pivot table, and basic operations like addition/multipilcation of columns.
import seaborn as sns
# Will be using line plot and reg plot
import matplotlib.pyplot as plt

I want to make the project as free as possible for the students, but make it restrictive enough so that SCTs can be built that can test the quality of submission.

Project outline:

  • Compute: gross margin, current ratio, and operating cash flow ratio.
  • Answer the following questions:
    • Which company has the highest gross margin ratio on average?
    • Which company has the lowest current ratio on average?
    • Which company has the highest positive correlation between operating cash flow ratio and gross margin?
    • When comparing the current ratio of Apple and Microsoft over the years in the dataset, which company has a higher current ratio more often? Make a plot to answer this.
    • Make a scatter plot with current ratio on the horizontal axis and gross margin on the verticle axis. Add a line of best fit to this scatter plot without a confidence interval. What is the slope of the line of best fit?
balance_sheet = pd.read_excel("balance_sheet.xlsx")
balance_sheet.columns

balance_sheet_imp = balance_sheet[['Year', 'comp_type', 'company', 'Accounts Payable', 'Cash', 'Inventory',
                                   'Property Plant Equipment', 'Short Term Investments', 'Total Assets',
                                   'Total Current Assets', 'Total Current Liabilities',
                                   'Total Liab', 'Total Stockholder Equity']]
balance_sheet_imp.to_excel("Balance_sheet.xlsx")
income_statement = pd.read_excel("financials.xlsx")
income_statement.columns

income_statement_imp = income_statement[['Year', 'comp_type', 'company', 'Cost Of Revenue', 'Gross Profit', 'Operating Income',
                                        'Total Operating Expenses', 'Total Revenue']]
income_statement_imp.rename(columns={'Cost Of Revenue':'Cost Of Goods Sold'}, inplace=True)

income_statement_imp.to_excel("Income_Statement.xlsx")
df_merged = pd.merge(income_statement_imp, balance_sheet_imp, on = ["Year", "company", "comp_type"])
# Function from course
def compute_ratio(df, numerator, denominator, ratio_name, 
                  addition_in_numerator = True,
                  addition_in_denominator = True):
  numerator_of_ratio = np.where(addition_in_numerator,
                             df[numerator].sum(axis=1), 
                             df[numerator[0]] - df[numerator[1:]].sum(
                               axis=1))
  denominator_of_ratio = np.where(addition_in_denominator, 
                               df[denominator].sum(axis=1), 
                               df[denominator[0]] - df[denominator[1:]].sum(axis=1))
  df[ratio_name] = numerator_of_ratio/denominator_of_ratio
  return df
# Compute profitability ratio: gross margin / net margin
# Call the ratio computed profitability_ratio
# compute gross margin
df_merged = compute_ratio(df_merged, numerator=["Total Revenue", "Cost Of Goods Sold"], denominator=["Total Revenue"], ratio_name = "profitability_ratio", addition_in_numerator=False, addition_in_denominator=True)

# df_merged["profitability_ratio"] avg rounded to 4 decimal: 0.5404, ... median 0.5407

# compute operating margin:
df_merged = compute_ratio(df_merged, numerator=["Total Revenue", "Total Operating Expenses"], denominator=["Total Revenue"], ratio_name = "profitability_ratio", addition_in_numerator=False, addition_in_denominator=True)
# df_merged["profitability_ratio"] avg rounded to 4 decimal: 0.2603, ... median 0.2436

# compute leverage ratio 
# debt to equity ratio
df_merged = compute_ratio(df_merged, numerator=["Total Liab"], denominator=["Total Stockholder Equity"], ratio_name = "leverage_ratio")
# df_merged["leverage_ratio"] avg rounded to 4 decimal: 3.4891, ... median 2.7379

# Equity multiplier ratio
df_merged = compute_ratio(df_merged, numerator=["Total Assets"], denominator=["Total Stockholder Equity"], ratio_name = "leverage_ratio")
# df_merged["leverage_ratio"] avg rounded to 4 decimal: 4.7269, ... median 3.7379
round(df_merged["leverage_ratio"].mean(), 4)
# Analysis with Gross margin:
df_merged.pivot_table(index="comp_type", values="profitability_ratio") # fmcg lowest#, tech highest
# Analysis with operating margin:
df_merged.pivot_table(index="comp_type", values="profitability_ratio") # fmcg lowets#, real_est highest
# Analysis with debt to equity
df_merged.pivot_table(index="comp_type", values="leverage_ratio") # tech lowets#, #real_est highest#
# Analysis with equity multiplier
df_merged.pivot_table(index="comp_type", values="leverage_ratio") # tech lowets#, real_est highest
# Compute profitability ratio: gross margin / net margin
# Call the ratio computed profitability_ratio
# compute gross margin
df_merged = compute_ratio(df_merged, numerator=["Total Revenue", "Cost Of Goods Sold"], denominator=["Total Revenue"], ratio_name = "gross_margin", addition_in_numerator=False, addition_in_denominator=True)

# df_merged["profitability_ratio"] avg rounded to 4 decimal: 0.5404, ... median 0.5407

# compute operating margin:
df_merged = compute_ratio(df_merged, numerator=["Total Revenue", "Total Operating Expenses"], denominator=["Total Revenue"], ratio_name = "operating_margin", addition_in_numerator=False, addition_in_denominator=True)
# df_merged["profitability_ratio"] avg rounded to 4 decimal: 0.2603, ... median 0.2436

# compute leverage ratio 
# debt to equity ratio
df_merged = compute_ratio(df_merged, numerator=["Total Liab"], denominator=["Total Stockholder Equity"], ratio_name = "debt_to_equity")
# df_merged["leverage_ratio"] avg rounded to 4 decimal: 3.4891, ... median 2.7379

# Equity multiplier ratio
df_merged = compute_ratio(df_merged, numerator=["Total Assets"], denominator=["Total Stockholder Equity"], ratio_name = "equity_multiplier")
# df_merged["leverage_ratio"] avg rounded to 4 decimal: 4.7269, ... median 3.7379
‌
‌
‌