Topic 2 — Create the Chart of Accounts
This topic creates Dim_Account.csv, the accounting language of the entire training. It defines assets, liabilities, equity, revenue, expenses, cash flow items, and market data items.
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.
Before automating any real financial or accounting process, validate backups, permissions, least-privilege access, change-control approval, sandbox testing, audit requirements, and organizational cybersecurity protocols.
Business Scenario
We already have companies and fiscal periods from Topic 1. Now we need a serious Chart of Accounts so every company follows the same financial structure.
Each account is classified by Account Type, Financial Statement, Financial Category, Normal Balance, and Ratio Category.
Step-by-Step Practice
Step 1 — Import Libraries
import pandas as pd
from pathlib import PathStep 2 — Set the Project Folder
base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)
print(f"Project folder ready: {base_path.resolve()}")Step 3 — Create the Chart of Accounts
import pandas as pd
from pathlib import Path
base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)
accounts_data = [
# Assets
{"AccountID":1000,"AccountNumber":"1000","AccountName":"Cash and Cash Equivalents","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Cash available for short-term obligations"},
{"AccountID":1100,"AccountNumber":"1100","AccountName":"Accounts Receivable","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Amounts owed by customers"},
{"AccountID":1200,"AccountNumber":"1200","AccountName":"Inventory","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Goods available for sale or production"},
{"AccountID":1300,"AccountNumber":"1300","AccountName":"Prepaid Expenses","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Assets","NormalBalance":"Debit","RatioCategory":"Liquidity","Description":"Expenses paid in advance"},
{"AccountID":1500,"AccountNumber":"1500","AccountName":"Property Plant and Equipment","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Long-term productive assets"},
{"AccountID":1600,"AccountNumber":"1600","AccountName":"Accumulated Depreciation","AccountType":"Contra Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Credit","RatioCategory":"Operating Performance","Description":"Cumulative depreciation of fixed assets"},
{"AccountID":1700,"AccountNumber":"1700","AccountName":"Intangible Assets","AccountType":"Asset","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Assets","NormalBalance":"Debit","RatioCategory":"Valuation","Description":"Non-physical assets such as software or patents"},
# Liabilities
{"AccountID":2000,"AccountNumber":"2000","AccountName":"Accounts Payable","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Liquidity","Description":"Amounts owed to suppliers"},
{"AccountID":2100,"AccountNumber":"2100","AccountName":"Short-Term Debt","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Debt due within one year"},
{"AccountID":2200,"AccountNumber":"2200","AccountName":"Accrued Expenses","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Current Liabilities","NormalBalance":"Credit","RatioCategory":"Liquidity","Description":"Expenses incurred but not yet paid"},
{"AccountID":2500,"AccountNumber":"2500","AccountName":"Long-Term Debt","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Debt due after one year"},
{"AccountID":2600,"AccountNumber":"2600","AccountName":"Deferred Tax Liability","AccountType":"Liability","FinancialStatement":"Balance Sheet","FinancialCategory":"Non-Current Liabilities","NormalBalance":"Credit","RatioCategory":"Debt","Description":"Future tax obligations"},
# Equity
{"AccountID":3000,"AccountNumber":"3000","AccountName":"Common Stock","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Valuation","Description":"Capital contributed by shareholders"},
{"AccountID":3100,"AccountNumber":"3100","AccountName":"Additional Paid-In Capital","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Valuation","Description":"Capital paid above par value"},
{"AccountID":3200,"AccountNumber":"3200","AccountName":"Retained Earnings","AccountType":"Equity","FinancialStatement":"Balance Sheet","FinancialCategory":"Equity","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Accumulated profits retained in the business"},
# Revenue and Expenses
{"AccountID":4000,"AccountNumber":"4000","AccountName":"Revenue","AccountType":"Revenue","FinancialStatement":"Income Statement","FinancialCategory":"Revenue","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Total sales or service income"},
{"AccountID":4100,"AccountNumber":"4100","AccountName":"Other Income","AccountType":"Revenue","FinancialStatement":"Income Statement","FinancialCategory":"Other Income","NormalBalance":"Credit","RatioCategory":"Profitability","Description":"Income from non-core activities"},
{"AccountID":5000,"AccountNumber":"5000","AccountName":"Cost of Goods Sold","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Cost of Revenue","NormalBalance":"Debit","RatioCategory":"Profitability","Description":"Direct cost of goods or services sold"},
{"AccountID":6000,"AccountNumber":"6000","AccountName":"Salaries and Wages Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Employee compensation expense"},
{"AccountID":6100,"AccountNumber":"6100","AccountName":"Rent Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Facility rental expense"},
{"AccountID":6200,"AccountNumber":"6200","AccountName":"Utilities Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Utility costs"},
{"AccountID":6300,"AccountNumber":"6300","AccountName":"Marketing Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Advertising and promotion costs"},
{"AccountID":6400,"AccountNumber":"6400","AccountName":"Depreciation Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Operating Expenses","NormalBalance":"Debit","RatioCategory":"Operating Performance","Description":"Periodic depreciation expense"},
{"AccountID":7000,"AccountNumber":"7000","AccountName":"Interest Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Non-Operating Expenses","NormalBalance":"Debit","RatioCategory":"Debt","Description":"Cost of borrowed funds"},
{"AccountID":7100,"AccountNumber":"7100","AccountName":"Tax Expense","AccountType":"Expense","FinancialStatement":"Income Statement","FinancialCategory":"Tax Expense","NormalBalance":"Debit","RatioCategory":"Profitability","Description":"Income tax expense"},
# Cash Flow and Market Data
{"AccountID":8000,"AccountNumber":"8000","AccountName":"Operating Cash Flow","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Operating Cash Flow","NormalBalance":"Debit","RatioCategory":"Cash Flow","Description":"Cash generated from operations"},
{"AccountID":8100,"AccountNumber":"8100","AccountName":"Capital Expenditures","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Investing Cash Flow","NormalBalance":"Credit","RatioCategory":"Cash Flow","Description":"Cash spent on long-term assets"},
{"AccountID":8200,"AccountNumber":"8200","AccountName":"Dividends Paid","AccountType":"Cash Flow Item","FinancialStatement":"Cash Flow Statement","FinancialCategory":"Financing Cash Flow","NormalBalance":"Credit","RatioCategory":"Cash Flow","Description":"Cash distributed to shareholders"},
{"AccountID":9000,"AccountNumber":"9000","AccountName":"Share Price","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Market price per share"},
{"AccountID":9100,"AccountNumber":"9100","AccountName":"Shares Outstanding","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Number of shares outstanding"},
{"AccountID":9200,"AccountNumber":"9200","AccountName":"Market Capitalization","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Share price times shares outstanding"},
{"AccountID":9300,"AccountNumber":"9300","AccountName":"Enterprise Value","AccountType":"Market Data","FinancialStatement":"Market Data","FinancialCategory":"Market Valuation","NormalBalance":"N/A","RatioCategory":"Valuation","Description":"Market cap plus debt minus cash"}
]
Step 4 — Export Dim_Account to CSV
dim_account = pd.DataFrame(accounts_data)
dim_account.to_csv(base_path / "Dim_Account.csv", index=False)
print("Dim_Account.csv created successfully.")Step 5 — Validate the Number of Accounts
print("Total accounts:", len(dim_account))
dim_account.groupby("AccountType")["AccountID"].count().reset_index(name="NumberOfAccounts")Step 6 — Validate Financial Statement Mapping
statement_summary = (
dim_account
.groupby("FinancialStatement")["AccountID"]
.count()
.reset_index(name="NumberOfAccounts")
)
statement_summaryStep 7 — Review Accounts by Ratio Category
ratio_summary = (
dim_account
.groupby("RatioCategory")["AccountID"]
.count()
.reset_index(name="NumberOfAccounts")
.sort_values("RatioCategory")
)
ratio_summaryStep 8 — Preview the Final Chart of Accounts
dim_account[["AccountNumber","AccountName","AccountType","FinancialStatement","FinancialCategory","NormalBalance","RatioCategory"]].head(15)Step 9 — Check Created Files
for file in base_path.glob("*.csv"):
print(file.name)Business Interpretation
This topic creates the accounting vocabulary of the BI model. Cash supports liquidity analysis. Debt supports debt analysis. Revenue supports profitability. PP&E supports operating performance. Share price supports valuation.
After this topic, the project folder should contain Dim_Company.csv, Dim_Period.csv, and Dim_Account.csv.