Pages

2011/08/23

Bank Statement Automation in NetSuite

Bank statement reconciliation is an important step in the accounting process. It can be time-consuming to manually manage sales from approval to deposit and reconciliation, so here are a few tips to make your NetSuite experience more pleasant.

First, let's discuss the difference between a cleared transaction and reconciled one. A transaction starts its life as neither cleared nor reconciled. When you get confirmation that the money transfer is complete, the transaction is reputed as cleared. At the end of the month, when you get your official bank statement, every transaction can then be verified against the statement, corrected if necessary, and become reconciled. One way to view it is: cleared is for you, reconciled is for the auditor.

Many NetSuite users participate in e-business and deal with a very large number of transactions, which include a credit card processor such as PayPal's Payflow. In this case, it is useful to retrieve deposits via a CSV file import. Such a file is usually provided by the payment service provider. Unfortunately, NetSuite doesn't support direct third-party payment integration, nor does it support automated importation of bulk CSV data. Ideally, we would have liked to script a process by which we can work with deposits, but bank deposits are not an exposed part of the NetSuite API. This leaves us with a rather unseemly work-around by which we have to clear each transaction and mark them as reconciled. This solution works, but it will still make the reconciliation process difficult when dealing with exchange rates, ultimatel leaving us with rounding errors.

Since we are considering the problem of automated reconciliation, a custom record with matching fields should be created to receive the csv data. It's good practice to add an extra field (checkbox) in the custom record to flag processed entries, so as to limit the number of operations when running the script, which is described shortly hereafter.


















For help importing csv data, see your NetSuite Help Center


Once the data is imported, the process of creating a SuiteScript deployment, script and .js file is fairly straightforward.

1. Search for unprocessed records of the custom type we have set up for this purpose,

2. Find the matching transaction for each,













Navigating to the Cash Sales list

3. Set the Account (account) in which the payment processor makes the deposits (usually one per processor or at least one per csv file) on the Cash Sale record,











Manually setting a Cash Sale's account


4. Set the Cleared field (cleared) to t, i.e. true, on the Cash Sale record (usually found in the Chart of Accounts)






















The cleared (Clr) field in the chart of accounts


Once all deposited transactions are marked as cleared, we can reconcile them with one click on the "Mark all cleared to reconcile" [sic] button as illustrated below.













Navigating to Reconcile Bank Statement

















The elusive Mark All Cleared to Reconcile [sic] button


As of this writing, this is the most automated this solution can be. There's no telling what NetSuite may decide to expose through its API in the future. We can always hope...

3 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Thanks for the article. You images are missing, is there any way you can put them back up?

    ReplyDelete
  3. NetSuite's Accounting forces us to either use QuickBooks/etc to Auto Sync and Reconcile Entries or buy a super expensive add on.

    Unfortunately, the cost of either is still less than that of hiring a book keeper or allocating someone else from their regular job to import/export/match transactions for half a day.

    ReplyDelete