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
from datetime import datetime

# ============================================================
# Financial Ratios Analysis in BI
# Topic 20 - Final Training Package
# ============================================================

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

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

# ------------------------------------------------------------
# Step 2 - Define expected final training files
# ------------------------------------------------------------

expected_files = [
    # Core dimensions
    {
        "FileName": "Dim_Company.csv",
        "FileGroup": "Dimensions",
        "CreatedByTopic": "Topic 1",
        "RequiredForTraining": "Yes",
        "Description": "Company dimension with company names, industries, tickers, risk profile, and business profile."
    },
    {
        "FileName": "Dim_Period.csv",
        "FileGroup": "Dimensions",
        "CreatedByTopic": "Topic 1",
        "RequiredForTraining": "Yes",
        "Description": "Fiscal period dimension from 2021 Q1 to 2025 Q4."
    },
    {
        "FileName": "Dim_Account.csv",
        "FileGroup": "Dimensions",
        "CreatedByTopic": "Topic 2",
        "RequiredForTraining": "Yes",
        "Description": "Chart of accounts used for the synthetic financial model."
    },

    # Core financial facts
    {
        "FileName": "Fact_Trial_Balance.csv",
        "FileGroup": "Financial Facts",
        "CreatedByTopic": "Topic 3",
        "RequiredForTraining": "Yes",
        "Description": "Balanced trial balance by company, period, and account."
    },
    {
        "FileName": "Fact_Income_Statement.csv",
        "FileGroup": "Financial Facts",
        "CreatedByTopic": "Topic 6",
        "RequiredForTraining": "Yes",
        "Description": "Income statement with base and calculated lines."
    },
    {
        "FileName": "Fact_Balance_Sheet.csv",
        "FileGroup": "Financial Facts",
        "CreatedByTopic": "Topic 7",
        "RequiredForTraining": "Yes",
        "Description": "Balance sheet with assets, liabilities, equity, and working capital."
    },
    {
        "FileName": "Fact_Cash_Flow.csv",
        "FileGroup": "Financial Facts",
        "CreatedByTopic": "Topic 8",
        "RequiredForTraining": "Yes",
        "Description": "Cash flow statement and cash flow indicators."
    },
    {
        "FileName": "Fact_Market_Data.csv",
        "FileGroup": "Financial Facts",
        "CreatedByTopic": "Topic 9",
        "RequiredForTraining": "Yes",
        "Description": "Synthetic market data including share price, market cap, enterprise value, EPS, and valuation inputs."
    },
    {
        "FileName": "Fact_Employees.csv",
        "FileGroup": "Operational Facts",
        "CreatedByTopic": "Topic 13",
        "RequiredForTraining": "Yes",
        "Description": "Synthetic employee count by company and period."
    },

    # Mapping and validation
    {
        "FileName": "Mapping_Financial_Statements.csv",
        "FileGroup": "Mapping",
        "CreatedByTopic": "Topic 5",
        "RequiredForTraining": "Yes",
        "Description": "Account-to-financial-statement mapping."
    },
    {
        "FileName": "Mapping_Coverage_Check.csv",
        "FileGroup": "Mapping",
        "CreatedByTopic": "Topic 5",
        "RequiredForTraining": "Optional",
        "Description": "Mapping coverage validation file."
    },
    {
        "FileName": "Trial_Balance_Validation_Report.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 4",
        "RequiredForTraining": "Yes",
        "Description": "Trial balance validation by company and period."
    },
    {
        "FileName": "Trial_Balance_Statement_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 4",
        "RequiredForTraining": "Optional",
        "Description": "Trial balance summary by financial statement."
    },
    {
        "FileName": "Trial_Balance_Account_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 4",
        "RequiredForTraining": "Optional",
        "Description": "Trial balance account-level summary."
    },
    {
        "FileName": "Trial_Balance_Data_Quality_Issues.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 4",
        "RequiredForTraining": "Optional",
        "Description": "Initial trial balance data quality issue report."
    },

    # Ratio facts
    {
        "FileName": "Fact_Liquidity_Ratios.csv",
        "FileGroup": "Ratio Facts",
        "CreatedByTopic": "Topic 10",
        "RequiredForTraining": "Yes",
        "Description": "Liquidity ratios including current ratio, quick ratio, cash ratio, and working capital."
    },
    {
        "FileName": "Fact_Profitability_Ratios.csv",
        "FileGroup": "Ratio Facts",
        "CreatedByTopic": "Topic 11",
        "RequiredForTraining": "Yes",
        "Description": "Profitability ratios including margins, ROA, ROE, and ROCE."
    },
    {
        "FileName": "Fact_Debt_Ratios.csv",
        "FileGroup": "Ratio Facts",
        "CreatedByTopic": "Topic 12",
        "RequiredForTraining": "Yes",
        "Description": "Debt ratios including debt ratio, debt-to-equity, interest coverage, and cash flow to debt."
    },
    {
        "FileName": "Fact_Operating_Performance_Ratios.csv",
        "FileGroup": "Ratio Facts",
        "CreatedByTopic": "Topic 13",
        "RequiredForTraining": "Yes",
        "Description": "Operating performance ratios including asset turnover and revenue per employee."
    },
    {
        "FileName": "Fact_Cash_Flow_Ratios.csv",
        "FileGroup": "Ratio Facts",
        "CreatedByTopic": "Topic 14",
        "RequiredForTraining": "Yes",
        "Description": "Cash flow indicator ratios including operating cash flow margin and free cash flow margin."
    },
    {
        "FileName": "Fact_Investment_Valuation_Ratios.csv",
        "FileGroup": "Ratio Facts",
        "CreatedByTopic": "Topic 15",
        "RequiredForTraining": "Yes",
        "Description": "Investment valuation ratios including P/E, P/B, P/S, dividend yield, and enterprise value multiples."
    },

    # Ratio validation
    {
        "FileName": "Liquidity_Ratios_Validation_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 10",
        "RequiredForTraining": "Optional",
        "Description": "Liquidity ratio validation summary."
    },
    {
        "FileName": "Profitability_Ratios_Validation_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 11",
        "RequiredForTraining": "Optional",
        "Description": "Profitability ratio validation summary."
    },
    {
        "FileName": "Debt_Ratios_Validation_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 12",
        "RequiredForTraining": "Optional",
        "Description": "Debt ratio validation summary."
    },
    {
        "FileName": "Operating_Performance_Validation_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 13",
        "RequiredForTraining": "Optional",
        "Description": "Operating performance validation summary."
    },
    {
        "FileName": "Cash_Flow_Ratios_Validation_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 14",
        "RequiredForTraining": "Optional",
        "Description": "Cash flow ratio validation summary."
    },
    {
        "FileName": "Investment_Valuation_Validation_Summary.csv",
        "FileGroup": "Validation",
        "CreatedByTopic": "Topic 15",
        "RequiredForTraining": "Optional",
        "Description": "Investment valuation validation summary."
    },

    # Data quality
    {
        "FileName": "DQ_Test_Fact_Trial_Balance_With_Issues.csv",
        "FileGroup": "Data Quality",
        "CreatedByTopic": "Topic 16",
        "RequiredForTraining": "Optional",
        "Description": "Trial balance dataset with intentional data quality issues."
    },
    {
        "FileName": "DQ_Test_Fact_Market_Data_With_Issues.csv",
        "FileGroup": "Data Quality",
        "CreatedByTopic": "Topic 16",
        "RequiredForTraining": "Optional",
        "Description": "Market data dataset with intentional data quality issues."
    },
    {
        "FileName": "DQ_Test_All_Ratios_With_Issues.csv",
        "FileGroup": "Data Quality",
        "CreatedByTopic": "Topic 16",
        "RequiredForTraining": "Optional",
        "Description": "Combined ratio dataset with intentional data quality issues."
    },
    {
        "FileName": "Data_Quality_Issues.csv",
        "FileGroup": "Data Quality",
        "CreatedByTopic": "Topic 16",
        "RequiredForTraining": "Yes",
        "Description": "Summary of detected data quality issues."
    },
    {
        "FileName": "Data_Quality_Issues_Detail.csv",
        "FileGroup": "Data Quality",
        "CreatedByTopic": "Topic 16",
        "RequiredForTraining": "Yes",
        "Description": "Detailed data quality issue report."
    },

    # SQL Server
    {
        "FileName": "SQL_Create_Tables.sql",
        "FileGroup": "SQL Server",
        "CreatedByTopic": "Topic 17",
        "RequiredForTraining": "Yes",
        "Description": "SQL Server create table script."
    },
    {
        "FileName": "SQL_Load_Data_Template.sql",
        "FileGroup": "SQL Server",
        "CreatedByTopic": "Topic 17",
        "RequiredForTraining": "Yes",
        "Description": "SQL Server data load template using BULK INSERT."
    },
    {
        "FileName": "SQL_Validation_Queries.sql",
        "FileGroup": "SQL Server",
        "CreatedByTopic": "Topic 17",
        "RequiredForTraining": "Yes",
        "Description": "SQL validation queries for the financial model."
    },
    {
        "FileName": "SQL_Financial_Views.sql",
        "FileGroup": "SQL Server",
        "CreatedByTopic": "Topic 17",
        "RequiredForTraining": "Yes",
        "Description": "SQL financial views for reporting."
    },
    {
        "FileName": "SQL_Table_Metadata.csv",
        "FileGroup": "SQL Server",
        "CreatedByTopic": "Topic 17",
        "RequiredForTraining": "Optional",
        "Description": "SQL table metadata generated from CSV files."
    },

    # Power BI
    {
        "FileName": "PowerBI_Model_Tables.csv",
        "FileGroup": "Power BI",
        "CreatedByTopic": "Topic 18",
        "RequiredForTraining": "Yes",
        "Description": "Power BI model table catalog."
    },
    {
        "FileName": "PowerBI_Relationships.csv",
        "FileGroup": "Power BI",
        "CreatedByTopic": "Topic 18",
        "RequiredForTraining": "Yes",
        "Description": "Power BI relationship plan."
    },
    {
        "FileName": "PowerBI_Import_Checklist.csv",
        "FileGroup": "Power BI",
        "CreatedByTopic": "Topic 18",
        "RequiredForTraining": "Yes",
        "Description": "Power BI import and validation checklist."
    },
    {
        "FileName": "PowerBI_Model_Validation.csv",
        "FileGroup": "Power BI",
        "CreatedByTopic": "Topic 18",
        "RequiredForTraining": "Yes",
        "Description": "Power BI model file validation report."
    },
    {
        "FileName": "PowerBI_DAX_Measures.txt",
        "FileGroup": "Power BI",
        "CreatedByTopic": "Topic 18",
        "RequiredForTraining": "Yes",
        "Description": "Recommended DAX measures for the Power BI model."
    },

    # Executive dashboard
    {
        "FileName": "Executive_Financial_Dashboard.csv",
        "FileGroup": "Executive Dashboard",
        "CreatedByTopic": "Topic 19",
        "RequiredForTraining": "Yes",
        "Description": "Consolidated executive dashboard dataset."
    },
    {
        "FileName": "Executive_KPI_Summary.csv",
        "FileGroup": "Executive Dashboard",
        "CreatedByTopic": "Topic 19",
        "RequiredForTraining": "Yes",
        "Description": "Latest-period executive KPI summary."
    },
    {
        "FileName": "Company_Financial_Health_Score.csv",
        "FileGroup": "Executive Dashboard",
        "CreatedByTopic": "Topic 19",
        "RequiredForTraining": "Yes",
        "Description": "Financial health score trend by company and period."
    },
    {
        "FileName": "Executive_Dashboard_Validation.csv",
        "FileGroup": "Executive Dashboard",
        "CreatedByTopic": "Topic 19",
        "RequiredForTraining": "Yes",
        "Description": "Executive dashboard validation report."
    }
]

