Blog

Easily reconcile your Sales Invoices into Xero!

Guide for easy Xero Reconciliation

You can now upload Sales Invoices and associated Transaction Fee Bills from your App and online Web Dashboard within Xero to allow easy reconciliation to Bank Statement Payments

A .CSV File is created with all unique invoices associated with Bank Payments occurring within the Selected Period. You can download it from the payments area on the Web Dashboard. Watch a video of how to do that here. This guide assumes you are familiar with Xero and more references are found at Xero Help Centre.

STEPS:

  1. DOWNLOAD .CSV FILES: Login to your online Web Dashboard click Payments tab and select the period in which you want to reconcile payments in your bank account with invoices. For reconciliation it is important to download both the Sales Invoice .CSV file and the Transaction Fee .CSV file as both will be required to reconcile against Job Payments received into your Bank Account, net of Fees.

  .

 

  1. EDIT SALES INVOICE.CSV FILE: It is worthwhile to check the .CSV file to ensure all the information for your Xero records is complete. 

The client, job, tax and transaction information will be automatically populated by SmartTrade app but you may want to add some extra information on, for example, Account Code or Tracking Names

Account Code: In order to save your Sales Invoices to the right line in your accounts one can either insert the Chart of Account Number Code in the .CSV file or if left blank, edit the imported Invoice within Xero (e.g. Insert 200 in Column for Account Code if you have the code 200 for Sales in your Xero Chart of Accounts).  Or you can edit to include individual Codes from the Xero Chart of Accounts for different lines in your accounts (e.g. 300 – Labour, 400 – Transaction Fees for Consumables etc.).

TrackingName: If you have multiple Business Divisions (e.g. Plumbing, Electrician) you may want to add the sub-division to your .CSV file so you can account for them separately in Xero.

 

  1. EDIT TRANSACTION FEE BILL.CSV FILE: It is worthwhile to check the .CSV file to ensure all the information for your Xero records is complete, as above. 

The payment provider, tax and transaction information will be automatically populated but you may want to add some extra information on, for example, Account Code or Tracking Names. In order to save your Transaction Fee to the right line in your accounts one can either insert the Number Code in the .CSV file or if left blank, edit the imported Invoice within Xero (e.g. Insert 400 in Column X if you have 400 - Transaction Fees in your Xero Chart of Accounts).  

  1. XERO IMPORT OF SALES INVOICES: Go to INVOICES OWED TO YOU on Xero Dashboard.

 

Click and then Select IMPORT.  

Select the Sales Invoice file by pressing the BROWSE button in Xero and referencing the file location you have saved the .CSV file. 

            

There are two options:

  1. Would you like to update contact address details – If it is a new contact then select Yes, update contacts with imported address details, otherwise No, ignore all address details can be selected if contact already in Xero. 
  2. Is the UnitAmount field tax inclusive or exclusive? – leave as Default Tax Exclusive as this is how it is calculated by SmartTrade in the .CSV file. 

             

Press Import Button and in next screen press Complete Import Button.

Once the Sales Invoice has been imported it can be approved, after review below, that all the required fields are populated (i.e. ensure Accounting Code is included).

 

The invoice can be edited in Xero by including the Account Code or Tracking Names (e.g. Division), if left blank in the .CSV file.

 

  1. XERO IMPORT OF TRANSACTION FEES: Go to BILLS YOU NEED TO PAY on Xero Dashboard.

    

Select IMPORT and follow the same instructions as given above for the import of the Sales Invoice.                        

           

Once the Transaction Fee Bill has been imported it can be approved, after review below, that all the required fields are populated (i.e. ensure Accounting Code is included).

 SmartTrade App - SME

The Bill can be edited in Xero by including the Account Code or Tracking Names (e.g. Division), if left blank in the .CSV file. The Transaction Fee references the Invoice number and Job Number of the associated Sales Invoice so these can be easily combined.

  1. RECONCILIATION OF PAYMENTS IN XERO: There will be a Stripe Reference Code (e.g. STRIPE-69QDDRB7173) in each Bank Payment in your Bank account shown in Xero Reconciliation Window. 

             

The first four letters/numbers of the Stripe Reference Code will be included in the .CSV files (e.g. 69QD) in the Reference column to link that Invoice to that Bank Payment. 

This is included in the Sales Invoice .CSV:

              And the Transaction Fee Bill .CSV:

This will allow reconciliation in Xero to be done simply, by including all the invoices for Sales and Transaction Fee Bills that are identified by that Stripe Reference Code (insert this Code in field, Search by name or reference in Xero) and associated bank payment. Stripe bundles multiple job payments so there may be several invoices and associated transaction fee bills that are reconciled to a single bank payment and code. Click on Show Spent items to also include the Transaction Fee Bill. 

Selecting the Items that have this Stripe Reference should then allow the Bank Payment to be reconciled to the Invoice less Transaction Fee. In this Worked Example, there is a 1p rounding error which is included as an Adjustment to reconcile the Bank Payment to the Sales Invoice and associated Transaction Fee Bill.

Note: The Consumable is the transaction fee applied to the Invoice Amount that is passed onto the Consumer (i.e. £0.98 from £0.82 + VAT in the Example). Stripe will charge a transaction fee (i.e. £1.01 in the Example) on the Total Amount processed which is the Invoice Amount plus the Consumable. There is therefore a net Fee amount (i.e. Fee on a Fee) that is payable by the User even when passing on the Invoice fee via the Consumable but this is a negligible cost (c. 0.07% of Invoice Amount or 3p in the Example).

Part Payments

If there is a P instead of the Stripe Reference Code this relates to a Part Payment of an invoice (e.g. initial deposit). A single invoice can only be imported into Xero so it is necessary to import this invoice once into Xero and split the invoice to the multiple part payments.  The first part payment will be assigned to this imported invoice and for subsequent payments reference will require to be made to the SmartTrade payment download to assign the balance of the bank payment to the appropriate invoice already in the system. 

In the Example below, a 50% deposit payment has been made against the Invoice and Transaction Fee Bill that are flagged with P.  It is necessary to show both the Invoice and associated Transaction Fee Bill by clicking on Show Spent Items in Xero. 

 

There is the option to Split these invoices and bills (i.e. assign 50% of the invoice to the payments in this case) by clicking on Split in Xero and filling in the appropriate part payment.

 Sales Invoice   |   Transaction Fee Bill

                         

This allows the bank payment to be reconciled against 50% of the Invoice and Transaction Fee bill. 

 

The part paid Sales Invoice and Transaction Fee Bill will remain in the Xero system until the outstanding payments have been made and 100% reconciled. The SmartTrade app will highlight all the jobs and invoices for each bank payment and as the outstanding part paid invoices are already in the Xero system, reference will have to be made to this SmartTrade summary to assign further part payments to the outstanding bank payment. If there is a net amount after reconciling all the Stripe Reference coded jobs then it should be an outstanding part payment, for which reference to SmartTrade summary will be required.

FINAL NOTE: The .CSV File does not contain Invoices that are created within the Selected Period unless actual Bank settlement has occurred. The Purpose of the Report is to allow reconciliation of Payments as opposed to a general upload of Invoices during this Period. The SmartTrade app will allow the User to review the status of unpaid invoices and chase accordingly, as opposed to undertaking such an exercise from Xero.

An automatic synchronised upload directly from SmartTrade to Xero is anticipated in future app releases. Other accounting packages will also be included in future releases.

Next article

comments powered by Disqus