AR and AP Balances
Loading AR and AP Balances in NetSuite
Introduction: Purpose of Loading AR and AP Balances
The main reason for loading Accounts Receivable (AR) and Accounts Payable (AP) balances during a NetSuite migration is to enable your AR and AP teams to manage outstanding transactions after go-live. This allows your finance team to work with open invoices and bills without affecting other areas of the accounts.
Key Points:
Transactions are loaded without tax.
One line per invoice/bill, even if the original had multiple lines.
Memos (both line-level and header) should be added to the memo field.
The double entry for each transaction will be between the AR/AP account and the Opening Balance Migration account.
Part-paid invoices/bills: Only the unpaid portion is loaded.
External ID for each transaction: Should be loaded as the "Line Unique Key" from the source system.
1. Creating a Migration Account in Your COA
Before starting, create a Migration Account (also known as an Opening Balance Clearing Account) in your Chart of Accounts (COA). This account should be of type Equity and assigned a unique account number (e.g., 99999) similar to a suspense account. It serves as a temporary holding account for migrated AR and AP balances.
Why it’s important:
Simplifies reconciliation: You can track and clear migrated balances easily.
Increases transparency: Helps monitor your migration progress and outstanding balances.
2. Managing Double Entries for AR and AP Balances
When importing your trial balance, AR and AP balances are already included. Importing detailed transactions (invoices, bills) will temporarily double these balances.
What to Expect:
Doubling: After importing detailed transactions, AR and AP balances will temporarily double.
Verification: Check that balances have doubled, confirming all transactions were imported correctly.
Resolving the Doubling:
To correct the doubled balances, create offsetting journal entries. These entries reset the AR and AP balances to the correct amounts.
Steps:
Import Transactions: Load all open invoices (AR) and bills (AP) from your source system. Include credit memos and bill credits, if applicable.
Verify Doubling: After the import, check that AR and AP balances have doubled.
Offset Journal Entries:
For AR:
Debit: Opening Balance Clearing Account (Equity)
Credit: Accounts Receivable
For AP:
Debit: Accounts Payable
Credit: Opening Balance Clearing Account (Equity)
Final Check: After posting, your AR and AP balances should match your trial balance. The Opening Balance Clearing Account should now have a zero balance.
3. Loading Transactions in Original Currency
If your source accounting system revalued transactions at the month-end exchange rate (which is common in many systems), the base currency balance of all open AR transactions, when summed at their original exchange rates, will not exactly match the AR balance in the loaded balance sheet.
How to Handle This:
There are options to manage this discrepancy, which are detailed in a separate article, Dealing with Foreign Currency Revaluations. These approaches help align the loaded transactions with the base currency balances in NetSuite.
4. Importing AR and AP Balances via CSV
When importing AR and AP balances via CSV, you’ll need to include certain fields to ensure the import goes smoothly. Below is an example of the fields required for importing an opening balance bill:
Field
Description
Entity ID
Internal ID for vendor or customer (e.g., external system ID)
Currency
ISO currency code for the transaction (e.g., USD, EUR)
Date
Original transaction date
Memo
Any relevant information for AR/AP teams (e.g., line or header-level details)
Due Date
Original due date of the transaction
Exchange Rate
Original exchange rate from the source system
Subsidiary
(If using OneWorld) The subsidiary for the customer/vendor
Item
Hard-coded to the opening balance item (e.g., "Opening Balance AR/AP")
Amount
Amount of the opening balance bill in foreign currency
Rate
Set to the same amount as the balance
Quantity
Hard-coded to 1
CSV Import Tips:
Use Internal IDs: For fields like "Entity" and "Item", always use internal IDs to avoid issues with special characters.
Test Imports: Start with a small dataset to catch any errors before importing the full data.
5. Importing AR and AP Balances via Script
When importing opening balances, using scripts offers greater control. It allows for data validation and confirmation with the finance team before posting transactions to the ledger.
NetSuite’s CSV imports can be challenging, especially when dealing with multiple subsidiaries or complex transactions. In such cases, writing custom scripts to load data into custom records is more efficient. This also allows for data validation before converting records to invoices or bills.
Custom Record: "Opening Balance Queue"
To handle the script import process, you would create a custom record called "Opening Balance Queue". This record should include all the fields listed above, plus additional fields for validation:
Entity External ID (text): Used to identify the customer/vendor from the source system.
Originating Transaction Type: Source system transaction type (e.g., invoice, bill).
Target Transaction Type: NetSuite transaction type (e.g., invoice, bill).
Processed (tickbox): To indicate when the balance has been processed.
Exclude (tickbox): To exclude specific balances from the import.
Error (text): Captures any errors during processing.
6. Map/Reduce Scripts for Importing Opening Balances
Script 1: Validate Opening Balance Data
This script validates that each item exists, sets the Internal ID based on the Entity External ID, and checks that the currency, subsidiary, and item are valid. Errors are logged in the Error field.
Script 2: Convert Custom Record to Transactions
This script transforms the validated custom record items into the actual transaction types (invoices, bills, etc.).
Last updated
Was this helpful?