Revenue Reconciliation

Non-Cash Reconciliation

27min

Overview

There are 2 versions of non-cash reconciliation.

  • Scenario 1: 3rd party pay you at an outlet level
  • Scenario 2: 3rd party pays you at an entity level

Typically for sales reconciliation it is feasible to reconcile at an outlet level however for payment it is dependent on the payment schedule and arrangement with your 3rd party.

Please refer to our list of supported found in Connector Library to identify if they pay at an entity or outlet level.

Designed to reduce manual effort, this tool ensures accurate reconciliation and provides clear visibility into your revenue data.



Steps for performing reconciliation

  1. Collect data
  2. Run reconciliation
  3. Review Recon Results


1. Collect data

Depending on the platform, VAL uses a variety of way to collect data. The available options 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 of what is the format to upload and what views are available, you can refer to our list of Connector Library. Click on the connector to view the instructions for uploading the report into VAL.



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. Please refer to the related workflows at the end of this article to find the workflow for triggering the reconciliation.




3. Review Recon Results

Understanding the Recon Results



Go to the corresponding 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

Data Availability

Highlights the missing data required for reconciliation. 3 sets of data are required: POS, 3rd Party Settlement Report and Bank Statements

  • Look out for dark colors dots indicating which data is not available on the Data Availability widget. Hover over the dot to see the details of which outlets is missing data
Document image

  • Alternatively, the Data Availability Summary will highlight the specific dates and outlets that are missing in a table form. Perform filter to drill down.
Document image


Company Level Mismatch

Document image


Highlights the company level mismatch for Platform vs POS variance and Bank vs Platform variance.

  • Positive variance means platform recorded more than POS
  • Negative variance means platform recorded less than POS

Entity Level Month Mismatch

Outlet level and entity level payment will have slightly different views given the nature of payments. Both will highlights the entity/brand level mismatch for Platform vs POS variance and Bank vs Platform variance. This is based on the order month.

Outlet Level
Outlet Level




Entity Level
Entity Level




Take note that Disc, SR Brks are only relevant for 3rd party delivery platforms

Outlet Level Month Mismatch

Outlet level and entity level payment will have slightly different views given the nature of payments. At outlet level, users will be able to see payment at a outlet level however for entity level, there will be no breakdown of payment at a outlet level. Highlights the outlet level mismatch for Platform vs POS variance and Bank vs Platform variance. This is based on the order month.

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
Outlet Level

Document image


The difference between outlet level payment and entity level payment is that the widget is split into two widgets. The sales reconciliation is at a outlet level while the payment is at a entity level

Outlet Level Day Mismatch

Highlights the outlet level mismatch for Platform vs POS variance and Bank vs Platform variance.

Outlet Level (Sales Recon)
Outlet Level (Sales Recon)

Outlet Level (Bank Recon)
Outlet Level (Bank Recon)

  • Two widgets are provided: Sales reconciliation and Bank reconciliation
  • The date used in sales reconciliation is based on Order Date and the date used in bank reconciliation is Value Date (V. Date)
  • Take note that you can identify the expected receivable in the sales recon widget in the bank recon widget to check if it has been received. There are scenarios where multiple day sales are paid out in single day such as weekends and holidays.
  • Below example
    • 18 and 19 Sep orders are paid out on the 23 Sep.
    • 20 Sep orders are paid out on the 24 Sep.
Document image


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

Take note that adjustments made at a daily level currently do not rollup to monthly entity widget.

Fields Explain

Name

Description

O. Dte

Order date

Entity

Entity of the company

POS Sales

Net sales calculated from POS transaction or collection report data

Plt Sales

3rd party platform recorded sales from platform settlement report

S. Var

Discrepancy between platform and pos (Platform - POS)

  • Positive variance means platform recorded more than POS
  • Negative variance means platform recorded less than POS

Disc Brks*

Mismatches due to discounts not captured correctly in POS based on merchant discount and not platform discount

SR Brks*

Mismatches due to discounts not captured correctly in POS based on merchant discount and not platform discount

Oth Brks

Mismatches due to data capture error or interface problems

Receivables

How much 3rd party platform have indicated to payout

Received

How much received from 3rd party platform in the bank

B. Var

Discrepancy between bank and platform (Bank - Platform)

  • Positive variance means received more money than expected
  • Negative variance means received less money than expected

S. Adj

Shows the adjustments manually made to sales variance

B.Adj

Shows the adjustments manually made to bank variance

S. Adj Var

Shows the post adjusted platform and pos variance

B. Adj Var

Shows the post adjusted bank and platform variance

Take note that Disc, SR Brks are only relevant for 3rd party delivery platforms



Related Tables

Below is a list of where data are stored in VAL.

Table Name

Description

DW-TA: RevRec | Day | POS Systems

Collection of all POS settlement data by day

DW-TA: RevRec | Day | 3P Platforms

Collection of all 3P settlement data by day

DW-TA: RevRec | Day | Banks

Collection of all bank settlement data by day

SOL-RR: Sales & Bank Recon | Day

Reconciliation result at a day level by 3P, Brand, Outlet

SOL-RR: Sales & Bank Recon | Month | Brand Platform Outlet

Reconciliation result at a month level by 3P, Brand, Outlet

SOL-RR: Sales Recon | Month

Reconciliation result (Sales only) at a month level by 3P, Brand, Outlet

