Aged Accounts Receivable

Navi_Learner

There's one problem in our aged acounts receivable report recently. In this report all the data are correct except one cusomter's. The report shows there's credit of balance of $1,4440 in one customer's account, but in fact he owes us some money. I check the code and find everything is fine. Can anyone tell what the problem is? Thanks in advance!

ara3n

what are the cust. ledger entries for this customer? It could be a posting date issue or the journal that was posted did not have a document type, and sign were reversed.

Savatage

I agree - check the ledger entries - PS take a look at Due dates too to see if anything is screwy.

themave

Check the positng dates also, of the entries, we had an invoice post in the wrong priod once, (before we set g/l posting date restrictions) and an invoice was posted in a future period, when we ran an aging using the filter ending date as the current period that entry did not show up.

DenSter

I'd say that's affirmative. If all customers are ok except for one, then chances are that the data for that customer is screwed up somehow. Analyze the CLE's for the customer and see if you can find the difference. I think Mave's suggestion of a posting in a closed or future period may have been the cause of this issue.

Navi_Learner

I check customer Ledger Entry including document type, posting group and everything and didn't find anything unnormal. I tested the report for each individual date and find that after September 18, all the data are wrong. I really don't know what to do. Why the data is like that after September 18?

Savatage

don't test the report - go into the customer card & drill in on the Balance($) & look at the ledger entries.

ps. are you changing the date on the OPTIONS tab of the A/R aging report?

look at post date & due date's.

I assume you are positive this report is wrong...are any other customers wrong or just the 1?

Navi_Learner

Thank you Harry. Yes, I test each individual customer on balance of the customer card and run the report and find just this customer is wrong and the balance is 1652 in the customer card, but in the report it's -11,400.

themave

Why don't you cut and past the ledger entries into a message here so we can see what you are dealing with. Just show the columns:

posting date, due date and remaining amount

only show open enteries

Navi_Learner

Here is the columns of the three fields:
Posting Date Remaining Amount Due Date
5/4/2005 0 6/3/2005
6/14/2005 0 7/14/2005
6/14/2005 0 6/14/2005
7/15/2005 0 7/15/2005
8/8/2005 0 9/7/2005
8/12/2005 0 9/11/2005
8/19/2005 0 9/18/2005
9/13/2005 12 10/13/2005
9/15/2005 0 9/15/2005
9/15/2005 0 9/15/2005
9/27/2005 0 10/27/2005
9/27/2005 0 9/27/2005
11/2/2005 0 11/2/2005
11/22/2005 795.45 12/22/2005
11/30/2005 845.15 12/30/2005
7/28/2007 0 8/27/2007

themave

Your last entry has a posting date of 7/28/2007

it is in a future period so it is being filtered out when you run your aging.

change your aging date to be 7/31/2007 and run the report, it should then show the same balance as the customer card.

To correct, you should post a reversing entry for the last item date 7/28/2007, and apply it to this entry,

then post an entry with the correct date.

Savatage

2007 - Mystery Solved

This used to be an issue once in a while until we started limiting the Allowable posting dates to the current month only.

General Ledger->Setup->General Ledger Setup->Allow Posting From/To

We change it on every 1st of the month.

Navi_Learner

Harry, Would you please tell me why the posting data has such a big effect on this amount, plus wrong posting date 2007 is invoice not the payment, the report shoud be + amount not - amount

themave

Here is why, the aging is giving you information as of a specific date, so your report is showing payments on the accounts that do not have an invoice against them as of the aging date. That is why it is a credit balance. Let me guess, the open payments the are closed by the 2007 invoice should equal $13052

Savatage

themave wrote:

Here is why, the aging is giving you information as of a specific date, so your report is showing payments on the accounts that do not have an invoice against them as of the aging date. That is why it is a credit balance. Let me guess, the open payments the are closed by the 2007 invoice should equal $13052

exactly, your report is running (for example) AS OF [a date] 11/30/2005

2007 hasn't happened yet so the report isn't even picking this entry up.
Do as THEMAVE says. If it's wrong, Post a reversing Journal entry using the same wrong dates (apply them together) to wipe out the problem entry and enter another journal entry with the correct dates.

**just a note & tip - remember by double clicking on the DUE DATE of a ledger entry you can change the date. The other dates posting, document you cannot change this way.

**And think about using the G/L setup Posting Date limitations.

Navi_Learner

