A Guide for General Ledger to Subledger Reconciliation

What is general ledger to subledger reconciliation?

In the simplest terms, subledger to general ledger reconciliation is the process of comparing your subledger and general ledger, investigating the differences between them, and adjusting one and/or the other until they match.

By nature, a subledger and general ledger should contain the same data but with different level of account detail. If your Accounting team needs to reconcile the data in the balance of a general ledger account, then the operational transactions in your subledger weren’t properly calculated to match with the general ledger data.

Welcome to the ensuing chaos

This creates chaos for accounting. Inaccurate financial reporting and a loss of trust in the data can wreak havoc. They hurt your finance team’s ability to effectively make strategic decisions. Accounting is forced into an endless cycle of jumping between Excel and platforms to figure out what happened.

It’s easy to manipulate or delete transaction data, especially when Excel is used as a bridge between source data and the general ledger. A fat finger error here, an engineering mistake there, or a report pulled an hour earlier than normal and missing some of yesterday’s transaction, and all your calculations are skewed.

As a result, you’re often left with irreconcilable differences. What do you do when your subledger or general ledger accounts don’t include key transaction activities?

Teams spend hours and hours performing account reconciliations across many parts of the chart of accounts, from revenue to expense accounts to payables. Unfortunately, even after that, the best they can hope for is that any remaining differences are under a materiality threshold and that they can get it done without delaying the month-end close too much.

Reconciling differences in your accounts and tracking down the source of revenue numbers in your income statement (and downstream impacts to balance sheet accounts like bad debt liabilities) to make sure ending balances are right takes up as much as 50% of your accounting team’s time.

If the numbers don’t reconcile across your ledger and subledger accounts, a material misstatement or audit failure is only a matter of time. Unfortunately for most teams, there are almost always some irreconcilable differences each month.

These are the types of things that cause your CEO to send out emails on Friday evenings demanding to know why your income statement doesn’t look right before they walk into a firing squad board meeting on Monday morning. (And if your CEO is going to be stressed on the weekend, you better believe you will be, too!)

But you don’t have to live with this uncomfortable and unsatisfying situation. In this post, we’ll share some tips on how to untangle subledger to general ledger reconciliation.

General ledger to subledger reconciliation: Ultimate guide

We’ll walk through an example to make the concepts easier to apply to your scenarios. We’ll also share tips on how to make your accounting workflows more efficient.

Why is general ledger to subledger reconciliation so difficult?

There are a few reasons subledger to general ledger reconciliation is so complicated. We’ll cover some of the most common ones below.

1. Systems and data structure issues

Operational and finance data should, in theory, tell the same story. They should be able to account for different activities along the transaction journey, attribute those activities to the appropriate accounting periods, and generate a clear connection between source data, financial records, and appropriate journal entries in the subledger and general ledger.

Unfortunately, data mismatches cause out of balance ledger accounts frequently. The root of the problem is that the Order to Cash process isn’t designed to generate this clear connection. in fact it’s just the opposite, and a lot of relevant data get lost along the transaction journey from operational systems — like order management and fulfillment systems, billing systems, and payment processors — to ERP.

Twenty years ago, companies worked with much simpler data and business models. The ERP was the main accounting system of record and contained much of the operational data directly because it was used to process orders, generate invoices, and most if not all of the rest of the Order to Cash journey. Activities such as order entered, filled, invoiced, and paid, were all tracked in a single repository.

Today, the operational data needed to represent a single financial transaction is scattered across five, ten, or more operational systems, designed to specialize in different parts of the Order to Cash, and focused on the operation and processing steps, rather than as accounting systems.

Worse yet, many companies operate multiple business models, in multiple geographies, with multiple currencies or billing systems. Many also take payments through mobile app stores that have their own data standards and limitations.

While each of these systems may perform one or two steps of the Order to Cash workflow well, they aren’t designed with the whole process in mind, and they aren’t designed for the financial traceability that accounting needs to observe and report on the whole process, from initial order to the monthly financial reports. Moreover, Finance teams lose pieces of the transaction story along the way from initial order to the month-end statement.

Worse, mutable data across systems create inconsistencies in the data throughout the Order to Cash accounting process. To counter that, some of these operational tools do offer audit logs. But these aren’t designed for accounting, they’re primarily built to optimize their specific portion of the OTC process. They lack the level of insight that would make reconciliation easier.

Operational systems also record only the end states of the financial transactions. They update to the most recent modifications to optimize for performance. They don’t necessarily reflect all of the changes you made in between. 

