Finance Audit

Overview

Comprehensive financial audit tool that validates balance sheets and income statements. Performs four core audits: balance sheet equilibrium, income statement verification, account change tracking, and cross-statement relationship validation.

Quick Start

Basic Balance Sheet Audit

Verify balance sheet with account changes:

scripts/reconcile.py \
    --balance-sheet balance_sheet.xlsx \
    --account-changes account_changes.xlsx \
    --output audit_report.xlsx

Complete Financial Audit (Balance Sheet + Income Statement)

Full audit including profit statement:

scripts/reconcile.py \
    --balance-sheet balance_sheet.xlsx \
    --account-changes account_changes.xlsx \
    --income-statement income_statement.xlsx \
    --income-details income_details.xlsx \
    --transactions transactions.xlsx \
    --output complete_audit.xlsx \
    --period "2024年1月"

Using Custom Column Names

Use JSON config file for custom column names:

scripts/reconcile.py \
    --balance-sheet bs.xlsx \
    --account-changes ac.xlsx \
    --config my_config.json

Command Parameters

| Parameter | Short | Required | Description | |-----------|-------|----------|-------------| | --balance-sheet | -b | Yes | Balance sheet file path | | --account-changes | -a | Yes | Account change details file path | | --income-statement | -i | No | Income statement file path | | --income-details | -d | No | Income statement details file path | | --transactions | -t | No | Transaction details file path | | --output | -o | No | Output Excel audit report path | | --period | -p | No | Audit period (default: 本期) | | --tolerance | - | No | Tolerance for acceptable difference (default: 0.01) | | --config | -c | No | JSON config file for column names | | --bs-sheet | - | No | Balance sheet sheet name or index | | --ac-sheet | - | No | Account changes sheet name or index | | --is-sheet | - | No | Income statement sheet name or index | | --id-sheet | - | No | Income details sheet name or index | | --trans-sheet | - | No | Transaction details sheet name or index |

Required File Formats

1. Balance Sheet (资产负债表) - Required

| Column | Description | |--------|-------------| | 科目 | Account name | | 期初余额 | Opening balance | | 期末余额 | Closing balance | | 类型 | Account type (资产/负债/所有者权益) | | 金额 | Amount for totals |

2. Account Changes (科目变动明细表) - Required

| Column | Description | |--------|-------------| | 科目 | Account name | | 借方 | Debit amount | | 贷方 | Credit amount |

3. Income Statement (利润表) - Optional

| Column | Description | |--------|-------------| | 项目 | Income statement item | | 金额 | Amount |

4. Income Details (利润明细表) - Optional

| Column | Description | |--------|-------------| | 项目 | Item name (must match income statement) | | 金额 | Detail amount |

5. Transactions (交易明细表) - Optional

| Column | Description | |--------|-------------| | 科目 | Affected account | | 借方 | Debit amount | | 贷方 | Credit amount | | 日期 (optional) | Transaction date | | 凭证号 (optional) | Voucher number |

See references/file_formats.md for detailed format specifications and custom column configuration.

Audit Objectives

The script performs four core audit objectives:

1. Balance Sheet Equilibrium Verification

Formula: 资产总计 = 负债总计 + 所有者权益总计

Verifies that the balance sheet is balanced overall.

2. Income Statement Verification

Logic: Income statement items = Corresponding detail summary

Validates each income statement item (revenue, expenses, etc.) against detail records.

3. Account Change Tracking

Logic: Opening Balance + Changes = Closing Balance

For each account:

• Asset accounts: Closing = Opening + Debit - Credit
• Liability/Equity accounts: Closing = Opening + Credit - Debit

4. Cross-Statement Relationship Validation

Logic: Retained earnings change ≈ Net profit

Validates relationships between balance sheet and income statement.

See references/verification_rules.md for detailed audit logic and troubleshooting.

Audit Outputs

Console Report

================================================================================
                              财务审计报告 - 本期
================================================================================

