A/P Aging Doesn't Tie To GL

4876 views
Skip to first unread message

RSI

unread,
Jan 21, 2008, 4:12:03 PM1/21/08
to
For almost a year now, our aging doesn't tie to the GL. The auditors are here
now and we need an explanation.

We've already checked for incorrect accounts and have found some, but we are
still quite a bit off.

Any suggestions?

Frank Hamelly, MCP-GP

unread,
Jan 21, 2008, 4:30:35 PM1/21/08
to
Man, the possibilities are endless but the first things I look for
are:

Before taking the following steps, make a backup of your database and
then run Checklinks (File>Maintenance>Checklinks>Purchasing), then
Reconcile Purchasing Documents (Tools>Utilities>Purchasing>Reconcile
Purchasing Documents) and then Reconcile Payables
(Tools>Utilities>Purchasing>Reconcile). Then:

1. Look for journal entries in the AP account - run a SmartList on
the AP account, add the Series column to the SmartList and sort it by
Series. If any entries from the Financial Series are there, you have
JE's in the AP account and they need to be reclassed.
2. Are you running an Historical Aged Trial Balance with Options?
You'll never tie with a non-historical aging unless you run it on the
last day of the month, after all transactions have been posted, and
then save it. Make sure when you run the Historical TB that the
correct options are checked or unchecked in the report definition
window.
3. Another possibility is incorrect account distributions, ie. the
Payables Type in the Distributions window is being posted to an
account other than AP but you said you've looked at the account
distributions, right?

Hope this helps,

Frank Hamelly
MCP-GP, MCT
East Coast Dynamics
www.eastcoast-dynamics.com


Jamrock

unread,
Jan 22, 2008, 7:59:58 AM1/22/08
to
As Frank has said, there can be several reasons. One of the most frequent
causes is people making entries to the A/P control account in the G/L from
modules other than the payables module.

Let us assume that your G/L balance is 100.00 and your payables trial
balance shows an accumualted balance of 100.00.

If someone does a G/L journal to debit accruals and credit payables, it will
cause a difference between the G/L and the payables ledger. This will
happen because the G/L has been updated but not the sub-ledger.

This should be easy to spot since the journal will have a souce code of GJ
while the sub ledger entries will have a souce code starting with PM.

To prevent this, untick the box "allow account entry" on each control
account in the account maintenance window

To find the differeces I would do the following:

Print the G/L trial balance and the historical aged trial balance with
options (payables) day by day to find out when the difference occured in a
specific month.

On the day that the difference occurred, print the G/L detailed trial
balance and the payables distribution history report. Restrict the payables
distribution history report to the account number of the G/L payables
account.

The payables distribution history report will show all the transactions sent
to the G/L from the payables module. The detailed trial balance will show
all the entries being booked to the G/L payables account. Check to see if
an entry is in one that is not in the other.


"RSI" <R...@discussions.microsoft.com> wrote in message
news:B0D41CAF-58F5-45E9...@microsoft.com...

Bud

unread,
Jan 22, 2008, 10:38:55 AM1/22/08
to
We waited about a year before attempting to reconcile, too - and it wasn't
fun. Our accountants started with the current month and worked backwards.

I think most of our problems were due to incorrect A/P account numbers on
the vendor records. To check these, run a SmartList of the Vendor records,
adding the Accounts Payable account number to the list. Remember that blank
ones will default to the company-level setup.

"RSI" <R...@discussions.microsoft.com> wrote in message
news:B0D41CAF-58F5-45E9...@microsoft.com...

Bud

unread,
Jan 22, 2008, 10:31:44 AM1/22/08
to
Wow...after a year you're going to have a heck of a time reconciling them.
We finally started doing it after about a year, too - I think our
accountants started with the current month and worked their way backwards,
doing one month at a time.

I think one of our biggest problems was, as Frank mentioned, the posting of
vouchers to the wrong A/P account. Some of our vendors had an incorrect A/P
account set up in their vendor master record. You can dump off the vendors &
their Accounts Payable accounts using SmartList to see if any have been
coded incorrectly. Remember, any blank ones will default to the company
setup.

"RSI" <R...@discussions.microsoft.com> wrote in message
news:B0D41CAF-58F5-45E9...@microsoft.com...

Bud

