Credit card reconciliation is the process of matching credit card transactions to general ledger transactions. It is an essential activity for every business, as it allows checking for fraudulent activities and preventing errors in financial statements.
Credit card reconciliation can be a time-consuming and tedious task, especially when it involves multiple credit card accounts, currencies, and high-volume transactions. Accounting software such as NetSuite can be valuable in streamlining the process.
NetSuite credit card reconciliation automatically allows users to match credit card transactions within minutes. It also allows users to manage multiple credit card accounts and currencies. The redesigned feature, in NetSuite version 2021.1, enables users to match and reconcile transactions without requiring Microsoft Excel or third-party tools.
To activate the new feature, go to Setup > Accounting > Manage G/L > Chart of Accounts, then click the Edit link of the account to be modified. The Account page will appear. Check the Use Match Bank Data and Reconcile Account Statement pages box.
The credit card reconciliation process starts by importing credit card statements into NetSuite. The imported transactions will appear on the Match Bank Data page, where users can view the imported credit card data lines and account transactions in NetSuite side by side.
The table below provides a high-level overview process of credit card reconciliation in NetSuite.
Certain permissions for NetSuite Banking features are required to do the credit card reconciliation.
NetSuite Credit Card Reconciliation Process
This section will walk users with the A/P Clerk role through the process of reconciling credit card statements in NetSuite.
Credit Card Statement Import
The process starts with the Credit Card Statement Import. When the statement is imported, NetSuite uses reconciliation rules or the Intelligent Transaction Matching features to automatically match imported lines to corresponding transactions.
The Intelligent Transaction Matching feature will attempt to run two types of rules:
- System Rules: The default matching rules in NetSuite that cannot be modified or removed. These default rules automatically match the imported credit card lines to the current account transactions.
- User Rules: Custom rules that were created, modified, and assigned by users.
There are two ways to import credit card statements:
- Automatic Import: This is applicable to users with the Administrator role. Importing credit card data can be automated by configuring a Format Profile page to set up daily automatic imports. It uses a connectivity plug-in and transaction parser to configure a direct connection to a financial institution to retrieve credit card data automatically. Plug-ins can be standard or customized. Standard plug-ins are available in the Bank Feeds Suite App. In contrast, Customized plug-ins were built using the Financial Institutions Connectivity Plug-in and Financial Institution Parser Plug-in interface. For more details, please see Netsuite Application Suite.
- Manual Import: This involves downloading credit card statements from the financial institution and manually importing them into NetSuite. Users can manually import credit card statements using Financial Institution records. To manually import credit statements, go to Transaction > Bank > Banking Import History > Upload File > Choose File. Under Import Options, select a Financial institution > Financial Institution list and then select a Financial Institution record. Select a Format Profile and click Import. If users don't have a Financial Record setup, they can manually import using a default parser. To import, Download the statement from the bank website using one of the accepted file formats: CSV, OFX, QFX, BAI2, or CAMT.053. Go to Transaction > Bank > Banking Import History > Upload File > Choose File. Under Import Options, choose Import with default parser and click Import.
- The Bank Feeds SuiteApp is only available in select countries, such as Australia, Canada, Europe, Hong Kong, Malaysia, New Zealand, Philippines, Singapore, Indonesia, Thailand, and the United States.
- The statement’s file name is limited to 50 characters only, including the extensions.
- To avoid performance degradation, users should not configure a manual import exceeding 10,000 transactions.
Note: If there is an error in the imported data, users can delete unreconciled credit card transactions; however, this is only possible if the import was successful or if data is imported manually and the account statement is unreconciled. To delete the imported statement, go to Transactions > Bank > Banking Import History. Click the “+” icon on the imported statement to expand the details. Then click Delete Import and click Ok.
Automated Cash Application
The next step after importing the credit card statement is to generate customer payments using the Automated Cash Application. This NetSuite feature allows users to automatically assign customer payments to each credit card imported line transaction if a match is found. Full access is required to use this feature.
To generate a customer payment, go to Transactions > Bank > Match Bank Data. Click Automated Cash Application in the top-right corner, then select the credit card account from the Account list. Review invoices for which payments will be applied, then click Submit and Ok.
- The Automated Cash Application does not support multiple customers per credit card line.
- NetSuite cannot generate a payment for credit card lines that specify invoice details if there are changes in the customer ID or invoice currency.
Note: The imported lines in the Automated Cash Application page only appear to credit card lines with positive amounts and credit card lines that have never been matched to an existing transaction in NetSuite.
The third step in the credit card reconciliation process is Manual Matching. Here, all the transactions that remain unmatched after running the Intelligent Transaction Matching and Automated Cash Application will be manually matched. To manually match transactions, go to Transactions > Bank > Match Bank Data. In the To Be Matched subtab, select the credit card account from the account list, and select the credit card lines to be matched against the NetSuite transaction. Check the Difference field in the bottom section of the page and ensure that it is 0 (zero), then click Match.
Bank Transaction Matching Types:
- One-to-One: Matching a single credit card imported line to a single account transaction in NetSuite
- Many-to-One: Matching multiple imported credit card lines to a single account transaction in NetSuite with the same transaction number
- One-to-Many: Matching a single credit card imported line to multiple account transactions in NetSuite
- Many-to-Many: The most complex type of data matching, involving multiple credit card imported lines and multiple account transactions in NetSuite
Submit Matched or Cleared Transactions
All matched and cleared transactions from the To Be Matched subtab are transferred to the Review subtab, which is the fourth step in the credit card reconciliation process. Once the transactions have been reviewed, click the Submit button in the upper left corner of the Match Bank Data page.
This is the final step in the credit card reconciliation process. Here, all the submitted transactions that have been matched or marked as cleared can be reconciled. To reconcile transactions, go to Transactions > Bank > Reconcile Account Statement and select the credit card account from the account list. Select all the transactions that need to be reconciled and click the Reconcile button at the upper left corner of the page. Click OK.
Challenges and Limitations
- Missing Invoices/Receipts:
All company credit card transactions should be accompanied by receipts to demonstrate that the purchases were legitimate and done on behalf of the business. If the receipts are missing, reconciliation will be difficult because there will be no transactions to book in NetSuite.
- Anonymous Transactions:
Some credit card statements lack sufficient information to identify a transaction. Extra research will be required, which may take time, especially if the credit card is shared, as the business will be unsure of whom to chase.
- Timing Issues:
Businesses typically close the books at the end of every month; however, some credit card statements are issued after the end of the month, which could force the business to wait before closing the books.
- Personal Purchases:
Personal purchases are occasionally made on credit cards supplied to some employees.
- Batched Transactions:
If one of the transactions in a batch payment doesn't match the reference, it will be difficult to reconcile the batch.
- Reconcile credit card accounts monthly to ensure no discrepancy between the general ledger and credit card statement.
- All credit card purchases must be supported by receipts and approval from the employee's manager before they can be made.
- Automate the reconciliation process by creating a rule for each recurring credit card transaction.
- If possible, minimize one-to-many, many-to-one, and many-to-many transactions.
Reconciliation of credit cards is one method of determining a business's financial status. Regular reconciliation enables the detection of inaccuracies and the prompt resolution of issues with banks and vendors. It also helps businesses closely monitor their employees' credit card usage to avoid fraudulent behavior.