Exchange Accounting Reconciliation

OVERVIEW

This article describes the basics behind the reconciliation process that merchants must follow to make their Shopify reporting accurate. Merchants will learn:

  • What reconciliation means for Shopify accounting.
  • Why merchants need to reconcile their accounts.
  • Which factors play into reconciliation.
  • How to complete the reconciliation process.

Important:

  • Loop highly suggests reviewing the basic and then the advanced exchange accounting articles before continuing; these articles give context for why reconciliation is necessary.
  • This article is specific to Shopify merchants and merchants using Shopify as a source of truth for accounting.

In this article:

What is reconciliation?

For Loop merchants, reconciliation refers to identifying and resolving discrepancies in the Discounts, Returns, and Net Sales columns within Shopify's sales reports. This process includes the use of the Loop Sales Adjustment report to offset those discrepancies.


Why do merchants need to reconcile their reports?

Shopify's lack of native support for exchanges leads to accounting discrepancies that occur when:

  • Shopify makes order adjustments for returns without refunds (exchanges and store credit returns).
  • The Loop Discount is applied to exchange orders.

Therefore, financial reconciliation is necessary to arrive at accurate sales and accounting figures.

Note: Merchants that rely on a third-party finance tool for accounting (rather than Shopify) may not reconcile their accounts exactly like Loop recommends. However, Loop still encourages merchants to learn how Shopify and Loop interact regarding exchanges and store credit returns; this understanding can help merchants ensure that their third-party finance tools are accurate.


Loop's Sales Adjustment report

To reconcile their accounts, merchants can use Loop's Sales Adjustment report, which contains three values: discounts, returns, and net sales. By adding these adjustments to the corresponding values in Shopify's sales reports, merchants can resolve discrepancies and get accurate sales information.


Reconciliation instructions

The instructions below detail how to complete reconciliation for Shopify sales reports.

Important: Reporting information cannot be changed within Shopify. Therefore, reconciliation must happen outside of Shopify (in Excel or a similar platform).

1. Export the Shopify sales report

To export the Shopify sales report:

  1. In Shopify, go to Analytics > Reports.

    The Shopify home menu, with Analytics and Reports highlighted for emphasis.

  2. Select the report being reconciled. Loop recommends the "Sales by channel" report, but merchants can use any report with (or add to a report) the following fields:
    • Adjustment
    • Discounts
    • Gross sales
    • Net sales
    • Returns
    • Sale kind
    • Tax
    • Total sales

Important:

  • Adjustments must be in the report to yield accurate numbers. If the chosen report does not include adjustments, add them by clicking the Columns button.
  • For order-specific reports, add the "Order name" column by clicking the Columns button.

This image shows the Columns button in the top right corner of a Shopify report. It is a rounded square with three vertical sections.

  1. Select the dates to the range needed for account reconciliation, then click Apply.

    The image shows date range options on Shopify reports. Options include set parameters such as the last 30 days, 90 days, etc., as well as the ability to choose a custom date range.

  2. If using the "Sales by channel" report, please include all active sales channels aside from Draft Orders.

    This image shows the Manage Filters button in Shopify reports, as well as the options provided therein. This example shows the chosen filter, Sales Channel Name; a search bar with the Sales Channel Name options such as Loop Returns and Exchanges; a conditional dropdown to relate the filter and chosen channel name; and an Apply Filters button.

  3. After setting all columns, dates, and other parameters, click Export in the top right corner. Loop recommends exporting the full report.

    This image shows a view of Shopify's Sales by Channel report with the Export button in the upper right corner.

2. Export the Loop Sales Adjustment report

To export the Loop Sales Adjustment report:

  1. In the Loop admin, go to Analytics > Reports > Sales Adjustment report.

    This image shows the Loop admin menu with Analytics, selected, then Reports. The Sales Adjustment Report is at the bottom of the page.

  2. Set the Data Type field to Returns Processed and the Timeframe field to whatever range is needed for account reconciliation. The dates should match the date range set for the Shopify sales report.

    This image shows two Sales Adjustment Report filters: Data Type and Timeframe.

  3. In the top right corner of the report table, download the report as a CSV file.

    This image shows the Tile Actions button within Loop's Sales Adjustment Report. The icon is three vertical dots in the upper right corner of the report table. When clicking this, the image reveals a pop-up menu with the Download Data option.

    Tip: If experiencing issues with exporting, please follow the instructions in the Loop Reports article.


Note: Merchants can review the impacts of individual orders (returned orders or Loop-generated exchange orders) with this report, but the most important part of the report for account reconciliation is the top line.

3. Reconcile the reports

To reconcile the Loop and Shopify reports:

  1. Open a new sheet in Excel or a similar platform. This example uses Google Sheets.

  2. Import the Shopify sales report downloaded in the previous steps.

    This image shows a clicked File tab in the upper left corner of a Google Sheets document. There is a menu under the File button that includes the Import option, as well as some others.

    This image shows the pop-up that appears after clicking the Import option from the File menu. There are several import options, the preferred in this case being Upload, which contains a Browse button to let users choose files to upload from their computer.

  3. Open the Loop Sales Adjustment report outside of the spreadsheet and copy the totaled figures.

  4. In the spreadsheet, paste the totaled figures from Loop's Sales Adjustment report under the corresponding columns from the Shopify report.

    Image of a Google Sheets document with numbers from the Shopify sales report and Loop's Sales Adjustment report. The Discounts, Returns, and Net Sales numbers have been added together to produce reconciled sales numbers.
    This example shows an order-specific reconciliation. Review the next section to see an aggregate example.

    Note: The formatting on Loop's example was done for clarity. Merchants can format their reconciliation spreadsheets as needed.

  5. Total the Discounts, Returns, and Net Sales columns using both Loop's and Shopify's values. 
    1. The Discounts column will become more positive (this is when the Loop Discount is turned on).
    2. The Returns column will become more negative.
    3. Net Sales will become more positive by the amount on the adjustment report but will net out to become more negative due to the changes in the Discounts and Returns columns.

