Financial Ratios Analysis in BI

Learning by Doing Series — Topic 7: Build the Balance Sheet

Objective

Aggregate current assets, non-current assets, liabilities, equity, and working capital from the synthetic trial balance.

The main output of this topic is Fact_Balance_Sheet.csv.

Objetivo

Aggregate current assets, non-current assets, liabilities, equity, and working capital from the synthetic trial balance.

El archivo principal de salida de este tópico es Fact_Balance_Sheet.csv.

Production / Cybersecurity Warning

This exercise uses synthetic training data only. Do not run scripts in production, real corporate folders, financial databases, or work environments without formal authorization. Validate backups, sandbox testing, least-privilege permissions, change-control approval, audit requirements, and organizational cybersecurity protocols before automating any real financial process.

Advertencia de Producción / Ciberseguridad

Este ejercicio usa data sintética. No ejecutes scripts en producción, carpetas reales, bases financieras corporativas o ambientes de trabajo sin autorización formal. Valida respaldos, pruebas en sandbox, permisos mínimos, control de cambios, auditoría y protocolos de ciberseguridad antes de automatizar cualquier proceso financiero real.

Business Scenario

The training has already created dimensions and core financial data. This topic moves one step closer to an executive BI model by creating a clean analytical file for Balance Sheet.

Escenario de Negocio

El training ya creó dimensiones y data financiera base. Este tópico avanza hacia el modelo ejecutivo de BI creando un archivo analítico limpio para Balance Sheet.

Input: Dim_Company.csv

Input: Dim_Period.csv

Output: Fact_Balance_Sheet.csv

Focus: Balance Sheet

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

from pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 7 - Build the Balance Sheet
# Corrected version: Balance Sheet closes by construction
# ============================================================

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."
    )

trial_balance_path = base_path / "Fact_Trial_Balance.csv"
mapping_path = base_path / "Mapping_Financial_Statements.csv"
dim_company_path = base_path / "Dim_Company.csv"
dim_period_path = base_path / "Dim_Period.csv"

if not trial_balance_path.exists():
    raise FileNotFoundError(
        "Fact_Trial_Balance.csv was not found. Please run Topic 3 first."
    )

if not mapping_path.exists():
    raise FileNotFoundError(
        "Mapping_Financial_Statements.csv was not found. Please run Topic 5 first."
    )

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

# ------------------------------------------------------------
# Load files
# ------------------------------------------------------------

trial_balance = pd.read_csv(trial_balance_path)
mapping = pd.read_csv(mapping_path)
dim_company = pd.read_csv(dim_company_path)
dim_period = pd.read_csv(dim_period_path)

trial_balance["AccountNumber"] = trial_balance["AccountNumber"].astype(str)
mapping["AccountNumber"] = mapping["AccountNumber"].astype(str)

print("Fact_Trial_Balance loaded:", len(trial_balance), "rows")
print("Mapping_Financial_Statements loaded:", len(mapping), "rows")

# ------------------------------------------------------------
# Filter balance sheet accounts
# ------------------------------------------------------------

balance_mapping = mapping[mapping["FinancialStatement"] == "Balance Sheet"].copy()

balance_trial_balance = trial_balance.merge(
    balance_mapping[
        [
            "AccountNumber",
            "StatementSection",
            "StatementLine",
            "LineGroup",
            "LineOrder",
            "SignMultiplier",
            "RatioInput"
        ]
    ],
    on="AccountNumber",
    how="inner"
)

print("Balance sheet source rows:", len(balance_trial_balance))

# ------------------------------------------------------------
# Calculate amount
# ------------------------------------------------------------

balance_trial_balance["RawAmount"] = balance_trial_balance.apply(
    lambda row: row["Debit"] if row["Debit"] > 0 else row["Credit"],
    axis=1
)

balance_trial_balance["Amount"] = (
    balance_trial_balance["RawAmount"]
    * balance_trial_balance["SignMultiplier"]
).round(2)

# ------------------------------------------------------------
# Aggregate base lines
# ------------------------------------------------------------

