Hauptbuch in Business Central mit Excel abstimmen

Reconciling the general ledger in Business Central does not have to start with an export. You perform the entire audit in Excel on live data from Business Central: You place the current month next to the previous month, budget, and previous year, reconcile the subledgers for customers, vendors, and banks with their control accounts in the general ledger, let conditional formatting highlight every deviation, and jump directly from a conspicuous cell to the posting. If a late posting is added, you refresh instead of exporting again. In this article, Thomas Werkhoven, Managing Director of Exsion365 and still a controller at heart, shows how he audits his general ledger in Excel without the usual effort.

Why does general ledger auditing fail in practice?

Every controller knows the drill: Before the monthly closing, the general ledger is reviewed once more. You want to know if the postings are on the correct G/L account, if an account suddenly turns out significantly higher than in the previous month, and if an error is hidden somewhere. In itself, this is not complicated work, but an entire morning is quickly consumed.

The problem lies not in the audit itself, but in gathering the data. You export a G/L account overview, paste it into Excel, find the numbers for the previous month, and place both side by side. As soon as another posting comes in, you start from scratch. Thus, you spend more time preparing the numbers than questioning what is actually behind them.

In Business Central itself, G/L entries can be displayed; however, you click from the account to the entries and further to the underlying postings, screen by screen. Placing two periods side by side or comparing multiple companies is not easily possible. That is why many controllers resort to Excel; the only drawback of the old way is that the numbers get there manually and quickly become outdated.

Which subledgers do you reconcile with the general ledger?

A general ledger audit has two levels. The first is the plausibility check per G/L account: Does the balance fit compared to the previous month, budget, and previous year? The second is the reconciliation of the subledgers: Do the detailed tables in Business Central result in the balances of the control accounts in the general ledger? Business Central keeps both levels in separate tables, and a good audit reads them side by side.

[[TABLE_START]]

Audit | Detailed table in Business Central | Counterpart in General Ledger | What you compare

Customers | Customer Ledger Entry (21) and Detailed Cust. Ledg. Entry (379) | G/L Account (15), Accounts Receivable per Customer Posting Group | Sum of remaining amounts per posting group versus balance as of the key date

Vendors | Vendor Ledger Entry (25) | G/L Account (15), Accounts Payable per Vendor Posting Group | Sum of open vendor ledger entries versus balance as of the key date

Bank | Bank Account Ledger Entry (271) | G/L Entry (17) on the G/L account of the bank | Balance in the subledger versus general ledger balance per bank account

Plausibility check | G/L Entry (17) and G/L Budget Entry (96) | G/L Account (15), net change per period | Current month versus previous month, budget, and previous year per account

[[TABLE_END]]

A detail of Business Central is crucial here. The amounts of a customer ledger entry are FlowFields that sum up the Detailed Cust. Ledg. Entry table (379); all applications, payment discounts, and exchange rate differences are stored there. Anyone who only retrieves Table 21 might miss a payment applied yesterday in the reconciliation. Use 379 if the reconciliation is to match to the penny.

The classic cause of a difference is a direct posting to a control account. Someone posts a journal line directly to the accounts receivable account without a customer; the general ledger changes, the subledger does not, and the difference remains until the year-end closing. With both tables side by side in Excel, you see them on the very same day.

How do you reconcile the general ledger in Excel on live data from Business Central?

With Exsion Reporting, you fetch the tables from Business Central directly into Excel via the API, using your own Business Central permissions. No export step, no data warehouse, no Power BI license. These are the steps I go through.

Step 1: Retrieve G/L entries per account and period

Start with the G/L entries (Table 17), grouped by G/L account and posting date, filtered to the period you are closing. Exsion writes the query definition as a readable grid in the workbook (table, companies, fields, filters) so that a colleague can see exactly what is behind the numbers. The volume is not critical: 700,000 G/L entries load in under five seconds, and Excel remains responsive during the refresh.

Step 2: Place periods side by side

Next to the current month, I put the previous month, previous year, and the budget from the G/L Budget Entries (Table 96). If you prefer working with formulas rather than a data query, use Exsion's 16 live financial functions, such as account balance and account name, which accept Business Central's own filter syntax: 01-01-26..31-03-26 for a date range, 4000..4999|6100 for an account range. Microsoft describes the syntax in the instructions for filter criteria. Keep the number of live formulas under about 40,000 to 50,000 per workbook; beyond that, a data query into a PivotTable is faster.

Step 3: Reconcile subledgers

On a second sheet, I retrieve the Detailed Cust. Ledg. Entries (379) summed up per customer posting group, the Vendor Ledger Entries (25) per vendor posting group, and the Bank Account Ledger Entries (271) per bank account. Next to each sum, I place the balance as of the key date of the associated control account from the G/L Accounts (15). The difference column must result in zero in every row. For the bank side, the article on bank reconciliation in Excel describes the same setup in more detail.

Step 4: Conditional formatting handles the first review

