Objective
Create Mapping_Financial_Statements.csv from Dim_Account.csv. This mapping prepares the model for the income statement, balance sheet, cash flow statement, and ratio calculations.
Production / Cybersecurity Warning: This exercise uses synthetic data only. Do not run scripts in production, real folders, corporate databases, or accounting environments without authorization, backups, testing, least-privilege permissions, change-control approval, and compliance with cybersecurity protocols.
Business Scenario
The chart of accounts contains individual accounts. BI needs a reporting map that tells each account where it belongs and how it supports analysis.
Dim_AccountFinancial Statement MappingFinancial StatementsRatiosDashboard
Step-by-Step Practice
Step 1 — Import, load, map, and export
from pathlib import Path
import pandas as pd
# ============================================================
# Financial Ratios Analysis in BI
# Topic 5 - Map Accounts to Financial Statements
# ============================================================
# ------------------------------------------------------------
# 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."
)
account_path = base_path / "Dim_Account.csv"
if not account_path.exists():
raise FileNotFoundError(
"Dim_Account.csv was not found. Please run Topic 2 first."
)
print(f"Project folder found: {base_path.resolve()}")
# ------------------------------------------------------------
# Step 2 - Load Dim_Account
# ------------------------------------------------------------
dim_account = pd.read_csv(account_path)
print("Dim_Account loaded:", len(dim_account), "rows")
# ------------------------------------------------------------
# Step 3 - Create Financial Statement Mapping
# ------------------------------------------------------------
# This mapping connects each account to a financial statement line.
# Later topics will use this file to build:
# - Income Statement
# - Balance Sheet
# - Cash Flow Statement
# - Ratio inputs
mapping_data = [
# --------------------------------------------------------
# Balance Sheet - Current Assets
# --------------------------------------------------------
{
"AccountNumber": "1000",
"AccountName": "Cash and Cash Equivalents",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Assets",
"StatementLine": "Cash and Cash Equivalents",
"LineGroup": "Current Assets",
"LineOrder": 101,
"SignMultiplier": 1,
"RatioInput": "Cash"
},
{
"AccountNumber": "1100",
"AccountName": "Accounts Receivable",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Assets",
"StatementLine": "Accounts Receivable",
"LineGroup": "Current Assets",
"LineOrder": 102,
"SignMultiplier": 1,
"RatioInput": "Accounts Receivable"
},
{
"AccountNumber": "1200",
"AccountName": "Inventory",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Assets",
"StatementLine": "Inventory",
"LineGroup": "Current Assets",
"LineOrder": 103,
"SignMultiplier": 1,
"RatioInput": "Inventory"
},
{
"AccountNumber": "1300",
"AccountName": "Prepaid Expenses",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Assets",
"StatementLine": "Prepaid Expenses",
"LineGroup": "Current Assets",
"LineOrder": 104,
"SignMultiplier": 1,
"RatioInput": "Other Current Assets"
},
# --------------------------------------------------------
# Balance Sheet - Non-Current Assets
# --------------------------------------------------------
{
"AccountNumber": "1500",
"AccountName": "Property Plant and Equipment",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Assets",
"StatementLine": "Property Plant and Equipment",
"LineGroup": "Non-Current Assets",
"LineOrder": 201,
"SignMultiplier": 1,
"RatioInput": "Fixed Assets"
},
{
"AccountNumber": "1600",
"AccountName": "Accumulated Depreciation",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Assets",
"StatementLine": "Accumulated Depreciation",
"LineGroup": "Non-Current Assets",
"LineOrder": 202,
"SignMultiplier": -1,
"RatioInput": "Accumulated Depreciation"
},
{
"AccountNumber": "1700",
"AccountName": "Intangible Assets",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Assets",
"StatementLine": "Intangible Assets",
"LineGroup": "Non-Current Assets",
"LineOrder": 203,
"SignMultiplier": 1,
"RatioInput": "Intangible Assets"
},
# --------------------------------------------------------
# Balance Sheet - Current Liabilities
# --------------------------------------------------------
{
"AccountNumber": "2000",
"AccountName": "Accounts Payable",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Liabilities",
"StatementLine": "Accounts Payable",
"LineGroup": "Current Liabilities",
"LineOrder": 301,
"SignMultiplier": 1,
"RatioInput": "Accounts Payable"
},
{
"AccountNumber": "2100",
"AccountName": "Short-Term Debt",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Liabilities",
"StatementLine": "Short-Term Debt",
"LineGroup": "Current Liabilities",
"LineOrder": 302,
"SignMultiplier": 1,
"RatioInput": "Short-Term Debt"
},
{
"AccountNumber": "2200",
"AccountName": "Accrued Expenses",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Liabilities",
"StatementLine": "Accrued Expenses",
"LineGroup": "Current Liabilities",
"LineOrder": 303,
"SignMultiplier": 1,
"RatioInput": "Accrued Expenses"
},
# --------------------------------------------------------
# Balance Sheet - Non-Current Liabilities
# --------------------------------------------------------
{
"AccountNumber": "2500",
"AccountName": "Long-Term Debt",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Liabilities",
"StatementLine": "Long-Term Debt",
"LineGroup": "Non-Current Liabilities",
"LineOrder": 401,
"SignMultiplier": 1,
"RatioInput": "Long-Term Debt"
},
{
"AccountNumber": "2600",
"AccountName": "Deferred Tax Liability",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Liabilities",
"StatementLine": "Deferred Tax Liability",
"LineGroup": "Non-Current Liabilities",
"LineOrder": 402,
"SignMultiplier": 1,
"RatioInput": "Deferred Tax Liability"
},
# --------------------------------------------------------
# Balance Sheet - Equity
# --------------------------------------------------------
{
"AccountNumber": "3000",
"AccountName": "Common Stock",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Equity",
"StatementLine": "Common Stock",
"LineGroup": "Equity",
"LineOrder": 501,
"SignMultiplier": 1,
"RatioInput": "Common Stock"
},
{
"AccountNumber": "3100",
"AccountName": "Additional Paid-In Capital",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Equity",
"StatementLine": "Additional Paid-In Capital",
"LineGroup": "Equity",
"LineOrder": 502,
"SignMultiplier": 1,
"RatioInput": "Additional Paid-In Capital"
},
{
"AccountNumber": "3200",
"AccountName": "Retained Earnings",
"FinancialStatement": "Balance Sheet",
"StatementSection": "Equity",
"StatementLine": "Retained Earnings",
"LineGroup": "Equity",
"LineOrder": 503,
"SignMultiplier": 1,
"RatioInput": "Retained Earnings"
},
# --------------------------------------------------------
# Income Statement - Revenue
# --------------------------------------------------------
{
"AccountNumber": "4000",
"AccountName": "Revenue",
"FinancialStatement": "Income Statement",
"StatementSection": "Revenue",
"StatementLine": "Revenue",
"LineGroup": "Revenue",
"LineOrder": 601,
"SignMultiplier": 1,
"RatioInput": "Revenue"
},
{
"AccountNumber": "4100",
"AccountName": "Other Income",
"FinancialStatement": "Income Statement",
"StatementSection": "Revenue",
"StatementLine": "Other Income",
"LineGroup": "Other Income",
"LineOrder": 602,
"SignMultiplier": 1,
"RatioInput": "Other Income"
},
# --------------------------------------------------------
# Income Statement - Cost of Revenue
# --------------------------------------------------------
{
"AccountNumber": "5000",
"AccountName": "Cost of Goods Sold",
"FinancialStatement": "Income Statement",
"StatementSection": "Cost of Revenue",
"StatementLine": "Cost of Goods Sold",
"LineGroup": "Cost of Revenue",
"LineOrder": 701,
"SignMultiplier": 1,
"RatioInput": "COGS"
},
# --------------------------------------------------------
# Income Statement - Operating Expenses
# --------------------------------------------------------
{
"AccountNumber": "6000",
"AccountName": "Salaries and Wages Expense",
"FinancialStatement": "Income Statement",
"StatementSection": "Operating Expenses",
"StatementLine": "Salaries and Wages Expense",
"LineGroup": "Operating Expenses",
"LineOrder": 801,
"SignMultiplier": 1,
"RatioInput": "Operating Expenses"
},
{
"AccountNumber": "6100",
"AccountName": "Rent Expense",
"FinancialStatement": "Income Statement",
"StatementSection": "Operating Expenses",
"StatementLine": "Rent Expense",
"LineGroup": "Operating Expenses",
"LineOrder": 802,
"SignMultiplier": 1,
"RatioInput": "Operating Expenses"
},
{
"AccountNumber": "6200",
"AccountName": "Utilities Expense",
"FinancialStatement": "Income Statement",
"StatementSection": "Operating Expenses",
"StatementLine": "Utilities Expense",
"LineGroup": "Operating Expenses",
"LineOrder": 803,
"SignMultiplier": 1,
"RatioInput": "Operating Expenses"
},
{
"AccountNumber": "6300",
"AccountName": "Marketing Expense",
"FinancialStatement": "Income Statement",
"StatementSection": "Operating Expenses",
"StatementLine": "Marketing Expense",
"LineGroup": "Operating Expenses",
"LineOrder": 804,
"SignMultiplier": 1,
"RatioInput": "Operating Expenses"
},
{
"AccountNumber": "6400",
"AccountName": "Depreciation Expense",
"FinancialStatement": "Income Statement",
"StatementSection": "Operating Expenses",
"StatementLine": "Depreciation Expense",
"LineGroup": "Operating Expenses",
"LineOrder": 805,
"SignMultiplier": 1,
"RatioInput": "Depreciation Expense"
},
# --------------------------------------------------------
# Income Statement - Non-Operating / Tax
# --------------------------------------------------------
{
"AccountNumber": "7000",
"AccountName": "Interest Expense",
"FinancialStatement": "Income Statement",
"StatementSection": "Non-Operating Expenses",
"StatementLine": "Interest Expense",
"LineGroup": "Non-Operating Expenses",
"LineOrder": 901,
"SignMultiplier": 1,
"RatioInput": "Interest Expense"
},
{
"AccountNumber": "7100",
"AccountName": "Tax Expense",
"FinancialStatement": "Income Statement",
"StatementSection": "Tax Expense",
"StatementLine": "Tax Expense",
"LineGroup": "Tax Expense",
"LineOrder": 1001,
"SignMultiplier": 1,
"RatioInput": "Tax Expense"
},
# --------------------------------------------------------
# Cash Flow Statement
# --------------------------------------------------------
{
"AccountNumber": "8000",
"AccountName": "Operating Cash Flow",
"FinancialStatement": "Cash Flow Statement",
"StatementSection": "Operating Activities",
"StatementLine": "Operating Cash Flow",
"LineGroup": "Operating Cash Flow",
"LineOrder": 1101,
"SignMultiplier": 1,
"RatioInput": "Operating Cash Flow"
},
{
"AccountNumber": "8100",
"AccountName": "Capital Expenditures",
"FinancialStatement": "Cash Flow Statement",
"StatementSection": "Investing Activities",
"StatementLine": "Capital Expenditures",
"LineGroup": "Investing Cash Flow",
"LineOrder": 1201,
"SignMultiplier": 1,
"RatioInput": "Capital Expenditures"
},
{
"AccountNumber": "8200",
"AccountName": "Dividends Paid",
"FinancialStatement": "Cash Flow Statement",
"StatementSection": "Financing Activities",
"StatementLine": "Dividends Paid",
"LineGroup": "Financing Cash Flow",
"LineOrder": 1301,
"SignMultiplier": 1,
"RatioInput": "Dividends Paid"
}
]
mapping = pd.DataFrame(mapping_data)
# ------------------------------------------------------------
# Step 4 - Validate mapping coverage
# ------------------------------------------------------------
trial_balance_accounts = dim_account[
dim_account["FinancialStatement"] != "Market Data"
].copy()
trial_balance_accounts["AccountNumber"] = trial_balance_accounts["AccountNumber"].astype(str)
mapping["AccountNumber"] = mapping["AccountNumber"].astype(str)
coverage_check = trial_balance_accounts.merge(
mapping[["AccountNumber", "StatementLine"]],
on="AccountNumber",
how="left"
)
unmapped_accounts = coverage_check[
coverage_check["StatementLine"].isna()
]
if len(unmapped_accounts) > 0:
print()
print("WARNING: Unmapped accounts found:")
print(unmapped_accounts[["AccountNumber", "AccountName"]])
else:
print("All trial balance accounts are mapped successfully.")
# ------------------------------------------------------------
# Step 5 - Export mapping files
# ------------------------------------------------------------
mapping.to_csv(
base_path / "Mapping_Financial_Statements.csv",
index=False
)
coverage_check.to_csv(
base_path / "Mapping_Coverage_Check.csv",
index=False
)
if len(unmapped_accounts) > 0:
unmapped_accounts.to_csv(
base_path / "Mapping_Unmapped_Accounts.csv",
index=False
)
print("Mapping_Financial_Statements.csv created successfully.")
print("Mapping_Coverage_Check.csv created successfully.")
# ------------------------------------------------------------
# Step 6 - Validation summary
# ------------------------------------------------------------
print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")
print("Total trial balance accounts:", len(trial_balance_accounts))
print("Total mapped accounts:", len(mapping))
print("Unmapped accounts:", len(unmapped_accounts))
print()
print("Mapping by FinancialStatement:")
print(
mapping
.groupby("FinancialStatement")["AccountNumber"]
.count()
.reset_index(name="NumberOfAccounts")
)
print()
print("Mapping by StatementSection:")
print(
mapping
.groupby(["FinancialStatement", "StatementSection"])["AccountNumber"]
.count()
.reset_index(name="NumberOfAccounts")
)
print()
print("Preview of Mapping:")
print(
mapping[
[
"AccountNumber",
"AccountName",
"FinancialStatement",
"StatementSection",
"StatementLine",
"LineGroup",
"LineOrder",
"RatioInput"
]
].head(20)
)
print()
print("Files currently in project folder:")
for file in base_path.glob("*.csv"):
print("-", file.name)
print()
print("Topic 5 completed successfully.")Expected Output
The script creates:
financial_ratios_bi_training/Mapping_Financial_Statements.csvValidation
The report checks that each account has a financial statement, statement section, and ratio category.
Business Interpretation
This topic creates the bridge between accounting structure and BI analysis. Without this mapping, the model cannot reliably build statements or ratios.
Final Result
After Topic 5, the account map is ready. Topic 6 will build the synthetic income statement.