Resolution:
- The Accounts Receivable, Accounts Payable, and Unbilled File Reconciliations are typically user entry error and should be researched.
- As a new year is initialized, any difference in the first three categories will clear off the report but technically the File Reconciliation still exists.
- However, it is most important to determine when the File Reconciliation began by running the reports in each period.
Unbilled File Rec.
The accounting side of the File Reconciliation report numbers should equal the unbilled services account (default 121) and the project side should equal the Office Earnings Report unbilled field when run for all regular projects.
Note: Make sure you print for all projects, not only projects with activity.
- Have there been any historical entries to the 121 account or the Unbilled Revenue field in Project invoice and Receipt history? The total of all the Unbilled Revenue fields needs to equal the dollar amount entered to 121 in account balances.
- Check the General Ledger revenue and 121 accounts. There should only be invoice and Revenue Generation entries. Any other entries cause File Reconciliations. Look out specifically for Cash Receipts, AP, or Journal Entries to these accounts.
- Check the Office Earnings Report, printed for all projects, to determine which specific project has an incorrect unbilled amount. This will help narrow down what is causing the discrepancy.
- Run an Accounts Receivable Ledger with the Show modification detail option checked. Look for retainer entries that appear in any section rather than Other. If there are such entries, check their Invoice posting logs for the account that the retainer was applied to. It should not be a revenue account, but instead the liability account that the retainer was initially cash receipted to. When applying the retainer to an invoice, that liability account should be entered in the account field in the Add-on section of the billing terms for that project.
To fix, create a data entry invoice using the same invoice header information. Enter a positive amount to the account that the retainer was in correctly entered against. On a second line, enter the retainer liability account and enter a negative amount. This will reapply the retainer correctly.
AR file Rec
Accounting numbers on File Reconciliation report should be equal to the sum of the AR accounts listed on Invoice Mapping. The Accounts Receivable side should be the AR Aging Report balance less the interest amounts.
- Did it ever tie? Find the period that the reports first started to show a discrepancy.
- In that period, check the General Ledger for any entries to AR that are not conversion, invoice or cash receipt related. Any other entries will cause a file reconciliation.
- One potential cause is a cash receipt without an invoice or project number identified.
- Make sure the Accounts Receivable accounts from Invoice Mapping are being added together in the General Ledger to compare to the AR Aged report.
- Interest is on the AR Ledger but not on the General Ledger.
- Always be sure to the AR Aged Reports sorted/grouped at the lowest WBS level rather than the project level.
AP File Rec
The sum of the accounts set up in Liability Codes should tie to the balance on the AP Schedule run for all vendors.
- Make sure that all Balance Sheet you are comparing are referenced in a liability code.
- Did it ever tie? Find the period the File Reconciliation first appeared by going back in to each period and comparing the GL to the AP Schedule report
- Check the General Ledger in that period for any entries to AP Liability accounts that are not AP Vouchers or Payment Processing.