Conditional formatting makes implausible deviations visible immediately. I color a cell if the month deviates by more than a fixed percentage from the previous month or budget, and if a reconciliation difference is not zero. This way, I can see at a glance whether the month's depreciation has been posted, whether an accrual is correct, or if an amount suddenly stands out.

Step 5: Click through to the posting

If something stands out, I click through to the underlying posting, all the way to the invoice. Show Details opens Business Central filtered to exactly the account and date range behind the cell, and double-clicking a pivot value shows the underlying rows in Excel (as long as the subset remains under about one million rows).

Step 6: Refresh instead of exporting again

If something changes in Business Central, I refresh with one click, and everything is up to date again. The comparison, the conditional formatting, and the reconciliation sheet recalculate. Once the audit is complete, Formulas to Values converts the workbook into fixed numbers for the closing documents.

How I audit my general ledger with Exsion

The most important check for me is the plausibility of the numbers. I place the current month next to the previous month, budget, and previous year. In Exsion, I select the new period, click Refresh, and exactly these comparisons are ready. No export, no copying.

After that, I go through a few balance sheet accounts and suspense accounts. Does the wage tax I pay this month match the declaration of the previous month? Are the bank balances correct? Do the accounts receivable and accounts payable accounts match their subledgers? Because the workbook is already set up, the entire run takes minutes instead of a morning.

What is the benefit of a general ledger audit on live data?

A concrete example: a tax posting that ended up on the wrong G/L account. In the overview, it stood out to me immediately because the account deviated from the previous month. Without this comparison, I would have found it only after the numbers had already been sent.

Furthermore, I set up a few checks before the actual general ledger audit. Are all banks up to date, are all contracts invoiced? A simple sign, for example a smiley face, shows me if my accounting is up to speed before I begin. This leaves fewer errors for the audit itself.

The biggest gain is in the cost of errors. You create the reporting once correctly and in the correct order, instead of correcting in hindsight. You audit instead of gathering data, and you keep time for the question that really matters: Why does this deviate?

[[TABLE_START]]

Part of the audit | Export and paste | Live in Excel with Exsion

Gathering numbers | Export per account or period, paste, place next to the previous month | Refresh once, comparisons are already standing

Reconciliation of subledgers | Separate exports of customer, vendor, and bank entries | Tables 379, 25, and 271 next to G/L account 15 on one sheet

A late posting is added | Start export from scratch | Refresh, differences recalculate

Finding the cause | Search in Business Central, screen by screen | Click through from the cell to the posting

Multiple companies | One export per company | Check the companies, the result gets a company column

[[TABLE_END]]

Does the audit change for the year-end closing?

For the year-end closing, the routine remains the same, only the tolerance is smaller. Every reconciliation difference must be explained or corrected before the auditor sees it, so the reconciliation sheet gets the most attention. Since the workbook already contains twelve months, the annual audit essentially consists of expanding the date filter and clicking Refresh.

Groups with multiple Business Central companies use the same workbook across all companies: Check the companies in the query grid, and the result gets a company column, so you can check the control accounts of each entity in one view. If you consolidate additionally, the instructions for consolidating multiple companies in Business Central with Excel build exactly on this setup.

Try it yourself

Would you like to see how this works for your own general ledger audit? You can install Exsion from AppSource in about five minutes, and the 30-day trial version includes all functions. We create the first report together with you, so you can see immediately how it works in your environment. More than 20,000 users already work with their Business Central data this way. Prices are openly available on the pricing page, and you can find more articles in our German-language blog.

Frequently Asked Questions

[[FAQ_START]]

What does reconciling the general ledger in Business Central mean? | It is the audit that a controller performs before closing: Each G/L account is compared with the previous month, budget, and previous year, and the subledgers for customers, vendors, and banks are reconciled with their control accounts in the general ledger. In Excel on live data, the entire audit runs from one workbook.

Which Business Central tables do I need for subledger reconciliation? | Use the Detailed Cust. Ledg. Entries (Table 379) for receivables, the Vendor Ledger Entries (25) for payables, and the Bank Account Ledger Entries (271) for bank balances, and compare each sum with the balance of the control account in the G/L Accounts (15) or with the G/L Entries (17).

Why doesn't my customer subledger match the general ledger? | The most common cause is a journal line posted directly to the accounts receivable account without a customer, so the general ledger changes but the subledger does not. If you place Table 379 next to G/L account 15 in Excel, you will see the difference per posting group.

Can I click through from Excel to the posting in Business Central? | Yes. Show Details opens Business Central filtered to exactly the account and date range behind a cell, and double-clicking a pivot value lists the underlying rows in Excel, as long as the subset remains under about one million rows.

Do I need Power BI or a data warehouse for this? | No. Exsion Reporting reads Business Central directly via the API with your own user permissions, so there is no data warehouse, no Azure SQL copy, and no Power BI license. Installation from AppSource takes about five minutes.

[[FAQ_END]]