Voided Payment in Wrong Month - Issue with AR GL account

Hello, Loretta,

I understand that cash is correct now. 

In what months does the AR Aged Period Sensitive report match the GL Accounts Receivable control account? (I hope every month.)

Is the customer’s balance correct in every month now?

If the customer’s balance is correct now but was not correct in every month between 11-2023 and now, then I expect you will need both a debit memo and a credit memo to set the customer right in every financial period.

If I’m correct so far…

1. Enter a debit memo to AR and [any account] in the period where the customer’s balance is too low, and
2. Enter a credit memo to AR and [the same ‘any account’ used in step 1] in the period where the customer’s balance is too high.
3. Apply the Credit from step 2 to the Debit from step 1.

Laura

Thank you for the response Laura.  The AR Aged Period Sensitive report does not match the GL AR.  It hasn’t and still doesn’t.  The customer’s balance is zero.

This is what the AR Aged report looks like for 2/29/24.  When the voided payments are re-applied in March 2024, these line items are no longer on the AR Aged report.  But the GL AR control account balance is overstated by the $354k.

Since the customer’s balance is correct ($0) as of March 2024 but the GL AR account balance is still incorrect, when the voided payments are re-applied, do I perform Steps 1 and 2 in the month of March?

For context, this is how the mess of payments and voids appears on the Payments screen.

Hello,

The solution isn’t coming together for me, yet. Friday night may not be the best time for me to work on this. 😉

I notice the Invoice has a negative balance on the AR Aging report: this is unexpected. Does the invoice show a negative balance in every period?  Is the Aging report customized in any way?  If so please temporarily de-activate any custom versions and reprint. (People in Report Designer Role can see custom reports on the Report Versions tab. I am showing an un-customized report here:

If you recalculate the customer balance, does the situation improve?

I worked up the history in Excel, in order by Post Period. Does your Aging match when printed one period at a time?

Laura

I appreciate your brain power on a Friday evening!  As you can tell, this issue has been puzzling me for months.  I began working here in February.  The initial errors weren’t mine, but the two voids are.  Our service team walked me through the solution (the 1st void), and we were so excited to have figured out the negative AR issue, that we weren’t paying attention to the Void dates.  I didn’t think anything of the wrong void date until we were finalizing year end (1/31/24) and reconciling.  Wanted the matter to be resolved on the prior year’s financials.  But then I did the 2nd void (based on the recommendation of my service team), and created the issue we now have.  The service team has looked at this extensively and can’t find a solution.  I thought I’d post here because there are really smart people on this forum!

Couple more points on this particular issue and see the below screen shot of the Aging report by period:

• Invoice 00004 was brought into the system in Migration mode.  So it was a one-sided entry.  Does that make a difference?
• The negative AR was achieved because she posted the payment as $354,880.64 instead of the actual check amount of $256,857.59.  My 1st void was to Void the $354k payment and re-apply as the actual check amount of $256k.  This transaction worked fine, except I posted the VOID in 03/2024 and re-applied the payment in 03/2024.
• Oddly enough, after reconciliation, I discovered that the revenue for Inv4 was never recognized in the prior system, and since it was brought into Acumatica in Migration Mode, an adjusting entry was made (along with other year end adjustments) to credit income.
• The customer/balance no longer appears on the Aged AR after 2/29/24, but the overstatement in the AR GL account remains.
• The customer issued two payments:  $256,857.59 (Inv 4) and $107,682.28 ($98,023.05 to Inv4 and $9,659.23 to Inv22)  
• Early on, the the team was confusing Post Period and dates - our “periods” aren’t calendar but rather fiscal, so they were initially posting in the wrong periods.

• 

   

Hello,

Next steps:

1. Run Recalculate Customer Balances (found in Processes section of Receivables menu) for the one customer, one period at a time, beginning with the Post Period of the Invoice and ending with period 03-2024.

2. Reprint the AR Aged Period Sensitive and compare to theAR control account balance on the GL Trial Balance report for the same range of periods.

Let us know your findings.  Thank you.

Laura

More information in regards to above suggestions: I remember, your GL has never matched the AR Aged Period-Sensitive report. However, we can see whether the difference between GL and AR remains the same month to month.

If the difference remains the same month to month, we can guess the Aging is correct and GL just needs to be adjusted to match.

Adjusting GL to match AR might require another post; this can wait for another day!

Laura

You have no idea how much I appreciate your brain power on this issue.  

Couple of comments on the below:

1. As mentioned, our implementation was not as planned.  The CPA brought our TB #’s into Acumatica as of 1/31/2023, expecting that we would begin using the system very soon after.  That never happened.  They continued using QuickBooks up until 10/1/2023, and even that was still messy.
2. As a result, the CPA advised that I should make adjusting entries at year end.  So rather than reconcile the AR GL account each month, I made an adjusting entry for the end of the year (along with many other AJE’s).  The adjusting entry brought the AR GL balance to actual outstanding Accounts Receivables.
3. The pink shaded cells represent the correct AR balance.
4. As I looked at this issue from this angle, I am puzzled even further.  If you adjust the GL AR balance for periods 07-2023 to 12-2023 by the amount of my AJE (Column G in the below screen shot), the remaining difference in all periods is the $354k figure.  I cannot figure out how this is possible.
5. I know the GL AR balance for period 12-2023 (1/312024) to be correct.
6. I know the GL AR balance for period 03-2024 (4/30/2024) to be overstated and that the Aging balance is correct.

Hello, 
I’d like to confirm:

Was the recalculation process completed?

Is the Aging report customized? If so we need to deactivate the customized report and use the standard one to match GL.

I’m bothered by the invoice itself showing a negative balance on the Aging. We expect the customer’s total balance to go negative when payments exceed invoices, but we don’t expect the invoice to have a balance less than $0.

I always correct one period at a time when adjusting the subsidiary ledger to match GL, beginning when there was first a difference between AR and GL, until all periods balance.

Laura

Hi there,

Yes, I performed the recalculation process as you had suggested earlier - each month beginning at the Post Period of the original invoice through the Post Period the voided payments were posted.

No, the Aging report is not customized.  

Does the negative invoice have something to do with the fact that the invoice was initially entered in Migration Mode?  It’s been so long since I’ve been dealing with this, but I am certain that before I processed any voids at all, the AR Aging report showed as a negative PAYMENT, not a negative invoice, like the screen shot below.

  I believe it’s correct to have a negative PAYMENT showing on the Aged AR if the customer overpays, correct?

I believe the negative invoice didn’t show on the Aging report until after the voids happened.

I will also share that our partner/service team has a ticket in to Acumatica because we have an issue they can’t resolve where Reclassified AP Bills vanish from an Aged AP report.  GL AP balance is correct, but the Aged AP report does not show any bills that have been Reclassified (as a Bill).  Bills reclassified as a GL reclassification do show on the report, however.  So perhaps the negative invoice showing on the Aged AR report is a fluke as well?

Thanks for your support on this issue Laura.  I appreciate it. 

No, the payment was not migrated, just the invoice.  I’ll do as you suggest.  

After looking at this again, I realized that periods 10-2023 and 11-2023 both have incorrect AR GL and Aging AR balances.  Now that I see this, I believe the adjusting journal entry for period 12-2023 should’ve been $579,589.18 and not $224,708.54.  If this adjusting entry is made, there is a discrepancy on the GL AR account balance, but perhaps we can code that discrepancy to a suspense account because it clears out in period 02-2024.  Not sure how that entry is then handled in period 02-2024 though.