For example, if you re-invoice a customer with adjusted line items, the new invoice may overwrite the existing invoice you sent out last month. As a result, your balance sheet may reflect the accounts receivable balance for last month that you can’t substantiate with operational data anymore.

Accountants handle these issues with new adjustment entries, but in invoicing system the old transaction, and the delta you need for an adjustment entry, no longer exists. Accounting has to recreate that in Excel. As they say, the road to hell is paved with good intentions.

This is how general ledger reconciliation becomes a holy mess. But that’s not all. These discrepancies can also cause downstream issues when it’s time to audit the books.

Mutable historical records make life a living hell for auditors, and thus for the internal teams whose work is being audited. They won’t be able to find the financial transactions that power your general ledger chart of accounts because there’s a lack of data lineage. All of a sudden, your audit bill goes through the roof and your weekend plans go out the door.

2. Rapidly developing business models

As business models have become more complex, so too has the subledger to general ledger reconciliation process.

Companies with high transaction volumes have a whole host of transaction activities to keep up with current customer demands.

This includes things like:

• Discounts (sometimes stacked, sometimes at the order level, sometimes for a line item)
• Promotions
• Mid-license upgrades or downgrades
• Cancellations
• Refunds
• Disputes

To address these customer behaviors and accurately recognize revenue according to accounting principles in your general ledger chart of accounts, accounting teams have to apply the appropriate revenue logic to each transaction activity and create the correct adjusting journal entries.

This requires a very tight financial accounting process. One that’s almost impossible to achieve. Further complicating things, high transaction volume businesses also tend to have multiple subledgers.

What this looks like in practice

Let’s take a fairly common subscription business scenario as an example.

Some subscription companies collect money from their customers before the start of a contract. That unearned revenue represents a liability on their balance sheet. When that happens, their subledger will contain data about those transactions.

However, it won’t correspond to the right T accounts and transactions in the general ledger. Why? Most subscription businesses have a subscription order system and a billing system, which are both subledgers of their general ledger. Each subledger only has part of the story, and they don’t talk accounting!

Now imagine this happening to millions of transactions every month. It causes your Accounting team to get stuck reconciling items in your general ledger ad infinitum.

Existing accounting policies, per ASC-606, are designed to help your digital business successfully report financial data monthly.

Unfortunately, most finance tech stacks are outdated because the proliferation of operational tools has moved all their financially relevant data out of the ERP and finance’s control. This leads to downstream effects with reporting inaccurate account balances and cash flows, material misstatement, and audit failure.

All of this emphasizes the need for accurate bookkeeping. You need accounting systems and operational tools in place to free your team from spending all of their time in spreadsheets and help them focus on forward-looking, strategic activities instead.

3. Segmentation

Segmentation analysis also makes subledger to general ledger reconciliation incredibly challenging.

Businesses analyze their operational and finance data across various segments, such as:

• product lines
• markets
• customer types
• locations and geographies

This adds significant complexity to the reconciliation process. Looking at aggregated financial reporting data at month end won’t help you figure out why your general ledger and subledger don’t line up.

To understand the full story, you have to analyze the segments that power your aggregated numbers. You have to be able to dive deeper into them individually so you can better reconcile items that don’t match. Unfortunately, there are a number of things that can slow you down in the process.

For example, what if there’s a change to a product at a certain point in time? Now the subledger becomes mutable to reflect that change.

But that change isn’t reflected in the general ledger. Since changes to segments like product lines, customer types, and others happen often in a scaling business, finance teams are often stuck trying to untangle and reconcile a huge mess of mutable and immutable data. They’ll never be able to figure out why debits don’t match credits.

Just imagine looking at mutable transactions at a point in time. It’s extremely difficult to compare them to locked transactions and trying to get 5 of 6 combinations of segments to match.

Doing this type of work wastes valuable time for Accounting. It messes with the accuracy and reliability of financial reports and is a huge red flag for auditors.

To avoid burdening Accounting with days of manual work, there’s a better way to reconcile your general ledger and your subledger. This is exactly what we’ll unpack in the next sections.

General ledger to subledger reconciliation steps

The general ledger to subledger reconciliation process generally consists of the following steps:

1. Compare general ledger balance to the sub-ledger balance
2. Investigate reasons for the difference
3. Adjust general and/or subledger
4. Compare adjusted balances

Of course, this is much easier said than done, and your team’s reconciliation process may include more steps. As pointed out in earlier, there are so many things that can cause your general ledger and subledger chart of accounts to differ. Here are 8 steps that can help you overcome that challenge.

General ledger to subledger reconciliation examples: An 8 step process

