Topic 3 - Generate Synthetic Trial Balance

Topic 3 — Generate Synthetic Trial Balance

This topic creates Fact_Trial_Balance.csv, the first financial fact table of the training. It uses the companies, periods, and chart of accounts created in Topics 1 and 2.

Dim_Company→Dim_Period→Dim_Account→Fact_Trial_Balance

Production / Cybersecurity Warning

This exercise uses synthetic data and must be executed only in a practice environment. Do not run scripts in production, real folders, corporate databases, or accounting systems without formal authorization, backups, testing, least-privilege permissions, change-control approval, and cybersecurity compliance.

Business Scenario

We need a realistic synthetic trial balance for five companies across twenty fiscal quarters. Each industry behaves differently, so ratios later will have business meaning instead of random numbers.

Step-by-Step Practice

Step 1 — Generate the Trial Balance

Topic 3 Full Python Script

from pathlib import Path
import pandas as pd

# ============================================================
# Test - Validate Trial Balance Difference
# ============================================================

base_path = Path("financial_ratios_bi_training")
file_path = base_path / "Fact_Trial_Balance.csv"

if not file_path.exists():
    raise FileNotFoundError(
        "Fact_Trial_Balance.csv was not found. Run Topic 3 first."
    )

df = pd.read_csv(file_path)

# ------------------------------------------------------------
# Overall balance check
# ------------------------------------------------------------

total_debit = round(df["Debit"].sum(), 2)
total_credit = round(df["Credit"].sum(), 2)
overall_difference = round(total_debit - total_credit, 2)

print("==============================")
print("OVERALL TRIAL BALANCE CHECK")
print("==============================")
print("Total Debit :", total_debit)
print("Total Credit:", total_credit)
print("Difference  :", overall_difference)

if overall_difference == 0:
    print("Overall Status: PASSED")
else:
    print("Overall Status: FAILED")

# ------------------------------------------------------------
# Balance check by CompanyID and PeriodID
# ------------------------------------------------------------

balance_check = (
    df
    .groupby(["CompanyID", "PeriodID"])
    .agg(
        TotalDebit=("Debit", "sum"),
        TotalCredit=("Credit", "sum")
    )
    .reset_index()
)

balance_check["TotalDebit"] = balance_check["TotalDebit"].round(2)
balance_check["TotalCredit"] = balance_check["TotalCredit"].round(2)
balance_check["Difference"] = (
    balance_check["TotalDebit"] - balance_check["TotalCredit"]
).round(2)

max_difference = balance_check["Difference"].abs().max()

print()
print("==============================")
print("COMPANY / PERIOD BALANCE CHECK")
print("==============================")
print("Maximum absolute difference:", max_difference)

failed_rows = balance_check[balance_check["Difference"] != 0]

if failed_rows.empty:
    print("Company / Period Status: PASSED")
else:
    print("Company / Period Status: FAILED")
    print()
    print("Failed rows:")
    print(failed_rows)

# ------------------------------------------------------------
# Final result
# ------------------------------------------------------------

print()
print("==============================")
print("FINAL RESULT")
print("==============================")

if overall_difference == 0 and max_difference == 0:
    print("Trial Balance is perfectly balanced.")
else:
    print("Trial Balance has differences. Review Topic 3 balancing logic.")

Step 2 — Validate Output

Validation

validation = trial_balance.groupby(["CompanyID","PeriodID"]).agg(TotalDebits=("Debit","sum"), TotalCredits=("Credit","sum")).reset_index()
validation["Difference"] = validation["TotalDebits"] - validation["TotalCredits"]
validation.head()

print("Total rows:", len(trial_balance))
print("Companies:", trial_balance["CompanyID"].nunique())
print("Periods:", trial_balance["PeriodID"].nunique())
print("Accounts:", trial_balance["AccountNumber"].nunique())

Business Interpretation

The synthetic trial balance is now the center of the financial BI model. From this table we can build the income statement, balance sheet, cash flow indicators, and later the financial ratios.

Expected output: Fact_Trial_Balance.csv.

Topic 3 — Generar Trial Balance Sintético

