AR Aging Report Not Matching Balance Sheet
Top Reasons Your AR Aging Report Does Not Match Your Balance Sheet
There are several reasons the AR Aging report is not matching the Balance Sheet. Generally, it is a date timing issue. The Balance Sheet strictly looks at the transaction date while the AR Aging reports look at the transaction debit date, the transaction credit date, and the date the credit was applied to the debit transaction.
Here are the most common reasons the AR Aging Report does not match your Balance Sheet Report.
- The AR Aging reports were not run with the same date parameters as the Balance Sheet.
- Transactions affecting Accounts Receivable are out of balance in the General Journal.
- Transactions affecting Accounts Receivable are missing from the General Journal.
- Credit application dates are not in sync with General Journal transaction dates.
- Accounts Receivable account is improperly being used in Invoice Item setup.
- Accounts Receivable account is improperly selected on transactions.
AR Aging Not Matching Balance Sheet – Report Date Verification
When running the Balance Sheet and A/R Aging reports, please verify the dates on the two reports are the same. If you find the report dates to be different, re-run the reports with the same date parameters.
Out of Balance Transactions
Occasionally a transaction may become out of balance if it was not properly saved to the General Journal. Many factors can contribute to this behavior. There may have been a network disconnect in the middle of the save procedure, incomplete data, a power surge, etc. Total Office Manager has built in utilities to scan for these types of transactions. The Database Checkup utility is located under Tools | Utilities | Database Checkup. Run the scan for Out of Balance Transactions.
Locate any Invoices, Credits, or Payments and double click to open. Once in the transaction, right click and choose the option Find In GJ. A review of the General Journal entry may indicate why this transaction is out of balance. Typically, typing a space on the Internal Memo and resaving the transaction will update the General Journal entry. Once you have reviewed and resaved all located out of balance transactions, rerun the scan again to validate you have in fact corrected these entries.
Missing GJ Entries
For the same reasons as the out of balance scan, entries may also be missing from the General Journal. Running the scan for Missing GJ Entries will locate any records that are missing from the General Journal that impact accounting.
Locate any Invoices, Credits, or Payments and double click to open. Review each transaction and resave by typing a space on the Internal Memo to input into the General Journal. Once you have reviewed and resaved all located missing entries, rerun the scan again to validate you have in fact corrected these entries.
Incorrect Payment Applications
Many times the number one culprit of the reports not matching is improper payment application dates. There are many ways to review these entries. Review the A/R Payment Applications Review CDV will help to identify where payment application dates may cause discrepancies between the reports. Custom Data Views (CDV) are located under Reports | Custom Data Views. To access this Custom Data View, please navigate to Reports | Custom Data Views. Under the Data Views menu, select A/R Payment Applications Review. If you do not see A/R Payment Applications Review listed, please select Download/Update Views. Check the box next to A/R Payment Applications Review and click the Update Selected Views button. Once confirmation of download has been displayed, close the Custom Data View Download form and choose A/R Payment Applications Review from the Data Views menu.
When reviewing the transactions in the list, compare the Debit Date and Credit Date against the Date Applied. The Date Applied is to be the greater of the Debit and Credit dates. If you find a Date Applied that is incorrect, double click on the entry to open the Customer:Job record. Right click within the Customer:Job and choose the History option. Locate the Invoice or Sale on Sales tab, right click, and choose the option Adjust. Check the box next to the credit transaction and click the Delete and Close button.
Click on the Payments tab, right click and choose New. Locate the debit transaction and click on the line entry. There is no need to check the box. Click the Set Credit button in the lower left corner of the payments form. Ensure the Date Applied is the greater of the two dates. Click Save & Close on the credits form. Close out of the payment form. There is no need to save the payment. The credit application is saved when the credits form is saved and closed.
Item Account Setup
If Smart Account Selection Filtering turned off, it is possible to select an inappropriate account when setting up items. This could also lead to A/R Agint to not match the Balance Sheet
Open your Invoice Item List. Be sure that you have the Income Acct, COGS Acct, and the Asset Acct columns included in your list view.
Go through your list of items to be sure that the correct chart of account has been selected. You should only see “Income” type of accounts in the Income Acct column. You should only see Cost of Goods Sold accounts in the COGS Acct column. Don’t just rely on the name to indicate the COA type. If you are not sure, look at your Chart of Accounts list to double check names against COA types.
You may also review the Invoice Item Setup in the Custom Data Viewer.
Improper Account Usage
We have commonly seen Accounts Receivable selected as the Discount Account on payment entries. We have commonly seen Bad Debt setup using an Accounts Receivable chart of account. Neither should be setup this way.
When using an Accounts Receivable type as type as a discount, no customer is associated with this record. Typically discount transactions affect an expense account type. Total Office Manager no longer allows for the selection of an Accounts Receivable account in the discount account selection on a payment. If the intent was to move the balance to a bad debt receivable account, it is customary to create an invoice item for bad debt which is tied to an expense account and use this item as outlined in the Help Topic: Bad Debt – Dealing with Collection Agencies.
Is it also possible that, at one time, certain items could have had an incorrect COA and have since been corrected. If the user did not click “Yes” to the “Update Historical” option, old transactions using these items would still be causing problems. Currently, there is no easy way to locate transactions that may have been created using items with an incorrect Chart of Account selection. If you believe that this may describe your problem, please contact technical support for more information.
A/R Aging Not Matching Balance Sheet – Additional Troubleshooting
If all the above have been reviewed, verified, and corrected, the next step would be to compare the A/R Aging customer balances to the Balance Sheet customer balances. The Register Accounts Receivable CDV can assist in summing the customer entries in the General Journal and allow for easier comparison to the AR Aging reports. Locate the Customer:Jobs that are different from one report to the next. Determine why the source of the discrepancy and correct as outlined in the Incorrect Payment Applications section.
This is an easy fix; however it is a very time consuming fix. Other than taking the time to analyze the records as outlined above, there is no “easy” button to correct this. Please remember that patience is a virtue and patience is the key.
Please note: The same steps would be taken for Accounts Payable if the amounts on the AP Aging Report did not match the Executive Summary or the Balance Sheet. You would just need to replace AR forms and functions with AP forms and functions and continue to follow the steps as outlined.
Accounting Review
If you are unable to locate the differences, or would like assistance in troubleshooting the reports and balances, our Accounting Review service may be the way to go. Many times we are asked, “But isn’t that why we have a support plan?” Our technical support is more for the software functionality than for dissecting the numbers. When there is question with where a number is coming from and it requires digging through financials that we are not familiar with, we do refer to the Accounting Review service for two reasons:
- Our Accounting Review team is very thorough. Not only do they look for data anomalies, they document discrepancies, offer resolutions, explain a particular process or procedure that will prevent the reason for the accounting review referral.
- Our Accounting Review team validates that the software is functioning as the software is intended to where accounting aspects are concerned. If a bug is determined to be the cause of the issue, they fully acknowledge as such and you do receive a credit of $150.00 per bug determined to be applied towards to review fees.
If you are interested in our Accounting Review service, please contact our Help Desk via phone (913-322-4666) or by email (helpdesk@aptora.com) for further direction.
