--- id: financeiros/dre-consolidado-multiempresa industry: Financial services usage: widely used effort: medium grain: Entry x category allocation x company; P&L by accrual month x management account --- # Consolidated P&L across multiple companies A group P&L on an accrual basis built from one ERP account per CNPJ (company tax ID), with a management chart of accounts and no intercompany counted twice. ## Executive summary **For:** Accounting firms and outsourced finance (BPO) firms, and groups with several companies that need a single bottom line. **What it solves** - The group P&L comes from every company in the ERP at once, including the ones added later. - Transfers and loans between group companies stop inflating revenue and expenses. - Each company still matches the P&L in its own ERP. **Why it matters:** A group with one ERP account per CNPJ has no ready-made consolidated P&L anywhere. Adding up the companies without eliminating intercompany counts the same transfer as revenue in one and expense in the other, and the group margin becomes fiction. For a BPO firm, the same model replicated per client is what turns the month-end close into a product. **Business questions it answers** - What is the group's consolidated EBITDA for the month, without intercompany? - How much did the holding receive in transfers from the operating companies? - What is the gross margin by company and by business line? - Which expenses have no management account? - How do the quarter's actuals compare with the budget? - What is the group's cash balance at month end? **What you need** - Each group company connected to Nekt through its ERP (Omie, Conta Azul or Sankhya) - The list of group companies as they appear in the counterparty name - The mapping from ERP category to management account **What you get** - A consolidated management ledger, one row per entry, allocation and company - P&L by accrual month, per company and consolidated, with intercompany eliminated - An AI agent that answers questions about results using the group's rules - KPIs that match the P&L by construction ## Detailed ### The problem Omie and Conta Azul have one account per company, and each account becomes a set of tables with its own prefix. The group P&L requires combining all of them, exploding the category allocation, applying the sign once and removing transfers between group companies. Anyone who identifies intercompany by the entry description or with a rigid name pattern lets transfers through as operating expenses and delivers a margin the board does not recognize. ### Business rules 1. **Every company, with a source column.** Each company is its own connection and generates tables with its own prefix. Combine them all with UNION ALL and a company (or source) column filled with the name. Discover the companies from the table catalog (INFORMATION_SCHEMA) instead of a fixed list, so a new company comes in on its own. Sandbox accounts stay out. (Avoid: A fixed list of companies in the SQL, or answering about the group by looking at a single company.) 2. **Category and entry from the same company.** A category id is only valid within its company. Join entry and category by id and by company. The same applies to cost center, bank account and customer. (Avoid: Joining category by code only.) 3. **Allocation explodes the entry.** An entry can be allocated across several categories and cost centers (in Conta Azul, evento.rateio; in Omie, the allocation percentage and the departments array). Generate one row per allocation, with amount = entry amount x percentage / 100; a null percentage counts as 100%. In Conta Azul, the allocation's valor_bruto can be null in old records: use valor. (Avoid: Summing the entry total after exploding the allocation.) 4. **Sign applied once.** Revenue positive, costs and expenses negative. In Omie, natureza R = +1 and P = -1; if the column already comes signed, do not flip it again. The month's result is the sum of all rows in the accrual month. (Avoid: Applying the sign twice or adding revenue and expenses as absolute values.) 5. **Intercompany by counterparty, never by description.** Intercompany is identified only by the counterparty name (customer on revenue, supplier on expenses), uppercased, against a list of patterns for the group companies. Write patterns that tolerate spaces and variations, such as an acronym with and without a space before the number. A loan paid by a group company is also intercompany, never an operating expense. (Avoid: Searching for "repasse" or "intercompany" in the entry description.) 6. **Eliminate both sides in the consolidated view.** A transfer appears as revenue in one company and as an expense in the other. In the consolidated view, intercompany revenue comes out of gross revenue (gross revenue minus intercompany = group net revenue) and the matching expense comes out of costs and expenses. In the per-company view, it still appears. (Avoid: Eliminating only the revenue and leaving the transfer expense as an operating expense.) 7. **P&L by accrual date, cash by payment date.** The P&L uses the accrual date. Cash flow uses the payment date of actual settlements. When the ERP has no accrual date on the bill, state which date stands in and keep that criterion. (Avoid: Building the P&L by due date or by payment date.) 8. **Management chart of accounts via mapping.** Each ERP category points to a management account and a group (gross revenue, deductions, costs, operating expenses, financial result) in a mapping table per company. A category without a mapping falls into an unclassified line, which shows up in the P&L and never disappears. (Avoid: Using the ERP category name as a P&L line.) 9. **Misleading statuses and fields.** In Conta Azul, RENEGOTIATED comes with a zero total and is replaced by the new installments; canceled is excluded. For Conta Azul expenses, the actual amount is the pago field, not total. Automatic bank statement entries (Pix, fees, interest income) have no counterparty and are never intercompany. (Avoid: Summing every entry without looking at status.) ### Consolidated P&L structure By accrual month. Intercompany comes out before net revenue. - **Revenue from every company** → Gross revenue - **Counterparty is a group company** → (-) Intercompany revenue - **Taxes and deductions** → (-) Deductions - **What the group billed to outsiders** → = Group net revenue - **Direct cost of the service or product** → (-) Costs - **Net revenue minus costs** → = Gross profit - **Without intercompany** → (-) Operating expenses - **Gross profit minus operating expenses** → = EBITDA - **Interest, income, fees** → Financial result - **Category without a mapping** → Unclassified (`always visible`) ### Definitions | Metric | Definition | |---|---| | Consolidated gross revenue | sum of allocated revenue from every company, by accrual month | | Intercompany revenue | revenue whose counterparty matches the list of group companies, by accrual month | | Group net revenue | consolidated gross revenue minus intercompany revenue minus deductions, by accrual month | | Costs | allocated expenses mapped to the costs group, without intercompany | | EBITDA | group net revenue minus costs minus operating expenses, by accrual month | | EBITDA margin | EBITDA / group net revenue in the same period, summing before dividing | | Group cash balance | sum of the closing balance of every bank account of every company; past month = last day of the month, current month = latest available date | | Budget vs. actuals | amount by accrual month and management account from the budget against the P&L, with the budget source stated | ### Pitfalls - **Pattern without a space.** The intercompany rule looked for the acronym attached to the number. Two companies registered with a space slipped through as operating expenses, a six-digit amount over the period. How to avoid: Use space-tolerant patterns and test every company on the list. - **Omie time zone.** Omie stores the financial summary date as a UTC timestamp at 03:00. Converting without a time zone worked by coincidence, and any record before 3 a.m. would land on the previous day. How to avoid: Convert explicitly to America/Sao_Paulo. - **Financial summary showing zero.** One company's summary showed receivables and payables at zero while the bills table showed a significant overdue portfolio. How to avoid: From the summary, use only the balance; check the totals against the bills. - **Current month with a high margin.** In a group where the service cost is booked to the service month and paid the following month, the current month's P&L showed low costs and a high margin for about 30 days. How to avoid: Flag the month as provisional until the close at D+30. - **The wrong client's connection.** In a BPO firm, each client has its own ERP connection. A query used another client's connection and mixed portfolios. How to avoid: Write in the context document which connection belongs to each client. - **Made-up budget.** With no budget spreadsheet connected, the budget was generated as the average of the last 12 months plus a margin and came to be read as a target. How to avoid: Flag the budget source and replace it with the real spreadsheet as soon as it is connected. ### Acceptance test - [ ] For a closed month, each company's revenue and expenses match the P&L in its own ERP (difference below 0.5%). - [ ] Eliminated intercompany revenue equals eliminated intercompany expense in the same month, or the difference is explained by accrual timing. - [ ] No entry with a group counterparty appears in consolidated operating expenses. - [ ] The sum of the allocation rows for each entry equals the entry amount. - [ ] Every company in the catalog appears in the consolidated base, and no sandbox account does. ### Context document ```text Group consolidated P&L - Group companies: . Each one has tables with its own prefix in the ERP. Every question about "the group" uses the union of all of them, with the company column. - Entry and category are joined by id and company. - Allocation: one row per category and cost center; amount = amount x percentage / 100; null percentage = 100%. - Sign: revenue positive, costs and expenses negative, applied once. - Intercompany = counterparty (customer on revenue, supplier on expenses) that matches , compared uppercased and tolerant of spaces. Never by description. - In the consolidated view, eliminate intercompany revenue and expense. In the per-company view, show them. - P&L by accrual date; cash by payment date of settlements. - Exclude renegotiated and canceled statuses. For Conta Azul expenses, use pago. - A category without a mapping appears as unclassified. - Omie financial summary dates are in UTC: convert to America/Sao_Paulo. - The current month is provisional until D+. Per-client exceptions: - : this client's ERP connection is ; never use another client's. - : a loan paid by company to the group is intercompany. - : service cost identified by , except for the managing partners, who are operating expenses. ``` ### Variations - **Receivables reconciliation:** Gateway statement against the settlement in the ERP, matched 1 to 1 by receipt date and gross amount; status matched (difference up to R$ 1.00), divergent or one side only. - **13-week cash flow forecast:** Today's consolidated balance plus inflows by expected date minus outflows, with the lowest point and the week it happens; manual adjustments always explicit. - **Replicable data standard:** For BPO firms: the same model of entries, P&L, cash, bills and KPIs per client, with the management account mapping as the only custom part.