Automated Bank Reconciliation: Statement vs. Cash Ledger
ESCOPO
Ideal for financial analysts, controllers, accountants, treasury teams, and consultants who need to reduce manual work without losing control, evidence, or auditability. The generated answer includes Excel formulas in Portuguese, a detailed sheet layout, an optional VBA approach, and clear instructions to apply directly in the spreadsheet.
Conteudo
Prompt principal
Act as a senior specialist in controllership, treasury, financial audit, and advanced automation in Microsoft Excel. Your mission is to design an automated bank reconciliation solution between bank statements and the cash ledger, ready for implementation in corporate Excel. Prioritize traceability, robustness, maintenance by financial users, and appropriate controls for month-end closing. Before proposing the solution, analyze the context and the data I will paste below. If there are gaps that make a safe configuration impossible, first present a short section called "Critical Questions" with no more than 8 objective questions. If the data is sufficient, proceed without asking for confirmation. Do not invent fields, balances, or transactions; state assumptions explicitly. CONTEXT TO BE FILLED IN BY THE USER: - Excel version and formula language: [e.g., Microsoft 365 in Portuguese] - Company/industry and reconciliation objective: [fill in] - Reconciliation period and frequency: [daily, weekly, monthly] - Bank accounts involved, currencies, and banks: [fill in] - Acceptable tolerance for amount and date window: [e.g., R$ 0.01 and +/- 3 days] - Priority criteria for matching: [amount, date, document, description, ID, others] - Fields available in the STATEMENT: [paste headers and 5 to 20 masked rows] - Fields available in the CASH LEDGER/ERP: [paste headers and 5 to 20 masked rows] - Known rules: fees, PIX, TED, boletos, cards, deposits, internal transfers, reversals, check clearing, grouped entries, and duplicates: [fill in] - Constraints: [e.g., no Power Query, macro allowed, network file, LGPD] Follow this framework mandatory: 1. Diagnosis: compare the layouts, identify key fields, define the technical key for each entry, and classify data quality risks such as dates stored as text, inverted signs, duplicate amounts, inconsistent descriptions, and missing IDs. 2. Architecture: propose a file structure with the sheets "Parametros", "Extrato_Raw", "LivroCaixa_Raw", "Extrato_Base", "LivroCaixa_Base", "Regras", "Conciliacao", "Pendencias", "Dashboard" and "Log_Auditoria". Explain the purpose, columns, and recommended table format for each sheet. Use clear Excel Table names, such as tbExtrato and tbLivroCaixa. 3. Standardization: detail auxiliary columns to normalize date, absolute amount, debit/credit, document, description, counterparty, and unique identifier. Include formulas in Brazilian Portuguese, using structured references and modern functions when available. For each formula, specify exactly which column it should be entered in and provide a compatible alternative if the function does not exist in the informed version. 4. Matching engine: define layered rules in the following order: exact match by ID/document; exact date and amount; exact amount within the date window; match by normalized text; one-to-many or many-to-one match; and manual handling. Establish unambiguous statuses: "Conciliado automático", "Conciliado com tolerância", "Possível correspondência", "Pendente no extrato", "Pendente no livro caixa", "Duplicidade" and "Exceção". Explain how to prevent the same transaction from being reconciled twice. 5. Exceptions and controls: create rules for fees, interest, IOF, reversals, transfers between own accounts, grouped payments, and net deposits. Include data validations, conditional formatting, formula protection, audit trail, responsible/review date fields, and reconciliation of opening balance + movement = closing balance. 6. Automation: first recommend a no-macro solution. Then, if VBA is allowed, deliver complete, commented, ready-to-paste VBA code, with precise installation and execution instructions. The VBA must import/update bases, clear previous results without deleting raw data, execute exact reconciliations, log activity, and preserve manually reviewed entries. Do not provide pseudo-code. MANDATORY RESPONSE FORMAT: A. Executive summary of the solution and assumptions. B. Sheet map in table form: sheet, objective, table, columns, and responsible parties. C. Numbered, sequential step-by-step implementation. D. Block "Ready-to-paste formulas", with formula, destination column/sheet, explanation, and compatibility alternative. E. Reconciliation rules matrix, including priority, criterion, tolerance, status, and action. F. Complete VBA code, only if I authorize macros; otherwise, describe the automation without VBA and suggest Power Query only if permitted. G. Dashboard layout, with recommended KPIs, slicers, and charts. H. Testing, closing, and audit checklist. Respond in Brazilian Portuguese. Use semicolon separators in formulas in Portuguese, structured Excel Table references, and clear financial terminology. Do not provide generic recommendations: each instruction must be actionable, verifiable, and compatible with the data provided. At the end, highlight operational risks, limitations of automatic matching, and items that absolutely require human review.
