Financial Ratios Analysis in BI

Learning by Doing Series — Topic 11: Profitability Ratios

Objective

Calculate profit margin, gross margin, effective tax rate, ROA, ROE, and ROCE.

The main output of this topic is Fact_Profitability_Ratios.csv.

Objetivo

Calculate profit margin, gross margin, effective tax rate, ROA, ROE, and ROCE.

El archivo principal de salida de este tópico es Fact_Profitability_Ratios.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 Profitability.

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 Profitability.

Input: Dim_Company.csv

Input: Dim_Period.csv

Output: Fact_Profitability_Ratios.csv

Focus: Profitability

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

income_statement_path = base_path / "Fact_Income_Statement.csv"
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 = [
    income_statement_path,
    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
# ------------------------------------------------------------

income_statement = pd.read_csv(income_statement_path)
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_Income_Statement loaded:", len(income_statement), "rows")
print("Fact_Balance_Sheet loaded:", len(balance_sheet), "rows")

# ------------------------------------------------------------
# Step 3 - Extract profitability inputs
# ------------------------------------------------------------

income_inputs = income_statement[
    income_statement["RatioInput"].isin(
        [
            "Revenue",
            "COGS",
            "Gross Profit",
            "Operating Income",
            "Pretax Income",
            "Tax Expense",
            "Net Income"
        ]
    )
].copy()

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

balance_inputs = balance_sheet[
    balance_sheet["RatioInput"].isin(
        [
            "Total Assets",
            "Total Equity",
            "Total Liabilities",
            "Current Liabilities"
        ]
    )
].copy()

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

profitability_base = income_pivot.merge(
    balance_pivot,
    on=["CompanyID", "PeriodID"],
    how="left"
)

required_columns = [
    "Revenue",
    "COGS",
    "Gross Profit",
    "Operating Income",
    "Pretax Income",
    "Tax Expense",
    "Net Income",
    "Total Assets",
    "Total Equity",
    "Total Liabilities",
    "Current Liabilities"
]

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

profitability_base = profitability_base.fillna(0)

# ------------------------------------------------------------
# Step 4 - Calculate profitability ratios
# ------------------------------------------------------------

records = []

for _, row in profitability_base.iterrows():

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

    revenue = float(row["Revenue"])
    cogs = float(row["COGS"])
    gross_profit = float(row["Gross Profit"])
    operating_income = float(row["Operating Income"])
    pretax_income = float(row["Pretax Income"])
    tax_expense = float(row["Tax Expense"])
    net_income = float(row["Net Income"])

    total_assets = float(row["Total Assets"])
    total_equity = float(row["Total Equity"])
    total_liabilities = float(row["Total Liabilities"])
    current_liabilities = float(row["Current Liabilities"])

    capital_employed = total_assets - current_liabilities

    gross_margin = gross_profit / revenue if revenue != 0 else 0
    operating_margin = operating_income / revenue if revenue != 0 else 0
    net_profit_margin = net_income / revenue if revenue != 0 else 0

    effective_tax_rate = tax_expense / pretax_income if pretax_income != 0 else 0

    return_on_assets = net_income / total_assets if total_assets != 0 else 0
    return_on_equity = net_income / total_equity if total_equity != 0 else 0
    return_on_capital_employed = (
        operating_income / capital_employed
        if capital_employed != 0
        else 0
    )

    records.extend([
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Revenue",
            "RatioValue": round(revenue, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Total sales or service income generated by the company."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Gross Profit",
            "RatioValue": round(gross_profit, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Revenue minus cost of goods sold."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Operating Income",
            "RatioValue": round(operating_income, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Profit generated from core operations before non-operating expenses and taxes."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Net Income",
            "RatioValue": round(net_income, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Final profit after expenses, interest, and taxes."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Gross Margin",
            "RatioValue": round(gross_margin, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures how much revenue remains after direct costs."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Operating Margin",
            "RatioValue": round(operating_margin, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures operating efficiency before interest and taxes."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Net Profit Margin",
            "RatioValue": round(net_profit_margin, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures final profit generated from each dollar of revenue."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Effective Tax Rate",
            "RatioValue": round(effective_tax_rate, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures tax expense as a percentage of pretax income."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Return on Assets",
            "RatioValue": round(return_on_assets, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures how efficiently assets generate net income."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Return on Equity",
            "RatioValue": round(return_on_equity, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures how efficiently shareholder equity generates net income."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Return on Capital Employed",
            "RatioValue": round(return_on_capital_employed, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures operating return generated from capital employed."
        }
    ])

fact_profitability_ratios = pd.DataFrame(records)

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

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

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

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

# ------------------------------------------------------------
# Step 6 - Export Fact_Profitability_Ratios
# ------------------------------------------------------------

fact_profitability_ratios.to_csv(
    base_path / "Fact_Profitability_Ratios.csv",
    index=False
)

print("Fact_Profitability_Ratios.csv created successfully.")

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

validation_records = []

for _, row in profitability_base.iterrows():

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

    revenue = float(row["Revenue"])
    cogs = float(row["COGS"])
    gross_profit = float(row["Gross Profit"])
    operating_income = float(row["Operating Income"])
    pretax_income = float(row["Pretax Income"])
    tax_expense = float(row["Tax Expense"])
    net_income = float(row["Net Income"])

    total_assets = float(row["Total Assets"])
    total_equity = float(row["Total Equity"])
    current_liabilities = float(row["Current Liabilities"])
    capital_employed = total_assets - current_liabilities

    expected_gross_profit = revenue - cogs
    gross_profit_difference = round(gross_profit - expected_gross_profit, 2)

    expected_net_margin = net_income / revenue if revenue != 0 else 0
    expected_roa = net_income / total_assets if total_assets != 0 else 0
    expected_roe = net_income / total_equity if total_equity != 0 else 0
    expected_roce = (
        operating_income / capital_employed
        if capital_employed != 0
        else 0
    )

    validation_records.append({
        "CompanyID": company_id,
        "PeriodID": period_id,
        "Revenue": round(revenue, 2),
        "COGS": round(cogs, 2),
        "GrossProfit": round(gross_profit, 2),
        "ExpectedGrossProfit": round(expected_gross_profit, 2),
        "GrossProfitDifference": gross_profit_difference,
        "NetIncome": round(net_income, 2),
        "TotalAssets": round(total_assets, 2),
        "TotalEquity": round(total_equity, 2),
        "CapitalEmployed": round(capital_employed, 2),
        "ExpectedNetMargin": round(expected_net_margin, 4),
        "ExpectedROA": round(expected_roa, 4),
        "ExpectedROE": round(expected_roe, 4),
        "ExpectedROCE": round(expected_roce, 4),
        "Status": (
            "PASSED"
            if gross_profit_difference == 0
            else "FAILED"
        )
    })

profitability_validation = pd.DataFrame(validation_records)

profitability_validation.to_csv(
    base_path / "Profitability_Ratios_Validation_Summary.csv",
    index=False
)

print("Profitability_Ratios_Validation_Summary.csv created successfully.")

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

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

print("Profitability ratio rows:", len(fact_profitability_ratios))
print("Validation rows:", len(profitability_validation))

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

print()
print("Profitability Summary by Company:")
print(
    fact_profitability_ratios[
        fact_profitability_ratios["RatioName"].isin(
            [
                "Gross Margin",
                "Operating Margin",
                "Net Profit Margin",
                "Return on Assets",
                "Return on Equity",
                "Return on Capital Employed"
            ]
        )
    ]
    .groupby(["CompanyID", "CompanyName", "RatioName"])["RatioValue"]
    .mean()
    .round(4)
    .reset_index(name="AverageRatio")
)

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

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

print()
print("Preview of Fact_Profitability_Ratios:")
print(fact_profitability_ratios.head(20))

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

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

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

Expected Output

The script or instructions create:

Resultado Esperado

El script o las instrucciones crean:

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

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 11: Profitability Ratios.

from pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 11 - Profitability 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."
    )

income_statement_path = base_path / "Fact_Income_Statement.csv"
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 = [
    income_statement_path,
    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
# ------------------------------------------------------------

income_statement = pd.read_csv(income_statement_path)
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_Income_Statement loaded:", len(income_statement), "rows")
print("Fact_Balance_Sheet loaded:", len(balance_sheet), "rows")

# ------------------------------------------------------------
# Step 3 - Extract profitability inputs
# ------------------------------------------------------------

income_inputs = income_statement[
    income_statement["RatioInput"].isin(
        [
            "Revenue",
            "COGS",
            "Gross Profit",
            "Operating Income",
            "Pretax Income",
            "Tax Expense",
            "Net Income"
        ]
    )
].copy()

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

balance_inputs = balance_sheet[
    balance_sheet["RatioInput"].isin(
        [
            "Total Assets",
            "Total Equity",
            "Total Liabilities",
            "Current Liabilities"
        ]
    )
].copy()

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

profitability_base = income_pivot.merge(
    balance_pivot,
    on=["CompanyID", "PeriodID"],
    how="left"
)

required_columns = [
    "Revenue",
    "COGS",
    "Gross Profit",
    "Operating Income",
    "Pretax Income",
    "Tax Expense",
    "Net Income",
    "Total Assets",
    "Total Equity",
    "Total Liabilities",
    "Current Liabilities"
]

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

profitability_base = profitability_base.fillna(0)

# ------------------------------------------------------------
# Step 4 - Calculate profitability ratios
# ------------------------------------------------------------

records = []

for _, row in profitability_base.iterrows():

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

    revenue = float(row["Revenue"])
    cogs = float(row["COGS"])
    gross_profit = float(row["Gross Profit"])
    operating_income = float(row["Operating Income"])
    pretax_income = float(row["Pretax Income"])
    tax_expense = float(row["Tax Expense"])
    net_income = float(row["Net Income"])

    total_assets = float(row["Total Assets"])
    total_equity = float(row["Total Equity"])
    total_liabilities = float(row["Total Liabilities"])
    current_liabilities = float(row["Current Liabilities"])

    capital_employed = total_assets - current_liabilities

    gross_margin = gross_profit / revenue if revenue != 0 else 0
    operating_margin = operating_income / revenue if revenue != 0 else 0
    net_profit_margin = net_income / revenue if revenue != 0 else 0

    effective_tax_rate = tax_expense / pretax_income if pretax_income != 0 else 0

    return_on_assets = net_income / total_assets if total_assets != 0 else 0
    return_on_equity = net_income / total_equity if total_equity != 0 else 0
    return_on_capital_employed = (
        operating_income / capital_employed
        if capital_employed != 0
        else 0
    )

    records.extend([
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Revenue",
            "RatioValue": round(revenue, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Total sales or service income generated by the company."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Gross Profit",
            "RatioValue": round(gross_profit, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Revenue minus cost of goods sold."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Operating Income",
            "RatioValue": round(operating_income, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Profit generated from core operations before non-operating expenses and taxes."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Net Income",
            "RatioValue": round(net_income, 2),
            "RatioFormat": "Currency",
            "Interpretation": "Final profit after expenses, interest, and taxes."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Gross Margin",
            "RatioValue": round(gross_margin, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures how much revenue remains after direct costs."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Operating Margin",
            "RatioValue": round(operating_margin, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures operating efficiency before interest and taxes."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Net Profit Margin",
            "RatioValue": round(net_profit_margin, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures final profit generated from each dollar of revenue."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Effective Tax Rate",
            "RatioValue": round(effective_tax_rate, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures tax expense as a percentage of pretax income."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Return on Assets",
            "RatioValue": round(return_on_assets, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures how efficiently assets generate net income."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Return on Equity",
            "RatioValue": round(return_on_equity, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures how efficiently shareholder equity generates net income."
        },
        {
            "CompanyID": company_id,
            "PeriodID": period_id,
            "RatioCategory": "Profitability",
            "RatioName": "Return on Capital Employed",
            "RatioValue": round(return_on_capital_employed, 4),
            "RatioFormat": "Percent",
            "Interpretation": "Measures operating return generated from capital employed."
        }
    ])

fact_profitability_ratios = pd.DataFrame(records)

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

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

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

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

# ------------------------------------------------------------
# Step 6 - Export Fact_Profitability_Ratios
# ------------------------------------------------------------

fact_profitability_ratios.to_csv(
    base_path / "Fact_Profitability_Ratios.csv",
    index=False
)

print("Fact_Profitability_Ratios.csv created successfully.")

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

validation_records = []

for _, row in profitability_base.iterrows():

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

    revenue = float(row["Revenue"])
    cogs = float(row["COGS"])
    gross_profit = float(row["Gross Profit"])
    operating_income = float(row["Operating Income"])
    pretax_income = float(row["Pretax Income"])
    tax_expense = float(row["Tax Expense"])
    net_income = float(row["Net Income"])

    total_assets = float(row["Total Assets"])
    total_equity = float(row["Total Equity"])
    current_liabilities = float(row["Current Liabilities"])
    capital_employed = total_assets - current_liabilities

    expected_gross_profit = revenue - cogs
    gross_profit_difference = round(gross_profit - expected_gross_profit, 2)

    expected_net_margin = net_income / revenue if revenue != 0 else 0
    expected_roa = net_income / total_assets if total_assets != 0 else 0
    expected_roe = net_income / total_equity if total_equity != 0 else 0
    expected_roce = (
        operating_income / capital_employed
        if capital_employed != 0
        else 0
    )

    validation_records.append({
        "CompanyID": company_id,
        "PeriodID": period_id,
        "Revenue": round(revenue, 2),
        "COGS": round(cogs, 2),
        "GrossProfit": round(gross_profit, 2),
        "ExpectedGrossProfit": round(expected_gross_profit, 2),
        "GrossProfitDifference": gross_profit_difference,
        "NetIncome": round(net_income, 2),
        "TotalAssets": round(total_assets, 2),
        "TotalEquity": round(total_equity, 2),
        "CapitalEmployed": round(capital_employed, 2),
        "ExpectedNetMargin": round(expected_net_margin, 4),
        "ExpectedROA": round(expected_roa, 4),
        "ExpectedROE": round(expected_roe, 4),
        "ExpectedROCE": round(expected_roce, 4),
        "Status": (
            "PASSED"
            if gross_profit_difference == 0
            else "FAILED"
        )
    })

profitability_validation = pd.DataFrame(validation_records)

profitability_validation.to_csv(
    base_path / "Profitability_Ratios_Validation_Summary.csv",
    index=False
)

print("Profitability_Ratios_Validation_Summary.csv created successfully.")

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

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

print("Profitability ratio rows:", len(fact_profitability_ratios))
print("Validation rows:", len(profitability_validation))

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

print()
print("Profitability Summary by Company:")
print(
    fact_profitability_ratios[
        fact_profitability_ratios["RatioName"].isin(
            [
                "Gross Margin",
                "Operating Margin",
                "Net Profit Margin",
                "Return on Assets",
                "Return on Equity",
                "Return on Capital Employed"
            ]
        )
    ]
    .groupby(["CompanyID", "CompanyName", "RatioName"])["RatioValue"]
    .mean()
    .round(4)
    .reset_index(name="AverageRatio")
)

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

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

print()
print("Preview of Fact_Profitability_Ratios:")
print(fact_profitability_ratios.head(20))

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

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

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