file_catalog = pd.DataFrame(expected_files)

# ------------------------------------------------------------
# Step 3 - Validate expected files
# ------------------------------------------------------------

validation_records = []

for _, row in file_catalog.iterrows():

    file_name = row["FileName"]
    file_path = base_path / file_name

    exists = file_path.exists()

    if exists:
        file_size_kb = round(file_path.stat().st_size / 1024, 2)

        try:
            if file_path.suffix.lower() == ".csv":
                df = pd.read_csv(file_path)
                row_count = len(df)
                column_count = len(df.columns)
                status = "FOUND"
                validation_message = "File exists and is readable."
            elif file_path.suffix.lower() in [".txt", ".sql"]:
                content = file_path.read_text(encoding="utf-8", errors="ignore")
                row_count = len(content.splitlines())
                column_count = 0
                status = "FOUND"
                validation_message = "Text file exists and is readable."
            else:
                row_count = 0
                column_count = 0
                status = "FOUND"
                validation_message = "File exists."
        except Exception as error:
            row_count = 0
            column_count = 0
            status = "ERROR"
            validation_message = str(error)

    else:
        file_size_kb = 0
        row_count = 0
        column_count = 0
        status = "MISSING"
        validation_message = "File was not found."

    validation_records.append({
        "FileName": file_name,
        "FileGroup": row["FileGroup"],
        "CreatedByTopic": row["CreatedByTopic"],
        "RequiredForTraining": row["RequiredForTraining"],
        "FileStatus": status,
        "FileSizeKB": file_size_kb,
        "RowCount": row_count,
        "ColumnCount": column_count,
        "ValidationMessage": validation_message,
        "Description": row["Description"]
    })

