Reverse-engineering a chart of accounts you inherited

"Can we get consolidated revenue by region? It takes my team three days every month to build it in Excel."

Sounds like an automation request on the surface. If you’ve received such requests from Finance teams before, you and I both know the real assignment is aligning multiple charts of accounts, intercompany eliminations, and currency between different entities:

• Which accounts should be mapped to revenues, expenses, etc.
• Where are intercompany transactions removed?
• When did an acquired entity adopt the parent company's accounting definitions?

My team applies these decisions every month, often without describing them as rules. They live across mapping files, ERP configurations, prior reports, and the experience of the people responsible for the close.

A data team has to turn accumulated judgement into a repeatable system. A dashboard answers one question, but the record of how those numbers are defined improves every financial data project that follows.

Here’s my take on how a data engineer can get to those definitions without a month of meetings.

A chart of accounts is a history of the business

A chart of accounts determines where transactions land in the general ledger. It shapes what the company can report about revenue, costs, margins, assets, and liabilities.

The structure usually begins with an ERP implementation and reflects the company at that moment. Of course, the business then keeps evolving.

After a decade, the chart of accounts represents far more than an accounting structure. It has become a history of the company's reporting decisions, making it difficult to automate reports trying to show consolidated revenue by entity.

Revenue by region may require thousands of accounts to be grouped into a smaller set of categories. Some accounts contain both geography and product information, or changed meaning after a reorganization.

The data team sits between a current business question and years of accumulated decisions. So before touching SQL, you need to establish business requirements accurate to the way the organization operates today.

My tips to ease this hard work:

Start with the behaviour of the ledger

The account names provide clues, then the posting history provides evidence.

• A useful first step is to profile journal activity by account, entity, period and posting source.
• Look at first- and last-posted dates, manual versus automated entries, debit and credit patterns, close-only activity, and recently created accounts.
• These patterns help separate stable operating activity from adjustments and exceptions.

An account populated through a subledger batch usually reflects a defined business process. An account used through manual journals during quarter-end may depend on a policy or judgement that lives outside the system.

Even an awkward account name can reveal an old requirement. "Revenue - South America parts" suggests that the company once needed product and geography combined badly enough to encode both in the account structure.

The goal of profiling is to create a focused set of questions. Instead of asking finance to explain the entire ledger, you can bring forward the accounts, periods, and posting patterns that require interpretation.

Bring us evidence instead of broad questions

A secret about Finance teams: we answer specific questions far more effectively than abstract or vague questions.

"How does revenue work?" invites a general explanation.

"We found forty manual postings to a retired account during quarter-end close. What do they represent, and where should they appear in management revenue?" creates a decision.

For each metric, you need to establish the reporting purpose, the authoritative output, the included accounts and entities, the effective dates, the treatment of eliminations and the known exceptions.

Finance also needs to tell you who owns the definition and who approves changes.

The useful outcome is a clear relationship between each definition and its intended context. You give structure to that distinction, while Finance supplies the accounting and operating judgement behind it.

Not sure how to extract the best questions during the requirements gathering stage with your Finance stakeholder? Contact Maxa, we can help. 🙂

Reconcile until every difference has an explanation

Once the initial mapping is built, reconciliation becomes the main discovery tool.

Take a financial statement or management report that finance already trusts. Run the proposed logic against the raw ledger and compare every line.

Each variance points somewhere useful:

• A revenue difference may reveal eliminations applied during consolidation
• A cost line may expose an entity using a 13-period adjustment calendar
• A duplicated balance may come from overlapping effective dates in a mapping table

The strongest engineers I work with treat reconciliation as part of requirements discovery rather than a final quality check.

A simple reconciliation log can record the metric, variance, entities, periods, suspected cause, finance owner and final resolution. Over time, that log becomes a history of how the model earned trust.

It also creates a shared language between finance and data, and you gain the precision required to implement it consistently.

Preserve the decision behind the code

Suppose a controller says:

“Exclude freight rebills from net revenue starting in Q3 2023.”

The SQL may require only a few lines, but the underlying decision contains much more information.

Documenting the minute decisions gives finance a record to review in business language. It gives you a maintainable spec you can give to an engineer or coding agent, even after the people involved in the original project have moved on.

The same principle applies when new accounts appear. A durable model includes a process for detecting, reviewing, approving and testing unmapped activity. The mapping then becomes a governed financial asset rather than a one-time project artifact.

Case study: Calumet

Calumet Specialty Products Partners implemented a new ERP system in late 2017. According to a later SEC order, the rollout produced operating and reporting disruptions that limited the company's ability to maintain an effective internal control environment.

Q3 2017 results were filed seven weeks late with two disclosed material weaknesses. In March 2018 the company released 2017 earnings anyway. Eleven days later it disclosed that actual results would differ materially, and the shares fell more than eight percent. The annual report showed the earnings release had under-reported the 2017 net loss.

The system held every transaction, but the transition lost the rules that made the numbers trustworthy: which accounts rolled where, which reconciliations mattered, who signed off on what.

A ledger can be technically complete and still fail to produce a number anyone will stand behind, because the meaning of the accounts is a separate asset from the accounts themselves. Most projects do not end in an SEC order, but the quieter version of the same loss happens every time the understanding leaves with the person who recovered it.

Faster coding makes Finance and Data team collaboration critical

Coding agents can generate SQL, tests, and transformation models quickly, which moves the constraint upstream. What limits the work now is the precision of the logic the agent is handed. Give it the effective dates, fiscal calendars, exclusions and exceptions, and it carries finance's intent through the build. Leave it to infer those rules from account names, and it produces code that looks defensible and that no one in finance can sign.

Autopilot Spec works from the material where the rules already sit: mapping spreadsheets, recorded conversations, dashboard screenshots, the policy note last updated two controllers ago. It draws out the implicit logic, flags where two sources disagree, and turns what finance approves into a specification the data team and the agent both build from. The controller's sentence about freight rebills becomes a rule with a scope, an effective date, exceptions and a record of who approved it.

The dashboard delivers the answer the business needs this month. What outlasts it is the understanding recovered while building it, and a company that keeps that record starts its next financial data project with its own rules already written down.