unread,
Jan 22, 2008, 10:41:15 AM1/22/08
to
Sorry for the dup post....for some reason I haven't been able to use the web
reader for the last couple of days to make posts, so I'm getting the hang of
using Outlook Express :-)


"RSI" <R...@discussions.microsoft.com> wrote in message
news:B0D41CAF-58F5-45E9...@microsoft.com...

Sabryant

unread,
Jan 22, 2008, 6:58:07 PM1/22/08
to
Make sure that you are running a Historical Aged Trial Balance instead of an
Aged Trial Balance. The Historicial report gives you an aging as of a
certain date. The Aged Trial Balance gives you today's balance, provided
you've posted today's batches to the G/L.

Doug T

unread,
Jan 22, 2008, 7:00:18 PM1/22/08
to
Any unposted batches? Also can you run a query to see if any transactions
didn't post an entry to the AP GL number? Did the amount that you are out
change?
--
Doug T

Charles Allen

unread,
Jan 22, 2008, 7:26:16 PM1/22/08
to
Go back to the last month the balances tied. For the following month, make
sure that the documents posted in Payables were posted in GL. Each audit
control code in A/P should have a record in G/L. Look at the distribution
history in Payables for A/P and compare it to a cross-reference report for
the same account in G/L.
--
Charles Allen, MVP

Todd McDaniel

unread,
Jan 22, 2008, 7:31:14 PM1/22/08
to
Two of the most common issues:

1. Are you running the historical aged trial balance based on G/L posting
date? If not you may have timing issues between the A/P document date and
the G/L posting date.

2. If you use PO receipts, does your accrued purchases account balance to
your Received Not Invoiced report.

I am stealing his thunder buy Microsoft MVP Mark Polino has some scripts on
his blog for balancing subledgers. www.dynamicaccounting.net

jav

unread,
Jan 22, 2008, 8:38:02 PM1/22/08
to
The first step is to find a timeframe where the GL did tie to AP then run a
month by month comparison to narrow down your issue. You can run a
Inquiry-Financial-Summary or History Summary on your liability account to get
period totals. Then run a AP Historical Aged Trial Balance with the Select
Transactions for Report Using GL Posting Date. Exclude everything but the
Credit Balance and run it in Summary as of the end of the period for each
month.

Once you find the issue and the month, you can run a Financial Trial Balance
report per day and compare it to the AP Historical Aged Trial Balance report
to narrow the difference down to a day.

You should then be able to look for the detail on that day to see if there
is any differences. Also, be sure all of the GL transactions have been
posted. Check Batch Recovery for stuck batches.

Good Luck

Doug T

unread,
Jan 22, 2008, 8:49:33 PM1/22/08
to
Make sure there are no unposted Purchasing batches. Also see if you can run
a query to see if there were transactions that at least one side of the entry
did not hit the AP account number. I have seen instances where cash posting
transactions debit and credit the cash account rather than credit cash and
debit accounts payable.

Has the amount of the difference changed? If it is a carry forward that
doesn't change you are most likely going to need to make an entry to the AP
account number forcing a balance.

Good luck
--
Doug T

Leslie Vail

unread,
Jan 23, 2008, 12:50:13 AM1/23/08
to
Here's another idea. Using this idea will depend on how many AP
transactions you have, how many vendors have balances and whether you
are posting in detail.

Create a SmartList that includes only the Accounts Payable account(s)
and excludes any source document that equals 'BF'. Be sure to include
the discount account if you are keeping track of it in a separate GL
account. Add the Originating Master ID to the SmartList. One thing
you'll notice right away is if there is no vendor ID, there's a
possible trx that's trouble. Export the list to Excel. Subtotal the
excel spreadsheet by Master ID. You'll need to add another column in
Excel to net the Dr and Cr columns. Now, the balance by vendor ID
should be equal to the Balance on your AP trial balance. Check each
vendor and for the ones that don't match you'll need to take a look at
the detail transactions and compare.

Good Luck!

SHEIKH ASIF

unread,
Jan 23, 2008, 3:42:40 AM1/23/08
to
Hi,

Following are the things that could cause difference.