Este tópico crea Fact_Trial_Balance.csv, la primera tabla de hechos financiera del training. Usa las compañías, períodos y catálogo contable creados en los Topics 1 y 2.

Dim_Company→Dim_Period→Dim_Account→Fact_Trial_Balance

Advertencia de Producción / Ciberseguridad

Este ejercicio usa data sintética y debe ejecutarse solamente en ambiente de práctica. No ejecutes scripts en producción, carpetas reales, bases corporativas o sistemas contables sin autorización, respaldos, pruebas, permisos mínimos, control de cambios y cumplimiento de ciberseguridad.

Escenario de Negocio

Necesitamos un trial balance sintético realista para cinco compañías durante veinte trimestres fiscales. Cada industria se comporta diferente para que los ratios futuros tengan sentido financiero.

Práctica Paso a Paso

Step 1 — Generar el Trial Balance

Script Completo Python Topic 3

from pathlib import Path
import pandas as pd

# ============================================================
# Test - Validate Trial Balance Difference
# ============================================================

base_path = Path("financial_ratios_bi_training")
file_path = base_path / "Fact_Trial_Balance.csv"

if not file_path.exists():
    raise FileNotFoundError(
        "Fact_Trial_Balance.csv was not found. Run Topic 3 first."
    )

df = pd.read_csv(file_path)

# ------------------------------------------------------------
# Overall balance check
# ------------------------------------------------------------

total_debit = round(df["Debit"].sum(), 2)
total_credit = round(df["Credit"].sum(), 2)
overall_difference = round(total_debit - total_credit, 2)

print("==============================")
print("OVERALL TRIAL BALANCE CHECK")
print("==============================")
print("Total Debit :", total_debit)
print("Total Credit:", total_credit)
print("Difference  :", overall_difference)

if overall_difference == 0:
    print("Overall Status: PASSED")
else:
    print("Overall Status: FAILED")

# ------------------------------------------------------------
# Balance check by CompanyID and PeriodID
# ------------------------------------------------------------

balance_check = (
    df
    .groupby(["CompanyID", "PeriodID"])
    .agg(
        TotalDebit=("Debit", "sum"),
        TotalCredit=("Credit", "sum")
    )
    .reset_index()
)

balance_check["TotalDebit"] = balance_check["TotalDebit"].round(2)
balance_check["TotalCredit"] = balance_check["TotalCredit"].round(2)
balance_check["Difference"] = (
    balance_check["TotalDebit"] - balance_check["TotalCredit"]
).round(2)

max_difference = balance_check["Difference"].abs().max()

print()
print("==============================")
print("COMPANY / PERIOD BALANCE CHECK")
print("==============================")
print("Maximum absolute difference:", max_difference)

failed_rows = balance_check[balance_check["Difference"] != 0]

if failed_rows.empty:
    print("Company / Period Status: PASSED")
else:
    print("Company / Period Status: FAILED")
    print()
    print("Failed rows:")
    print(failed_rows)

# ------------------------------------------------------------
# Final result
# ------------------------------------------------------------

print()
print("==============================")
print("FINAL RESULT")
print("==============================")

if overall_difference == 0 and max_difference == 0:
    print("Trial Balance is perfectly balanced.")
else:
    print("Trial Balance has differences. Review Topic 3 balancing logic.")

Step 2 — Validar Output

Validación

validation = trial_balance.groupby(["CompanyID","PeriodID"]).agg(TotalDebits=("Debit","sum"), TotalCredits=("Credit","sum")).reset_index()
validation["Difference"] = validation["TotalDebits"] - validation["TotalCredits"]
validation.head()

print("Total rows:", len(trial_balance))
print("Companies:", trial_balance["CompanyID"].nunique())
print("Periods:", trial_balance["PeriodID"].nunique())
print("Accounts:", trial_balance["AccountNumber"].nunique())

Interpretación de Negocio

El trial balance sintético ya es el centro del modelo financiero BI. Desde esta tabla construiremos income statement, balance sheet, indicadores de cash flow y después los ratios financieros.

Output esperado: Fact_Trial_Balance.csv.

Financial Ratios Analysis in BI