Post-Launch Reconciliation


If Stripe is used in production without SuiteSync, there will be a transition period where some newly created transactions reference old Stripe data not processed by SuiteSync. These "pre-launch transactions" (i.e. transactions associated with invoices, charge, refunds, etc created before your launch date) are not pushed to NetSuite.

Additionally, cash collected before launch—but deposited afterwards—needs to be handled manually as well.

Handling this is simple, at the end of the month:

  1. SuiteSync will generate two reports: pre-launch transactions and unsettled cash.
  2. You'll create a journal entry to income and cash for all transactions that SuiteSync could not process because of the launch cut-off date.
  3. You'll create a journal entry to undeposited funds and your bank account to handle all unsettled cash collected before launch, but deposited after launch.

If you are using rev rec, or have a more complicated or customized setup, you may need to customize this process. The details below are aimed to provide the details to enable you to customize this process.

Pre-launch Transactions

Transactions created after launch, but associated with records created before launch are not translated to NetSuite.

For example:

  • Refunds issued for charges created before launch
  • Disputes (chargebacks) for charges before launch
  • Charges for invoices created before launch. This can happen if a customer's card failed to charge during a subscription renewal, which causes the date of the invoice and the charge which pays off the invoice to differ significantly.

SuiteSync can't represent these transactions because revenue and cash entries associated with the transactions were generated by another system (or manually). The integration can't determine if the original charge was accounted for in a journal entry, customer payment, etc.

In order to limit manual work during the transition period:

  1. Deposits are created, but the amount will not match your bank deposit if they contain pre-launch transactions. The correct deposit amount will be noted on the deposit's memo field to help reconcile these transfers.
  2. You will be given a report of the "missing" transaction. You can determine the total cash amount that is not accounted for because of these pre-launch transactions.
  3. You will be given a report of transactions collected before the go-live date, but settled after launch.

Here's what we recommend for representing these pre-launch transactions in SuiteSync

  1. Determine the total amount of missing cash using the month-end report provided by SuiteSync.
  2. Create a journal entry to income and cash for the total amount.

If your technical team wants to have more control and insight into this process, there's an open source version of this report available that you can customize and inspect.

View Open Source Report

Pre-launch Transactions: Report Structure

Here are the key columns provided in the pre-launch transaction report we provide:

  • Transaction ID
  • Related Transaction ID
  • Amount
  • Creation Date

Below are columns available if the transaction is associated with an invoice.

These columns provide detail about revenue. This is helpful for properly accounting for adjustments in pre-launch revenue based on post-launch refunds or disputes.

  • Invoice ID
  • Invoice Total
  • Plan ID
  • Rev rec start (available if a subscription-created invoice)
  • Rev rec end (available if a subscription-created invoice)

Unsettled Cash

The first month after launch, you'll have some amount of cash in your undeposited funds (sometimes called deposits in transit) account. Moving these funds to your bank needs to be done manually. This manual process only needs to be executed the first month after launch.

Here's an example of this case:

  • A charge is created on 12/30
  • The SuiteSync integration is launched on Jan 1
  • You run a charge export from Stripe and create journal entries to represent cash collected before 01/01 manually
  • You run a transfer export from Stripe and create journal entries for deposited cash before 01/01
  • The charge is deposited in your bank on 01/03

In this case, the charge created is manually accounted for, but remains in your undeposited funds account since it was deposited in your bank after launch.

The recommended manual adjustment is to create a journal entry debiting undeposited funds, and crediting your bank account, for the sum of the unsettled cash report that is generated.

Unsettled Cash: Report Structure

The report structure for unsettled cash is identical to the "Transactions Before Launch" report.

Reconciliation Guide

There are three key NetSuite numbers you can reconcile with Stripe:

  • Revenue. There is not an invoice export in the Stripe dashboard to determine Stripe revenue. Revenue reports for pre-launch transactions are provided in the report provided by SuiteSync
  • Settled cash. In NetSuite, this will be off by the day delay of your transfer schedule at the frontend of your launch date. The pre-launch transactions will also cause settled cash to be off. Use the transfer export from the Stripe dashboard to get these reports.
  • Cash Collected. Export payments from the Stripe dashboard; these will correspond to CustomerPayments and Refunds

Here's how to reconcile cash collected and settled cash:

  • Cash Collected. The payment export from Stripe equals the sum of CustomerPayments, CustomerRefunds, and the list of pre-launch transactions.
  • Settled cash. It's very hard to get this number to reconcile perfectly. Here are the components you'll need to combine:
    • Sum of all deposits in NetSuite for the month
    • Sum of transactions reported by the SuiteSync pre-launch report. Transactions towards the end of the month that settled in the next month should be excluded. It's challenging to determine the exact cut off date and time due to the way Stripe handles various edge cases. Here's more nitty gritty details on this
    • Sum of all transactions created before the 1st of the month, but deposited in the current month. Use the "Unsettled Cash Collected Before Launch" report for this.

Some things to consider:

  • The easiest way to determine the cut off date for payments settled within the last month is to look at the earliest transaction date of transactions contained in the first transfer of the next month. Use the day before as the cut-off date. Note that this will not be exact, but will get you pretty close.
  • Bank holidays, weekends, timezones, etc can cause settlement times to shift. For instance, a transfer from Jan 5th can contain transactions from 12/31.
  • Depending on your Stripe timezone configuration transactions in a given transfer can stretch over more business days than you would expect. For instance, in reference to the above example, a transfer from Jan 5th can contain transactions from 12/30. A 12/31 1 am GMT0 transaction will appear as a 12/30 transaction in the Stripe dashboard. Stripe seems to batch transactions based on GMT0, not on your local timezone
  • Settlement time can change depending on the payment method. ACH payments take much longer to settle compared to credit card or debit card payments.

Some helpful Google Sheets tricks:

  • Aggregate functions do not respect the filters in a sheet. In order to run an aggregate function on a filtered subset of the sheets rows use: =SUM(FILTER(C:C, D:D < "2017-01-27 00:00:00 +0000"))
  • You can stack filters. For instance, if you wanted to sum up all transactions between 01/27/17 and 02/27/17 you could use =SUM(FILTER(C:C, D:D > "2017-01-27 00:00:00 +0000", D:D < "2017-02-27 00:00:00 +0000"))
  • To extract the original amount from the memo of the deposit from a NetSuite CSV Deposit export use this formula: =IFERROR(DOLLAR(REGEXEXTRACT(G2, " -?[0-9]+.[0-9]{1,2}"),2),DOLLAR(H2))
  • To select deposits which did not match because they contained transactions before go-live =FILTER(D:D, REGEXMATCH(G:G, "WARNING"))
  • To filter deposits that were not generated by Stripe =FILTER(D:D, NOT(REGEXMATCH(G:G, "tr_")))