Learning by Doing Series — Topic 12: Debt Ratios
Calculate debt ratio, debt-to-equity, capitalization ratio, interest coverage, and cash flow to debt.
The main output of this topic is Fact_Debt_Ratios.csv.
Calculate debt ratio, debt-to-equity, capitalization ratio, interest coverage, and cash flow to debt.
El archivo principal de salida de este tópico es Fact_Debt_Ratios.csv.
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.
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.
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 Debt.
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 Debt.
from pathlib import Path
import pandas as pd
# ============================================================
# Financial Ratios Analysis in BI
# Topic 12 - Debt 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"
income_statement_path = base_path / "Fact_Income_Statement.csv"
cash_flow_path = base_path / "Fact_Cash_Flow.csv"
dim_company_path = base_path / "Dim_Company.csv"
dim_period_path = base_path / "Dim_Period.csv"
required_files = [
balance_sheet_path,
income_statement_path,
cash_flow_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)
income_statement = pd.read_csv(income_statement_path)
cash_flow = pd.read_csv(cash_flow_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")
print("Fact_Income_Statement loaded:", len(income_statement), "rows")
print("Fact_Cash_Flow loaded:", len(cash_flow), "rows")
# ------------------------------------------------------------
# Step 3 - Extract debt inputs from Balance Sheet
# ------------------------------------------------------------
balance_inputs = balance_sheet[
balance_sheet["RatioInput"].isin(
[
"Short-Term Debt",
"Long-Term Debt",
"Total Assets",
"Total Liabilities",
"Total Equity"
]
)
].copy()
balance_pivot = (
balance_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
required_balance_columns = [
"Short-Term Debt",
"Long-Term Debt",
"Total Assets",
"Total Liabilities",
"Total Equity"
]
for column in required_balance_columns:
if column not in balance_pivot.columns:
balance_pivot[column] = 0
# ------------------------------------------------------------
# Step 4 - Extract income statement inputs
# ------------------------------------------------------------
income_inputs = income_statement[
income_statement["RatioInput"].isin(
[
"Operating Income",
"Interest Expense",
"Net Income"
]
)
].copy()
income_pivot = (
income_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
required_income_columns = [
"Operating Income",
"Interest Expense",
"Net Income"
]
for column in required_income_columns:
if column not in income_pivot.columns:
income_pivot[column] = 0
# ------------------------------------------------------------
# Step 5 - Extract cash flow inputs
# ------------------------------------------------------------
cash_flow_inputs = cash_flow[
cash_flow["RatioInput"].isin(
[
"Operating Cash Flow",
"Cash Flow to Debt"
]
)
].copy()
cash_flow_pivot = (
cash_flow_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
required_cash_flow_columns = [
"Operating Cash Flow",
"Cash Flow to Debt"
]
for column in required_cash_flow_columns:
if column not in cash_flow_pivot.columns:
cash_flow_pivot[column] = 0
# ------------------------------------------------------------
# Step 6 - Create debt ratio base table
# ------------------------------------------------------------
debt_base = (
balance_pivot
.merge(income_pivot, on=["CompanyID", "PeriodID"], how="left")
.merge(cash_flow_pivot, on=["CompanyID", "PeriodID"], how="left")
)
debt_base = debt_base.fillna(0)
# ------------------------------------------------------------
# Step 7 - Calculate debt ratios
# ------------------------------------------------------------
records = []
for _, row in debt_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
short_term_debt = float(row["Short-Term Debt"])
long_term_debt = float(row["Long-Term Debt"])
total_debt = short_term_debt + long_term_debt
total_assets = float(row["Total Assets"])
total_liabilities = float(row["Total Liabilities"])
total_equity = float(row["Total Equity"])
operating_income = float(row["Operating Income"])
interest_expense = float(row["Interest Expense"])
net_income = float(row["Net Income"])
operating_cash_flow = float(row["Operating Cash Flow"])
debt_ratio = (
total_liabilities / total_assets
if total_assets != 0
else 0
)
debt_to_equity_ratio = (
total_debt / total_equity
if total_equity != 0
else 0
)
liabilities_to_equity_ratio = (
total_liabilities / total_equity
if total_equity != 0
else 0
)
capitalization_ratio = (
total_debt / (total_debt + total_equity)
if (total_debt + total_equity) != 0
else 0
)
interest_coverage_ratio = (
operating_income / interest_expense
if interest_expense != 0
else 0
)
cash_flow_to_debt_ratio = (
operating_cash_flow / total_debt
if total_debt != 0
else 0
)
records.extend([
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Short-Term Debt",
"RatioValue": round(short_term_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Debt obligations due within one year."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Long-Term Debt",
"RatioValue": round(long_term_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Debt obligations due after one year."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Total Debt",
"RatioValue": round(total_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Short-term debt plus long-term debt."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Total Liabilities",
"RatioValue": round(total_liabilities, 2),
"RatioFormat": "Currency",
"Interpretation": "All obligations owed by the company."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Debt Ratio",
"RatioValue": round(debt_ratio, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures the percentage of assets financed by liabilities."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Debt-to-Equity Ratio",
"RatioValue": round(debt_to_equity_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures debt financing relative to shareholder equity."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Liabilities to Equity Ratio",
"RatioValue": round(liabilities_to_equity_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures total liabilities relative to shareholder equity."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Capitalization Ratio",
"RatioValue": round(capitalization_ratio, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures debt as a percentage of total capital structure."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Interest Coverage Ratio",
"RatioValue": round(interest_coverage_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures ability to cover interest expense with operating income."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Cash Flow to Debt Ratio",
"RatioValue": round(cash_flow_to_debt_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures ability to repay debt using operating cash flow."
}
])
fact_debt_ratios = pd.DataFrame(records)
# ------------------------------------------------------------
# Step 8 - Add company and period labels
# ------------------------------------------------------------
fact_debt_ratios = fact_debt_ratios.merge(
dim_company[["CompanyID", "CompanyName", "Industry"]],
on="CompanyID",
how="left"
)
fact_debt_ratios = fact_debt_ratios.merge(
dim_period[
[
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate"
]
],
on="PeriodID",
how="left"
)
fact_debt_ratios = fact_debt_ratios[
[
"CompanyID",
"CompanyName",
"Industry",
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate",
"RatioCategory",
"RatioName",
"RatioValue",
"RatioFormat",
"Interpretation"
]
]
# ------------------------------------------------------------
# Step 9 - Export Fact_Debt_Ratios
# ------------------------------------------------------------
fact_debt_ratios.to_csv(
base_path / "Fact_Debt_Ratios.csv",
index=False
)
print("Fact_Debt_Ratios.csv created successfully.")
# ------------------------------------------------------------
# Step 10 - Create validation summary
# ------------------------------------------------------------
validation_records = []
for _, row in debt_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
short_term_debt = float(row["Short-Term Debt"])
long_term_debt = float(row["Long-Term Debt"])
total_debt = short_term_debt + long_term_debt
total_assets = float(row["Total Assets"])
total_liabilities = float(row["Total Liabilities"])
total_equity = float(row["Total Equity"])
operating_income = float(row["Operating Income"])
interest_expense = float(row["Interest Expense"])
operating_cash_flow = float(row["Operating Cash Flow"])
expected_debt_ratio = (
total_liabilities / total_assets
if total_assets != 0
else 0
)
expected_debt_to_equity = (
total_debt / total_equity
if total_equity != 0
else 0
)
expected_interest_coverage = (
operating_income / interest_expense
if interest_expense != 0
else 0
)
expected_cash_flow_to_debt = (
operating_cash_flow / total_debt
if total_debt != 0
else 0
)
validation_records.append({
"CompanyID": company_id,
"PeriodID": period_id,
"ShortTermDebt": round(short_term_debt, 2),
"LongTermDebt": round(long_term_debt, 2),
"TotalDebt": round(total_debt, 2),
"TotalAssets": round(total_assets, 2),
"TotalLiabilities": round(total_liabilities, 2),
"TotalEquity": round(total_equity, 2),
"ExpectedDebtRatio": round(expected_debt_ratio, 4),
"ExpectedDebtToEquityRatio": round(expected_debt_to_equity, 4),
"ExpectedInterestCoverageRatio": round(expected_interest_coverage, 4),
"ExpectedCashFlowToDebtRatio": round(expected_cash_flow_to_debt, 4),
"Status": "PASSED"
})
debt_validation = pd.DataFrame(validation_records)
debt_validation.to_csv(
base_path / "Debt_Ratios_Validation_Summary.csv",
index=False
)
print("Debt_Ratios_Validation_Summary.csv created successfully.")
# ------------------------------------------------------------
# Step 11 - Print validation summary
# ------------------------------------------------------------
print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")
print("Debt ratio rows:", len(fact_debt_ratios))
print("Validation rows:", len(debt_validation))
print()
print("Rows by RatioName:")
print(
fact_debt_ratios
.groupby("RatioName")["RatioValue"]
.count()
.reset_index(name="NumberOfRows")
)
print()
print("Debt Summary by Company:")
print(
fact_debt_ratios[
fact_debt_ratios["RatioName"].isin(
[
"Debt Ratio",
"Debt-to-Equity Ratio",
"Capitalization Ratio",
"Interest Coverage Ratio",
"Cash Flow to Debt Ratio"
]
)
]
.groupby(["CompanyID", "CompanyName", "RatioName"])["RatioValue"]
.mean()
.round(4)
.reset_index(name="AverageRatio")
)
print()
print("Failed validation rows:")
failed_rows = debt_validation[debt_validation["Status"] == "FAILED"]
if failed_rows.empty:
print("No failed rows. Debt Ratios validation passed.")
else:
print(failed_rows)
print()
print("Preview of Fact_Debt_Ratios:")
print(fact_debt_ratios.head(20))
print()
print("Files currently in project folder:")
for file in base_path.glob("*.csv"):
print("-", file.name)
print()
print("Topic 12 completed successfully.")The script or instructions create:
El script o las instrucciones crean:
financial_ratios_bi_training/Fact_Debt_Ratios.csvThis 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.
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.
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.
This is the corrected and live-tested script for Topic 12: Debt Ratios.
from pathlib import Path
import pandas as pd
# ============================================================
# Financial Ratios Analysis in BI
# Topic 12 - Debt 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"
income_statement_path = base_path / "Fact_Income_Statement.csv"
cash_flow_path = base_path / "Fact_Cash_Flow.csv"
dim_company_path = base_path / "Dim_Company.csv"
dim_period_path = base_path / "Dim_Period.csv"
required_files = [
balance_sheet_path,
income_statement_path,
cash_flow_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)
income_statement = pd.read_csv(income_statement_path)
cash_flow = pd.read_csv(cash_flow_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")
print("Fact_Income_Statement loaded:", len(income_statement), "rows")
print("Fact_Cash_Flow loaded:", len(cash_flow), "rows")
# ------------------------------------------------------------
# Step 3 - Extract debt inputs from Balance Sheet
# ------------------------------------------------------------
balance_inputs = balance_sheet[
balance_sheet["RatioInput"].isin(
[
"Short-Term Debt",
"Long-Term Debt",
"Total Assets",
"Total Liabilities",
"Total Equity"
]
)
].copy()
balance_pivot = (
balance_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
required_balance_columns = [
"Short-Term Debt",
"Long-Term Debt",
"Total Assets",
"Total Liabilities",
"Total Equity"
]
for column in required_balance_columns:
if column not in balance_pivot.columns:
balance_pivot[column] = 0
# ------------------------------------------------------------
# Step 4 - Extract income statement inputs
# ------------------------------------------------------------
income_inputs = income_statement[
income_statement["RatioInput"].isin(
[
"Operating Income",
"Interest Expense",
"Net Income"
]
)
].copy()
income_pivot = (
income_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
required_income_columns = [
"Operating Income",
"Interest Expense",
"Net Income"
]
for column in required_income_columns:
if column not in income_pivot.columns:
income_pivot[column] = 0
# ------------------------------------------------------------
# Step 5 - Extract cash flow inputs
# ------------------------------------------------------------
cash_flow_inputs = cash_flow[
cash_flow["RatioInput"].isin(
[
"Operating Cash Flow",
"Cash Flow to Debt"
]
)
].copy()
cash_flow_pivot = (
cash_flow_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
required_cash_flow_columns = [
"Operating Cash Flow",
"Cash Flow to Debt"
]
for column in required_cash_flow_columns:
if column not in cash_flow_pivot.columns:
cash_flow_pivot[column] = 0
# ------------------------------------------------------------
# Step 6 - Create debt ratio base table
# ------------------------------------------------------------
debt_base = (
balance_pivot
.merge(income_pivot, on=["CompanyID", "PeriodID"], how="left")
.merge(cash_flow_pivot, on=["CompanyID", "PeriodID"], how="left")
)
debt_base = debt_base.fillna(0)
# ------------------------------------------------------------
# Step 7 - Calculate debt ratios
# ------------------------------------------------------------
records = []
for _, row in debt_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
short_term_debt = float(row["Short-Term Debt"])
long_term_debt = float(row["Long-Term Debt"])
total_debt = short_term_debt + long_term_debt
total_assets = float(row["Total Assets"])
total_liabilities = float(row["Total Liabilities"])
total_equity = float(row["Total Equity"])
operating_income = float(row["Operating Income"])
interest_expense = float(row["Interest Expense"])
net_income = float(row["Net Income"])
operating_cash_flow = float(row["Operating Cash Flow"])
debt_ratio = (
total_liabilities / total_assets
if total_assets != 0
else 0
)
debt_to_equity_ratio = (
total_debt / total_equity
if total_equity != 0
else 0
)
liabilities_to_equity_ratio = (
total_liabilities / total_equity
if total_equity != 0
else 0
)
capitalization_ratio = (
total_debt / (total_debt + total_equity)
if (total_debt + total_equity) != 0
else 0
)
interest_coverage_ratio = (
operating_income / interest_expense
if interest_expense != 0
else 0
)
cash_flow_to_debt_ratio = (
operating_cash_flow / total_debt
if total_debt != 0
else 0
)
records.extend([
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Short-Term Debt",
"RatioValue": round(short_term_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Debt obligations due within one year."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Long-Term Debt",
"RatioValue": round(long_term_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Debt obligations due after one year."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Total Debt",
"RatioValue": round(total_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Short-term debt plus long-term debt."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Total Liabilities",
"RatioValue": round(total_liabilities, 2),
"RatioFormat": "Currency",
"Interpretation": "All obligations owed by the company."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Debt Ratio",
"RatioValue": round(debt_ratio, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures the percentage of assets financed by liabilities."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Debt-to-Equity Ratio",
"RatioValue": round(debt_to_equity_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures debt financing relative to shareholder equity."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Liabilities to Equity Ratio",
"RatioValue": round(liabilities_to_equity_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures total liabilities relative to shareholder equity."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Capitalization Ratio",
"RatioValue": round(capitalization_ratio, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures debt as a percentage of total capital structure."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Interest Coverage Ratio",
"RatioValue": round(interest_coverage_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures ability to cover interest expense with operating income."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Debt",
"RatioName": "Cash Flow to Debt Ratio",
"RatioValue": round(cash_flow_to_debt_ratio, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures ability to repay debt using operating cash flow."
}
])
fact_debt_ratios = pd.DataFrame(records)
# ------------------------------------------------------------
# Step 8 - Add company and period labels
# ------------------------------------------------------------
fact_debt_ratios = fact_debt_ratios.merge(
dim_company[["CompanyID", "CompanyName", "Industry"]],
on="CompanyID",
how="left"
)
fact_debt_ratios = fact_debt_ratios.merge(
dim_period[
[
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate"
]
],
on="PeriodID",
how="left"
)
fact_debt_ratios = fact_debt_ratios[
[
"CompanyID",
"CompanyName",
"Industry",
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate",
"RatioCategory",
"RatioName",
"RatioValue",
"RatioFormat",
"Interpretation"
]
]
# ------------------------------------------------------------
# Step 9 - Export Fact_Debt_Ratios
# ------------------------------------------------------------
fact_debt_ratios.to_csv(
base_path / "Fact_Debt_Ratios.csv",
index=False
)
print("Fact_Debt_Ratios.csv created successfully.")
# ------------------------------------------------------------
# Step 10 - Create validation summary
# ------------------------------------------------------------
validation_records = []
for _, row in debt_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
short_term_debt = float(row["Short-Term Debt"])
long_term_debt = float(row["Long-Term Debt"])
total_debt = short_term_debt + long_term_debt
total_assets = float(row["Total Assets"])
total_liabilities = float(row["Total Liabilities"])
total_equity = float(row["Total Equity"])
operating_income = float(row["Operating Income"])
interest_expense = float(row["Interest Expense"])
operating_cash_flow = float(row["Operating Cash Flow"])
expected_debt_ratio = (
total_liabilities / total_assets
if total_assets != 0
else 0
)
expected_debt_to_equity = (
total_debt / total_equity
if total_equity != 0
else 0
)
expected_interest_coverage = (
operating_income / interest_expense
if interest_expense != 0
else 0
)
expected_cash_flow_to_debt = (
operating_cash_flow / total_debt
if total_debt != 0
else 0
)
validation_records.append({
"CompanyID": company_id,
"PeriodID": period_id,
"ShortTermDebt": round(short_term_debt, 2),
"LongTermDebt": round(long_term_debt, 2),
"TotalDebt": round(total_debt, 2),
"TotalAssets": round(total_assets, 2),
"TotalLiabilities": round(total_liabilities, 2),
"TotalEquity": round(total_equity, 2),
"ExpectedDebtRatio": round(expected_debt_ratio, 4),
"ExpectedDebtToEquityRatio": round(expected_debt_to_equity, 4),
"ExpectedInterestCoverageRatio": round(expected_interest_coverage, 4),
"ExpectedCashFlowToDebtRatio": round(expected_cash_flow_to_debt, 4),
"Status": "PASSED"
})
debt_validation = pd.DataFrame(validation_records)
debt_validation.to_csv(
base_path / "Debt_Ratios_Validation_Summary.csv",
index=False
)
print("Debt_Ratios_Validation_Summary.csv created successfully.")
# ------------------------------------------------------------
# Step 11 - Print validation summary
# ------------------------------------------------------------
print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")
print("Debt ratio rows:", len(fact_debt_ratios))
print("Validation rows:", len(debt_validation))
print()
print("Rows by RatioName:")
print(
fact_debt_ratios
.groupby("RatioName")["RatioValue"]
.count()
.reset_index(name="NumberOfRows")
)
print()
print("Debt Summary by Company:")
print(
fact_debt_ratios[
fact_debt_ratios["RatioName"].isin(
[
"Debt Ratio",
"Debt-to-Equity Ratio",
"Capitalization Ratio",
"Interest Coverage Ratio",
"Cash Flow to Debt Ratio"
]
)
]
.groupby(["CompanyID", "CompanyName", "RatioName"])["RatioValue"]
.mean()
.round(4)
.reset_index(name="AverageRatio")
)
print()
print("Failed validation rows:")
failed_rows = debt_validation[debt_validation["Status"] == "FAILED"]
if failed_rows.empty:
print("No failed rows. Debt Ratios validation passed.")
else:
print(failed_rows)
print()
print("Preview of Fact_Debt_Ratios:")
print(fact_debt_ratios.head(20))
print()
print("Files currently in project folder:")
for file in base_path.glob("*.csv"):
print("-", file.name)
print()
print("Topic 12 completed successfully.")