Accounting and financial services: data by client, consolidated
The standard for offices serving many clients: consolidate accounting and tax data for each person in a reliable and auditable model.
- See typical sources for an accounting office
- Consolidate financial data by customer
- Have an end-to-end implementation checklist
An accounting office doesn't have one system, it has dozens. Each client company lives in its own ERP, in its own system accounting and in its own bank, and the office needs to stitch everything together this in a single and reliable view, client by client. This playbook shows the pattern that Nekt uses to transform accounting data and tax spread in a model consolidated by client, correctly typed and auditable, where each number has a traceable origin.
1. Context: one office, many clients
The accounting office serves N client companies at the same time. Each one has its own way of operating: one issues notes through a system, another on the other; one uses a well-known ERP, the other a spreadsheet; each bank delivers the statement in a format. The office's job is to look after on top of all this and answer, for each customer, simple questions which in practice are difficult: how much did this company earn in the month, how much you spent, what the results are like, if the releases match the extract.
Two requirements guide this work and are valid for every client of the office:
- A consolidated and reliable view per customer: bringing together everything that comes from each company into a single model, with right values and right dates, so that the office sees each customer in a standardized way, without depending on opening each system in the hand.
- Traceability (audit): be able to prove where each number came. In accounting, it is not enough for the value to be right, it is I need to show the origin: which entry, which note, which extract gave that total. This protects the office and the client in any inspection or conference.
It is exactly this double objective, consolidating and tracking, that modeling at Nekt solves it, and it is what organizes everything else playbook.
2. Typical sources for an accounting office
An office's data comes from systems that almost never talk to each other each other. Each source delivers a piece, and the value appears when they are joined by client in a modeled layer. All these data sources fall raw, the way they came, in the layer Bronze.
- Accounting and tax systems. The heart of the die: chart of accounts, accounting entries, tax calculation, obligations. This is where each client's ledger comes from. There are often one system per customer, or the same system with each company in a separate base.
- Customer ERPs. The management system of each company: sales, purchases, accounts payable and receivable, payroll. Bring the operational detail that the accounting summarizes, and serves to reconcile the which was launched with what actually happened in the business.
- Bank statements. The actual movement of money: inflows, outflows, balances. They are the external proof against which releases are checked. Reconciling the ledger with the statement is a core office tasks.
- Invoices. Issued and received, with values, taxes, dates and CNPJ of the parties. Tie revenue and expenses to official documents, and feed both the tax calculation and the conciliation.
Here, more than in any other vertical, the reliability and correct typing matter. Values must be entered as a number, dates as a date, and CNPJ must be entered be treated as an identifier (with the digits preserved, without lose leading zeros). Each source speaks in its own way: one sends value with a comma, another with a period; one sends date as text, another as a number. Define from Bronze what is the source of each field by client prevents consolidation from mixing units and formats different.
3. Modeling recommended at Gold
The objective is to reach a few tables in the layer Gold, consolidated by client, each with a clear granularity. The raw sources of each system enter the Bronze, cleaning and typing takes place in Silver, and the final consolidated model (the tables we describe below) lives in Gold. This is a classic case of multi-source consolidation: many different systems, a common key (the client) and standardized fields.
- Releases of each accounting system
- ERPs, statements and invoices
- As it came, unedited, by customer
- Values, dates and CNPJ still as text
- Values as number, truth dates
- CNPJ standardized as identifier
- Fields aligned between systems
- Source of truth defined by field
- Consolidated ledger by customer and account
- Monthly financial summary per customer
- Common key: the customer
- This is where panel and MCP read from
Consolidated ledger (one line per journal entry)
The backbone of the model. Each line is an accounting entry, since standardized and tied to the customer, the chart of accounts account and the period. It doesn't matter which system the release came from: at Gold, everyone speak the same language. It is this table that allows you to open the details of any number and navigate to the origin.
Monthly financial summary (one line per customer per month)
The view from above. Each line is a customer in a month, with revenue, expenses and results already determined from the consolidated ledger. That's what feeds the office's financial dashboard and allows you to compare customers, look at month-to-month evolution and detect variations that deserve attention.
One line per customer per month, with the totals already calculated from of each company's consolidated ledger:
| month | customer | revenue | expense | result |
|---|---|---|---|---|
| 2026-04 | Aurora Bakery | R$ 182,400.00 | R$ 154,900.00 | R$ 27,500.00 |
| 2026-05 | Aurora Bakery | R$ 175,200.00 | R$ 161,800.00 | R$ 13,400.00 |
| 2026-04 | Vale Transport | R$ 340,000.00 | R$ 298,700.00 | R$ 41,300.00 |
| 2026-05 | Vale Transport | BRL 355,100.00 | R$ 351,900.00 | R$ 3,200.00 |
Comparing the column despesa between months, the office
immediately see that Transportes Vale had a strong variation in
expense in May. Every value here can be opened until release
in the consolidated ledger, and from the ledger to the note or statement of
origin. Illustrative values, not real data.
Keep the customer as a common key across all Gold tables. Each line, from ledger to summary, must carry the customer identifier (ideally the standardized CNPJ). This is what allows us to consolidate dozens of companies in the same table without mixing them, filter by customer at the time of consumption, and ensure that a customer number never leak into someone else's report.
4. Reliability and typing
In financial data, a format error is not a detail: it is a number wrong that goes unnoticed. Two fronts require care before consolidate anything.
Financial value needs to be typed as number, and date as real date. A value that arrives as text ("1,234.50") does not add up: either the pipeline breaks, or worse, it adds up wrong and no one notices. Dates as text get in the way of any cutting per period. And the CNPJ needs to be treated as an identifier, keeping all digits and leading zeros, never as a number (otherwise "01234..." becomes "1234..." and the customer stops knocking). Do all this typing in Silver, once, before consolidating. Each source has its own format (comma or period, date in different orders), then standardize everything for the same type before any summation.
Consolidating is not just stacking data, it is creating sources hit each other. The accounting ledger needs reconcile with the bank statement, and the invoices need tie in with what was released. When the numbers don't add up, that's a sign, not a mistake to hide: it could be missing release, note not registered or payment not reconciled. Model the reconciliation as part of Gold (e.g. a column marking the what hit and what didn't hit by client and period), so that the inconsistency appears and can be investigated, rather than being masked by a total that feels right.
5. Traceability: why keep Bronze untouched
In accounting, proving the origin of a number is worth as much as the number itself. This is where the layer Bronze it stops being a detail technician and becomes an insurance policy. How Bronze stores each source exactly as it arrived, without editing, it works like the record original of everything that entered.
When someone asks where a certain total came from on the panel, it gives to move down from Gold to the consolidated ledger in Silver, and from the ledger for the raw data in Bronze, arriving on the exact line of the invoice, entry or statement that originated that value. This intact chain is what sustains a audit: instead of "trust the number", the office shows "this number came from here, from this note, from this day". So the rule is simple: transform as desired in the following layers, but never edit the Bronze. The preserved raw data is the proof.
6. Use case: financial dashboard, alerts and agent
With the consolidated ledger and monthly summary in Gold, three consumptions appear almost for free on the same model.
One financial dashboard per customer read directly from the summary monthly: revenue, expenses and results of each company, month by month, side side by side. The office sees the entire portfolio in one place and opens the details of any customer without having to enter their system.
Inconsistency alerts rotate on the column reconciliation and about non-standard variations: a customer whose ledger did not match the statement for the month, an expense that jumped far above the average, a note without a corresponding release. Instead of discovering the problem at closing, the office is notified when it appears.
An AI agent, via MCP, answers questions in natural language on the same tables: "which customer had the highest expense variation this month", "which customers are rightly not reconciled", "what was the result of Transportes Vale in the last quarter". Whoever needs the answer asks and receives the number in the hour, without waiting for an analyst to put together the query.
What makes the agent trustworthy is the Semantic Layer describing these tables: what is each column, that the value is in reais, that the customer is the key, what means the reconciliation column. Without this description, the AI guesses and wrong. With it, the AI responds with the same rules that the panel uses, and the two hit. A consolidated model, several consumptions that never change contradict.
An accounting office served a large portfolio of companies, each one in its system, and put together a consolidated view of each customer in hand, month after month, with risk of error and no traceability easy. Bringing accounting systems, ERPs, statements and notes for Bronze, typing values, dates and CNPJ in Silver, and consolidating a ledger per customer and a monthly summary in Gold, the office began to have each client in a unique and auditable model. On top of the same layer, plugged in a financial panel per client, reconciliation and an agent who answers variation questions on the day a day. When a doubt arises about a number, the chain to the source note is always one click away.
7. Implementation checklist
The end-to-end path, in the order that usually makes sense. Each step is based on the previous one, so it's worth following it from top to bottom.