How to Reconcile cash orders against cash book deposits to make sure all the cash deposits have been recorded in your cash account

HOW TO RECONCILE THE CASH ORDERS VERSUS THE CASH BOOK

The purpose of this reconciliation is to know if all transactions paid through cash is tallying against the Cash Book Balance. After getting the order report, which we can see from the previous topic, this is what we will get:

In order to get the Orders paid through cash,

1. Filter with the following from the above extracted file:

• Payment/Session

• Payments/Amount 

• Payments/Payment Method

2. Make sure to keep the three column mentioned above add it to a Pivot table.

3. In your Pivot Table, make the Payments/Session as ROWS, Payments/Amount will be the VALUES, and the Payments/Payment Method will be the FILTERS.

4. In this way, you will be able to filter the data per payment method. This will help you filter out the cash paid transactions.

5. You can then get the information extracted from above scenario. We now have the necessary information to proceed with the reconciliation.

Now, the first report is ready to be used for the reconciliation. Now, we will need the Cash Book report, which we will be able to get through the following steps:

1. Go to Accounting Module > Accounting > Journal Items.

2. In order to ensure the availability of the Journal Items, make sure that you Activate the Developer Mode. In order to do that, please watch this video how to Activate developer Mode: https://waoconnect.screencasthost.com/watch/c0etQ9VafiO

3. Next thing to do is to filter the information accordingly to the data that we needed for the reconciliation.

Please filter with the following under the custom filter:

• Account: Cash Account of the Store

• Date: Date you want to do the reconciliation

Note: Journal Item amount will be equal to your General Ledger Amounts.

4. After you have generated the needed line items, simply tick on the checkbox to select all.

5. Then click on Action > Export.

6. When exporting, the relevant data that we will be needing is: Date, Account, Label, and Balance. You can delete and add the fields as preferred.

7. Right after that simply click on Export.

8. This is what your exported file will look like:

9. You can then get the information extracted from above scenario by creating a pivot table and have the fields as follows:

• Labels will be the ROWS

• Balance will be the VALUES. Note: Make sure that you are getting the sum of values/balance.

10. Now all the information are ready. We have now prepared two sheets for the reconciliation.

Now, to reconcile, 

1. Compile these two extracted reports into one single file. Add titles and descriptions, as well as highlighting of the data as preferred to ease the reconciliation process.

2. And now we should be able to do a vlookup for the information in order to create a reconciliation.

Tip: You can do vlookup for both reports for the purpose of checking

3. You can now create a variance column as desired.

NOTE: These data should have no variance, except for the labels/description in the cash book coming from other purposes of Cash In and Cash Out, EG: Cash out for Safe, etc. You will also notice these information without any corresponding data from the other report as N/A.