final_catalog = pd.DataFrame(validation_records)

# ------------------------------------------------------------
# Step 4 - Create topic summary
# ------------------------------------------------------------

topic_summary_data = [
    {
        "TopicNumber": 1,
        "TopicName": "Create Companies and Periods",
        "MainOutput": "Dim_Company.csv, Dim_Period.csv",
        "LearningObjective": "Create the base dimensions required for financial BI analysis.",
        "BusinessValue": "Defines who is being analyzed and over what time periods."
    },
    {
        "TopicNumber": 2,
        "TopicName": "Create Chart of Accounts",
        "MainOutput": "Dim_Account.csv",
        "LearningObjective": "Create a structured chart of accounts for financial statements.",
        "BusinessValue": "Creates account-level structure for financial reporting."
    },
    {
        "TopicNumber": 3,
        "TopicName": "Generate Synthetic Trial Balance",
        "MainOutput": "Fact_Trial_Balance.csv",
        "LearningObjective": "Generate a clean, balanced trial balance dataset.",
        "BusinessValue": "Creates the accounting foundation for all financial statements."
    },
    {
        "TopicNumber": 4,
        "TopicName": "Validate Trial Balance",
        "MainOutput": "Trial_Balance_Validation_Report.csv",
        "LearningObjective": "Validate debits, credits, and company-period balancing.",
        "BusinessValue": "Prevents unreliable reporting caused by unbalanced financial data."
    },
    {
        "TopicNumber": 5,
        "TopicName": "Map Accounts to Financial Statements",
        "MainOutput": "Mapping_Financial_Statements.csv",
        "LearningObjective": "Map accounts to balance sheet, income statement, and cash flow lines.",
        "BusinessValue": "Connects accounting data to financial reporting logic."
    },
    {
        "TopicNumber": 6,
        "TopicName": "Build Income Statement",
        "MainOutput": "Fact_Income_Statement.csv",
        "LearningObjective": "Build revenue, expenses, margins, operating income, and net income.",
        "BusinessValue": "Enables profitability analysis."
    },
    {
        "TopicNumber": 7,
        "TopicName": "Build Balance Sheet",
        "MainOutput": "Fact_Balance_Sheet.csv",
        "LearningObjective": "Build assets, liabilities, equity, and working capital.",
        "BusinessValue": "Enables liquidity, leverage, and financial position analysis."
    },
    {
        "TopicNumber": 8,
        "TopicName": "Build Cash Flow Indicators",
        "MainOutput": "Fact_Cash_Flow.csv",
        "LearningObjective": "Build operating cash flow, free cash flow, and coverage indicators.",
        "BusinessValue": "Shows whether the business generates cash, not only accounting profit."
    },
    {
        "TopicNumber": 9,
        "TopicName": "Create Market Data",
        "MainOutput": "Fact_Market_Data.csv",
        "LearningObjective": "Create share price, market cap, enterprise value, EPS, and book value data.",
        "BusinessValue": "Enables investment valuation analysis."
    },
    {
        "TopicNumber": 10,
        "TopicName": "Liquidity Ratios",
        "MainOutput": "Fact_Liquidity_Ratios.csv",
        "LearningObjective": "Calculate current ratio, quick ratio, cash ratio, and working capital metrics.",
        "BusinessValue": "Measures ability to meet short-term obligations."
    },
    {
        "TopicNumber": 11,
        "TopicName": "Profitability Ratios",
        "MainOutput": "Fact_Profitability_Ratios.csv",
        "LearningObjective": "Calculate margins, effective tax rate, ROA, ROE, and ROCE.",
        "BusinessValue": "Measures how effectively the company generates profit."
    },
    {
        "TopicNumber": 12,
        "TopicName": "Debt Ratios",
        "MainOutput": "Fact_Debt_Ratios.csv",
        "LearningObjective": "Calculate debt ratio, debt-to-equity, capitalization, and coverage ratios.",
        "BusinessValue": "Measures leverage, solvency, and debt risk."
    },
    {
        "TopicNumber": 13,
        "TopicName": "Operating Performance Ratios",
        "MainOutput": "Fact_Operating_Performance_Ratios.csv",
        "LearningObjective": "Calculate asset turnover, fixed asset turnover, revenue per employee, and expense ratios.",
        "BusinessValue": "Measures operational efficiency."
    },
    {
        "TopicNumber": 14,
        "TopicName": "Cash Flow Ratios",
        "MainOutput": "Fact_Cash_Flow_Ratios.csv",
        "LearningObjective": "Calculate operating cash flow margin, free cash flow margin, coverage, and payout ratios.",
        "BusinessValue": "Measures cash generation quality."
    },
    {
        "TopicNumber": 15,
        "TopicName": "Investment Valuation Ratios",
        "MainOutput": "Fact_Investment_Valuation_Ratios.csv",
        "LearningObjective": "Calculate P/E, P/B, P/S, dividend yield, and enterprise value multiples.",
        "BusinessValue": "Measures market valuation and investor perspective."
    },
    {
        "TopicNumber": 16,
        "TopicName": "Data Quality Issues",
        "MainOutput": "Data_Quality_Issues.csv, Data_Quality_Issues_Detail.csv",
        "LearningObjective": "Create and detect controlled data quality issues.",
        "BusinessValue": "Teaches validation and audit thinking before dashboard creation."
    },
    {
        "TopicNumber": 17,
        "TopicName": "SQL Server Financial Model",
        "MainOutput": "SQL_Create_Tables.sql, SQL_Financial_Views.sql",
        "LearningObjective": "Prepare the model for SQL Server loading and validation.",
        "BusinessValue": "Moves the model from CSV files to a database-ready architecture."
    },
    {
        "TopicNumber": 18,
        "TopicName": "Power BI Model Preparation",
        "MainOutput": "PowerBI_Model_Tables.csv, PowerBI_DAX_Measures.txt",
        "LearningObjective": "Prepare model tables, relationships, DAX measures, and checklist for Power BI.",
        "BusinessValue": "Converts the data model into a BI-ready semantic layer."
    },
    {
        "TopicNumber": 19,
        "TopicName": "Executive Financial Dashboard Dataset",
        "MainOutput": "Executive_Financial_Dashboard.csv",
        "LearningObjective": "Create a consolidated executive dashboard dataset with health scores and KPIs.",
        "BusinessValue": "Supports executive-level financial analysis and decision making."
    },
    {
        "TopicNumber": 20,
        "TopicName": "Final Training Package",
        "MainOutput": "Training catalog, topic summary, final checklist, and README.",
        "LearningObjective": "Package and validate the full training output.",
        "BusinessValue": "Creates a complete reusable training asset."
    }
]

