When using A2X for Shopify to reconcile your PayPal payouts in QuickBooks online, there are now two different methods to set up a bank connection: the old PayPal - QuickBooks Online bank connection and the new PayPal Connect - QuickBooks Online bank connection.
This guide will cover the process of setting up the PayPal account in QuickBooks, so that you can easily reconcile your A2X entries and the Shopify Clearing Account.
1/ New PayPal Connect > QuickBooks Online bank connection > PayPal payouts > Shopify Clearing Account
The A2X for Shopify account in this screenshot is set up to receive daily PayPal payouts:
If we review the Sep 21 - Sep 22 PayPal payout, we see that all sales, refunds, discounts, shipping and tax amounts are allocated as per our accounts and taxes mapping. There is also our 'clearing' line for the $198.88, which reflects the expected payment from the PayPal gateway for the orders in this payout. For more information on these gateway transactions, click HERE
When the journal is posted to QuickBooks, it will have a zero balance because we are not expecting to directly match the journal to a single deposit, and the expected payment has been allocated to the clearing account as shown below.
This means we have an amount in our clearing account that is waiting to be cleared out by the deposits.
As the deposits come into the PayPal bank account in QuickBooks Online, they will likely be on a per order basis. In our example below, we have the two deposits that make up the $198.88 sitting in our Shopify Clearing Account:
Notice how QuickBooks displays the net amount which is the $198.88 less the PayPal fees of $6.37. Broken down as follows:
Gateway payout total
$51.00 + $147.88 = $198.88
less the PayPal fees of
$1.78 + $4.59 = $6.37
The new PayPal Connect method in QuickBooks online drafts a sales receipt entry by default. This method is not recommended by us, as it is not necessary.
What we do recommend is to set up a bank rule to allocate the deposit amounts to the clearing account at the same time as allocating out the PayPal fees. The bank rule can set up so that it applies automatically as shown below.
Steps to set the bank rule up in QuickBooks Online:
You can call the rule anything you like, we've opted for 'Shopify PayPal Clearing'
The rule will generally apply for 'Money In' however if you have instances where there were negative payouts and funds were taken back from your PayPal account, then you may need to create the same bank rule for 'Money Out'
Make sure you apply the rule to the PayPal bank account only
Setting the rule condition as 'Bank Text contains PayPal' means that no transactions will be missed
Set the transaction type to 'Deposit / Credit card Credit'
We would generally leave the payee blank
Set the transaction type to 'Deposit / Credit card Credit'
Select Auto-categorize and manually review. This will allow you to review all transactions in the PayPal bank account before adding.
Ensure the 'Apply rule' option is selected
Once the rule has been saved, QuickBooks Online will automatically apply it to the future dated transactions. It is now a simple process of clicking 'Add' on the applicable ones:
Once the payments have been allocated to the clearing account, we can run a general ledger report on the Shopify Clearing Account to see that the account first received the expected payment from A2X, and then had the deposit payments applied to offset the balance:
You can see that the clearing account has fully cleared out. For high volume stores you may expect a small residual balance in the clearing account as it can take some time for payment deposits to arrive corresponding to the orders A2X has imported.
2/ Old PayPal > QuickBooks Online bank connection > PayPal payouts > Shopify Clearing Account
In this second example we will cover the process generally followed when using QuickBooks with a PayPal account that was connected to QuickBooks Online prior to 2019.
This is an example of a PayPal payout that covers one month:
The expected deposit amount is allocated to the Shopify Clearing Account.
This transaction reflects the expected payment from the PayPal gateway for the orders in this payout. For a more detailed explanation of how these gateway transactions work you can refer to this article.
In our example, we have mapped this transaction to the Shopify Clearing account.
When the journal is posted to QuickBooks, it will have a zero balance because we are not expecting to directly match the journal to a single deposit, the expected payment has been allocated to the clearing account as you can see below:
The corresponding deposits in the QuickBooks PayPal account would look something like this:
You can see the two deposits for 23.99 and 40.00 that will be allocated against the clearing account, and will clear out the expected 63.99 from A2X.
The PayPal Fee amounts in the example above of 1.25 should be directly allocated to payment processing fees.
For this scenario, you can also set up a bank rule so that the Shopify deposits automatically get allocated to the Shopify Clearing account.
Once the payments have been allocated to the clearing account, we can run a general ledger report on the Shopify Clearing Account to see how the clearing account firstly received the expected payment from A2X, and then had those payments applied:
You can see that the clearing account has fully cleared out as the payments for the orders have arrived. For high volume stores you may expect a small residual balance in the clearing account as it can take some time for payment deposits to arrive corresponding to the orders A2X has imported.
Hint: If you are in chat and wish to view this article outside of the chat window, please click HERE