Reconciliation Best Practices

Cheat Sheet: Reconciliation Best Practices in NetSuite Migrations

The Foundation of Good Reconciliation: Data Load

The basis for a successful reconciliation process is a solid data load. Ensuring that your data is properly mapped and loaded is critical to minimizing discrepancies and ensuring accuracy. For a smooth reconciliation, make sure you have the following:

1. Mapping Sheet for the Chart of Accounts (COA): Create a mapping sheet with the old COA and the new COA. This should include the internal database identifier, account number, and account name for both systems.

2. Opening Balances Loaded with External ID: Use the external ID as the line unique key from the source system when loading opening balances.

3. Customers and Vendors Loaded with External ID: Ensure that customers and vendors are loaded with an external ID that matches the line key from the source system.

---

Exporting Data from NetSuite for Reconciliation

There are two main options for exporting data from NetSuite for reconciliation purposes:

---

1. Export Using NetSuite Financial Reports

This option presents the financial data in a format that is consistent with NetSuite’s financial statements. Finance staff may find this approach easier to digest, as it closely resembles the standard financial reporting layout.

Benefits:

• The reports are presented in a familiar format, which can make them easier for finance teams to review.

Considerations:

• Customize the financial reports to include the Internal ID for each account. This makes matching records in Excel, using VLOOKUP or similar functions, much easier.

• Use Excel formulas to sum and match balances between the source and NetSuite.

• Be cautious of the polarity of accounts. Some accounts may show a positive balance in the financial reports, while they should be negative (or vice versa). This can happen when asset balances are incorrectly assigned to liability accounts, so make sure to correct for this during reconciliation.

Example Reconciliation Sheet

---

2. Export Using Saved Searches

Another option is to create saved searches in NetSuite to extract the necessary data for reconciliation, such as trial balances, open AR/AP transactions, or other transactional data. Saved searches allow for more granular control over the data.

Benefits:

• Saved searches can be tailored to your exact needs and can include fields that aren’t available in standard financial reports, such as Internal IDs and transaction-level details.

Example Saved Searches for Reconciliation:

• Trial Balance: Generate a saved search that captures account balances for each period.

• Open AR/AP Balances: Use saved searches to capture all open receivables and payables to reconcile with your legacy system.

• Transaction-Level Data: For detailed reconciliation, saved searches can export each transaction, allowing for a line-by-line match with the source system.

---

Key Reconciliation Best Practices:

1. Reconcile in Phases

Break your reconciliation process into clear phases. Start with high-level accounts like opening balances, accounts receivable (AR), and accounts payable (AP) before moving to more complex areas like inventory or deferred revenue.

Steps:

• Reconcile balance sheet accounts first to ensure assets and liabilities match.

• Move on to P&L accounts, ensuring the year-to-date balances match those in your legacy system.

---

2. Use Reports for Validation

Use NetSuite’s built-in reports and saved searches to validate data. Running reports during and after data load helps identify discrepancies early.

Steps:

• Run Trial Balance reports and compare them to your legacy system.

• Use Aging Reports for AR and AP to validate outstanding balances.

• Set up saved searches to compare transaction-level data between systems.

---

3. Reconcile Customer and Vendor Balances

Once AR/AP data is loaded, verify that customer and vendor balances match the legacy system. External IDs will help tie these transactions to the correct records.

Steps:

• Reconcile AR/AP reports in NetSuite with those from the legacy system.

• Check that customer and vendor balances match and use external IDs to track any discrepancies.

---

4. Validate Transactional Data by Line Item

For more detailed reconciliation, use the line unique key from your source system to track individual transactions, especially for multi-line entries.

Steps:

• Use transaction detail reports in NetSuite to compare each line item with the source system.

• Verify that each line is posted to the correct account and that no discrepancies exist.

---

5. Document Discrepancies and Adjust as Needed

Discrepancies are inevitable. Document these as they arise and adjust your data, mapping, or processes to ensure everything reconciles correctly.

Steps:

• Keep a log of any discrepancies and categorize them by type (e.g., incorrect account mapping, missing transactions).

• Adjust transactions or update mapping sheets and import scripts to fix recurring issues.

---

Summary of Best Practices:

• Start with a good data load, using external IDs and a solid mapping sheet.

• Reconcile in phases, starting with the balance sheet and moving through AR/AP and P&L accounts.

• Use reports and saved searches to validate data.

• Track line items with external IDs for detailed reconciliation.

• Document and adjust as needed to resolve discrepancies.