Good job! Having reconciled the discrepancies caused by Loop's exchange process, the Shopify sales report should now be accurate.


Reconciliation example breakdown (optional)

This section provides a more in-depth analysis of what each value represents in the reconciliation report. Totals for the Discounts, Returns, and Net Sales columns are bolded and shaded for clarity.

Aggregate-level reconciliation

Reconciling on an aggregate level is the most helpful way to use the Loop Sales Adjustment report. It helps merchants understand their total discounts, returns, and net sales figures for a given date range.

The image below shows a finished reconciliation of Loop's and Shopify's discounts, returns, and net sales numbers. The Loop Discount is configured to be on.

This image shows a Google Sheets document with rows and aggregate data from a merchant's Shopify sales report and Loop's Sales Adjustment Report. The last row shows the reconciled figures produced by combining the discounts, returns, and net sales values from both reports.
Click this image to view it in its original spreadsheet.

The report example above represents the following values by row:

  • Row two shows the aggregate data for the date range set on Shopify's sales report.
  • Row three shows the aggregate gross sales, discounts, returns, and net sales data from Loop's Sales Adjustment report for the same date range. The latter three are the most important.
  • Row four shows the reconciled discounts, returns, and net sales numbers from adding Shopify's totals to Loop's totals.

The reconciled numbers in row four show the following:

Discounts: The example merchant spent $46,928.27 in discounts for the date range set for the report. The positive discount adjustments from Loop's Sales Adjustment report cancel out Loop Discount amounts applied to new exchange orders, so the discounts left are ones the merchant gave.
Returns: The example merchant returned $56,435.51 to customers whose orders are included in the report's date range. The negative return adjustments on Loop's report cancel out Shopify's positive return order adjustments for the orders, leaving a more accurate and negative value to reflect the total of the actual return amounts.
Net Sales: The example merchant gained $227,690.58 in net sales. They did not lose sales on their returns because that return money was redirected to new exchange orders instead of refunds.

Order-level reconciliation

This sub-section explains and instructs how to reconcile on a more granular level, helping merchants understand how the Loop Sales Adjustment report impacts specific transactions and orders.

The image below shows a finished reconciliation of Loop's and Shopify's discounts, returns, and net sales numbers:

This image shows a Google Sheets document. The imported Shopify sales report has several rows: the original order, shipping charges, the return, the return order adjustment, and the new exchange order. Each row shows the values for the columns selected in the Shopify sales report. Below these rows is a row totaling the Shopify values. Under the Shopify totals have been added the Loop Sales Adjustment Report values for discounts, returns, and net sales. These values are added to the Shopify totals for those columns, which produce the actual reconciled figures for the exchange.
Click this image to view it in its original spreadsheet.

The report example above represents the following values by row:

  • Row two shows the values for the original order.

  • Row three shows shipping costs associated with the order.

    Note: This parameter was not mentioned in earlier instructions but is important for some merchants' reporting and was therefore included to visualize here.

  • Row four is the return row, representing the return itself.
  • Row five is Shopify's positive return order adjustment, created when the item was marked returned.

  • Row six contains values for the new exchange order. Its order name prefix shows it as an exchange.

    In this example, the merchant has the Loop Discount turned on. This is indicated by the exchange order's $148 discount (shown as -148), which brought the sale amount down to $0 for the exchange.

  • Row seven shows the totals for Discounts, Returns, and Net Sales from the first five rows.
  • Rows eight and nine are where the example merchant added Loop's Sales Adjustment report totals:
    • Row eight shows adjustments for the original order.
    • Row nine shows adjustments for the new exchange order.
  • Row ten shows the reconciled numbers from adding Shopify's totals (row seven) to Loop's figures (rows eight and nine).
This image shows a consolidated version of the previous report example. It contains the same rows but only the columns for channel name, order name, gross sales, discounts, returns, and net sales.
A condensed version of the example above, focusing on the Discounts, Returns, and Net Sales totals.

The reconciled numbers in row ten show the following:

Discounts: The example merchant spent $68 in discounts for this order. The Loop Discount applied to the new exchange order (cell G6) is cancelled out by the positive discount adjustment from Loop's Sales Adjustment report (cell G9), so the only discount left is the original one the merchant gave.
Returns: The example merchant returned $148 to the customer. The negative return adjustment on Loop's report cancels out Shopify's positive return order adjustment (cell H5), leaving a negative value for the amount of the return by itself.
Net Sales: The example merchant gained $272 in net sales. They did not lose sales on the return because that return money was redirected to the exchange order instead of a refund.

FAQ

Can I change the numbers directly in Shopify? At this time, merchants cannot change figures directly in Shopify. They must export the reports and reconcile them externally in Excel or a similar platform. Loop cannot edit the Shopify reports directly.

Which time zone does Loop reporting reflect? Loop reporting reflects GMT timezone.

Please contact support@loopreturns.com with additional questions.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Related Articles