If your Amazon Reserved Balance Account in Xero or QuickBooks doesn’t agree to the current reserved balance Amazon holds, then here are a few pointers of how to identify discrepancies and reconcile these.
(This article is written from the perspective of reconciling an Amazon reserved balances account, but the principles can be applied for any sales channel reserved balance account)
Please note firstly - it is important to make sure that all of your settlement invoices have been sent from Link My Books to Xero/QuickBooks for the period. Also, ensure that none have been voided or deleted on Xero/QuickBooks by mistake and that there are no duplicates.
If you have sent all your invoices, not voided or duplicated any, and followed the help articles from Link My Books, then your Reserved Balances account should balance off correctly to the exact amount that Amazon has reserved and owe you.
If this is not the case then this article will help you identify the difference and the common situations which result in discrepancies.
What is the Amazon Reserved Balances account used for?
The Amazon reserved balances account is used for two things:
Funds reserved by Amazon for future payout and the release of those funds
Split month settlement rollover balances (for other sales channels like Shopify, Etsy and eBay these split month rollovers are allocated to the split month rollover account instead)
Since these two items should always have an amount going into the reserved balances account and the same amount going back out again, it should be relatively straightforward to identify the ones causing the issue.
Begin by matching off all the transactions with corresponding matching debit and credit entries.
Then you’ll be able to see the remaining transactions you are left with that don’t match up, so you can then investigate these.
Download Account Activity Report from Xero/QuickBooks
Firstly, you’ll want to download the account activity report for your Reserved balances account (If you are using the LMB defaults then it will be the LMB9 Amazon Reserved Balances account on your Chart of Accounts.)
This can be found in Xero: Accounting > Account Transactions.
This can be found in QuickBooks: Chart of Accounts > Find the reserved balance account > Run Report.
It’s easiest for reconciling if you export this as an excel or numbers file.
If you sell on multiple marketplaces or currencies, you may find it easier to filter this report by Marketplace or Currency so you can handle each one separately in turn.
To do this on an excel file: on the Data tab, click Filter.
To do this on a numbers file: Click on the Organise Sidebar, click Filter.
Common Discrepancies
Exchange Rate Differences
One common situation is that if you handle foreign currencies within your accounts, then there are likely to be exchange rate differences which will need to be adjusted for.
In this example below, you’ll see that the source currency (EUR) balances off completely (1937.39 DR and 1937.39 CR). However the exchange rate used to convert this to the home currency of GBP is different over the two dates (£1676.86 DR, but £1653.63 CR). This has resulted in a £23.23 exchange rate difference.
You will need to make an adjustment for exchange rate differences via a journal entry between the Reserved Balance account and an Exchange rate profit/loss account.
Your bookkeeper or accountant will be able to advise how to do this if you are unsure.
Negative Balances Rolled Over
Another situation which may result in a discrepancy in your Amazon Reserved Balance account is when Amazon rolls over a negative balance on a settlement rather than charging your card on file.
This help article shows how to handle this situation.
You’ll read that there is an adjustment is needed on the negative balance settlement to move the balance to the reserved balance account. If you haven’t made this adjustment already, then your Reserved Balance account won’t have the needed CR entry to balance. Please follow that help article first, and then you will have the corresponding entry to balance off the Reserved Balances account.
In the example below you’ll see that for the first amount of 111.08 EUR, Amazon successfully charged the account on file for the negative balance and so they match off.
However the second amount of 2696.88 EUR is a negative balance rolled over by Amazon and currently has no matching entry in Reserved Balances because the help article process has not yet been followed.
The action needed to follow the help article is to find the settlement from Link My Books of -2,696.88 in Xero or QuickBooks, and make the following adjustment:
On Xero -
Add an extra line to the bill to net the total off to 0.00.
Description: Small Balance Rolled over by Amazon
Amount: -2,696.88
Account: Amazon Reserved Balances
You also need to edit the ‘Total’ box at the top right of the invoice to ‘0.00’
On QuickBooks -
Change the bank transfer line item on the journal.
Change the account from your bank account to the Amazon Reserved Balances account, and the description from ‘bank transfer’ to ‘Small Balance Rolled over by Amazon’.
Please see the help article for more details and screenshots.
Once the help article steps are followed, there will be a corresponding CR transaction in Amazon Reserved Balances to match this off.
Reserved Balances for Different Marketplaces
If you have your different marketplaces mapped to different accounts on Xero or QuickBooks, then it is vital that you correctly allocate any unknown marketplace settlements. If incorrectly allocated then this will cause problems whereby the debit entry is in one account, and the credit could be incorrectly allocated to another, meaning that both accounts would be out of balance.
Be sure to correctly identify and allocate ‘Missing Marketplace’ settlements correctly. See this help article for more details on that.
Opening Balances Issues
If you make the transition from manually accounting for your e-commerce sales or using another app, to then using Link My Books - you’ll need to make sure your that your opening balances for your Reserved Balance account are correctly matching up.
For example, if you start using Link My Books from 1st January 2023, you’ll need to make sure the Reserved Balance amount with Amazon at 31st December 2022 is correctly included in the Reserved Balance account, so the first settlement from Link My Books will then balance this out. If you are unsure how to check this is correct then please check with your accountant or contact Support.
Other Situations
This isn’t an exhaustive list of situations which may occur, but rather the most common areas.
If you have sent all your invoices, not voided or duplicated any, and followed the help articles from Link My Books then your Reserved Balances account will balance off correctly to the exact amount that Amazon have reserved and owe you.
If you have other items which do not match up on your Reserved Balances Account, then your accountant should be able to identify what the issue is within Xero or QuickBooks.
If you need further assistance or clarification on this article, then please reach out to the Support Team.
If you have any questions about this article or feedback on how we could make it better please reach out to the support team via the blue chat icon on the bottom right of the page or via email to [email protected].