Payment Reconciliation and Accounts Receivable Tracking
Tracking Accounts Receivable (AR) and Matching Third Party Payments to Each Order
MarginDriver's Payment Reconciliation and AR Tracking feature automatically reconciles the amount due on each order with an amount paid by third party payment processors and provides the user with an active list of unpaid orders that make up the Current AR Balance as well as the End-of-Month AR Balance for any previous month.
The original order data and related payment amount are pulled directly from marketplace and payment processor APIs, thus MarginDriver considers the calculated AR balance to constitute to a third party balance since it relies entirely on data provided by these entities.
The Payment Reconciliation and AR Tracking feature sorts the order data by sales channel, order date and payment gateway. The total revenue for each order is reduced by the corresponding fees for that order to arrive at the estimated amount due for each order. Although product COGS and shipping cost are used to calculate an order's gross profit, they are not a consideration in calculating the amount due on each order as these are generally paid by the seller. One exception is the fulfillment costs associated with using Amazon's Fulfillment by Amazon (FBA) service. Amazon covers the up-front cost of fulfilling these orders and then reduces the amount owed to the seller (AR balance). The cost of orders fulfilled by the merchant (FBM), on the other hand, does not reduce the amount due from the order's respective sales channel or payment gateways as they are covered by the seller.
MarginDriver continually accesses every deposit made by a third party and matches each payment within each deposit to its identified order and MarginDriver’s estimated amount due for that order. This same process is also used for tracking and matching refunds.
The payment made for each order is shown in the column titled Amount Applied with the date of the payment in the Payment Gateway column. All orders that are not yet paid are marked as Unpaid in the Payment Gateway column. The total of all orders marked as Unpaid equal either the Current AR Balance or the End-of-Month Balance for the selected month.
The AR Order Tracking table defaults to the Current Balance which is the AR balance for all currently unpaid orders up to and including yesterday’s unpaid orders. The user can view all orders, Paid and Unpaid, for the last four months by selecting Include under Paid Transactions. To view only Unpaid orders, the user would select Exclude under Paid Transactions.
MarginDriver currently performs payment reconciliation and AR tracking for the following integration partners:
- Amazon
- Shopify Payments
- Shopify Installments
- Klarna
- PayPal
- Walmart
- eBay
- Etsy
Other gateways, marketplaces and payment methods such as cash, check, wire transfer, crypto, etc. are not currently tracked by MarginDriver. All orders paid via these methods fall under the classification of AR - Other Gateways and get separated into their own accounts receivable account in the user’s general ledger. The user will need to manually track the payments for these orders and place their respective payments in the AR - Other Gateways GL account.
Breakdown of Order Payments by Date and Gateway
All deposits from integrated marketplaces and payment processors can be found in the Deposit Breakdown by Payment Date and Gateway feature on each Payment Reconciliation & AR Tracking page. The deposits are broken down by sales channel and payment gateway, as well as order date and payment date, giving the user a clear and simple way to view all deposits and itemized details of each order payment.
The sole exception to this presentation of data is Amazon Settlement Reports which are displayed in a unque format that allows for enhanced sorting and filtering of Amazon's many fees.
For all sales channels and payment gateways, users have the option of sorting and displaying payments by the selected month's order dates or payment dates.
Order Dates
When Order Dates is selected, all orders placed in the month selected will be displayed with their payment amount and payment date for the sales channel and gateway selected. Only orders that have been paid will be displayed when Order Dates is selected; unpaid orders will not be shown. This allows the user to view all of the orders placed in the month selected with the date and the amount paid for each order, as well as the full deposit amount. This includes orders from the selected month that are not paid until the following month.
Payments Dates
When Payment Dates is selected, all deposits/payments made within the selected month will be displayed and organized by payment date. The user can click on any deposit to reveal a dropdown showing each order paid with in each deposit/payment (including orders placed in a prior month). This allows the user to view only the deposits/payments and orders paid in the selected month.
Breaking Down Amazon Settlement Reports
Breaking down payments from Amazon is handled differently by MarginDriver than payments from any other marketplace or payment gateway. This is because Amazon typically only pays every 14 days with what is known as Amazon Settlement Reports. Each Settlement Report includes an extensive amount of information in addition to the actual deposit amount. MarginDriver provides an in-depth analysis of the information contained in each Amazon Settlement Report.
The user can easily sort through their Amazon Settlement Reports by marketplace and payment date to select the report to be displayed in detail at the bottom of the page.
The deposits and the various adjustments to arrive at the amount paid are shown in detail in the currency of the Amazon sales channel being viewed (Marketplace Amount). And the same amounts are also shown in the second column in the user’s local currency (USD Amount). The USD Amount values are displayed in orange to indicate that they have been converted using the published currency conversion rate on the date of payment. These values will change to black once the deposit reaches the user’s checking account and the actual currency conversion rate on the day of the deposit is applied.
The line items in the Settlement Reports that make up the amount of the deposit include product sales, shipping revenue, order commissions, FBA fulfillment fees, advertising costs, inventory adjustments and a host of other fees. Several options are provided for sorting through the data including the capability to sort the data by transaction type or order ID and filter by transaction type, amount type or amount description, with additional sub-filters under each of these categories.
MarginDriver automatically sorts through and categorizes the data in each Settlement Report, allocating the releveant revenue and fees to their proper GL accounts, whether they pertain to gross profit, operating expenses or balance sheet transactions. Those pertaining to gross profit, including direct and indirect Amazon fees, are included in each individual order’s gross profit calculation in Order Detail. The summarized totals for the month from Order Detail are placed in journal entries and then sent to the user’s general ledger system via the Close Month & Posting Results to GL process. The Amazon Settlement Report amounts pertaining to operating expense and balance sheet accounts are shown at the bottom of the Gross Profit Statements as additional information, before also being populated in the month-end journal entries and sent off to the user’s GL.