Revenue Reconciliation

Cash Reconciliation

24min

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. 
Related Tables
Below is a list of where data are stored in VAL.
Name
Table Name
Individual outlet deposit declaration records
Deposit Declaration [Outlet]
Collection of all outlet deposit declaration records
DW-UDT: RevRec Deposit Declaration
﻿﻿    Related Tables and Queries
In our list of queries, you can identify [to be populated]
Table
Query Name
Description
﻿
﻿
﻿
Related Workflows
Below is a list of workflow to trigger for performing various automation in VAL.
Name
Workflow Name
Trigger data check
RevRec | Data Availability Check
Trigger reconciliation
[To be populated]
Related Dashboards
Below is a list of dashboards for viewing the results of reconciliation
Name
Dashboard Name
Cash Recon Dashboard (Last month)
RR | Cash Recon
Cash Recon Dashboard (For users to change the date and see reconciliation for different month
RR | Cash Recon - Adhoc
Cash Recon Dashboard (This month)
RR | Cash Recon - This Month
﻿
﻿

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

Name	Table Name
Individual outlet deposit declaration records	Deposit Declaration [Outlet]
Collection of all outlet deposit declaration records	DW-UDT: RevRec Deposit Declaration

Name	Workflow Name
Trigger data check	RevRec \| Data Availability Check
Trigger reconciliation	[To be populated]

Name	Dashboard Name
Cash Recon Dashboard (Last month)	RR \| Cash Recon
Cash Recon Dashboard (For users to change the date and see reconciliation for different month	RR \| Cash Recon - Adhoc
Cash Recon Dashboard (This month)	RR \| Cash Recon - This Month