topic_summary = pd.DataFrame(topic_summary_data)

# ------------------------------------------------------------
# Step 5 - Create final checklist
# ------------------------------------------------------------

total_required = len(final_catalog[final_catalog["RequiredForTraining"] == "Yes"])
found_required = len(
    final_catalog[
        (final_catalog["RequiredForTraining"] == "Yes")
        & (final_catalog["FileStatus"] == "FOUND")
    ]
)

missing_required = total_required - found_required

total_files = len(final_catalog)
found_files = len(final_catalog[final_catalog["FileStatus"] == "FOUND"])
missing_files = len(final_catalog[final_catalog["FileStatus"] == "MISSING"])
error_files = len(final_catalog[final_catalog["FileStatus"] == "ERROR"])

final_checklist_data = [
    {
        "ChecklistItem": "Required files are present",
        "Expected": total_required,
        "Actual": found_required,
        "Status": "PASSED" if missing_required == 0 else "FAILED",
        "Recommendation": "All required files should exist before publishing the training."
    },
    {
        "ChecklistItem": "No unreadable files",
        "Expected": 0,
        "Actual": error_files,
        "Status": "PASSED" if error_files == 0 else "FAILED",
        "Recommendation": "Fix unreadable CSV, SQL, or TXT files before distribution."
    },
    {
        "ChecklistItem": "Core dimension files exist",
        "Expected": 3,
        "Actual": len(
            final_catalog[
                (final_catalog["FileGroup"] == "Dimensions")
                & (final_catalog["FileStatus"] == "FOUND")
            ]
        ),
        "Status": "PASSED",
        "Recommendation": "Dimensions are required for a clean star schema."
    },
    {
        "ChecklistItem": "Financial fact files exist",
        "Expected": 5,
        "Actual": len(
            final_catalog[
                (final_catalog["FileGroup"] == "Financial Facts")
                & (final_catalog["FileStatus"] == "FOUND")
            ]
        ),
        "Status": "PASSED",
        "Recommendation": "Financial fact tables support statements and core analysis."
    },
    {
        "ChecklistItem": "Ratio fact files exist",
        "Expected": 6,
        "Actual": len(
            final_catalog[
                (final_catalog["FileGroup"] == "Ratio Facts")
                & (final_catalog["FileStatus"] == "FOUND")
            ]
        ),
        "Status": "PASSED",
        "Recommendation": "Ratio fact tables support financial ratio analysis."
    },
    {
        "ChecklistItem": "SQL package exists",
        "Expected": 5,
        "Actual": len(
            final_catalog[
                (final_catalog["FileGroup"] == "SQL Server")
                & (final_catalog["FileStatus"] == "FOUND")
            ]
        ),
        "Status": "PASSED",
        "Recommendation": "SQL package supports database deployment."
    },
    {
        "ChecklistItem": "Power BI package exists",
        "Expected": 5,
        "Actual": len(
            final_catalog[
                (final_catalog["FileGroup"] == "Power BI")
                & (final_catalog["FileStatus"] == "FOUND")
            ]
        ),
        "Status": "PASSED",
        "Recommendation": "Power BI package supports semantic model creation."
    },
    {
        "ChecklistItem": "Executive dashboard files exist",
        "Expected": 4,
        "Actual": len(
            final_catalog[
                (final_catalog["FileGroup"] == "Executive Dashboard")
                & (final_catalog["FileStatus"] == "FOUND")
            ]
        ),
        "Status": "PASSED",
        "Recommendation": "Executive dashboard files support final presentation and reporting."
    }
]

