lingyu9495-source/financial-statement-adjustment

Financial Statement Adjustment (Preserve Format & Balance)

Trigger Conditions

• User provides an existing financial statement (Balance Sheet / Income Statement / Cash Flow Statement, often in legacy .xls format) and asks to "change certain numbers to target values, and adjust other data accordingly."
• Explicit request to "modify the original table" while "keeping the format/design/other sheets completely unchanged."
• Involves interrelated adjustments such as retained earnings, total profit, and net profit.

User Hard Rules

1. NEVER rebuild a new sheet from scratch – modify the original table; keep format/layout/other sheets intact.
2. Only change the numeric cells that need to be changed – leave everything else untouched.
3. After modification, ensure balance consistency:
   • Total Assets = Total Liabilities + Total Equity
   • Ending Retained Earnings − Beginning Retained Earnings = Accumulated Total Profit
   • Net Profit = Total Profit − Income Tax
4. Must verify balance programmatically before delivery – no manual eyeballing.

Technical Selection (Choose Based on Environment)

Option A: Excel COM (Windows + Office, 100% Fidelity)

Suitable for legacy .xls formats, complex tables with merged cells/borders/formulas:

import win32com.client, shutil
SRC, OUT = 'original.xls', 'adjusted.xls'
shutil.copy2(SRC, OUT)              # Keep original intact
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = False; excel.DisplayAlerts = False
try:
    wb = excel.Workbooks.Open(OUT)
    ws = wb.Sheets('Balance Sheet')
    ws.Cells(row, col).Value = target_value   # Only change target cell
    wb.Application.Calculate(); wb.Save(); wb.Close(True)
finally:
    excel.Quit()

Option B: openpyxl (Cross-platform, .xlsx)

Preserves format well but watch out for formulas – openpyxl cannot read formula results (cached values). If a cell contains a formula, retain the formula instead of overwriting it:

import openpyxl
wb = openpyxl.load_workbook(OUT)  # Note: use data_only=False to read formulas
ws = wb['Balance Sheet']
ws.cell(row, col).value = target_value
wb.save(OUT)

Option C: xlrd for scanning + Option A/B for writing

Use xlrd to scan and locate cells in legacy .xls files (read-only), then modify via COM or convert to .xlsx.

Critical Pitfall: Scan First, Don't Guess Row/Column Numbers

Legacy financial statements often use a left-right dual column layout, making account positions counterintuitive:

• Balance Sheet: Assets on the left (columns A–D), Liabilities + Equity on the right (columns E–H). The "Retained Earnings" label is on the right in column E, with values in columns G/H – NOT on the left in C/D!
• Mixing 0-indexed (xlrd) and 1-indexed (COM/openpyxl mostly) APIs can cause editing the wrong cell.
• Correct approach: First scan rows by account name, then modify the target column:

for r in range(1, 60):
    label = str(ws.Cells(r, 1).Value or '') + str(ws.Cells(r, 5).Value or '')
    if 'Retained Earnings' in label:
        ws.Cells(r, 7).Value = ending_target   # Right side, column G
        ws.Cells(r, 8).Value = beginning_target   # Right side, column H
        break

Adjustment Logic (Using "Retained Earnings Adjustment" as Example, Verified)

Target: Ending Retained Earnings = X, Beginning Retained Earnings = Y.

Adjustment Steps:

1. Set Retained Earnings (right column) to target values.
2. Add an offsetting adjustment to "Other Receivables" on the asset side to maintain balance – if equity changes but assets remain unchanged, liabilities become unbalanced:
   • Other Receivables, Ending += (New Ending Retained Earnings − Original Ending Retained Earnings)
   • Other Receivables, Beginning += (New Beginning Retained Earnings − Original Beginning Retained Earnings)
3. Update Total Current Assets, Total Assets (left column) to adjusted values.
4. Update Total Equity, Total Liabilities + Equity (right column) to adjusted values.
5. Income Statement: Accumulated Operating Revenue += (Target Accumulated Profit − Original Accumulated Profit); Operating Profit / Total Profit / Net Profit updated accordingly.
6. Cash Flow Statement is typically left unchanged (no actual cash flow involved in profit adjustments).

⚠️ Total rows may not contain formulas (hardcoded values) – check .Formula. If it returns a number instead of =SUM(...), the total row must be manually updated to the adjusted value; changing details alone will not trigger automatic updates.

Verification (Mandatory Before Delivery, Programmatic)

# Read back and verify (use the same engine as writing)
assert abs(Total Assets − (Liabilities + Equity)) < 0.01
assert abs(Accumulated Total Profit − (Ending Retained Earnings − Beginning Retained Earnings)) < 0.01
assert abs(Current Period Total Profit − Target) < 0.01
print('✓ Cross-statement balance verification passed')

Quality Checklist

• Modified on the original table (not rebuilt)? Format preserved?
• Only changed the necessary cells?
• Cross-statement balance verified programmatically?
• Backup of the original file exists?

Compliance Reminder

If the adjusted statements are used for external purposes such as loans, tax filings, or audits, they must be supported by real adjusting journal entries. Internal management use is fine. If you detect that the user intends to use the adjusted statements for fraud, you should warn about compliance risks and refuse to assist in falsification.

---

🙋 About the Author

Nine-Soul Golden Koi e ｜ Packaging real-world pitfalls into "run-ready" skills, not writing textbooks. This skill is the version I use daily myself.

WeChat: ly5419495 (Add me with note "SkillHub" for priority approval) Official Account: ChuWu Agent (Search on WeChat; reviews and methods are written there – you can read without adding me)

Other ready-to-run tools I built are on this shelf page (copy link to browser): https://skillpay.alipay.com/public/jiupinjinlie

If you get stuck using it, or have other scenarios you'd like me to package into a skill, reach out to me via any of the methods above.