Validating your ledger accounts

One of the most important tasks for any accounting department is the periodic validation of the accounting system. This validation typically occurs monthly, during the period-end closing process. Assuming you stick to a regular schedule, validating your ledger controls should be a relatively straightforward routine. However, we don't live in a perfect world. Sometimes other priorities get in the way and the closing process can be delayed by weeks—or even several months.

The longer you wait to validate your accounting system, the harder and more complicated this process can become. Our hope is that this document will help you facilitate the validation process. You'll find several suggestions and approaches in this article, as well as tips and tricks along the way. It's important to note, though, that no two sets of books are exactly the same. The remedies for each system will vary from case to case. There are, however, common patterns and strategies that everyone should find useful, and those approaches are the focus of this article.

Ledger Control Report

A good place to start would be the ledger control screen. The ledger control report is available beginning with xTuple ERP version 4.10. The following screenshot shows the report, which can be found under Accounting > General Ledger > Reports

ledgercontrol_before

As you can see in the screenshot, the report groups the ledger control points into six core categories: Payables, Uninvoiced Receipts, Receivables, Inventory, WIP, Shipping Asset. Within each category, we see a difference reported—that is, t difference between general ledger activity and sub ledger activity for each of the controls. The goal, of course, is to show a difference of $0 for each category. If the difference does not equal to $0, then that means you have work to do analyzing the reason(s) for the difference and taking action to correct it.

Note: It's important to underscore these figures are summarized. In most, if not all, cases, the information shown is drawn from multiple accounts and document types.

To help you understand how the amounts for each control in the report is calculated, we've assembled the following tables. Each table identifies all the underlying sources used to calculate the amounts for each category. The idea is that knowing this underlying information should help make your analysis easier. As you discover problems and make corrections, be sure to re-query the ledger control report to see how your changes are impacting the results. You can continue doing this iteratively, until the difference is $0 for each category.

Payables
GL AMOUNT SUB LEDGER AMOUNT
Accounts from Payables Assignments:
A/P Account
Total value of all AP Open Items:
Accounting >  Payables > Reports > Open Payables
Uninvoiced Receipts
GL AMOUNT SUB LEDGER AMOUNT
Accounts from Cost Categories:
P/O Liability Clearing
Accounts from Expense Categories:
P/O Liability Clearing
Total value of PO Items received but not vouchered:
Accounting > Payables > Reports > Uninvoiced Receipts and Returns
Receivables
GL AMOUNT SUB LEDGER AMOUNT
Accounts from Receivables Assignments:
A/R Account, Deferred Revenue Account
Total value of all AR Open Items:
Accounting > Receivables > Reports > Open Receivables
Inventory
GL AMOUNT SUB LEDGER AMOUNT
Accounts from Cost Categories:
Inventory Asset
Total value of all Items in Inventory:
Inventory > Reports > Quantities on Hand > Posted Value
WIP
GL AMOUNT SUB LEDGER AMOUNT
Accounts from Cost Categories:
WIP Asset
Total value of all materials issued to WIP:
Manufacturing Desktop > Window > Desktop > Work Orders
Shipping Asset
GL AMOUNT SUB LEDGER AMOUNT
Accounts from Cost Categories:
Shipping Asset
Total value of all Items issued to Shipping:
Inventory > Shipping > Maintain Shipping Contents

Getting Started

So let's assume you've run the ledger control report and discovered you have differences in some (or all) six categories. You might be wondering, "What next?" Well, in this section, we'll describe a logical starting point, as well as point out some common gotchas which can explain accounting anomalies.

First Step: Identify accounting period where the problem began

Before you can analyze differences showing up in the ledger control report, you need to know the accounting period when the difference(s) first began to appear. Presumably, if you've been keeping on top of your control accounts somewhat regularly, the difference did not exist at some point in the past. The first challenge is to isolate the accounting period when the difference began to appear. Once you know that, you can dig deeper and analyze root causes. The following steps will help you identify the period you're looking for. The idea is to start with the current period and work your way backwards.

  1. Open the trial balance report for the current period.
  2. Locate the control account you're investigating and note the sum total of all GL activity for the current period. This total is listed in the trial balance's "Difference" column.
  3. Compare the trial balance difference to the open balance for the related Sub Ledger during the same period. For example, with receivables, query the AR aging report twice: once with an as-of date on the first of the month and the second time using the end of the month for the as-of date.
  4. Subtract the beginning amount from the ending amount. If the difference equals the trial balance difference, then you know the problem does not exist in the current period.
  5. Repeat the above steps with the next prior period.
  6. Keep moving backwards through time until you find a period where the two differences don't match.
  7. When you find this period, this is where the problem began.
  8. Begin to analyze root causes in the problem period.

Audit your account assignments