final_checklist = pd.DataFrame(final_checklist_data)

# Correct group status logic based on expected vs actual
final_checklist["Status"] = final_checklist.apply(
    lambda row: "PASSED" if row["Actual"] >= row["Expected"] else "FAILED",
    axis=1
)

# ------------------------------------------------------------
# Step 6 - Create README content
# ------------------------------------------------------------

created_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

readme_content = f"""
Financial Ratios Analysis in BI
Final Training Package
Generated: {created_at}

============================================================
Cybersecurity / Production Warning
============================================================

This training package uses synthetic data for educational purposes.

Do not run scripts against production folders, real financial data,
real company accounting records, or organizational databases without:

- Written authorization
- Backups
- Testing in a non-production environment
- Least-privilege permissions
- Change-control approval
- Compliance with organizational cybersecurity protocols

============================================================
Course Purpose
============================================================

This course teaches how to build a complete financial ratios analysis
model for Business Intelligence using Python, CSV files, SQL Server
preparation, and Power BI model preparation.

The training starts from synthetic company and accounting data, builds
financial statements, calculates ratio families, introduces data quality
issues, prepares SQL Server scripts, prepares Power BI model metadata,
and ends with an executive dashboard dataset.

============================================================
Main Training Flow
============================================================

Topic 1  - Create Companies and Periods
Topic 2  - Create Chart of Accounts
Topic 3  - Generate Synthetic Trial Balance
Topic 4  - Validate Trial Balance
Topic 5  - Map Accounts to Financial Statements
Topic 6  - Build Income Statement
Topic 7  - Build Balance Sheet
Topic 8  - Build Cash Flow Indicators
Topic 9  - Create Market Data
Topic 10 - Liquidity Ratios
Topic 11 - Profitability Ratios
Topic 12 - Debt Ratios
Topic 13 - Operating Performance Ratios
Topic 14 - Cash Flow Indicator Ratios
Topic 15 - Investment Valuation Ratios
Topic 16 - Data Quality Issues
Topic 17 - SQL Server Financial Model
Topic 18 - Power BI Model Preparation
Topic 19 - Executive Financial Dashboard Dataset
Topic 20 - Final Training Package

============================================================
Core Outputs
============================================================

Dimensions:
- Dim_Company.csv
- Dim_Period.csv
- Dim_Account.csv

Financial Facts:
- Fact_Trial_Balance.csv
- Fact_Income_Statement.csv
- Fact_Balance_Sheet.csv
- Fact_Cash_Flow.csv
- Fact_Market_Data.csv

Ratio Facts:
- Fact_Liquidity_Ratios.csv
- Fact_Profitability_Ratios.csv
- Fact_Debt_Ratios.csv
- Fact_Operating_Performance_Ratios.csv
- Fact_Cash_Flow_Ratios.csv
- Fact_Investment_Valuation_Ratios.csv

Executive Dashboard:
- Executive_Financial_Dashboard.csv
- Executive_KPI_Summary.csv
- Company_Financial_Health_Score.csv
- Executive_Dashboard_Validation.csv

SQL Server:
- SQL_Create_Tables.sql
- SQL_Load_Data_Template.sql
- SQL_Validation_Queries.sql
- SQL_Financial_Views.sql

Power BI:
- PowerBI_Model_Tables.csv
- PowerBI_Relationships.csv
- PowerBI_Import_Checklist.csv
- PowerBI_Model_Validation.csv
- PowerBI_DAX_Measures.txt

============================================================
Recommended Final Use
============================================================

1. Use the CSV files for local Python and Power BI practice.
2. Use the SQL scripts to create a SQL Server version of the model.
3. Use the Power BI metadata files to build relationships and DAX measures.
4. Use the executive dashboard files to build the final BI report.
5. Use the data quality files to teach validation and audit thinking.

============================================================
Footer
============================================================

Created for learning-by-doing financial BI training.
More training resources: https://jobaqui.com
"""