Here’s an example of an 8-step subledger to general ledger reconciliation process Leapfin’s Chief Architect Jason Berwanger employed in his past roles:

1. Generate a general ledger trial balance report with full segmentation for the period you wish to reconcile.
2. Run a Subledger report with data from the operational data store, your data warehouse, or your subledger system.
3. Map general ledger dates, segments, amounts to the schema/fields/columns of the subledger transaction columns.
4. Aggregate the subledger on the transaction date to match to accounting period, the current amount and the segmentation.
5. Compare the pivot and aggregation of subledger to general ledger.
6. Create a diff between the general & subledger with an adjustment amount.
7. Update your general ledger or subledger to account for these adjustments.
8. Repeat steps 1-5 to confirm accuracy and that both ledgers are in balance.

General ledger to subledger reconciliation best practices

There are two additional best practices worth calling out that can help you with the general to subledger reconciliation workflow.

Account for both data lineage and data provenance

Data lineage and data provenance are two related concepts. They are often used interchangeably but have different meanings.

Data lineage tracks the movement of data as it flows through different systems and applications.

Data provenance provides a more complete history of the data. This includes information about its origin, ownership, and any modifications it has undergone.

In short, data lineage focuses on the path that data takes. Data provenance focuses on the deeper history of the data, the circumstances it was created in, how it was altered, and so on.

Or, as one comment from Stack Overflow explains:

The key goal of a data lineage tool is data lifecycle management right from the data origination to the data exhaustion.

On the other hand, the key goal of data provenance is to specifically track the data origination and segregating data in three key stages. These stages are data-in-motion, data-in-process, and data-in-rest.

When data in your repository is immutable, data lineage and provenance mean the same thing. That’s because it’s unaltered and you can account for its state from its origin to its end state.

However, if your subledger data is mutable, that means your historical backup can be modified. And given change logs often don’t contain 100% of your historical data, you can easily lose track of data provenance.

From a finance systems perspective, subledger to general ledger reconciliation becomes much easier if your repositories can provide you with both data lineage and data provenance.

If you can drill down into records and review every change made to them, you can account for any differences between your general ledger and subledger — even if it takes a while.

Automate general ledger to subledger reconciliation

To further improve data lineage and provenance in your finance systems, you need to find a way to automate the subledger to general ledger reconciliation process.

In most cases, your Accounting team will find it impossible to stop operational data from changing. They’ll always operate around the constraints of makeshift subledgers and rigid and outdated general ledgers. Unfortunately, that is tedious work that gives you no strategic insight into how your business is doing.

Why should your teams have to spend so much effort for a low-value add task such as validating general ledger and subledger data?

What if you could free up their time to focus on analyzing your current cash management processes to improve your business’s cash position or creating new accounting policies to maximize revenues?

Automating the reconciliation process entirely can help your team break free from spreadsheets and discover new growth opportunities for the business, especially when dealing with a high volume of financial transactions.

To do so, you need to adopt a Operational to Financial data mindset, which requires you to rethink your existing Order to Cash accounting process. The best way to achieve end-to-end reconciliation is to create and centralize financial transaction data in one place.

This gives your team visibility into every transaction activity in your business. The easiest way to do so is to implement a Finance Data Platform.

How Leapfin streamlines general ledger to subledger reconciliation

The Leapfin Finance Data Platform provides both data lineage and provenance. It allows you to create and track all financial records connected to a transaction and the changes made to them — not just their end state. Leapfin is also built from the ground up with Accounting, Finance and high transaction volume businesses in mind.

The platform provides an accurate, unified, and real-time view of every financial transaction. You can analyze the flow of finance data through your business by integrating every operational tool into one system.

With centralized data, you can automatically create journal and ledger entries that reflect the current state of your business.

How?

Leapfin ingests operational data from every system – from payment service providers and order management systems to billing systems and app stores. It turns operational data into Financial Records, linking related activities to each other in a Finance Data Graph. It also maintains a link for each of those activities back to the source data. 

Then, Leapfin automatically applies revenue logic to transaction data. It creates journal entries that reflect every turn of a transaction. Finally, it generates ledger entries and publishes them automatically into your general ledger in your ERP.

To ensure data immutability Leapfin locks each transaction activity to the appropriate accounting period. This means your general ledger and subledger data always match. This allows you to:

• View performance for different business segments
• Generate financial reports filtered by dimensions including geography, market, product type, and time period. Feel confident in your financials,
• Free up Accounting team time, and conduct stress-free audits. 

This allows you to focus on the activities that help your business move the needle. You’ll be able to create tighter accounting policies and identify new growth levers for your business.