NetSuite | Settlement Reporting

In this article

Overview

Examples

Mappings

Overview

What are settlement reports/statements? -- A grouping of individual order payments that get batch deposited into merchant's bank account by the selling channel i.e. TikTok Shop, Shopify, Amazon. The batch deposit shows up as a single line entry in the merchant's bank account, but without detail on which orders and fees made up this batch deposit.

How are settlement reports used in relation to an ERP system? -- Monthly, the finance team needs to reconcile the deposits they see in their bank statement against the payments showing in their ERP. i.e. bank statement shows a $10,000 deposit from TikTok Shop for the month of June, ERP shows thousands of smaller payments related to customer orders from TikTok Shop. If settlements were to be entered manually into the ERP, the merchant would go through all of the small payment amounts in the ERP, combine them together along with fees taken by TikTok Shop, and create a batch deposit in the ERP totaling the same number as what shows up in their bank account/payout. For a low volume use case, this can be done manually by looking at the settlement report, selecting all of the relevant order payments in the ERP, adding the fees incurred, and saving the deposit in the ERP.

Why go through this process at all? -- The bank reconciliation process allows a finance team to confirm that each transaction in their bank statement is properly accounted for in their ERP system. In a perfect world, the ERP can automatically match up a bank statement to a bank report from the ERP without user intervention, so long as the deposit amounts all show equal $ values across both reports. In order to get the values to match up, there has to be a process of batching all the individual order payments shown in the ERP into the same batches that the selling channel used in each payout. Some channels do this biweekly, monthly, or at closer intervals, so the batches and number of included transactions can vary.

Why is this process such a pain point for merchants? -- For any use case involving higher volumes of orders, the manual process of selecting each relevant transaction and adding fees to create a batch deposit in the ERP is impossible. To do this, the merchant will need to go line by line against the bank statement and ERP ledger. This process can be automated to match up transactions if their amounts and info match in the ERP vs. the bank statement. The issue lies in the fact that the ERP will show thousands of orders individually, while the bank shows one lump sum. This will not auto-match up, and requires a process of logic to figure out which individual orders and fees incurred should be totaled together to equal the lump sum from the batch.

Channels supported: Shopify, TikTok Shop, Amazon

Examples

Example statement from TikTok Shop

Example Pipe17 view

Example NetSuite view of statement (bank deposit record in NetSuite)

Fees are represented within the 'Cash Back' subtab on the NetSuite bank deposit

Any variance showing in NetSuite compared to the payout amount from the selling channel will show up on the 'Other Deposits' subtab if it is a positive amount (+) or 'Cash back' subtab if it is a negative (-) variance

• Variance can happen due to different reasons such as an order was missing from NetSuite, but was on the statement from the selling channel - maybe this is an old order from prior to the go live date, or the order failed due to some other reason to be created in NetSuite
• Issues with orders will show up as exceptions on the order and on the statement
• Variance could also be due to differing order totals in NetSuite vs. the selling channel. This is typically seen if the mappings are incorrectly set up and can be remedied by adjusting the order mappings i.e. mapping tax on an order, mapping a discount item etc

End goal - bank reconciliation process can auto-match the bank deposits from the selling channel

Mappings

Work with Pipe17 rep to add mappings. Our recommendation is to begin by mapping the top level (summarized fees per statement level). Work on adding a more detailed breakdown once this is live and working as desired. 

Choose a NetSuite GL account to use for:

• Total fees
• Total adjustments
• Bank account for the statement payout amount
  • The individual orders will be moved from the Undeposited Funds native NetSuite GL account into this bank account once paid out by the channel
• A separate account to post any variances to (variances may occur if an order is not found in NetSuite that was on the statement, or other reasons). Pipe17 will work with you to resolve any repeating variance issues. Over time, this variance should be negligible