How can we help?

Lightspeed’s Help Centre is here to provide support, tips and troubleshooting

Reconciling your Lightspeed Payments settlement


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


 

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.


Single Day

If your settlement is the 5th of Jan - you would go to:
Insights > Reports > Reconciliation and search the 4th.

Screen_Shot_2022-02-24_at_3.57.20_pm.png

Weekend

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  

Screen_Shot_2022-02-24_at_3.58.03_pm.png

From here focus on the Payment summary for Lightspeed Payments

Screen_Shot_2022-02-24_at_4.04.21_pm.png

 

Compare this with the settlements found here
Insights > Lightspeed Payments > Settlement

Screen_Shot_2022-02-24_at_4.59.37_pm.png

 

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

Screen_Shot_2022-02-24_at_4.09.14_pm.png       Screen_Shot_2022-02-24_at_4.09.50_pm.png

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

  • SaleID
  • SaleDate
  • Total (I would recommend creating a composite column of TIPS+TOTAL)
    Scenario below highlights what can occur when TIPS are not included.

Screen_Shot_2022-02-24_at_4.15.12_pm.png

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)

Screen_Shot_2022-02-24_at_4.16.17_pm.png

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. 

 

Zero Value

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.

Screen_Shot_2022-02-24_at_4.21.17_pm.png

Screen_Shot_2022-02-24_at_4.21.59_pm.png

 

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.

Screen_Shot_2022-02-24_at_4.25.12_pm.png

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.

Screen_Shot_2022-02-24_at_4.29.43_pm.png

 

Tips

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.

Screen_Shot_2022-02-25_at_9.10.46_am.png

 

Screen_Shot_2022-02-25_at_9.24.41_am.png

NOTE: As mentioned this can be avoided by creating a composite column of both TIPS and TOTAL

Screen_Shot_2022-03-11_at_7.50.25_am.png

 

Refunds

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

Screen_Shot_2022-02-24_at_4.33.59_pm.png

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. 

Screen_Shot_2022-02-24_at_4.34.54_pm.png

Screen_Shot_2022-02-24_at_5.04.16_pm.png

 

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.

 

 

 

Was this article helpful?
1 out of 1 found this helpful