Your control accounts should only be used in the account assignments they were intended for. If your control accounts appear in non-standard assignments, this will cause (and explain) accounting irregularities. You should avoid using control accounts except where they are specifically expected. For example, your Inventory control account should be the inventory asset account in your cost categories. Don't use it anywhere else.

Carefully audit all your account assignments to make sure your control accounts are not used in unexpected assignments.

The impact of manual journal entries

If you make manual journal entries against your control accounts, know that these amounts are not reflected in your sub ledgers. For example, a credit journal entry posted to the AR account does not change your open receivables balance. These types of manual entries can easily explain differences showing up in the ledger control report.

Custom screen and/or report conflicts

Custom screens and custom reports can sometimes present inaccurate information—especially if you've upgraded recently and the customizations were not also upgraded at the same time. If you are running customizations, try disabling them temporarily. For example, try reverting to a grade = 0 report and see whether that impacts your results in a positive manner. Or disable a custom screen or script, if it's possible this is getting in the way.

Digging Deeper

In the next six sections, we've identified screens you should consider when analyzing discrepancies related to any of your control accounts. We've also added suggestions and tips to help you get started. Again, as we mentioned earlier, no one method is going to answer every accounting discrepancy in every accounting system. However, we can suggest patterns and places to look, as you work on validating your accounting controls for your system.

Payables

The following screens are key to helping you analyze your payables:

Open Payables

  • Accounting >  Payables > Reports > Open Payables
  • The open payables balance should match the trial balance for your primary payables ledger account.

AP Aging

  • Accounting >  Payables > Reports > Aging
  • The aging payables balance should match the trial balance for your primary payables ledger account.

Unposted Vouchers

  • Accounting >  Payables > Vouchers > List Unposted Vouchers
  • Check to make sure there are no unposted vouchers, as this could affect the amounts in the ledger control.

Payment Run - Unposted Payments

  • Accounting >  Payables > Payments > View Payment Run
  • Payments that may have been printed but are not yet posted will appear here.

Uninvoiced Receipts

When analyzing uninvoiced receipts, these are important screens to review and consider:

Uninvoiced Receipts and Returns

  • Purchase > Reports > Uninvoiced Receipts and Return 
    or
  • Accounting > Payables > Reports > Uninvoiced Receipts and Returns
  • Receipts or returns appearing on this screen have not yet been vouchered.

Unposted Receipts

  • Inventory > Receiving > List Unposted Receipts
  • Check to make sure you don't have saved, unposted receivings as this could affect your accounts.

Receivables

The following screens should be included in any analysis of your receivables:

Open Receivables

  • Accounting > Receivables > Reports > Open Receivables
  • The open receivables balance should match the trial balance for your primary receivables ledger account.

AR Aging

  • Accounting > Receivables > Reports > Aging
  • The aging receivables balance should match the trial balance for your primary receivables ledger account.

Unposted Invoices

  • Accounting > Accounts Receivable > Invoice > List Unposted
  • Invoices appearing on this list have not been posted to the general ledger.

Uninvoiced Shipments

  • Sales > Billing > Invoice > Uninvoiced Shipments
  • Sales orders/invoices on this list have been shipped but have not been invoiced yet.

Cash Receipt Edit List

  • Accounting > Accounts Receivable > Cash Receipts > Edit List
  • Cash receipts appearing on this list have not been posted to the general ledger.

Unapplied Customer Deposits

  • Sales > Customer > Workbench
  • Check this screen for unapplied customer deposits. When customer deposits are applied, general ledger activity is posted to the control account (unlike credit memos).
  • Also, check your deferred revenue account for activity related to customer deposits. Until those deposits are applied, you'll be off somehow since applying CDs impacts the control account. Look at your deferred revenue account to see if that's the amount of your difference on the ledger control.

Inventory

The following inventory related screens provide information that should be helpful to your analysis:

Quantity on Hand (QOH)

  • Inventory > Reports > Quantities on Hand > Posted Value
  • Displays the total value of all Items in Inventory.

Unposted Receipts

  • Inventory > Receiving > Unposted Receipts
  • If receipts haven't been posted, then will not appear in the general ledger.

WIP Value

  • Manufacturing Desktop > Window > Desktop > Work Orders
  • Shows WIP values for open work orders.

Shipping Assets

  • Inventory > Shipping > Maintain Shipping Contents
  • Total value of all Items issued to Shipping.

WIP

Work in process inventory values can be derived from the following screen:

Manufacturing Desktop

  • Manufacturing Desktop > Window > Desktop > Work Orders
  • Shows WIP values for open work orders.

Shipping Asset

Shipping asset values (i.e., inventory at shipping) can be found on the following screen:

Inventory at Shipping
  • Inventory > Shipping > Maintain Shipping Contents
  • Total value of all Items issued to Shipping.