Financial Ratios Analysis in BI

Step-by-Step Practice

Step 1 — Prepare the folder and load previous files

Step 2 — Build topic-specific calculations

Step 3 — Export the output CSV or documentation file

Step 4 — Validate rows, keys, and financial logic

Step 5 — Interpret the result as a BI analyst

Script / Instructions

Copyfrom pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 10 - Liquidity Ratios
# ============================================================

# ------------------------------------------------------------
# Step 1 - Set project folder
# ------------------------------------------------------------

base_path = Path("financial_ratios_bi_training")

if not base_path.exists():
    raise FileNotFoundError(
        "The project folder does not exist. Please run Topic 1 first."
    )

balance_sheet_path = base_path / "Fact_Balance_Sheet.csv"
dim_company_path = base_path / "Dim_Company.csv"
dim_period_path = base_path / "Dim_Period.csv"

required_files = [
    balance_sheet_path,
    dim_company_path,
    dim_period_path
]

for file_path in required_files:
    if not file_path.exists():
        raise FileNotFoundError(
            f"Required file not found: {file_path.name}. "
            "Please run the previous topics first."
        )

print(f"Project folder found: {base_path.resolve()}")

# ------------------------------------------------------------
# Step 2 - Load source files
# ------------------------------------------------------------

balance_sheet = pd.read_csv(balance_sheet_path)
dim_company = pd.read_csv(dim_company_path)
dim_period = pd.read_csv(dim_period_path)

print("Fact_Balance_Sheet loaded:", len(balance_sheet), "rows")

# ------------------------------------------------------------
# Step 3 - Extract liquidity inputs from Balance Sheet
# ------------------------------------------------------------

liquidity_inputs = balance_sheet[
    balance_sheet["RatioInput"].isin(
        [
            "Cash",
            "Accounts Receivable",
            "Inventory",
            "Current Assets",
            "Current Liabilities",
            "Working Capital"
        ]
    )
].copy()

liquidity_pivot = (
    liquidity_inputs
    .pivot_table(
        index=["CompanyID", "PeriodID"],
        columns="RatioInput",
        values="Amount",
        aggfunc="sum"
    )
    .reset_index()
)

required_columns = [
    "Cash",
    "Accounts Receivable",
    "Inventory",
    "Current Assets",
    "Current Liabilities",
    "Working Capital"
]

for column in required_columns:
    if column not in liquidity_pivot.columns:
        liquidity_pivot[column] = 0

# ------------------------------------------------------------
# Step 4 - Calculate liquidity ratios
# ------------------------------------------------------------

records = []

