This is a brief guide on performing reconciliation between Lightspeed POS and Lightspeed Payments. We will cover what to reconcile and how to isolate variances to specific transaction events.
What you achieve in this guide
- What to reconcile? - Firstly we must understand the data required to perform reconciliation.
- What if doesn't match? - Here we can find the data and understand the simplest way to compare to identify inconsistencies
- Finding the data - Downloading raw data is critical step in the data analysis step
- Comparing the reports- We will use excel to correlate the reports from Settlement and POS
- Working out Variances - You have found variances great! - What do they mean?
- Locating a missing transaction - Locating a transaction in POS that may have been misrepresented in settlement.
Before you begin
You would need:
- Access to back office
- Access to Excel or google docs
What to reconcile?
When balancing the books between Lightspeed and your Accounting please remember that the reconciliation is always the day before your settlement.
If your settlement is the 5th of Jan - you would go to:
Insights > Reports > Reconciliation and search the 4th.
If a settlement is after a weekend then Friday through Sunday will be batched together. In this instance for a settlement on the 10th - search for the 7th UNTIL BEFORE the 10th of Jan
From here focus on the Payment summary for Lightspeed Payments
Compare this with the settlements found here
Insights > Lightspeed Payments > Settlement
Your total reconciliation should match the Amount column.
If you are using Surcharging remove the Surcharging total from Amount before beginning your reconciliation.
Note: If you have surcharging you will note that there is a residual Net fee. This is caused by variance between the fees and the surcharge.
- The surcharge is calculated by the initial original amount from Lightspeed.
- The fee is calculated by the processor, based on actual paid amount. Original amount + Surcharge = Paid amount.
As a result there will always be a very small variance that may sometimes round up to no more than a few cents.
What if doesn't match? - Finding the data
This is where we need to go for a deep dive. There are many instances that can cause a discrepancy such as manual payment or the delayed processing of a Transaction.
Download the Settlement Break down and the Sales feed for the day in question.
- Back office > Reports download the Sales Feed. (Filter by Lightspeed Payments)
- Insights > Lightspeed Payments > Settlement > Settlement Breakdown
Comparing the reports
Import both reports into excel sheets - Sort each by the View Sale Link and SaleID in the Breakdown and Sales Feed respectively. Create a third working sheet where we can sort this out without changing the original data set.
From the Settlement Breakdown select and copy to new working sheet
- View Sale Link
- Created Time
- Net Amount (If you are not using Surcharging you can use the Amount column)
Important to note that if Surcharging then Amount when you're looking at INSIGHTS contains Surcharge.
From the Sales Feed select and copy to new working sheet
- Total (I would recommend creating a composite column of TIPS+TOTAL)
Scenario below highlights what can occur when TIPS are not included.
Now it's just about isolating differences - so the rudimentary approach I will provide is to create another column that compares Net Amount and Total
(This is at your own discretion - ultimately our goal is to match each transaction form the Sales feed to the Settlement Breakdown - how ever you achieve this is up to you)
If you use the method as prescribed and the columns balance it will display = 0. If there is a small variance it is likely a rounding event.
Working out Variances
There are two leading situations that will result in variances
- Cannot locate transaction in the Sales Feed data
This could mean it was a manual payment or mismanaged on the POS. Perhaps deleted order or marked as cash for example - the example below will explain this.
- Cannot locate transaction in the Settlement Breakdown data
As above - this might illustrate that a payment was marked as Lightspeed checkout where in fact it was not. However if it was correctly managed at check out and still not present in Settlement - This is something that needs to be escalated to the Payment team via support.
Here are just a few other examples of variances that can arise when matching transaction data.
At times you will note that there is a zero in the View Sale Link column. Simply use the Time column to match. This can occur when there is an issue at time of processing leading the payment to be passed to the process without the order - it's not necessary for processing so it's not a big problem.
Multiple Lightspeed payments per order
Another Variance you will see is when there are two or more Lightspeed payments for one order in the Sales Feed. In the below example you will see 6 payments in the Settlement breakdown for the one order in Sales feed. As long as they total correct its not a concern.
In this case study I was able to match all payments except the very last. There is a payment without a corresponding value in the Sales Feed.
If there is a small variance and Sales IDs match it could mean there was a tip on Lightspeed Side. A Tip won't be included in the SalesFeed total but it will be included in the payment if made by Lightspeed Terminal.
NOTE: As mentioned this can be avoided by creating a composite column of both TIPS and TOTAL
Another element that can cause mismatch is Refunds (specifically when SURCHARGING is enabled).
In the below example
- The Salesfeed has a 11.90 refund.
- The Settlement Breakdown has a 12.03 refund because it includes Surcharge.
This caused a 13c discrepancy
This is visible in the Settlement Breakdown because it had two line items and one was negative.
Also there was a negative in the Salesfeed without a corresponding order in Settlement Breakdown
So firstly, to Balance Payments against the POS you must Remove the -12.03 and replace it with the 11.90 which is what the POS refunded
By doing this - we can match the POS to the Transactions in Settlement Breakdown.
By discounting the Settlement Breakdown refund and substituting the Salesfeed refund it may indicate customer refunded more than they charged. This is purely cosmetic.
The customer was refunded precisely what they were charged. 12.03
The issue is the POS Salesfeed doesn't display surcharge resulting in an imbalanced representation of the refund.
Locating a missing transaction
So why is there a payment without an order on the POS???
As stated experience would tell me that this would be potentially a manual transaction or an order that was mismanaged on the POS. If we go back to Sales Feed - this time not searching for Lightspeed Payments but looking for all sales at the time provided 2022-02-17 21:58:56 we will find our answer. We can see this order was entered as cash
However when we look at the URL we can see the order number and link it back to the order in document proving that this was in fact a lightspeed payment.
This is not a catch all guide and there are many ways that a reconciliation can have variances. However this guide should empower the user to isolate variances to specific transaction events to raise to support for clarity.