***In Progress***

 

Month End Reconciliation Guide & Setup Best Practices

 

Formula

Starting AR + Sales - Payments = Ending AR

 

Month End Reconciliation Calculator – We have created an excel spreadsheet that will guide you through the reconciliation process.

 

To account for different database configurations or converted data, you may need to

include exceptions for Writeoff Adjustments and Cash Basis Adjustments. More info on

these below.

 

Reports

Run each report using the instructions below, export each report, and copy/paste all results to the associated tab in the calculator spreadsheet.

 

Starting AR – Run the Accounts Receivable Aging Details Report

For example, if you’re working on September, set the report to August 31st. Paste the results of this report in the spreadsheet on the AR Beg. Balance tab

 

Sales & Payments  - Run the Accounting Transactions Report

For the month that you are reconciling generate the report and paste results in the spreadsheet on the Acct Trans Detail tab

 

 

 

 

 

Ending AR  - Run the Accounts Receivable Aging Details Report

For example, if you’re working on September, set the report to September 30th. Paste the results on the A/R end balance tab

 

 

Void – Voids should only be done for a mistake when it occurs, not against existing open A/R, especially if it is in another month. This is what Write-Offs are for, to properly record what happened to an invoice that was in previous A/R.

 

Cash Basis Items - Only if using cash basis items like late fees

Late fees (and potentially some other items) are set up as cash basis. These will not

appear on any of the above reports so we need a separate report to identify what

their AR transactions and amounts would have been

Use the net amount of the report below to adjust the Sales and Payments amounts

 

Cash Basis Adjustment = Transactions by Contact Report

Set Transaction Date Range to the desired timeframe

e.g., to run AR for July, set the date range to be July 1 <—> July 31

Additional Criteria / Filters

Item Basis contains cash

AND Txn Type not contains void

Summarize By Txn Type

 

Goods/Services – Recommended Set-up

Accrual

Recognize on Invoice Date

Ensure income account is not set to AR account

 

Late Fees

Configure with Late Fee Good/Service type

Recognize on Invoice Date

Investigation

Copy and Paste the Contact Name from Prior Month AR into Column A
Copy and Paste the dollar amounts from Open Balance into Column B

Copy and Paste the Contact Name from Sales into Column A, below the existing
Copy and Paste the dollar amounts from Debit into Column C and the dollar amounts from Credit to Column D, keeping them lined up with the Contact Names

Copy and Paste the Contact Name from Payments into Column A, below existing
Copy and Paste the dollar amounts from Debit into Column E and the dollar amounts from Credit to Column F, keeping them lined up with the Contact Names

Copy and Paste the Contact Name from Current AR into Column A, below existing
Copy and Paste the dollar amounts from Open Balance into Column G

Rename columns as follows:
A – Contact Name
B – Open Balance
C – Sales Credit
D – Sales Debit
E – Pay Credit
F – Pay Debit
G – Current AR
**Add column H, name “Total”

Got to Home > Sort & Filter > Custom Sort, select “My data has headers” and sort by Contact Name A to Z


Insert formula into first cell of Total column - =+B2-C2+D2+E2-F2-G2 and copy down to apply to all cells

Go to Data > Subtotal


Configure Subtotal:
- At each change in: Contact Name
- Use Function: Sum
- Add subtotal to: Open Balance, Sales Credit, Sales Debit, Pay Credit, Pay Debit, Current AR, Total
- Check boxes, “Replace current subtotals” & “Summary below data”



The Total column should be all zeros, click on the small “2” in the upper left to view the subtotals and look for those that don’t equal zero