# ------------------------------------------------------------
# Step 7 - Export final package files
# ------------------------------------------------------------

final_catalog.to_csv(
    base_path / "Financial_Ratios_Training_File_Catalog.csv",
    index=False
)

topic_summary.to_csv(
    base_path / "Financial_Ratios_Training_Topic_Summary.csv",
    index=False
)

final_checklist.to_csv(
    base_path / "Financial_Ratios_Training_Final_Checklist.csv",
    index=False
)

(base_path / "Financial_Ratios_Training_ReadMe.txt").write_text(
    readme_content,
    encoding="utf-8"
)

print("Financial_Ratios_Training_File_Catalog.csv created successfully.")
print("Financial_Ratios_Training_Topic_Summary.csv created successfully.")
print("Financial_Ratios_Training_Final_Checklist.csv created successfully.")
print("Financial_Ratios_Training_ReadMe.txt created successfully.")

# ------------------------------------------------------------
# Step 8 - Print final summary
# ------------------------------------------------------------

print()
print("==============================")
print("FINAL TRAINING PACKAGE SUMMARY")
print("==============================")

print("Total catalog files:", total_files)
print("Found files:", found_files)
print("Missing files:", missing_files)
print("Error files:", error_files)
print("Required files:", total_required)
print("Required files found:", found_required)
print("Missing required files:", missing_required)