for _, row in liquidity_pivot.iterrows():

    company_id = int(row["CompanyID"])
    period_id = int(row["PeriodID"])

    cash = float(row["Cash"])
    accounts_receivable = float(row["Accounts Receivable"])
    inventory = float(row["Inventory"])
    current_assets = float(row["Current Assets"])
    current_liabilities = float(row["Current Liabilities"])
    working_capital = float(row["Working Capital"])

    current_ratio = (
        current_assets / current_liabilities
        if current_liabilities != 0
        else 0
    )

    quick_ratio = (
        (current_assets - inventory) / current_liabilities
        if current_liabilities != 0
        else 0
    )

    cash_ratio = (
        cash / current_liabilities
        if current_liabilities != 0
        else 0
    )

    accounts_receivable_to_current_assets = (
        accounts_receivable / current_assets
        if current_assets != 0
        else 0
    )

    inventory_to_current_assets = (
        inventory / current_assets
        if current_assets != 0
        else 0
    )

    records.extend([
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Current Assets",
            "RatioValue": round(current_assets, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Total short-term assets available to support operations."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Current Liabilities",
            "RatioValue": round(current_liabilities, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Short-term obligations due within one year."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Working Capital",
            "RatioValue": round(working_capital, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Current assets minus current liabilities."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Current Ratio",
            "RatioValue": round(current_ratio, 4),
            "RatioFormat": "Decimal",
            "Interpretation": "Measures ability to cover short-term liabilities with current assets."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Quick Ratio",
            "RatioValue": round(quick_ratio, 4),
            "RatioFormat": "Decimal",
            "Interpretation": "Measures short-term liquidity excluding inventory."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Cash Ratio",
            "RatioValue": round(cash_ratio, 4),
            "RatioFormat": "Decimal",
            "Interpretation": "Measures ability to cover current liabilities using cash only."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Accounts Receivable to Current Assets",
            "RatioValue": round(accounts_receivable_to_current_assets, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Shows how much of current assets are tied to receivables."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Liquidity",
            "RatioName": "Inventory to Current Assets",
            "RatioValue": round(inventory_to_current_assets, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Shows how much of current assets are tied to inventory."
        }
    ])

fact_liquidity_ratios = pd.DataFrame(records)

# ------------------------------------------------------------
# Step 5 - Add company and period labels
# ------------------------------------------------------------

fact_liquidity_ratios = fact_liquidity_ratios.merge(
    dim_company[["CompanyID", "CompanyName", "Industry"]],
    on="CompanyID",
    how="left"
)

fact_liquidity_ratios = fact_liquidity_ratios.merge(
    dim_period[
        [
            "PeriodID",
            "FiscalYear",
            "FiscalQuarter",
            "YearQuarter",
            "PeriodEndDate"
        ]
    ],
    on="PeriodID",
    how="left"
)

fact_liquidity_ratios = fact_liquidity_ratios[
    [
        "CompanyID",
        "CompanyName",
        "Industry",
        "PeriodID",
        "FiscalYear",
        "FiscalQuarter",
        "YearQuarter",
        "PeriodEndDate",
        "RatioCategory",
        "RatioName",
        "RatioValue",
        "RatioFormat",
        "Interpretation"
    ]
]

# ------------------------------------------------------------
# Step 6 - Export Fact_Liquidity_Ratios
# ------------------------------------------------------------

fact_liquidity_ratios.to_csv(
    base_path / "Fact_Liquidity_Ratios.csv",
    index=False
)

print("Fact_Liquidity_Ratios.csv created successfully.")

# ------------------------------------------------------------
# Step 7 - Create validation summary
# ------------------------------------------------------------

validation_records = []

for _, row in liquidity_pivot.iterrows():

    company_id = int(row["CompanyID"])
    period_id = int(row["PeriodID"])

    current_assets = float(row["Current Assets"])
    current_liabilities = float(row["Current Liabilities"])
    inventory = float(row["Inventory"])
    cash = float(row["Cash"])
    working_capital = float(row["Working Capital"])

    expected_working_capital = current_assets - current_liabilities
    working_capital_difference = round(
        working_capital - expected_working_capital,
        2
    )

    expected_current_ratio = (
        current_assets / current_liabilities
        if current_liabilities != 0
        else 0
    )

    expected_quick_ratio = (
        (current_assets - inventory) / current_liabilities
        if current_liabilities != 0
        else 0
    )

    expected_cash_ratio = (
        cash / current_liabilities
        if current_liabilities != 0
        else 0
    )

    validation_records.append({
        "CompanyID": company_id,
        "PeriodID": period_id,
        "CurrentAssets": round(current_assets, 2),
        "CurrentLiabilities": round(current_liabilities, 2),
        "WorkingCapital": round(working_capital, 2),
        "ExpectedWorkingCapital": round(expected_working_capital, 2),
        "WorkingCapitalDifference": working_capital_difference,
        "ExpectedCurrentRatio": round(expected_current_ratio, 4),
        "ExpectedQuickRatio": round(expected_quick_ratio, 4),
        "ExpectedCashRatio": round(expected_cash_ratio, 4),
        "Status": (
            "PASSED"
            if working_capital_difference == 0
            else "FAILED"
        )
    })

liquidity_validation = pd.DataFrame(validation_records)

liquidity_validation.to_csv(
    base_path / "Liquidity_Ratios_Validation_Summary.csv",
    index=False
)

print("Liquidity_Ratios_Validation_Summary.csv created successfully.")

# ------------------------------------------------------------
# Step 8 - Print validation summary
# ------------------------------------------------------------

print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")

print("Liquidity ratio rows:", len(fact_liquidity_ratios))
print("Validation rows:", len(liquidity_validation))

print()
print("Rows by RatioName:")
print(
    fact_liquidity_ratios
    .groupby("RatioName")["RatioValue"]
    .count()
    .reset_index(name="NumberOfRows")
)

print()
print("Liquidity Summary by Company:")
print(
    fact_liquidity_ratios[
        fact_liquidity_ratios["RatioName"].isin(
            [
                "Current Ratio",
                "Quick Ratio",
                "Cash Ratio"
            ]
        )
    ]
    .groupby(["CompanyID", "CompanyName", "RatioName"])["RatioValue"]
    .mean()
    .round(4)
    .reset_index(name="AverageRatio")
)

print()
print("Failed validation rows:")
failed_rows = liquidity_validation[liquidity_validation["Status"] == "FAILED"]

if failed_rows.empty:
    print("No failed rows. Liquidity Ratios validation passed.")
else:
    print(failed_rows)

print()
print("Preview of Fact_Liquidity_Ratios:")
print(fact_liquidity_ratios.head(20))

print()
print("Files currently in project folder:")

for file in base_path.glob("*.csv"):
    print("-", file.name)

print()
print("Topic 10 completed successfully.")

financial_ratios_bi_training/Fact_Liquidity_Ratios.csv

Validation Checklist

• Checkpoint 1: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 2: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 3: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 4: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 5: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 6: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 7: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.
• Checkpoint 8: Validate source fields, row counts, relationship keys, financial reasonableness, and business interpretation before moving to the next topic.

Final Recommendation

Before moving forward, open the generated file, review the columns, confirm the number of companies and periods, and make sure the numbers make financial sense. Good BI starts with clean, explainable, and validated data.