Post-Launch Reconciliation

When you go live with SuiteSync, there is an official “launch date”. If you have created transactions in Stripe before this date, these are not pushed to NetSuite. Additionally, transactions created after launch, but associated with records created before the launch will also be excluded. These transaction, will need to be manually reconciled.

Here are some examples of these types of transactions:

  • Refunds issued after launch, for charges created before launch
  • Disputes (chargebacks) issues after launch, for charges before launch
  • Charges launch 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.

Here is how this process is handled

  1. SuiteSync generates two reports: pre-launch transactions and unsettled cash.
  2. You 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 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 for this can be found in the “pre-launch transactions” section below.

What we do to make this process easier:

We have two options to make the go-live reconciliation process simple:

  1. We can summarize the skipped payments and refunds as line items on the "Cash Back" and "Other Deposits" area of the deposit.
  2. We can "skip" the transactions and provide you a report with missing transactions.

1. Automatic Summary of Skipped Transactions

  1. You set an account you'd like to use for all payments and refunds that are associated with transactions before go-live
  2. SuiteSync summarized the payments and refunds on a payout that were skipped because they were associated with transactions before go-live. The summarized amount posts to your selected account.

Here's how this looks on your deposit:

https://cl.ly/1H2H3g3a0y3S

2. Skip Transactions before Go-live

Here's how this option works:

  1. Deposits are created, but the amount will not match your bank deposit if they contain pre-launch transactions. We note the correct deposit amount on the deposit's memo field to help reconcile these transfers.
  2. We give you 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. We give you a report of transactions collected before the launch date, but settled after launch.

What you need to do

Here's the easiest way to manually account for the missing transactions:

  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.
  • Dispute reversals are never included in this report. They are accounted for on the deposit as a separate line item. Note that dispute reversals occurring at the end of the month, but deposited at the beginning of the next month, will cause undeposited funds to be slightly inaccurate.

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, "po_")))
  • To extract the payout ID from the sheet: =REGEXEXTRACT(G2, "po_[^\s]+")
  • To extract any Stripe ID from a field: =REGEXEXTRACT(A1, "(?:dp|py|ch|re|pyr|pyd|in|or)_\w+")