Data Cleaning — Financial Performance Analysis

Setup

import pandas as pd
import numpy as np

df = pd.read_excel("Financial Sample.xlsx")
print(f"Shape: {df.shape}")
print(f"Columns: {df.columns.tolist()}")

---

Step 1 — Fix Column Names

Excel financial exports notoriously have leading/trailing spaces in headers.

df.columns = df.columns.str.strip()
df.columns = df.columns.str.replace(" ", "_").str.lower()
print(df.columns.tolist())
# ['segment', 'country', 'product', 'discount_band', 'units_sold',
#  'manufacturing_price', 'sale_price', 'gross_sales', 'discounts',
#  'sales', 'cogs', 'profit', 'date', 'month_number', 'month_name', 'year']

---

Step 2 — Validate Financial Math

A critical step in financial data — verify the relationships hold.

# Recompute and compare to provided columns
df["gross_sales_calc"] = df["units_sold"] * df["sale_price"]
df["sales_calc"] = df["gross_sales"] - df["discounts"]
df["cogs_calc"] = df["units_sold"] * df["manufacturing_price"]
df["profit_calc"] = df["sales"] - df["cogs"]

# Check for discrepancies (allowing for floating point)
tol = 0.01
for col in ["gross_sales", "sales", "cogs", "profit"]:
    diff = (df[col] - df[f"{col}_calc"]).abs()
    mismatches = (diff > tol).sum()
    print(f"{col}: {mismatches} mismatches")

Always reconcile financial data

In finance, numbers must tie out. If profit ≠ sales − cogs, you either have a data error or a definitional difference (e.g., profit includes something COGS doesn't). Reconciling builds trust — finance stakeholders will not accept a dashboard whose numbers don't add up.

---

Step 3 — Parse Dates

df["date"] = pd.to_datetime(df["date"], errors="coerce")
df["year_month"] = df["date"].dt.to_period("M").astype(str)
df["quarter"] = df["date"].dt.quarter

---

Step 4 — Add Financial Metrics

# Gross margin per row
df["gross_margin_pct"] = np.where(df["sales"] > 0, df["profit"] / df["sales"], np.nan)

# Discount rate
df["discount_rate"] = np.where(
    df["gross_sales"] > 0, df["discounts"] / df["gross_sales"], 0
)

# Unit economics
df["profit_per_unit"] = np.where(df["units_sold"] > 0, df["profit"] / df["units_sold"], 0)

---

Step 5 — Create a Budget for Variance Analysis

# Build a synthetic budget: assume the plan was 10% growth over actuals
# (In a real project, you'd load the actual budget file)

monthly_actuals = (
    df.groupby(["year_month", "segment"])
    .agg(actual_sales=("sales", "sum"), actual_profit=("profit", "sum"))
    .reset_index()
)

# Synthetic budget: a smooth target line
monthly_actuals["budget_sales"] = monthly_actuals["actual_sales"].mean() * 1.0  # flat budget baseline
monthly_actuals["sales_variance"] = monthly_actuals["actual_sales"] - monthly_actuals["budget_sales"]
monthly_actuals["sales_variance_pct"] = (
    monthly_actuals["sales_variance"] / monthly_actuals["budget_sales"] * 100
)

---

Step 6 — Audit and Export

def audit(df):
    print(f"Rows: {len(df)}")
    print(f"Total revenue: ${df['sales'].sum():,.0f}")
    print(f"Total COGS:    ${df['cogs'].sum():,.0f}")
    print(f"Total profit:  ${df['profit'].sum():,.0f}")
    print(f"Overall margin: {df['profit'].sum() / df['sales'].sum():.1%}")
    print(f"Total discounts given: ${df['discounts'].sum():,.0f}")

audit(df)

df.to_csv("financials_clean.csv", index=False)
monthly_actuals.to_csv("budget_variance.csv", index=False)
print("Exported.")

---

← Dataset Guide · Next: SQL Analysis →