KB 854081 - Where is the Adjusted Book Balance coming from in the Select Bank Transactions Window?

Question:

Where exactly is the Adjusted Book Balance pulling from in the Select Bank Transactions Window (Transactions - Financial - Reconcile - Transactions)?

My reconciliation is off by a certain amount, and I see that I have a check for that amount posted AFTER the cutoff date.  Could a check for a later date affect a previous month's reconciliation? 

Answer:

Yes, all activity, no matter what date, affects the Adjusted Book Balance calculations in the Select Bank Transactions (ie. reconcile) window.

The Adjusted Book Balance pulls the Current Checkbook Balance from the Checkbook Maintenance Window (Cards - Financial - Checkbook). If a cutoff date is not entered, it pulls the exact balance that you see in the window. If a cutoff date is entered, it will take the current checkbook balance and add any checks and subtract any deposits that posted after the cutoff date, to get back to the balance as of that cutoff date. 

Example:

Scenario: Let's say the Checkbook balance as of today, (say today is 8/30) is $10,000.00, and you enter the Cut-off Date of 7/31 in the reconcile window. The following activity posted after the cutoff date of 7/31: 

8/2 Decrease Adjustment for $25
8/10 Check for $125
8/15 Check for $350
(Total decreases in August of $500)

8/5 Deposit for $100 
8/18 Deposit for $ 200
(Total increases in August of $300)


So the Adjusted Book Balance would be:

10,000.00 Today's balance
+500.00 Add back the checks/decrease transactions posted after the cutoff date
-300.00 Subtract out deposits/increase transactions posted after the cutoff date
------------------
$10,200 Adjusted Checkbook Balance as of 7/31


So you can see how it starts with today's current checkbook balance and works its way backwards to add/subtract out the transactions in the CM20200 table that were posted after the cutoff date, to get back to the balance 'as of' the cutoff date entered. 

-----------------------------------------------------------------------

ADDITIONAL /TROUBLESHOOTING:

You can use the SQL scripts below to help determine how the Adjusted Book Balance or Checkbook Balance as of Cutoff is calculated in your install. If you need help to do this, it would be considered a consulting service to dig into your data for you and determine exactly what makes up the calculation in your environment.

The calculation starts with today's current checkbook balance and add/subtracts transactions out from the Bank Rec table, back to the Cut-Off Date entered into the Reconcile Bank Statement window, to determine the adjusted checkbook balance at that point in time. Use these steps to review this data:


1. First, all users should be out of GP while you back into this amount, because if users are posting, the checkbook balance and data in the Bank Rec tables would be constantly changing, and you need to do this when the information is stationary.


2. Next, run the below script in SQL Server Management Studio against the company database to determine the current checkbook balance (or click on Cards, point to Financial, and click on Checkbook to look in GP.)



3. Next, GP adds back all transactions that would have originally decreased the checkbook balance that were posted AFTER the cutoff date. This would include checks, withdrawals, decrease adjustments, other expenses, service charges and transfers from the checkbook. Below are two scripts to help located these amounts to add back to the checkbook balance. The first script includes all decrease type transactions except transfers, and the second script is for transfers from the checkbook balance. Run both scripts and add back both



NOTE: The CMTrxType values are outlined in the 'Additional' section below. 


4. Then GP subtracts out all the transactions that would have original increased the checkbook balance. Modify and execute both scripts:



5. Then use the amounts you found in the above steps to start with today's checkbook balance and add back decrease type transactions and subtract out increase type transactions that were posted after the cutoff date.   If you are still off, then you will need to start investigating the following:

• Transactions in which the void date is not in the same period as the original issue date. (As maybe the transaction was valid at the Cut-Off Date, and voided later.)

• Transactions posted after the cutoff date that were dated before the Cut-Off Date. 

• Transactions in the CM20200 table that are dated way out into the future.

• It is also helpful to know the last time you reconciled. The CM Reconcile Header (CM20500) table stores all the summary figures recorded from your prior reconcilations for all checkbooks.

Any additional assistance needed to pinpoint these situations in your environment to help you reconcile or back into a system calculated amount would require digging into your data and therefore be considered a consulting service. 



----------------------------------------------------
ADDITIONAL:

The values in the CMTrxType column in the CM20200 table mean:

Transaction Types that increase the checkbook balance:
1 = Deposit (increase)
5 = Increase Adjustment
7 = Transfer To
101 = Interest Income 
102 = Other Income 

Transaction Types that decrease the checkbook balance:
3 = Check (decrease)
4 = Withdrawal
6 = Decrease Adjustment
7 = Transfer From
103 = Other Expense
104 = Service Charge

Transaction Types that have no effect on the checkbook balance: 
2 = Receipt (no affect)
Any type that is voided and the void date is in the same period as the issue date (Voided = 1)


QUERY WORDS:

chequebook Adjusted chequebook balance, Bank Rec, Bank Reconciliation, reconcile, reconciliation, difference, Reconcile bank statement,

This article was TechKnowledge Document ID:23169