base_lines = (
    balance_trial_balance
    .groupby(
        [
            "CompanyID",
            "PeriodID",
            "StatementSection",
            "StatementLine",
            "LineGroup",
            "LineOrder",
            "RatioInput"
        ]
    )
    .agg(Amount=("Amount", "sum"))
    .reset_index()
)

base_lines["Amount"] = base_lines["Amount"].round(2)
base_lines["LineType"] = "Base Line"

# ------------------------------------------------------------
# Calculated balance sheet lines
# ------------------------------------------------------------

calculated_records = []

for (company_id, period_id), group in base_lines.groupby(["CompanyID", "PeriodID"]):

    cash = group.loc[group["RatioInput"] == "Cash", "Amount"].sum()
    accounts_receivable = group.loc[group["RatioInput"] == "Accounts Receivable", "Amount"].sum()
    inventory = group.loc[group["RatioInput"] == "Inventory", "Amount"].sum()
    other_current_assets = group.loc[group["RatioInput"] == "Other Current Assets", "Amount"].sum()

    fixed_assets = group.loc[group["RatioInput"] == "Fixed Assets", "Amount"].sum()
    accumulated_depreciation = group.loc[group["RatioInput"] == "Accumulated Depreciation", "Amount"].sum()
    intangible_assets = group.loc[group["RatioInput"] == "Intangible Assets", "Amount"].sum()

    accounts_payable = group.loc[group["RatioInput"] == "Accounts Payable", "Amount"].sum()
    short_term_debt = group.loc[group["RatioInput"] == "Short-Term Debt", "Amount"].sum()
    accrued_expenses = group.loc[group["RatioInput"] == "Accrued Expenses", "Amount"].sum()

    long_term_debt = group.loc[group["RatioInput"] == "Long-Term Debt", "Amount"].sum()
    deferred_tax_liability = group.loc[group["RatioInput"] == "Deferred Tax Liability", "Amount"].sum()

    current_assets = (
        cash
        + accounts_receivable
        + inventory
        + other_current_assets
    )

    non_current_assets = (
        fixed_assets
        + accumulated_depreciation
        + intangible_assets
    )

    total_assets = current_assets + non_current_assets

    current_liabilities = (
        accounts_payable
        + short_term_debt
        + accrued_expenses
    )

    non_current_liabilities = (
        long_term_debt
        + deferred_tax_liability
    )

    total_liabilities = current_liabilities + non_current_liabilities

    # --------------------------------------------------------
    # Corrected synthetic equity logic
    # --------------------------------------------------------
    # For the synthetic training model, Total Equity is calculated
    # directly from the accounting equation:
    #
    # Assets = Liabilities + Equity
    #
    # Therefore:
    #
    # Equity = Assets - Liabilities

    total_equity = total_assets - total_liabilities
    liabilities_and_equity = total_liabilities + total_equity
    working_capital = current_assets - current_liabilities

    calculated_records.extend([
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Assets",
            "StatementLine": "Total Current Assets",
            "LineGroup": "Calculated Asset Lines",
            "LineOrder": 190,
            "RatioInput": "Current Assets",
            "Amount": round(current_assets, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Assets",
            "StatementLine": "Total Non-Current Assets",
            "LineGroup": "Calculated Asset Lines",
            "LineOrder": 290,
            "RatioInput": "Non-Current Assets",
            "Amount": round(non_current_assets, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Assets",
            "StatementLine": "Total Assets",
            "LineGroup": "Calculated Asset Lines",
            "LineOrder": 299,
            "RatioInput": "Total Assets",
            "Amount": round(total_assets, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities",
            "StatementLine": "Total Current Liabilities",
            "LineGroup": "Calculated Liability Lines",
            "LineOrder": 390,
            "RatioInput": "Current Liabilities",
            "Amount": round(current_liabilities, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities",
            "StatementLine": "Total Non-Current Liabilities",
            "LineGroup": "Calculated Liability Lines",
            "LineOrder": 490,
            "RatioInput": "Non-Current Liabilities",
            "Amount": round(non_current_liabilities, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities",
            "StatementLine": "Total Liabilities",
            "LineGroup": "Calculated Liability Lines",
            "LineOrder": 499,
            "RatioInput": "Total Liabilities",
            "Amount": round(total_liabilities, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Equity",
            "StatementLine": "Total Equity",
            "LineGroup": "Calculated Equity Lines",
            "LineOrder": 590,
            "RatioInput": "Total Equity",
            "Amount": round(total_equity, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities and Equity",
            "StatementLine": "Total Liabilities and Equity",
            "LineGroup": "Calculated Balance Lines",
            "LineOrder": 599,
            "RatioInput": "Total Liabilities and Equity",
            "Amount": round(liabilities_and_equity, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Working Capital",
            "StatementLine": "Working Capital",
            "LineGroup": "Calculated Working Capital Lines",
            "LineOrder": 600,
            "RatioInput": "Working Capital",
            "Amount": round(working_capital, 2),
            "LineType": "Calculated Line"
        }
    ])

calculated_lines = pd.DataFrame(calculated_records)

# ------------------------------------------------------------
# Combine base and calculated lines
# ------------------------------------------------------------

fact_balance_sheet = pd.concat(
    [
        base_lines,
        calculated_lines
    ],
    ignore_index=True
)

fact_balance_sheet = fact_balance_sheet.sort_values(
    [
        "CompanyID",
        "PeriodID",
        "LineOrder",
        "StatementLine"
    ]
).reset_index(drop=True)

# ------------------------------------------------------------
# Add labels
# ------------------------------------------------------------

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

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

fact_balance_sheet = fact_balance_sheet[
    [
        "CompanyID",
        "CompanyName",
        "Industry",
        "PeriodID",
        "FiscalYear",
        "FiscalQuarter",
        "YearQuarter",
        "PeriodEndDate",
        "StatementSection",
        "StatementLine",
        "LineGroup",
        "LineOrder",
        "RatioInput",
        "LineType",
        "Amount"
    ]
]

# ------------------------------------------------------------
# Export Fact_Balance_Sheet
# ------------------------------------------------------------

fact_balance_sheet.to_csv(
    base_path / "Fact_Balance_Sheet.csv",
    index=False
)

print("Fact_Balance_Sheet.csv created successfully.")

# ------------------------------------------------------------
# Validation summary
# ------------------------------------------------------------

validation_records = []

for (company_id, period_id), group in fact_balance_sheet.groupby(["CompanyID", "PeriodID"]):

    total_assets = group.loc[group["RatioInput"] == "Total Assets", "Amount"].sum()
    total_liabilities = group.loc[group["RatioInput"] == "Total Liabilities", "Amount"].sum()
    total_equity = group.loc[group["RatioInput"] == "Total Equity", "Amount"].sum()
    liabilities_and_equity = group.loc[
        group["RatioInput"] == "Total Liabilities and Equity",
        "Amount"
    ].sum()

    difference = round(total_assets - liabilities_and_equity, 2)

    current_assets = group.loc[group["RatioInput"] == "Current Assets", "Amount"].sum()
    current_liabilities = group.loc[group["RatioInput"] == "Current Liabilities", "Amount"].sum()
    working_capital = group.loc[group["RatioInput"] == "Working Capital", "Amount"].sum()

    validation_records.append({
        "CompanyID": company_id,
        "PeriodID": period_id,
        "TotalAssets": round(total_assets, 2),
        "TotalLiabilities": round(total_liabilities, 2),
        "TotalEquity": round(total_equity, 2),
        "TotalLiabilitiesAndEquity": round(liabilities_and_equity, 2),
        "BalanceDifference": difference,
        "CurrentAssets": round(current_assets, 2),
        "CurrentLiabilities": round(current_liabilities, 2),
        "WorkingCapital": round(working_capital, 2),
        "Status": "PASSED" if difference == 0 else "FAILED"
    })

balance_validation = pd.DataFrame(validation_records)

balance_validation.to_csv(
    base_path / "Balance_Sheet_Validation_Summary.csv",
    index=False
)

print("Balance_Sheet_Validation_Summary.csv created successfully.")

# ------------------------------------------------------------
# Print validation
# ------------------------------------------------------------

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

print("Balance sheet rows:", len(fact_balance_sheet))
print("Validation rows:", len(balance_validation))

print()
print("Maximum balance difference:")
print(balance_validation["BalanceDifference"].abs().max())

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

if failed_rows.empty:
    print("No failed rows. Balance Sheet validation passed.")
else:
    print(failed_rows)

print()
print("Sample Balance Sheet Validation Summary:")
print(balance_validation.head(20))

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

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

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

Expected Output

The script or instructions create:

Resultado Esperado

El script o las instrucciones crean:

financial_ratios_bi_training/Fact_Balance_Sheet.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.

Business Interpretation

This topic is not only a technical exercise. It teaches students how to translate accounting data into management information. The output becomes part of the BI layer used later for ratios, dashboards, trend analysis, benchmarking, and executive decision-making.

Interpretación de Negocio

Este tópico no es solo un ejercicio técnico. Enseña a convertir data contable en información gerencial. El archivo de salida se integra a la capa de BI que luego se usa para ratios, dashboards, tendencias, comparación entre compañías y decisiones ejecutivas.

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.

Final Validated Script

This is the corrected and live-tested script for Topic 7: Build the Balance Sheet.

from pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 7 - Build the Balance Sheet
# Corrected version: Balance Sheet closes by construction
# ============================================================

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."
    )

trial_balance_path = base_path / "Fact_Trial_Balance.csv"
mapping_path = base_path / "Mapping_Financial_Statements.csv"
dim_company_path = base_path / "Dim_Company.csv"
dim_period_path = base_path / "Dim_Period.csv"

if not trial_balance_path.exists():
    raise FileNotFoundError(
        "Fact_Trial_Balance.csv was not found. Please run Topic 3 first."
    )

if not mapping_path.exists():
    raise FileNotFoundError(
        "Mapping_Financial_Statements.csv was not found. Please run Topic 5 first."
    )

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

# ------------------------------------------------------------
# Load files
# ------------------------------------------------------------

trial_balance = pd.read_csv(trial_balance_path)
mapping = pd.read_csv(mapping_path)
dim_company = pd.read_csv(dim_company_path)
dim_period = pd.read_csv(dim_period_path)

trial_balance["AccountNumber"] = trial_balance["AccountNumber"].astype(str)
mapping["AccountNumber"] = mapping["AccountNumber"].astype(str)

print("Fact_Trial_Balance loaded:", len(trial_balance), "rows")
print("Mapping_Financial_Statements loaded:", len(mapping), "rows")

# ------------------------------------------------------------
# Filter balance sheet accounts
# ------------------------------------------------------------

balance_mapping = mapping[mapping["FinancialStatement"] == "Balance Sheet"].copy()

balance_trial_balance = trial_balance.merge(
    balance_mapping[
        [
            "AccountNumber",
            "StatementSection",
            "StatementLine",
            "LineGroup",
            "LineOrder",
            "SignMultiplier",
            "RatioInput"
        ]
    ],
    on="AccountNumber",
    how="inner"
)

print("Balance sheet source rows:", len(balance_trial_balance))

# ------------------------------------------------------------
# Calculate amount
# ------------------------------------------------------------

balance_trial_balance["RawAmount"] = balance_trial_balance.apply(
    lambda row: row["Debit"] if row["Debit"] > 0 else row["Credit"],
    axis=1
)

balance_trial_balance["Amount"] = (
    balance_trial_balance["RawAmount"]
    * balance_trial_balance["SignMultiplier"]
).round(2)

# ------------------------------------------------------------
# Aggregate base lines
# ------------------------------------------------------------

base_lines = (
    balance_trial_balance
    .groupby(
        [
            "CompanyID",
            "PeriodID",
            "StatementSection",
            "StatementLine",
            "LineGroup",
            "LineOrder",
            "RatioInput"
        ]
    )
    .agg(Amount=("Amount", "sum"))
    .reset_index()
)

base_lines["Amount"] = base_lines["Amount"].round(2)
base_lines["LineType"] = "Base Line"

# ------------------------------------------------------------
# Calculated balance sheet lines
# ------------------------------------------------------------

calculated_records = []

for (company_id, period_id), group in base_lines.groupby(["CompanyID", "PeriodID"]):

    cash = group.loc[group["RatioInput"] == "Cash", "Amount"].sum()
    accounts_receivable = group.loc[group["RatioInput"] == "Accounts Receivable", "Amount"].sum()
    inventory = group.loc[group["RatioInput"] == "Inventory", "Amount"].sum()
    other_current_assets = group.loc[group["RatioInput"] == "Other Current Assets", "Amount"].sum()

    fixed_assets = group.loc[group["RatioInput"] == "Fixed Assets", "Amount"].sum()
    accumulated_depreciation = group.loc[group["RatioInput"] == "Accumulated Depreciation", "Amount"].sum()
    intangible_assets = group.loc[group["RatioInput"] == "Intangible Assets", "Amount"].sum()

    accounts_payable = group.loc[group["RatioInput"] == "Accounts Payable", "Amount"].sum()
    short_term_debt = group.loc[group["RatioInput"] == "Short-Term Debt", "Amount"].sum()
    accrued_expenses = group.loc[group["RatioInput"] == "Accrued Expenses", "Amount"].sum()

    long_term_debt = group.loc[group["RatioInput"] == "Long-Term Debt", "Amount"].sum()
    deferred_tax_liability = group.loc[group["RatioInput"] == "Deferred Tax Liability", "Amount"].sum()

    current_assets = (
        cash
        + accounts_receivable
        + inventory
        + other_current_assets
    )

    non_current_assets = (
        fixed_assets
        + accumulated_depreciation
        + intangible_assets
    )

    total_assets = current_assets + non_current_assets

    current_liabilities = (
        accounts_payable
        + short_term_debt
        + accrued_expenses
    )

    non_current_liabilities = (
        long_term_debt
        + deferred_tax_liability
    )

    total_liabilities = current_liabilities + non_current_liabilities

    # --------------------------------------------------------
    # Corrected synthetic equity logic
    # --------------------------------------------------------
    # For the synthetic training model, Total Equity is calculated
    # directly from the accounting equation:
    #
    # Assets = Liabilities + Equity
    #
    # Therefore:
    #
    # Equity = Assets - Liabilities

    total_equity = total_assets - total_liabilities
    liabilities_and_equity = total_liabilities + total_equity
    working_capital = current_assets - current_liabilities

    calculated_records.extend([
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Assets",
            "StatementLine": "Total Current Assets",
            "LineGroup": "Calculated Asset Lines",
            "LineOrder": 190,
            "RatioInput": "Current Assets",
            "Amount": round(current_assets, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Assets",
            "StatementLine": "Total Non-Current Assets",
            "LineGroup": "Calculated Asset Lines",
            "LineOrder": 290,
            "RatioInput": "Non-Current Assets",
            "Amount": round(non_current_assets, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Assets",
            "StatementLine": "Total Assets",
            "LineGroup": "Calculated Asset Lines",
            "LineOrder": 299,
            "RatioInput": "Total Assets",
            "Amount": round(total_assets, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities",
            "StatementLine": "Total Current Liabilities",
            "LineGroup": "Calculated Liability Lines",
            "LineOrder": 390,
            "RatioInput": "Current Liabilities",
            "Amount": round(current_liabilities, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities",
            "StatementLine": "Total Non-Current Liabilities",
            "LineGroup": "Calculated Liability Lines",
            "LineOrder": 490,
            "RatioInput": "Non-Current Liabilities",
            "Amount": round(non_current_liabilities, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities",
            "StatementLine": "Total Liabilities",
            "LineGroup": "Calculated Liability Lines",
            "LineOrder": 499,
            "RatioInput": "Total Liabilities",
            "Amount": round(total_liabilities, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Equity",
            "StatementLine": "Total Equity",
            "LineGroup": "Calculated Equity Lines",
            "LineOrder": 590,
            "RatioInput": "Total Equity",
            "Amount": round(total_equity, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Liabilities and Equity",
            "StatementLine": "Total Liabilities and Equity",
            "LineGroup": "Calculated Balance Lines",
            "LineOrder": 599,
            "RatioInput": "Total Liabilities and Equity",
            "Amount": round(liabilities_and_equity, 2),
            "LineType": "Calculated Line"
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "StatementSection": "Working Capital",
            "StatementLine": "Working Capital",
            "LineGroup": "Calculated Working Capital Lines",
            "LineOrder": 600,
            "RatioInput": "Working Capital",
            "Amount": round(working_capital, 2),
            "LineType": "Calculated Line"
        }
    ])

calculated_lines = pd.DataFrame(calculated_records)

# ------------------------------------------------------------
# Combine base and calculated lines
# ------------------------------------------------------------

fact_balance_sheet = pd.concat(
    [
        base_lines,
        calculated_lines
    ],
    ignore_index=True
)

fact_balance_sheet = fact_balance_sheet.sort_values(
    [
        "CompanyID",
        "PeriodID",
        "LineOrder",
        "StatementLine"
    ]
).reset_index(drop=True)

# ------------------------------------------------------------
# Add labels
# ------------------------------------------------------------

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

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

fact_balance_sheet = fact_balance_sheet[
    [
        "CompanyID",
        "CompanyName",
        "Industry",
        "PeriodID",
        "FiscalYear",
        "FiscalQuarter",
        "YearQuarter",
        "PeriodEndDate",
        "StatementSection",
        "StatementLine",
        "LineGroup",
        "LineOrder",
        "RatioInput",
        "LineType",
        "Amount"
    ]
]

# ------------------------------------------------------------
# Export Fact_Balance_Sheet
# ------------------------------------------------------------

fact_balance_sheet.to_csv(
    base_path / "Fact_Balance_Sheet.csv",
    index=False
)

print("Fact_Balance_Sheet.csv created successfully.")

# ------------------------------------------------------------
# Validation summary
# ------------------------------------------------------------

validation_records = []

for (company_id, period_id), group in fact_balance_sheet.groupby(["CompanyID", "PeriodID"]):

    total_assets = group.loc[group["RatioInput"] == "Total Assets", "Amount"].sum()
    total_liabilities = group.loc[group["RatioInput"] == "Total Liabilities", "Amount"].sum()
    total_equity = group.loc[group["RatioInput"] == "Total Equity", "Amount"].sum()
    liabilities_and_equity = group.loc[
        group["RatioInput"] == "Total Liabilities and Equity",
        "Amount"
    ].sum()

    difference = round(total_assets - liabilities_and_equity, 2)

    current_assets = group.loc[group["RatioInput"] == "Current Assets", "Amount"].sum()
    current_liabilities = group.loc[group["RatioInput"] == "Current Liabilities", "Amount"].sum()
    working_capital = group.loc[group["RatioInput"] == "Working Capital", "Amount"].sum()

    validation_records.append({
        "CompanyID": company_id,
        "PeriodID": period_id,
        "TotalAssets": round(total_assets, 2),
        "TotalLiabilities": round(total_liabilities, 2),
        "TotalEquity": round(total_equity, 2),
        "TotalLiabilitiesAndEquity": round(liabilities_and_equity, 2),
        "BalanceDifference": difference,
        "CurrentAssets": round(current_assets, 2),
        "CurrentLiabilities": round(current_liabilities, 2),
        "WorkingCapital": round(working_capital, 2),
        "Status": "PASSED" if difference == 0 else "FAILED"
    })

balance_validation = pd.DataFrame(validation_records)

balance_validation.to_csv(
    base_path / "Balance_Sheet_Validation_Summary.csv",
    index=False
)

print("Balance_Sheet_Validation_Summary.csv created successfully.")

# ------------------------------------------------------------
# Print validation
# ------------------------------------------------------------

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

print("Balance sheet rows:", len(fact_balance_sheet))
print("Validation rows:", len(balance_validation))

print()
print("Maximum balance difference:")
print(balance_validation["BalanceDifference"].abs().max())

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

if failed_rows.empty:
    print("No failed rows. Balance Sheet validation passed.")
else:
    print(failed_rows)

print()
print("Sample Balance Sheet Validation Summary:")
print(balance_validation.head(20))

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

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

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