x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 10 replies
  • Subscribers 60 subscribers
  • Views 13454 views
  • Users 0 members are here
Browse Forums
Announcements
78
topics
166
replies
General Discussion
5k
topics
16k
replies
Installation and Upgrades
978
topics
2k
replies
Inventory, Projects, and Departments
339
topics
1k
replies
Payables and Receivables
1k
topics
4k
replies
Payroll
1k
topics
3k
replies
Reconciliation and Banking
318
topics
995
replies
Reporting and Analytics
207
topics
400
replies
Soutien Technique - Français
628
topics
1k
replies
Year End Activities
224
topics
596
replies
JJD

Balance sheet AP doesn't match vendor aged and ledger report of AP category

SOLVED
Posted By

Balance sheet shows amount for 'Account Payable' less than it should be.

B/S account payable shows 1051.28. In reality there is 1 invoice of 1095.00 remaining unpaid. The 1095.00 is confirmed as final running balance on GL report  for the AP category, as well as in 'vendor aged' and on home screen of the payables module.

This discrepancy dates back amost a year to the transactions pictured.

JE102 was an invoice to "pay later" entered at exchange rate 1.0

J103 was payment towards that invoice entered with the exchange rate, posting which prompted a warning onscreen and it seems JE104 was made automatically.

Upon seeing the warning, user pulled up the invoice and went to "Purchase" ->"Adjust Invoice" and altered the exchange rate, resulting in JE105 (reversal) and 106 (new version of invoice). Attempting to pull up JE 102,104,105 all result in "no data to report".

What can we do to fix this ?

  • 0
    The Balance Sheet Accounts Payable and the GL Running Balance should be the same. That's where the Balance Sheet pulls its numbers from. It almost looks like instead of an additional 43.72 or exchange rate besides the 104.95, it almost looks like the 43.72 should of been part of the 104.95...so exchange of 43.72 and rest of invoice of 61.23. If manual journal entries were done against the payable account# 2100 its going to throw things off. The GL would be different than the Sub-Ledger for the Vendor.
  • 0 in reply to Marj@Swab
    First thing you should check, unless you already have and just did not mention it, is the Data Integrity. From the Maintenance menu click on Check Data Integrity. If this indicates the A/P is out of balance then we start to determine the problem and can make suggestions for correcting.
    The original Journal Entries for an adjusted entry will no longer provide the detail. The Journal Entry will show in the report of all Journal Entries but you will not be able to drill down. Only the current Journal Entry, the adjusted entry, will allow you to drill down to the invoice level.
  • 0 in reply to Alwyn

    Yes,

    Sorry I omitted that. Data integrity says 'Data OK' and shows both "A/P Balance" and "Unpaid Invoice/Prepay" with the correct number. The Balance sheet number is 43.72 less.

    Outstanding are 2 bills (unrelated to that earlier messed up bill)

    66.33 CAD
    1095 USD (entered at X1.2971) = 1420.32 CAD

    In $CAD these total 1486.65 and this is shown on G/L running balance and data integrity.

    Revalued at 1.3427, A/P on B/S should be 1536.59
    66.33 + (1095*1.3427)

    It's showing 1492.87 which is a difference of 43.72

    See new pic

  • 0 in reply to JJD
    Have you tried the Advanced Data Base Check from the Maintenance Menu as well?
    If so, I then suggest that you talk with Sage 50 Canadian Customer Support by Chat from within the program or by telephone. I had a similar problem and once it was explained to them they asked about connecting remotely. Once connected they took the time to verify all the information provided and then did a Data base rebuild. This last process took very few minutes (depends on data base size) and then everything was fine again. Sometimes with a power hiccup only part of an entry gets posted. This is why we should be checking data integrity more frequently.
  • 0 in reply to Alwyn
    You are right Clyde, the Data Integrity found two entries that I corrected in Payroll but did it by a manual journal entry instead of doing it through the Payroll Module. Best little invention ever!
  • 0 in reply to Alwyn

    Yes, I had and advanced DB check said only that it was fixing a table column. Seemed unrelated. I do check integrity regularly; never shown a problem. Even going back to a backup shortly after these entries were made, the integrity check is ok. Does anyone know how much Sage will charge? I am using 2013 which I bought in mid-2013, but never got around to opening. I only installed and activated it this past August after a system crash/move to new computer.

  • +1 in reply to JJD
    verified answer
    Unfortunately, I do not know what Sage charges as I am on a service plan. I do know that a data base rebuild takes very little time to complete and can be done remotely. They will take some time to investigate to be sure that the rebuild will do what is necessary. Call them and ask, make your decision, and move ahead.
  • 0

    I still have this unresolved, and as I can still adjust 2016 (last year) before rolling over to 2018... now's the time because it's now throwing off my 2017 final numbers and carrying over to 2018. We made sure to have everything paid at year end 2017 - i.e. no outstanding A/P.
    So now I have additional information: The Balance Sheet when NOT revalued shows "Account Payable" = $0 as expected; but when it is printed with revaluation at the YE exchange rate, "Account Payable" = $-43.72

    Here is a pic of the Unrealized exchange Gain/Loss Report shows for "Account Payable"

    I made a copy of the company and tested rolling over to see what it would do. I first got a warning about $0 balance in a foreign account that would be automatically adjusted. I okayed that. It did an automatic adjustment to clear up the closed $USD bank account which was at $0 USD but $51.31 on non revalued BS.

    It did not make any automated adjustment for the A/P discrepancy.
    I then rolled over the year and still new as at Jan 2018 BS are:

    Not revalued Balance Sheet: "Account Payable" = $0

    Revalued Balance Sheet: "Account Payable" = $-43.72
    and a NEW line has appeared: "UNREALIZED-GAIN/LOSS ON..." = $43.72

    Here is how the entries involved (which are pictured in my original post above) appear in the A/P and "difference due to exchange" ledgers:
    N.b. Date in question *should all* be Feb 11; unable to correct Jan 31st wrong date on the payment after the related invoice had been adjusted already.

    ANY SUGGESTIONS FOR JOURNAL ENTRIES THAT WILL RIGHT THIS?

  • +1 in reply to JJD
    verified answer

    Hi JJD,

    I think that your only real option here is to call Sage support.  It sounds like there is an error somewhere in the database and they'll probably have to go in and fix it remotely.  As Alwyn mentioned last year, it doesn't take very long at all.  Like Alwyn, I am on a service plan, so I can't tell you what they charge, but they will tell you that as soon as you call and let you make the decision as to if it's worth it or not.  It definitely sounds like it's worth a bit of money here to get your books back in balance.

    Sorry I don't have a better solution for you.  Best of luck.

    Cheers,

    Andrew

  • 0

    Indeed, this was resolved with an interventionin the database.

    Thanks all!

*Community Hub is the new name for Sage City