Actually it's invoice not the payment, I pasted the table again below
Posting Date Document Type Amount Remaining Amount
5/4/2005 Invoice 1,387.28 0
6/14/2005 Invoice 931.82 0
6/14/2005 Payment -1,387.28 0
7/15/2005 Payment -931.82 0
7/28/2007 Invoice 2,966.00 0
8/8/2005 Invoice 881.82 0
8/12/2005 Invoice 9,205.46 0
8/19/2005 Invoice 1,738.94 0
9/13/2005 Invoice 12 12
9/15/2005 Credit Memo -90.87 0
9/15/2005 Payment -12,962.41 0
9/27/2005 Invoice 732.05 0
9/27/2005 Payment -1,738.94 0
11/2/2005 Payment -732.05 0
11/22/2005 Invoice 795.45 795.45
11/30/2005 Invoice 845.15 845.15


Is there any code to change the aged accounts receivable report?

Navi_Learner

If I reverse the posting, does it affect the system. This is the important issue I'm concerning about

themave

Date Type Amount Balance At Date
5/4/2005 Invoice 1,387.28 1,387.28
6/14/2005 Invoice 931.82 2,319.10
6/14/2005 Payment (1,387.28) 931.82
7/15/2005 Payment (931.82) -
8/8/2005 Invoice 881.82 881.82
8/12/2005 Invoice 9,205.46 10,087.28
8/19/2005 Invoice 1,738.94 11,826.22
9/13/2005 Invoice 12.00 11,838.22
9/15/2005 Credit me (90.87) 11,747.35
9/15/2005 Payment (12,962.41) (1,215.06)
9/27/2005 Invoice 732.05 (483.01)
9/27/2005 Payment (1,738.94) (2,221.95)
11/2/2005 Payment (732.05) (2,954.00)
11/22/2005Invoice 795.45 (2,158.55)
11/30/2005Invoice 845.15 (1,313.40)
7/28/2007 Invoice 2,966.00 1,652.60

Here is what your aging should show at the given dates,
try running one right now, on the customer tab, enter just the customer number, and nothing else.
on the options tab, enter the "aged as of date" to be 12/31/07, see if your report shows a balance of 1652.60. then do the same thing for a few more of the dates, such as 11/30/05, your balance should be a credit of 1313.40...Some of the numbers you have given in the prior post make me think you might have some type of additional filter set on the customer tab of the aging report.

Navi_Learner

Yes, I tested early this afternoon and on the tab key I keyed in any date after 7/28/07 and report shows a balance of 1652.60, but before that date, everything is -11,XXX(I can't remeber now). My question is that if I reverse the posting and repost it, does it affect the system. Has anyone done it before?

Savatage

i'm not sure what you mean "Affect the system"

this is the way you should do it. We do it all the time..no problems.

Navi_Learner

"Affect the system", I mean if it will mess up some data in the database. And alos Harry would you please tell me why the data is not positive but negetive if I run beofore August 2007, since it's an invoice not a payment? My next question is if I close the fiscal year, does the wrong data entry amount dated 8/7 2007 carry forward to 2006 if I don't reverse the posting of this amount? Thank you!

Savatage

Navi Learner wrote:

Yes, I tested early this afternoon and on the tab key I keyed in any date after 7/28/07 and report shows a balance of 1652.60

Ok, so since every other customer is correct and when you went out to 2007 everything was fine. At least you know the report is fine and it has something to do with the dates.

I don't know why it shows -11,xxx.xx. I don't know the date you were aging by or if you had filters on anything but the customer no.

That being said...There are two ways to reverse & re-enter this invoice.
1)the easiset way (if just getting the amount into the proper date) is create a journal entry reversing the data in error. And another journal entry putting it back with the correct dates.
7/28/2007 Invoice 2,966.00 (Orig)
7/28/2007 Credit 2,966.00 (Reverse)
7/28/2005 invoice 2,966.00 (Correction)

2)If accurate sales history is important to you, you must realize all the item ledger entries, value entries, etc have the wrong dates too. So you could go to Credit Memos->Copy Document and create an exact duplicate of that invoice. Post & reverse everything. Then go to Invoice->Copy document & do the same (just fix the dates before posting )

this way you've completely wiped out the orig invoice by balancing it to zero. I don't know what version you're using but I know in the older versions that reversing a payment had to be done with adding ledger entries not simply deleteing the payment (which couldn't be done) It's a pretty common practice.

I was thinking that if there is one invoice with a bad date there are usually more. Think about looking at the Customer Ledger Entry Table & filter on anything >12/10/2005 and see if anything else comes up.

