Cash reconciliation is use to track outlet deposit of cash receive from sales and ensure that the deposits have been made and managers are not holding on to the money too long resulting in Cash at Risk. Designed for finance and operations to track missing and late deposits.
Designed to reduce manual effort, this tool ensures accurate reconciliation and provides clear visibility into your revenue data.
Steps for performing reconciliation
Collect data
Run reconciliation
Review Recon Results
1. Collect data
For cash reconciliation, VAL uses data from the POS, Cash deposit declaration and Bank to perform the reconciliation. For POS and Bank data the available option today is as follows:
Direct API or database connection
Upload report received from FTP server
Upload report received from email
Upload report into VAL Drive
You can find the instructions on how to upload report into VAL Drive at VDrive
For details for each platform, you can refer to our list of Connector Library. Click on the connector to view the instructions for uploading the report into VAL.
For cash deposit declaration, the data is manually key in by outlet managers who does the deposit or manually uploaded by Finance team depending on customers workflow.
Cash Deposit Declaration (Form Entry)
Each outlet manager are provided a quick entry form for their outlet to record the cash deposit and upload the receipts via their mobile phones.
Each day’s sales is banked in separately for cleaner reconciliation matching, this means 1 entry for each day.
Enter the details of cash deposit
Sales/Business Dates
Amount
Time that deposit was done or cash bag was dropped
Cash bag number (only when cash bag is used)
Once ready click "Create" button
Once the entry is created, you can change the details if required and click on “Update” to save any changes made.
To enter a new deposit declaration, click on “Clear” and start entry again for a different business date.
2. Run Reconciliation
To run the reconciliation, you can do it either via the dashboard thru the buttons provided or going to the workflow module to run the specific workflow on the backend.
Matching rules
Match criteria | Match on | Filters applied |
01. Exact match on Manual Outlet Tagging and Sales Date and Bank Amount | Manual Outlet Tag = Declaration Outlet AND Manual Sales Date = Declaration Business Date AND Bank Amount = Declaration Bank In Amount | Bank: Trans with manual outlet and sales date tagging |
02. Exact match on Manual Outlet Tagging and Sales Date | Manual Outlet Tag = Declaration Outlet AND Manual Sales Date = Declaration Business Date | Bank: Trans with manual outlet and sales date tagging |
03. Exact match on Bagno, Amount, and Bank-in Date | Outlet bagno=Bank Stmt bagno AND Outlet amount=Bank Stmt Amount AND Outlet Bank in date =Bank Transaction Date | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
04. Exact match on Bagno, Amount and Best match for Bank-in Date | Outlet bagno=Bank Stmt bagno AND Outlet amount=Bank Stmt Amount AND Outlet Bank in date =Bank Transaction Date with diff of 1 day,2 days..x days(up to 10) | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
05. Exact match on Bagno and Bank-in Date | Outlet bagno=Bank Stmt bagno AND Outlet Bank in date =Bank Transaction Date | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
06. Exact match on Bagno and Best match on Bank-in Date | Outlet bagno=Bank Stmt bagno AND Outlet Bank in date =Bank Transaction Date with diff of 1 day,2 days..x days(up to 10) | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
07. Full match: Bagno, Amount ,Business Date | Outlet bagno=Bank Stmt bagno AND Outlet amount=Bank Stmt Amount AND Outlet Business Date = Bank Transaction Date | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
08. Exact match on Bagno, Amount, and Best match on Business Date | Outlet bagno=Bank Stmt bagno AND Outlet amount=Bank Stmt Amount AND Outlet Buisness Date is before Bank Transaction Date within 1 day,2 days..x days(up to 10) | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
09. Full match: Amount and Bank-in Date Best match: Bagno | Outlet bagno=Bank Stmt bagno(fuzzy match) AND Outlet amount=Bank Stmt Amount AND Outlet Bank in date =Bank Transaction Date | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
10. Full match: Amount and Best match: Bagno and Bank-in Date | Outlet bagno=Bank Stmt bagno(fuzzy match) AND Outlet amount=Bank Stmt Amount AND Outlet Bank in date =Bank Transaction Date with diff of 1 day,2 days..x days(up to 10) | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
11. Full match: Bank-in Date Best match: Bagno | Outlet bagno=Bank Stmt bagno(fuzzy match) AND Outlet Bank in date =Bank Transaction Date | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
12. Best match: Bagno and Bank-in Date | Outlet bagno=Bank Stmt bagno(fuzzy match) AND Outlet Bank in date =Bank Transaction Date with diff of 1 day,2 days..x days(up to 10) | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
13. Full match: Amount and Business Date and Best match: Bagno | Outlet bagno=Bank Stmt bagno(fuzzy match) AND Outlet amount=Bank Stmt Amount AND Outlet Business Date = Bank Transaction Date | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
14. Full match: Amount and Best match: Bagno and Business Date | Outlet bagno=Bank Stmt bagno(fuzzy match) AND Outlet amount=Bank Stmt Amount AND Outlet Business Date is before Bank Transaction Date within 1 day,2 days..x days(up to 10) | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
15. Exact match on Bagno, best match on Business Date | Outlet bagno=Bank Stmt bagno AND Outlet Business Date is before Bank Transaction Date within 1 day,2 days..x days(up to 10) | Bank: Trans with Cash Bag no Outlet: Declarations with Cash Bag no |
16. Exact match on Outlet Bank In Amount and Bank In Date and Time within 1 hour of Bank Transaction Date and Time | Outlet bank in date and time within 1 hour of Bank Transaction Date and Time AND Outlet amount=Bank Stmt Amount | All |
17. Exact match on Outlet Bank In Amount and Bank In Date and Time within 6 hours of Bank Transaction Date and Time | Outlet bank in date and time within 6h of Bank Transaction Date and Time AND Outlet amount=Bank Stmt Amount | All |
18. Exact match on Outlet Bank In Date and Amount | Outlet bank in date = Bank Transaction Date AND Outlet amount=Bank Stmt Amount | All |
19. Exact match on Amount and Best match on Bank In Date | Outlet bank in date = Bank Transaction Date with diff 1 day,2 days… (up to 10) AND Outlet amount=Bank Stmt Amount | All |
| Outlet Business Date = Bank Transaction Date AND Outlet amount=Bank Stmt Amount | All |
21. Exact match on Amount and Best match on Business Date | Outlet Business Date = Bank Transaction Date with diff 1 day,2 days… (up to 10) AND Outlet amount=Bank Stmt Amount | All |
22. Full match: Amount | Outlet amount=Bank Stmt Amount | All |
Additional notes:
Sequence of matching: Outlet declaration matched against bank statement by running 1st rule for all records. Once match is found, both outlet declaration and bank transaction is removed from matching process. Then match is run using 2nd rule for all remaining unmatched records and so on. This ensures the best match is done according to rule priority and also means bulk cash bag can be matched using the bank in transaction time and exact amount matching.
For multiple matches, do matching by outlet code asc.
Where deposit declaration is missing, matching will be done using POS amount.
Days Late is a setting which users can define. Default is 3 days, ie on the 4th day after POS sales, if bank trans is still not tagged, the record will be flagged as 1 day late.
Best match for cashbag no is set to default 0.9(1 = perfect match).
3. Review Recon Results
Go to the dashboard to view the reconciliation result. Please refer to our list of dashboard at the end of the article, where we list the dashboard name for each platform.
The recon dashboard contains the following widget
Data Availability
Company Level Mismatch
Entity or Brand Level Month Mismatch
Outlet Level Month Mismatch
Outlet Level Day Mismatch
Untagged Cash
Company Level Mismatch
Highlights the company level mismatch for Cash Declaration vs POS variance, Bank vs Cash Declaration variance and also Bank vs POS variance.
Positive variance means declaration/bank recorded more than POS
Negative variance means declaration/bank recorded less than POS
The Bank vs POS variance gives you an instant view of the cash at risk that needs to be addressed
Entity Level Month Mismatch
Highlights the entity/brand level mismatch for Cash Declaration vs POS variance and Bank vs Cash Declaration / POS variance. This is based on the order month.
Fields to take note
O.Dte: Order date
Entity: Entity of the company
POS Sales: Net sales calculated from POS transaction or collection report data
Bank In: Cash declaration recorded by outlet
S.Var: Discrepancy between delcaration and pos (Bank In - POS)
Positive variance means delcaration recorded more than POS
Negative variance means delcaration recorded less than POS
Received: How much received from cash deposit in the bank
B.Var: Discrepancy between bank and delcaration (Bank - Bank In)
Positive variance means received more money than expected
Negative variance means received less money than expected
Bank vs POS: Discrepancy between bank and POS (Bank - POS)
Adj: Shows the adjustments manually made to Bank vs POS variance
Bank vs POS Ad Varj: Shows the post adjusted bank and POS variance
Take note that the important variance is Bank vs POS. The declaration difference is a reference point and used for automating matching process
Outlet Level Month Mismatch
Highlights the outlet level mismatch for Cash Declaration vs POS variance and Bank vs Cash Declaration / POS variance. This is based on the order month.
The fields are similar to the fields in Entity Level Month Mismatch
Users can choose to perform the adjustments at this level or perform it separately at a daily level. Perform filter to drill down.
Take note that adjustments made at a daily level currently do not rollup to this widget. Monthly adjustments need to be manually made here.
Outlet Level Day Mismatch
Highlights the outlet level mismatch for Cash Declaration vs POS variance and Bank vs Cash Declaration / POS variance.
The fields are similar to the fields in Entity Level Month Mismatch with additional 3 fields
V. Dte: Value Date
Days Late: How many days elapsed for deposit since sales date
Bank vs POS Rec Status: A text description of the breaks amount
Users can choose to perform the adjustments at this level or perform it separately at a month level. Click on the remarks column to type in your comments and click on the adj fields to key in your adjustment amount. To see the adj take effect click on the reload icon on the top right of the widget.
Perform filter to drill down.
Take note that adjustments made at a daily level currently do not rollup to monthly entity widget.