Financial Ratios Analysis in BI

Step-by-Step Practice

Step 1 — Prepare the folder and load previous files

Step 2 — Build topic-specific calculations

Step 3 — Export the output CSV or documentation file

Step 4 — Validate rows, keys, and financial logic

Step 5 — Interpret the result as a BI analyst

Script / Instructions

Copyfrom pathlib import Path
import pandas as pd

# ============================================================
# Financial Ratios Analysis in BI
# Topic 9 - Create Market Data
# ============================================================

# ------------------------------------------------------------
# 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"
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 = [
    income_statement_path,
    balance_sheet_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
# ------------------------------------------------------------

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

# ------------------------------------------------------------
# Step 3 - Create company market assumptions
# ------------------------------------------------------------
# These assumptions are synthetic and designed for training.
# Each company has a different valuation profile.

market_assumptions = {
    1: {
        "BaseSharePrice": 22.50,
        "PriceGrowth": 0.012,
        "SharesOutstanding": 25000000,
        "PE_Multiple": 14,
        "DividendPayoutPct": 0.18
    },
    2: {
        "BaseSharePrice": 35.00,
        "PriceGrowth": 0.010,
        "SharesOutstanding": 18000000,
        "PE_Multiple": 18,
        "DividendPayoutPct": 0.12
    },
    3: {
        "BaseSharePrice": 18.75,
        "PriceGrowth": 0.006,
        "SharesOutstanding": 32000000,
        "PE_Multiple": 11,
        "DividendPayoutPct": 0.10
    },
    4: {
        "BaseSharePrice": 64.00,
        "PriceGrowth": 0.020,
        "SharesOutstanding": 15000000,
        "PE_Multiple": 28,
        "DividendPayoutPct": 0.05
    },
    5: {
        "BaseSharePrice": 27.25,
        "PriceGrowth": 0.009,
        "SharesOutstanding": 22000000,
        "PE_Multiple": 15,
        "DividendPayoutPct": 0.14
    }
}

# ------------------------------------------------------------
# Step 4 - Prepare financial values needed for market data
# ------------------------------------------------------------

net_income = income_statement[
    income_statement["RatioInput"] == "Net Income"
][
    ["CompanyID", "PeriodID", "Amount"]
].copy()

net_income = net_income.rename(columns={"Amount": "NetIncome"})

total_equity = balance_sheet[
    balance_sheet["RatioInput"] == "Total Equity"
][
    ["CompanyID", "PeriodID", "Amount"]
].copy()

total_equity = total_equity.rename(columns={"Amount": "TotalEquity"})

total_debt = balance_sheet[
    balance_sheet["RatioInput"].isin(["Short-Term Debt", "Long-Term Debt"])
].copy()

total_debt = (
    total_debt
    .groupby(["CompanyID", "PeriodID"])
    .agg(TotalDebt=("Amount", "sum"))
    .reset_index()
)

cash_balance = balance_sheet[
    balance_sheet["RatioInput"] == "Cash"
][
    ["CompanyID", "PeriodID", "Amount"]
].copy()

cash_balance = cash_balance.rename(columns={"Amount": "Cash"})

dividends_paid = cash_flow[
    cash_flow["RatioInput"] == "Dividends Paid"
][
    ["CompanyID", "PeriodID", "Amount"]
].copy()

dividends_paid = dividends_paid.rename(columns={"Amount": "DividendsPaid"})

# Dividends Paid is stored as negative cash flow.
# For market metrics, use positive dividend amount.
dividends_paid["DividendsPaid"] = dividends_paid["DividendsPaid"].abs()

financial_base = (
    net_income
    .merge(total_equity, on=["CompanyID", "PeriodID"], how="left")
    .merge(total_debt, on=["CompanyID", "PeriodID"], how="left")
    .merge(cash_balance, on=["CompanyID", "PeriodID"], how="left")
    .merge(dividends_paid, on=["CompanyID", "PeriodID"], how="left")
)

financial_base = financial_base.fillna(0)

# ------------------------------------------------------------
# Step 5 - Generate synthetic market data
# ------------------------------------------------------------

records = []

for _, row in financial_base.iterrows():

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

    period_index = period_id - 1

    shares_outstanding = assumptions["SharesOutstanding"]

    # Synthetic share price with growth and simple market seasonality
    share_price = assumptions["BaseSharePrice"] * (
        (1 + assumptions["PriceGrowth"]) ** period_index
    )

    if period_id % 4 == 0:
        share_price *= 1.04
    elif period_id % 4 == 1:
        share_price *= 0.98

    net_income_value = float(row["NetIncome"])
    total_equity_value = float(row["TotalEquity"])
    total_debt_value = float(row["TotalDebt"])
    cash_value = float(row["Cash"])
    dividends_paid_value = float(row["DividendsPaid"])

    market_cap = share_price * shares_outstanding
    enterprise_value = market_cap + total_debt_value - cash_value

    eps = (
        net_income_value / shares_outstanding
        if shares_outstanding != 0
        else 0
    )

    book_value_per_share = (
        total_equity_value / shares_outstanding
        if shares_outstanding != 0
        else 0
    )

    dividend_per_share = (
        dividends_paid_value / shares_outstanding
        if shares_outstanding != 0
        else 0
    )

    dividend_yield = (
        dividend_per_share / share_price
        if share_price != 0
        else 0
    )

    price_to_earnings = (
        share_price / eps
        if eps != 0
        else 0
    )

    price_to_book = (
        share_price / book_value_per_share
        if book_value_per_share != 0
        else 0
    )

    records.append({
        "CompanyID": company_id,
        "PeriodID": period_id,
        "SharePrice": round(share_price, 2),
        "SharesOutstanding": int(shares_outstanding),
        "MarketCapitalization": round(market_cap, 2),
        "EnterpriseValue": round(enterprise_value, 2),
        "NetIncome": round(net_income_value, 2),
        "TotalEquity": round(total_equity_value, 2),
        "TotalDebt": round(total_debt_value, 2),
        "Cash": round(cash_value, 2),
        "DividendsPaid": round(dividends_paid_value, 2),
        "EPS": round(eps, 4),
        "BookValuePerShare": round(book_value_per_share, 4),
        "DividendPerShare": round(dividend_per_share, 4),
        "DividendYield": round(dividend_yield, 4),
        "PriceToEarnings": round(price_to_earnings, 4),
        "PriceToBook": round(price_to_book, 4)
    })

fact_market_data = pd.DataFrame(records)

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

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

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

fact_market_data = fact_market_data[
    [
        "CompanyID",
        "CompanyName",
        "Ticker",
        "Industry",
        "PeriodID",
        "FiscalYear",
        "FiscalQuarter",
        "YearQuarter",
        "PeriodEndDate",
        "SharePrice",
        "SharesOutstanding",
        "MarketCapitalization",
        "EnterpriseValue",
        "NetIncome",
        "TotalEquity",
        "TotalDebt",
        "Cash",
        "DividendsPaid",
        "EPS",
        "BookValuePerShare",
        "DividendPerShare",
        "DividendYield",
        "PriceToEarnings",
        "PriceToBook"
    ]
]

# ------------------------------------------------------------
# Step 7 - Export Fact_Market_Data
# ------------------------------------------------------------

fact_market_data.to_csv(
    base_path / "Fact_Market_Data.csv",
    index=False
)

print("Fact_Market_Data.csv created successfully.")

# ------------------------------------------------------------
# Step 8 - Create market data validation summary
# ------------------------------------------------------------

validation_records = []

for _, row in fact_market_data.iterrows():

    expected_market_cap = row["SharePrice"] * row["SharesOutstanding"]
    market_cap_difference = round(
        row["MarketCapitalization"] - expected_market_cap,
        2
    )

    expected_enterprise_value = (
        row["MarketCapitalization"]
        + row["TotalDebt"]
        - row["Cash"]
    )

    enterprise_value_difference = round(
        row["EnterpriseValue"] - expected_enterprise_value,
        2
    )

    expected_eps = (
        row["NetIncome"] / row["SharesOutstanding"]
        if row["SharesOutstanding"] != 0
        else 0
    )

    eps_difference = round(row["EPS"] - expected_eps, 4)

    validation_records.append({
        "CompanyID": row["CompanyID"],
        "PeriodID": row["PeriodID"],
        "MarketCapitalization": row["MarketCapitalization"],
        "ExpectedMarketCapitalization": round(expected_market_cap, 2),
        "MarketCapDifference": market_cap_difference,
        "EnterpriseValue": row["EnterpriseValue"],
        "ExpectedEnterpriseValue": round(expected_enterprise_value, 2),
        "EnterpriseValueDifference": enterprise_value_difference,
        "EPS": row["EPS"],
        "ExpectedEPS": round(expected_eps, 4),
        "EPSDifference": eps_difference,
        "Status": (
            "PASSED"
            if abs(market_cap_difference) <= 0.01
            and abs(enterprise_value_difference) <= 0.01
            and abs(eps_difference) <= 0.0001
            else "FAILED"
        )
    })

market_validation = pd.DataFrame(validation_records)

market_validation.to_csv(
    base_path / "Market_Data_Validation_Summary.csv",
    index=False
)

print("Market_Data_Validation_Summary.csv created successfully.")

# ------------------------------------------------------------
# Step 9 - Print validation summary
# ------------------------------------------------------------

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

print("Market data rows:", len(fact_market_data))
print("Validation rows:", len(market_validation))

print()
print("Rows by Company:")
print(
    fact_market_data
    .groupby(["CompanyID", "CompanyName", "Ticker"])["PeriodID"]
    .count()
    .reset_index(name="NumberOfPeriods")
)

print()
print("Market Data Summary by Company:")
print(
    fact_market_data
    .groupby(["CompanyID", "CompanyName", "Ticker"])
    .agg(
        AverageSharePrice=("SharePrice", "mean"),
        AverageMarketCap=("MarketCapitalization", "mean"),
        AverageEnterpriseValue=("EnterpriseValue", "mean"),
        AveragePE=("PriceToEarnings", "mean"),
        AveragePB=("PriceToBook", "mean"),
        AverageDividendYield=("DividendYield", "mean")
    )
    .round(4)
    .reset_index()
)

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

if failed_rows.empty:
    print("No failed rows. Market Data validation passed.")
else:
    print(failed_rows)

print()
print("Preview of Fact_Market_Data:")
print(fact_market_data.head(10))

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

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

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

financial_ratios_bi_training/Fact_Market_Data.csv

Validation Checklist

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

Final Recommendation

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