* The Historical Aged Trial Balance report is printed with
restrictions.
Print the Historical Aged Trial Balance report again with only a date
restriction.
* Not all accounts payable accounts are viewed in General Ledger.
Make sure that you view all accounts payable accounts in General
Ledger.
* Batches in Payables Management were posted to General Ledger.
The batch in General Ledger was changed or edited before it was
posted.
* Adjustments to the accounts payable account may have been entered
directly in General Ledger.
These transactions update the account in General Ledger.
However, these transactions do not update the Historical Aged Trial
Balance report.
* The date range on the Detailed Trial Balance report in General
Ledger does not match
the date range on the Historical Aged Trial Balance report in Payables
Management.
When you print the Historical Aged Trial Balance report, click to
select the
GL Posting Date check box in the Select Transactions For Report Using
area.
* Transactions were posted in Payables Management. However, these
transactions
were not posted to General Ledger if they were for beginning
balances.
If the Post To General Ledger check box is not selected in the Posting
Setup window
for the Purchasing series , the transactions will be posted to
Payables Management.
However, these transactions will not be posted to General Ledger.
* The Track Discounts Available in GL check box is selected in the
Payables Management Setup window.
Then, the net amount of the invoice will be posted to General Ledger.
Additionally, the remaining amount is posted to a discounts available
account.
Only the net amount will appear on the Detailed Trial Balance report
in General Ledger.
However, the invoice displays the gross invoice total on the
Historical Aged Trial Balance in Payables Management.
* Documents were voided in a different period than they were
originally posted.
The Detailed Trial Balance report in General Ledger may not match the
Historical Aged Trial Balance report.
For example, assume an invoice was entered on 1/1/2007. This invoice
was voided on 2/1/2007.
A General Ledger Detailed Trial Balance report is printed for 2/1/2007
- 2/28/2007.
The voided transaction will appear on the report.
If a Historical Aged Trial Balance is printed by using the same date
range,
the voided document will not print on the report because it has been
voided.

* If you want to balance the accounts payable account balance in
General Ledger to the Historical Aged Trial Balance report for a
certain period,
the balances from the Historical Aged Trial Balance report must be
reconciled to the net change
on the Detailed Trial Balance in General Ledger for the same period.
* If you want to balance the accounts payable account balance in
General Ledger to the Historical Aged Trial Balance report for a day
that is not in a certain period,
determine whether Payables Management has ever been balanced.
If Payables Management has never been balanced, the beginning balances
may be incorrect.
In this situation, balance the most current period first, and then
reconcile the previous months in reverse order.
* If posting interruptions have occurred, batches may not have been
posted
correctly to General Ledger, to Payables Management.
* Not all General Ledger batches were posted.
* When you print the Historical Aged Trial Balance report, you did not
click to select
the following check boxes in the Exclude area:
* Unposted Applied Credit Documents
* Zero Balance
* Credit Balance
* Activity
Click to select these check boxes, and then print the Historical Aged
Trial Balance report.

Note If you want to match the General Ledger Detailed Trial Balance
report
and the Historical Aged Trial Balance report by specific documents,
click to clear the Fully Paid Documents check box.
* If you use Multicurrency Management when you revalue,
you selected to post to the Purchasing Offset Account.


Regards,
Sheikh Asif

Victoria [MVP]

unread,
Jan 23, 2008, 8:16:13 AM1/23/08
to
Leslie, this is a really awesome approach!

--
Victoria Yudin
Dynamics GP MVP
Flexible Solutions, Inc.


"Leslie Vail" <lesli...@earthlink.net> wrote in message
news:f0ab6b7b-84fc-44a0...@e10g2000prf.googlegroups.com...

Leslie Vail

unread,
Jan 24, 2008, 10:10:00 PM1/24/08
to
Thanks Victoria!

I think I'll write this up and add the article to the Confessions II book.

awh...@nccgreen.com

unread,
Jun 26, 2014, 3:56:48 PM6/26/14
to
Hi Leslie,

I went through you process to run a smartlist that only includes AP accounts, and almost none of the vendor balances tied to the trial balance.

Do you have any other suggestions on how I can reconcile the GL to the AP subledger?

Thanks!


Anita W

unread,
Jun 26, 2014, 4:25:29 PM6/26/14
to
Todd,

I am new to my company, and am trying to clean up a lot of accounts. My accrued purchases balance does not tie to my received not invoiced report, but then again nothing has been reconciled in over a year. Do you have any suggestions?

Thanks!
Reply all
Reply to author
Forward
0 new messages