# Generated migration to add comprehensive Chart of Accounts from django.db import migrations from decimal import Decimal def create_comprehensive_accounts(apps, schema_editor): """ Add comprehensive microfinance Chart of Accounts with all necessary operational accounts. Based on microfinance industry best practices and regulatory requirements. """ Account = apps.get_model('accounting', 'Account') # Define comprehensive accounts organized by type accounts_to_create = [ # ═══════════════════════════════════════════════════════════ # ASSETS (100000 - 199999) # ═══════════════════════════════════════════════════════════ # Current Assets - Cash & Bank (101000-101999) ('101001', 'Petty Cash', 'asset', 'current_asset', 'Small cash on hand for minor expenses', False), ('101002', 'Cash - Secondary Branch', 'asset', 'current_asset', 'Cash held at secondary branch location', False), # Mobile Money Accounts (102000-102999) ('102001', 'Airtel Money Account', 'asset', 'current_asset', 'Airtel Money mobile wallet balance', False), ('102002', 'T-Kash Account', 'asset', 'current_asset', 'T-Kash mobile wallet balance', False), # Loan Portfolio & Receivables (110000-119999) ('110001', 'Allowance for Loan Losses', 'asset', 'loan_portfolio', 'Provision for expected credit losses on loan portfolio', False), ('110002', 'Loan Loss Reserve', 'asset', 'loan_portfolio', 'Reserve fund for defaulted loans', False), ('110003', 'Written-Off Loans', 'asset', 'loan_portfolio', 'Loans written off as uncollectible', False), ('110004', 'Late Payment Penalties Receivable', 'asset', 'current_asset', 'Penalties charged on overdue loan payments', False), ('110005', 'Penalty Interest Receivable', 'asset', 'current_asset', 'Additional interest on late payments', False), # Fixed Assets (120000-129999) ('120001', 'Office Furniture & Fixtures', 'asset', 'fixed_asset', 'Desks, chairs, cabinets and office furnishings', False), ('120002', 'Computer Equipment', 'asset', 'fixed_asset', 'Computers, laptops, servers and IT hardware', False), ('120003', 'Office Equipment', 'asset', 'fixed_asset', 'Printers, scanners, photocopiers, phones', False), ('120004', 'Motor Vehicles', 'asset', 'fixed_asset', 'Company vehicles used for business operations', False), ('120005', 'Leasehold Improvements', 'asset', 'fixed_asset', 'Improvements made to rented office space', False), ('120006', 'Accumulated Depreciation - Furniture', 'asset', 'fixed_asset', 'Cumulative depreciation on office furniture', False), ('120007', 'Accumulated Depreciation - Equipment', 'asset', 'fixed_asset', 'Cumulative depreciation on computer and office equipment', False), ('120008', 'Accumulated Depreciation - Vehicles', 'asset', 'fixed_asset', 'Cumulative depreciation on motor vehicles', False), # Other Assets (130000-139999) ('130001', 'Security Deposits', 'asset', 'other_asset', 'Deposits paid for office rent and utilities', False), ('130002', 'Prepaid Insurance', 'asset', 'other_asset', 'Insurance premiums paid in advance', False), ('130003', 'Prepaid Rent', 'asset', 'other_asset', 'Office rent paid in advance', False), ('130004', 'Software Licenses', 'asset', 'other_asset', 'Software subscription and license fees prepaid', False), # ═══════════════════════════════════════════════════════════ # LIABILITIES (200000 - 299999) # ═══════════════════════════════════════════════════════════ # Current Liabilities - Payroll & Statutory (201000-201999) ('201001', 'PAYE Payable', 'liability', 'current_liability', 'Pay As You Earn income tax withheld from employee salaries', False), ('201002', 'NHIF Payable', 'liability', 'current_liability', 'National Hospital Insurance Fund contributions payable', False), ('201003', 'NSSF Payable', 'liability', 'current_liability', 'National Social Security Fund contributions payable', False), ('201004', 'Housing Levy Payable', 'liability', 'current_liability', 'Affordable Housing Levy contributions payable', False), ('201005', 'HELB Payable', 'liability', 'current_liability', 'Higher Education Loans Board deductions payable', False), ('201006', 'Staff Salary Payable', 'liability', 'current_liability', 'Accrued salaries awaiting payment', False), ('201007', 'Staff Bonuses Payable', 'liability', 'current_liability', 'Performance bonuses owed to staff', False), ('201008', 'Staff Advances Recoverable', 'liability', 'current_liability', 'Salary advances given to staff for recovery', False), # Tax Liabilities (202000-202999) ('202001', 'Corporate Tax Payable', 'liability', 'current_liability', 'Corporate income tax owed to Kenya Revenue Authority', False), ('202002', 'VAT Payable', 'liability', 'current_liability', 'Value Added Tax collected and owed to KRA', False), ('202003', 'Withholding Tax Payable', 'liability', 'current_liability', 'Withholding tax deducted from vendor payments', False), ('202004', 'Excise Duty Payable', 'liability', 'current_liability', 'Excise duty on applicable services', False), # Other Current Liabilities (203000-203999) ('203001', 'Accounts Payable - Suppliers', 'liability', 'current_liability', 'Money owed to suppliers for goods and services', False), ('203002', 'Accrued Utilities', 'liability', 'current_liability', 'Electricity, water, internet bills unpaid', False), ('203003', 'Accrued Rent', 'liability', 'current_liability', 'Office rent expense incurred but not yet paid', False), ('203004', 'Accrued Professional Fees', 'liability', 'current_liability', 'Legal, audit, consulting fees owed', False), ('203005', 'Mobile Money Transaction Fees Payable', 'liability', 'current_liability', 'M-Pesa, Airtel Money fees owed to telcos', False), # Borrowings & Long-term Liabilities (210000-219999) ('210001', 'Bank Loan - KCB', 'liability', 'borrowings', 'Term loan from Kenya Commercial Bank', False), ('210002', 'Bank Loan - Equity Bank', 'liability', 'borrowings', 'Term loan from Equity Bank', False), ('210003', 'Shareholder Loan', 'liability', 'borrowings', 'Loan from company shareholders', False), ('210004', 'Interest Payable on Borrowings', 'liability', 'current_liability', 'Accrued interest on bank loans', False), # ═══════════════════════════════════════════════════════════ # EQUITY (300000 - 399999) # ═══════════════════════════════════════════════════════════ ('301002', 'Retained Earnings', 'equity', 'retained_earnings', 'Cumulative net income retained in the business', False), ('301003', 'Current Year Earnings', 'equity', 'current_earnings', 'Net profit or loss for the current fiscal year', False), ('301004', 'Share Capital', 'equity', 'share_capital', 'Capital invested by shareholders', False), ('301005', 'Reserves - Loan Loss', 'equity', 'reserves', 'Statutory reserve for potential loan defaults', False), # ═══════════════════════════════════════════════════════════ # INCOME (400000 - 499999) # ═══════════════════════════════════════════════════════════ # Late Fees & Penalties (402000-402999) ('402001', 'Late Payment Penalty Income', 'income', 'fee_income', 'Revenue from penalties on overdue loan payments', False), ('402002', 'Penalty Interest Income', 'income', 'interest_income', 'Additional interest charged on late payments', False), ('402003', 'Loan Restructuring Fees', 'income', 'fee_income', 'Fees charged for loan modification or rescheduling', False), # Other Income (403000-403999) ('403001', 'Recovery of Written-Off Loans', 'income', 'other_income', 'Collections on previously written-off loans', False), ('403002', 'Bank Interest Income', 'income', 'interest_income', 'Interest earned on bank account balances', False), ('403003', 'Investment Income', 'income', 'investment_income', 'Returns from short-term investments', False), ('403004', 'Miscellaneous Income', 'income', 'other_income', 'Other non-operating revenues', False), # ═══════════════════════════════════════════════════════════ # EXPENSES (500000 - 599999) # ═══════════════════════════════════════════════════════════ # Personnel Expenses (501000-501999) ('501002', 'NHIF Employer Contribution', 'expense', 'personnel', 'Employer portion of health insurance contributions', False), ('501003', 'NSSF Employer Contribution', 'expense', 'personnel', 'Employer portion of social security contributions', False), ('501004', 'Staff Training & Development', 'expense', 'personnel', 'Employee training, workshops, and professional development', False), ('501005', 'Staff Welfare & Benefits', 'expense', 'personnel', 'Medical allowances, team building, staff welfare programs', False), ('501006', 'Recruitment & Hiring Costs', 'expense', 'personnel', 'Job advertising, background checks, onboarding costs', False), # Occupancy Expenses (502000-502999) ('502001', 'Office Rent', 'expense', 'occupancy', 'Monthly rent for office premises', False), ('502002', 'Electricity & Power', 'expense', 'utilities', 'Electricity bills for office operations', False), ('502003', 'Water & Sewerage', 'expense', 'utilities', 'Water and sewerage charges', False), ('502004', 'Internet & Telecommunications', 'expense', 'utilities', 'Internet service, phone lines, mobile airtime', False), ('502005', 'Office Cleaning & Maintenance', 'expense', 'occupancy', 'Janitorial services and office upkeep', False), ('502006', 'Security Services', 'expense', 'occupancy', 'Security guard services and alarm systems', False), # Technology & Systems (503000-503999) ('503001', 'Software Subscriptions', 'expense', 'technology', 'Cloud software, SaaS platforms, productivity tools', False), ('503002', 'IT Support & Maintenance', 'expense', 'technology', 'Technical support, system maintenance, repairs', False), ('503003', 'Website Hosting & Domain', 'expense', 'technology', 'Web hosting, domain registration, SSL certificates', False), ('503004', 'Data & SMS Costs', 'expense', 'technology', 'SMS gateway, bulk messaging, data services', False), # Transaction & Banking Costs (504000-504999) ('504001', 'M-Pesa Transaction Fees', 'expense', 'financial_services', 'M-Pesa paybill, till number, and withdrawal charges', False), ('504002', 'Airtel Money Transaction Fees', 'expense', 'financial_services', 'Airtel Money payment and withdrawal fees', False), ('504003', 'Bank Charges & Commission', 'expense', 'financial_services', 'Bank account maintenance, transfer fees, commissions', False), ('504004', 'Payment Gateway Fees', 'expense', 'financial_services', 'Online payment processor fees (Stripe, Flutterwave, etc)', False), ('504005', 'Foreign Exchange Loss', 'expense', 'financial_services', 'Losses from currency exchange transactions', False), # Professional & Administrative (505000-505999) ('505001', 'Legal & Professional Fees', 'expense', 'professional_services', 'Lawyer fees, legal consultations, contract reviews', False), ('505002', 'Audit & Accounting Fees', 'expense', 'professional_services', 'External audit, accounting, tax preparation services', False), ('505003', 'Consulting Fees', 'expense', 'professional_services', 'Business consulting, advisory services', False), ('505004', 'Licenses & Permits', 'expense', 'administrative', 'Business licenses, regulatory permits, compliance fees', False), ('505005', 'Insurance Premiums', 'expense', 'administrative', 'Business insurance, liability coverage, professional indemnity', False), ('505006', 'Office Supplies & Stationery', 'expense', 'administrative', 'Paper, pens, files, printer supplies, office materials', False), ('505007', 'Printing & Photocopying', 'expense', 'administrative', 'Document printing, photocopying, binding services', False), # Marketing & Business Development (506000-506999) ('506001', 'Marketing & Advertising', 'expense', 'marketing', 'Digital ads, print media, radio, promotional materials', False), ('506002', 'Customer Acquisition Costs', 'expense', 'marketing', 'Campaigns, referral bonuses, lead generation', False), ('506003', 'Client Events & Relationship', 'expense', 'marketing', 'Client appreciation events, networking, gifts', False), ('506004', 'Business Development', 'expense', 'marketing', 'Partnership development, market research', False), # Travel & Transport (507000-507999) ('507001', 'Fuel & Mileage', 'expense', 'transport', 'Vehicle fuel, mileage reimbursements', False), ('507002', 'Vehicle Maintenance & Repairs', 'expense', 'transport', 'Car servicing, repairs, parts replacement', False), ('507003', 'Vehicle Insurance', 'expense', 'transport', 'Comprehensive and third-party vehicle insurance', False), ('507004', 'Staff Transport Allowance', 'expense', 'transport', 'Employee transport reimbursements', False), ('507005', 'Travel & Accommodation', 'expense', 'transport', 'Business travel, hotel accommodation, per diems', False), # Depreciation & Amortization (508000-508999) ('508001', 'Depreciation - Furniture & Fixtures', 'expense', 'depreciation', 'Depreciation expense for office furniture', False), ('508002', 'Depreciation - Computer Equipment', 'expense', 'depreciation', 'Depreciation expense for IT hardware', False), ('508003', 'Depreciation - Office Equipment', 'expense', 'depreciation', 'Depreciation expense for office machines', False), ('508004', 'Depreciation - Motor Vehicles', 'expense', 'depreciation', 'Depreciation expense for company vehicles', False), ('508005', 'Amortization - Software', 'expense', 'depreciation', 'Amortization of capitalized software costs', False), # Other Operating Expenses (509000-509999) ('509001', 'Bad Debt Expense', 'expense', 'loan_portfolio', 'Actual write-off of uncollectible loans', False), ('509002', 'Interest Expense on Borrowings', 'expense', 'financial_expenses', 'Interest paid on bank loans and shareholder loans', False), ('509003', 'Penalties & Fines', 'expense', 'administrative', 'Regulatory penalties, late filing fines', False), ('509004', 'Donations & CSR', 'expense', 'other_expenses', 'Corporate social responsibility, charitable donations', False), ('509005', 'Miscellaneous Expenses', 'expense', 'other_expenses', 'Other operating expenses not categorized elsewhere', False), ] # Create accounts created_count = 0 for code, name, acc_type, subtype, description, is_system in accounts_to_create: # Check if account already exists if not Account.objects.filter(code=code).exists(): Account.objects.create( code=code, name=name, account_type=acc_type, subtype=subtype, description=description, is_system_account=is_system, is_active=True, ) created_count += 1 print(f"✅ Successfully created {created_count} new accounts") def reverse_migration(apps, schema_editor): """Reverse migration - delete added accounts""" Account = apps.get_model('accounting', 'Account') # List of account codes to delete codes_to_delete = [ '101001', '101002', '102001', '102002', '110001', '110002', '110003', '110004', '110005', '120001', '120002', '120003', '120004', '120005', '120006', '120007', '120008', '130001', '130002', '130003', '130004', '201001', '201002', '201003', '201004', '201005', '201006', '201007', '201008', '202001', '202002', '202003', '202004', '203001', '203002', '203003', '203004', '203005', '210001', '210002', '210003', '210004', '301002', '301003', '301004', '301005', '402001', '402002', '402003', '403001', '403002', '403003', '403004', '501002', '501003', '501004', '501005', '501006', '502001', '502002', '502003', '502004', '502005', '502006', '503001', '503002', '503003', '503004', '504001', '504002', '504003', '504004', '504005', '505001', '505002', '505003', '505004', '505005', '505006', '505007', '506001', '506002', '506003', '506004', '507001', '507002', '507003', '507004', '507005', '508001', '508002', '508003', '508004', '508005', '509001', '509002', '509003', '509004', '509005', ] Account.objects.filter(code__in=codes_to_delete).delete() print(f"✅ Reversed migration: deleted added accounts") class Migration(migrations.Migration): dependencies = [ ('accounting', '0004_alter_journalentryline_credit_amount_and_more'), ] operations = [ migrations.RunPython(create_comprehensive_accounts, reverse_migration), ]