[Entity Level Recon Only]

SOL-RR: Sales & Bank Recon | Month | Brand Platform

Reconciliation result at a month level by 3P, Brand

[Entity Level Recon Only]

Related Tables and Queries

In our list of queries, you can identify [to be populated]

Query Name

Description

Table

Time Aggregate | POS | Day | [Date Range][Platform]

This query provides all POS transaction aggregated for a selected date range by day filtered by Platform

DW-TA: RevRec | Day | POS Systems

Time Aggregate | 3P Platforms | Day | [Date Range] [Platform]

This query provides all 3P transaction aggregated for a selected date range by day filtered by Platform

DW-TA: RevRec | Day | 3P Platforms

Time Aggregate | Bank | Day | [Date Range] [Platform]

This query provides all bank transaction aggregated for a selected date range by day filtered by platform

DW-TA: RevRec | Day | Banks

Sales Recon | Day | [Platform] - [Date Range]

This query provides sales reconciliation by day for a selected date range and platform.

SOL-RR: Sales & Bank Recon | Day

Sales & Bank Recon | Month | Food Panda | Outlet - [Date Range]

This query provides sales & bank reconciliation by month, outlet and platform. The date is the last day of the month. Input date and platform filters.

SOL-RR: Sales & Bank Recon | Month | Brand Platform Outlet

[Date Range] [Platform] | Sales Recon | Month

This query provides sales reconciliation by month for a selected date range and platform, including deductions and fees

SOL-RR: Sales Recon | Month

Sales & Bank Recon | Month | [Platform] - [Date Range]

This query provides sales & bank reconciliation by month and platform. The date is the last day of the month. Input date and platform filters. Typically used for those platforms who pay at Entity level and not by Outlet.

SOL-RR: Sales & Bank Recon | Month | Brand Platform

Related Workflows

Below is a list of workflow to trigger for performing various automation in VAL.

Description

Workflow Name

Trigger data check

RevRec | Data Availability Check

Trigger reconciliation at outlet level

Executor-RR Recon1Outlet

Trigger reconciliation at entity level

[Entity Level Recon Only]

Executor-RR Recon2Platform

Related Dashboards

Below is a list of dashboards for viewing the results of reconciliation (Outlet Level)

Description

Dashboard Name

Adyen Recon Dashboard (Last month)

RR | Adyen Recon

Aigens Recon Dashboard (Last month)

RR | Aigens Recon

Aigens Recon Dashboard (For users to change the date and see reconciliation for different month

RR | Aigens Recon - Adhoc

AMEX Recon Dashboard (Last month)

RR | AMEX Recon

AMEX Recon Dashboard (For users to change the date and see reconciliation for different month

RR | AMEX Recon - Adhoc

CyberSource Recon Dashboard (Last month)

RR | CyberSource Recon

CyberSource Recon Dashboard (For users to change the date and see reconciliation for different month

RR | CyberSource Recon - Adhoc

DBS CC Recon Dashboard (Last month)

RR | DBS CC Recon

DBS Max Recon Dashboard (Last month)

RR | DBS Max Recon

Food Panda Recon Dashboard (Last month)

RR | Food Panda Bank Recon

Food Panda Recon Dashboard (For users to change the date and see reconciliation for different month

RR | Food Panda Recon - Adhoc

Grab Bank Recon Dashboard (Last month)

RR | Grab Bank Recon

Grab Recon Dashboard (Last month)

RR | Grab Recon

Grab Recon Dashboard (For users to change the date and see reconciliation for different month

RR | Grab Recon - Adhoc

NETS Recon Dashboard (Last month)

RR | NETS Recon

Nets Recon Dashboard (For users to change the date and see reconciliation for different month

RR | NETS Recon - Adhoc

OCBC CC Recon Dashboard (Last month)

RR | OCBC CC Recon

OCBC CC Recon Dashboard (For users to change the date and see reconciliation for different month

RR | OCBC CC Recon - Adhoc

Shopback Pay Recon Dashboard (Last month)

RR | Shopback Pay Recon

UOB CC Recon Dashboard (Last month)

RR | UOB CC Recon

UOB CC Recon Dashboard (For users to change the date and see reconciliation for different month

RR | UOB CC Recon - Adhoc

Below is a list of dashboards for viewing the results of reconciliation (Entity Level)

Description

Dashboard Name

Adyen Ecom Recon Dashboard (Last Month)

RR | Adyen_Ecom Recon

Deliveroo Bank Recon Dashboard (Last month)

RR | Deliveroo Bank Recon

Deliveroo Recon Dashboard (Last month)

RR | Deliveroo Recon

Deliveroo Recon Dashboard (For users to change the date and see reconciliation for different month

RR | Deliveroo Recon - Adhoc

Food Panda Recon Dashboard (Last month)

RR | Food Panda Recon (Bank without outlet split)

NETS CC Recon Dashboard (Last month)

RR | NETS CC Recon

Oddle Recon Dashboard (Last month)

RR | Oddle Recon

Oddle Recon Dashboard (For users to change the date and see reconciliation for different month

RR | Oddle Recon - Adhoc

Tabsquare Recon Dashboard (Last month)

RR | Tabsquare Recon

Tabsquare Recon Dashboard (For users to change the date and see reconciliation for different month

RR | Tabsquare Recon - Adhoc