Last, to feel completely safe (I don't know the size of your database) but you could make a copy of the database to your hard drive (if you have the space) Then connect File->Database->Client.,choose your database on your drive. Then feel free to TEST these things, report changes, etc,etc without being nervous about "Affecting the system" It's good to have a Test database around when your starting out.

Navi_Learner

Thank you, Harry! Actually we are using Navision 4.0. Do we have to add ledger entries too? Thank you for your time!

Navi_Learner

Thanks for everyone who spent time on my questions! Thanks for Harry' professinal suggestions. Yesterday I tried to reverse that amount, but the system gives me a warning message saying "You can only reverse the entry that posted from journal". Also I found I can only reverse the posting of the credit as I found there's Journal Batch Name filled with DEFAULT, but no Journal Batch Name filled with invoice. I can't reverse the Invoice posting. Can anyone tell me how to reverse the invoice posting of this amount? Thanks in advance!

Savatage

Ok, I think you're going about this wrong.
You don't want to UNDO what has been done. You want to create an entry that is the opposite.

(By your reply above - I'm assuming you don't want or need to create a Credit memo by using the Copy Document function in Sales & Receivables)

for example?
Sales & Receivables ->Sales Journals
Batch name - Default (is fine) * if you want to understand Batch names we can go into that later

Post date-> xx/xx/2007 (the date of the wrong entry)
Doc Date-> xx/xx/2007 (the date of the wrong entry)
Doc Type-> Credit Memo
Doc No.-> ?????? (can be the same as the wrong entry..plus an X or something.
Account Type: Customer
Account No.: Customer Number
Description: You can enter a brief description here about "this entry is being corrected , etc etc"
Amount: enter the amount here
Bal Account type: g\l account
Bal Account: your sales g/l account #

i don;t know if that 2007 entry is "open" - if it is you can use the Applies-to fields to link this credit to that invoice.

To create the correcting Invoice is basically the same except your changing document type to Invoice.

*Batch names can be used to save some time.
For example (In A/P) I can Create A batch Name called "TELEPHONE" where i can set the balance account there to "Telephone"
Now when I get a Bill from
Verizon
Cingular
Sprint
etc,etc
I can use that batch and just enter the amounts because I have already set the balance account when creating the new batch

Batch Names can also be used for Individual people by using the users name. I guess there are a bunch of reasons..in A/R we have ones called CASH, BOUNCED CHECKS, etc where we do these kinds of transactions in those batches. Note but they all could be done in 1 batch if we wanted to.

Navi_Learner

Thank you, Harry! It worked. BTW I checked the Item Leger Entry and found that amount with 2007 is still there, I tried to find from the credit memo, but nothing is there. Any way to reverse this amount in item ledger entry. Thank you!

Savatage

Ledger Entries Never Dissappear. You can nullify them but they will always be there. That's why I mentioned entering a brief description before posting in the "Desciption Field" so down the road if a questions about that entry ever comes up you'll know by the description why.

you always enter a note in the customers' comments.

as stated below you should create 2 journal entries.
I only mentioned the date, type & amount because you didn't provide the Doc Date & Doc No.

)the easiset way (if just getting the amount into the proper date) is create a journal entry reversing the data in error. And another journal entry putting it back with the correct dates.
7/28/2007 Invoice 2,966.00 (Orig)
7/28/2007 Credit 2,966.00 (Reverse)
7/28/2005 invoice 2,966.00 (Correction)

---

Note in the ledger entries there is a field called "Open" you may or may not have it showing. if it has a check mark it is "Open"

---

You should read up using the Navision ONLINE HELP for many topics you might be looking into. It should have a section on creating Journal Entries.

Navi_Learner

No, you misunderstood what I said. What I mean is Item General Ledger.
Last time you menion to me the sales history. Here is what you wrote last time. "2)If accurate sales history is important to you, you must realize all the item ledger entries, value entries, etc have the wrong dates too. So you could go to Credit Memos->Copy Document and create an exact duplicate of that invoice. Post & reverse everything. Then go to Invoice->Copy document & do the same (just fix the dates before posting )" I tried to do this as you said, nothig is there in the credit memos, so I can't copy anything. Thanks!

Savatage

ok here is a brief overview.

I am assuming that the 2007 entry was created from an ORDER that was just invoiced with the wrong date.

1)You go into Credit Memo's
2)F3 to get a new Credit Memo Number
3)at the bottom->Functions->Copy Document
4)Document Type->Posted Invoice
5)Document No.->The 2007 Invoice's Number
6)Include header->YES
7)Hit OK

This will Create a copy of the 2007(incorrect) invoice as a credit memo.
Hit F9 to check that the totals all match. Look at the posting date make sure it still had xx/xx/2007 as the date. Post it. It will create an opposite entry.

Then do the same steps as above (2 thru 7)in the Invoice area of Sales & receivables except changing the dates to 2005 , post & you're done.

now you can test this to see if you got the steps down - just don't do the POST part.

---
As mentioned above, it will not get rid of the entries but for example
10
-10

---

0

so it's basically a wash.