print()
print("Files by group:")
print(
    final_catalog
    .groupby(["FileGroup", "FileStatus"])["FileName"]
    .count()
    .reset_index(name="NumberOfFiles")
)

print()
print("Final checklist:")
print(final_checklist)

print()
print("Missing required files:")
missing_required_files = final_catalog[
    (final_catalog["RequiredForTraining"] == "Yes")
    & (final_catalog["FileStatus"] != "FOUND")
]

if missing_required_files.empty:
    print("No missing required files. Final training package is ready.")
else:
    print(missing_required_files[["FileName", "FileGroup", "CreatedByTopic", "FileStatus"]])

print()
print("Final package files created:")

created_files = [
    "Financial_Ratios_Training_File_Catalog.csv",
    "Financial_Ratios_Training_Topic_Summary.csv",
    "Financial_Ratios_Training_Final_Checklist.csv",
    "Financial_Ratios_Training_ReadMe.txt"
]

for file_name in created_files:
    if (base_path / file_name).exists():
        print("-", file_name)

print()
print("==============================")
print("COURSE COMPLETED")
print("==============================")
print("Financial Ratios Analysis in BI training package completed successfully.")
print("You now have a complete synthetic financial BI model ready for Python, SQL Server, and Power BI practice.")
print()
print("Topic 20 completed successfully.")

financial_ratios_bi_training/Final_BI_Case_Study.md

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.