一、审计概述
--------------------------------------------------------------------------------
审计结果: 通过 ✓

二、资产负债表审计
--------------------------------------------------------------------------------
资产总计: ¥1,000,000.00
负债总计: ¥600,000.00
所有者权益总计: ¥400,000.00
资产负债平衡: 是 ✓

三、利润表审计
--------------------------------------------------------------------------------
利润项目匹配: 5/5

四、报表勾稽关系
--------------------------------------------------------------------------------
净利润: ¥100,000.00
未分配利润变动: ¥100,000.00
勾稽关系验证: 通过 ✓

五、不平衡项目清单
--------------------------------------------------------------------------------
  无不平衡项目

Excel Audit Report

When --output is specified, generates an Excel file with these sheets:

| Sheet | Content | |-------|---------| | 审计报告 | Full audit report text | | 科目变动分析 | Account analysis with opening/closing balances | | 交易影响追踪 | Transaction impact tracking | | 利润表核对 | Income statement verification results | | 不平衡项 | List of unbalanced items |

Account Change Analysis Table

| 科目 | 科目类型 | 期初余额 | 借方变动 | 贷方变动 | 净变动 | 期末余额 | 是否平衡 | 差异 | |------|----------|----------|----------|----------|--------|----------|----------|------| | 货币资金 | 资产 | 100000 | 20000 | 0 | 20000 | 120000 | 是 | 0 |

Using the Script as a Module

from scripts.reconcile import (
    audit_financial_statements,
    generate_audit_report,
    export_audit_report
)

result, accounts = audit_financial_statements(
    balance_sheet_file='balance_sheet.xlsx',
    account_changes_file='account_changes.xlsx',
    income_statement_file='income_statement.xlsx',
    income_details_file='income_details.xlsx',
    transactions_file='transactions.xlsx',
    tolerance=0.01,
    period='2024年1月'
)

# Print console report
print(generate_audit_report(result, period='2024年1月'))

# Export Excel report
export_audit_report(result, 'audit_report.xlsx', accounts, period='2024年1月')

# Check audit result
if result.is_passed:
    print("Audit passed!")
else:
    print(f"Found {len(result.unbalanced_items)} unbalanced items")

Tolerance Setting

Default tolerance is ±0.01 yuan. Adjust with --tolerance:

scripts/reconcile.py \
    --balance-sheet bs.xlsx \
    --account-changes ac.xlsx \
    --tolerance 1.0

Consider these factors when setting tolerance:

• Exchange rate fluctuations (for foreign currency)
• System precision differences
• Rounding errors

Troubleshooting

Balance Sheet Not Balanced

Possible causes:

• Incorrect balance entries
• Incorrect totals calculation
• Opening/closing mismatch

Solutions:

1. Check balance sheet total rows
2. Verify account balances
3. Check account type classification

Account Changes Unbalanced

Possible causes:

• Missing change records
• Incorrect debit/credit direction
• Account name mismatch

Solutions:

1. Ensure account names match exactly
2. Verify debit/credit records
3. Check for missing change records

Income Statement Not Matching

Possible causes:

• Missing detail items
• Inconsistent item names
• Incorrect summary calculation

Solutions:

1. Ensure detail table includes all items
2. Check item name consistency
3. Verify summary calculations

Cross-Statement Relationship Failed

Possible causes:

• Profit distribution (dividends, retained earnings)
• Mismatched accounting periods
• Incorrect net profit calculation

Solutions:

1. Consider profit distribution events
2. Verify income statement net profit
3. Confirm period consistency

Resources

scripts/reconcile.py

Core audit script supporting:

• Balance sheet equilibrium verification
• Income statement validation
• Account change tracking
• Cross-statement relationship validation
• Transaction impact tracing
• Comprehensive audit report generation

references/file_formats.md

Detailed file format specifications, column name mapping, and configuration options.

references/verification_rules.md

Comprehensive audit rules, validation logic, output examples, and troubleshooting guide.