# Overview ## Overview: NetSuite Data Migration Guide Whether you're moving data from another system or restructuring your current NetSuite environment, this guide will walk you through the key steps and best practices for a successful data migration. *** ### 1. Project Timeline Effective planning is critical for a smooth data migration. In this section, we outline the recommended timeline for trial and live data loads. You'll learn how to use NetSuite's sandbox for testing, when to load static records, and how to prepare for the final live cutover. **Key Points:** * **Multiple sandbox data loads:** Perform multiple data loads in your sandbox environment to refine your process and resolve potential issues early. * **Early Static Record Loads:** Load static records like the Chart of Accounts (COA), Customers, Vendors, and Items early in the project for testing and validation purposes. * **Final Data Load Preparation:** Ensure the final load of static records takes place 2-4 weeks before the cutover date. Leave time for last-minute updates (e.g., addresses or other frequently changing data). [👉 Go to Project Timeline](/project-timeline) *** ### 2. Order of Data Load The sequence in which data is loaded plays a crucial role in ensuring the accuracy and integrity of your migration. This section outlines the recommended order to follow when loading data into NetSuite. **Key Points:** * **Step-by-Step Data Load Sequence:** Start by loading the Chart of Accounts (COA) and Subsidiaries to establish the foundation for other data. * **Load Static Records First:** Ensure that foundational data like Customers, Vendors, and Items is in place before loading transactional data (e.g., open AR/AP, inventory). * **Special Considerations for Historical Data:** Learn how to handle loading historical data such as Trial Balances, and how to ensure proper reconciliation. 👉 [Go to Order of Data Load](/loading-the-data/order-of-data-load) *** ### 3. Best Practices Migrating data to NetSuite can be complex, but with the right strategies and shortcuts, you can avoid common pitfalls. This section provides quick tips and best practices to ensure your migration runs smoothly. **Key Tips:** * [**Use External IDs**](/best-practices/use-of-external-ids)**:** External IDs are crucial for tracking and reconciling records across systems. They help ensure consistency and make it easier to reload or update records. * [**Handling Historical Data**](/best-practices/handling-historical-data-in-netsuite-migrations)**:** Historical data needs special attention. Here you'll find tips on how to load trial balances and handle transactional history. * [**Reconciliation Tips**](/best-practices/reconciliation-best-practices)**:** Ensure your migrated data aligns with your legacy system at every step by using automated reconciliation and validation methods. *** ## Top Tips & Cheat Sheet Main Page This page will be a short hub that links out to the individual cheat sheet items. ### Key Tips for a Successful Migration: 1. **Use External IDs:**\ External IDs ensure records can be uniquely identified, making it easier to reconcile and reload data if necessary.\ 👉 [Learn More About Using External IDs](/best-practices/use-of-external-ids) 2. **Handling Historical Data:**\ Historical data requires careful attention during migration. Learn the best approach to loading trial balances and transaction history.\ 👉 [Learn More About Handling Historical Data](/best-practices/handling-historical-data-in-netsuite-migrations) 3. **Reconciliation Best Practices:**\ Accurate reconciliation is key to ensuring that data in NetSuite matches your legacy system. Follow these tips for automated reconciliation processes.\ 👉 [Learn More About Reconciliation](/best-practices/reconciliation-best-practices) # Order of Data Load ## Order of Data Load: Key Sequence for NetSuite Data Migration When migrating data to NetSuite, the order in which you load your data plays a critical role in ensuring accuracy and preventing issues. This section will walk you through the step-by-step order to follow when loading your data into NetSuite. Loading data in the right sequence helps avoid dependency issues. For example, your **Chart of Accounts (COA)** and **Subsidiaries** before loading entities or transactions. Also checking at each stage as for example some subsidiary data cannot be changed after other data is loaded. While every project is different, the following order is a tried-and-true sequence that ensures smooth data migration. *** ### 1. Chart of Accounts (COA) The **Chart of Accounts** (COA) is the backbone of your financial system. Since all transactions will reference accounts from the COA, it needs to be loaded first. **Key Points:** * Ensure the COA structure is **finalized** and reflects the financial reporting needs of your organization. * Maintain a mapping document of the old system accounts to the new system account. You will need this during reconciliation. *** ### 2. Subsidiaries (if using OneWorld) If you’re working in a multi-subsidiary environment (NetSuite OneWorld), you’ll need to set up your **subsidiaries** early. Each subsidiary acts as a separate legal entity, so it’s important to have these in place before moving on to other data. **Key Points:** * Plan out your subsidiary hierachy carefullt as this cannot be changed later (or at least it is difficult to change this in many cases). * Define each **subsidiary’s base currency** - make sure you check details like this carefully at this can't be changed later! * Subsidiaries are required before loading any subsidiary-specific data (like transactions or employees). * Before assocaiting a tax nexus, check the subsidiary data carefully. *** ### 3. Tax Nexus and Tax Codes Once your subsidiaries are set up, it’s time to configure your **tax nexus** and **tax codes**. This step ensures that NetSuite knows where your business has tax obligations and how taxes should be applied to transactions. **Key Points:** * Set up tax nexuses for each region where your business operates (states, countries, etc.). * Link the appropriate tax codes to each tax nexus so that NetSuite can calculate taxes correctly for sales and purchases. *** ### 4. Departments, Classes, and Locations **Departments**, **Classes**, and **Locations** are essential for organizing and reporting your data. These categories help segment your data so that you can generate reports by department or location, for example. **Key Points:** * Load these **early** so they can be assigned to transactions when they are later imported. * Make sure your team has agreed on how you’ll use each segment (e.g., whether to track by location, department, or both). *** ### 5. Customers, Vendors, and Items With the backbone of your data in place, it’s time to load your **customers**, **vendors**, and **items**. These static records form the foundation for transactional data, such as invoices, bills, and sales orders. **Key Points:** * **Customers** and **vendors** need to be set up before importing any AR/AP transactions. * **Items** (inventory and non-inventory) need to be loaded before transactions like inventory receipts or sales orders. * Plan to load **customer and vendor addresses at go-live**. If they were loaded earlier in the project, you can perform a **full replacement** just before cutover. *** ### 6. Opening Balances / Trial Balance After loading static records, the next step is to bring in your **opening balances** and **trial balance**. This step ensures that your financial records are in sync between your legacy system and NetSuite. It may be that in practice, you cannot get closing balances from the old accounting system due to deplays in closing, if this is the case, don't worry, just load any historical months you need to load. You can come back to this and load the go live month at a later point. **Key Points:** * Import historical **trial balances** if needed for financial reporting. * Use this opportunity to ensure all accounts are reconciled between NetSuite and your legacy system. *** ### 7. Open Accounts Receivable (AR) / Accounts Payable (AP) Once the COA and static records are in place, it’s time to load **open AR and AP transactions**. These represent outstanding invoices and bills at the time of migration. **Key Points:** * Ensure that your AR and AP transactions tie out to the balances from your legacy system. * After loading, run a reconciliation to make sure the **aging reports** in NetSuite match your old system. *** ### 8. Open Sales Orders / Purchase Orders Next, you’ll want to bring in any open **sales orders** and **purchase orders**. These transactions reflect commitments that have been made but not yet completed. **Key Points:** * Verify that your sales and purchase orders are correctly linked to customers, vendors, and items. * Make sure to load these transactions before any inventory updates to avoid mismatches. *** ### 9. Inventory If you’re using NetSuite to manage inventory, you’ll need to import your **inventory quantities** and **values**. This is critical for ensuring accurate stock levels and cost of goods sold. **Key Points:** * Ensure **items** have already been loaded before bringing in inventory quantities. * Reconcile your inventory levels to match the quantities and values from your legacy system. *** ### 10. Deferred Revenue Lastly, if your organization deals with **deferred revenue**, you’ll need to load those transactions so that revenue can be recognized properly over time. **Key Points:** * Ensure that deferred revenue entries are tied to the correct accounts and transactions. * Make sure that the **revenue recognition schedules** are set up correctly in NetSuite. *** ### Summary of the Data Load Sequence: 1. **Chart of Accounts (COA)** 2. **Subsidiaries (if using OneWorld)** 3. **Tax Nexus and Tax Codes** 4. **Departments, Classes, and Locations** 5. **Customers, Vendors, and Items** 6. **Opening Balances / Trial Balance** 7. **Open Accounts Receivable (AR) / Accounts Payable (AP)** 8. **Open Sales Orders / Purchase Orders** 9. **Inventory** 10. **Deferred Revenue** *** By following this order, you’ll set up the foundation first and ensure that each subsequent data load builds on the previous one. This approach minimizes the risk of errors and ensures smooth financial reporting in NetSuite. # Chart of Accounts ## Loading the Chart of Accounts in NetSuite When setting up your **Chart of Accounts (COA)** in NetSuite, there are several fields that you need to configure for each account. Below is a guide to the most common fields you’ll encounter, along with advice on how to approach setting them. *** ### **Key Fields for Setting Up Accounts** #### **1. Account Number** When setting up your Chart of Accounts (COA) in NetSuite, it’s recommended to align your numbering system with the NetSuite standard where possible. This provides consistency and improves reporting clarity. NetSuite’s default numbering system assigns four-digit account numbers as follows: * **1xxx**: Asset accounts * **2xxx**: Liability accounts * **3xxx**: Equity accounts * **4xxx**: Income accounts * **5xxx**: Cost of Goods Sold (COGS) accounts * **6xxx**: Expense accounts * **8xxx**: Other Income accounts Working with your finance team to agree on a structured numbering system that fits your business needs is crucial. Following these guidelines helps maintain standardisation across accounts and enhances the overall efficiency of your financial reporting. #### **2. Name** * **Advice**: Enter the **name** of the account. If renaming a built in account, make sure you don't change the meaning of the account!!! #### **3. Subaccount of** * **Advice**: If the account is a **subaccount** of another parent account, select the parent account here. This helps in creating a hierarchy within your COA, useful for reporting purposes. #### **4. Type** * **Advice**: Set the **type** of account (e.g., Income, Expense, Asset, Liability). This field is crucial because it affects how transactions are posted to the account and how they appear in financial reports. #### **5. Currency** * **Advice**: **Leave this field blank** unless you are creating a **bank account**. For bank accounts, set the specific currency in which that account operates. Non-bank accounts should not have a currency set unless you need to restrict them to one currency. #### **6. General Rate Type** * **Advice**: **Leave the default value** as it is. This field is related to exchange rates, and in most cases, the default value works perfectly. #### **7. Revalue Open Balance for Foreign Currency Transactions** * **Advice**: Only tick this option for **balance sheet accounts** that hold balances in a foreign currency that you need to revalue at the end of each month. Examples include: * **Accounts Receivable (AR)** * **Accounts Payable (AP)** #### **8. Subsidiary** * **Advice**: Except for **bank accounts**, select the **parent subsidiary** and tick the **"Include Children"** checkbox so the account is available to all subsidiaries. For subsidiary-specific accounts like bank accounts, select the specific subsidiary. *** ### **Required Account to Create** * **999999 Opening Balance Migration** * **Advice**: Create this account as a **clearing account** to handle the migration of opening balances. This account will be used temporarily to hold balances during the migration process. *** ### **NetSuite's Built-In System Accounts** NetSuite provides a number of built-in **system accounts** by default, such as the **default AR and AP accounts**. While these accounts are necessary for the system to function, you can rename or renumber them to fit your company’s accounting structure and policies. However, make sure that: * You **maintain the same account type** (e.g., leave the AR account as a "Accounts Receivable" type). * The account’s **meaning and use remain consistent** with the system requirements. *** By following these steps, you’ll ensure that your Chart of Accounts in NetSuite is well-structured and aligned with your company’s financial reporting needs. This approach helps create clarity and maintain consistency across the organization. # 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:
FieldDescription
Entity IDInternal ID for vendor or customer (e.g., external system ID)
CurrencyISO currency code for the transaction (e.g., USD, EUR)
DateOriginal transaction date
MemoAny relevant information for AR/AP teams (e.g., line or header-level details)
Due DateOriginal due date of the transaction
Exchange RateOriginal exchange rate from the source system
Subsidiary(If using OneWorld) The subsidiary for the customer/vendor
ItemHard-coded to the opening balance item (e.g., "Opening Balance AR/AP")
AmountAmount of the opening balance bill in foreign currency
RateSet to the same amount as the balance
QuantityHard-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.).
# Project Timeline ## Project Timeline: Key Steps for a Successful NetSuite Data Migration This article is for you if you're planning a data migration to NetSuite and want to keep the process on track. Below is a timeline that outlines the major steps involved, assuming an average 12-week project. However, keep in mind, your project timeline might vary depending on complexity. While data migration is its own process, it runs in parallel with the overall NetSuite implementation. Getting your data into the **sandbox** early helps with User Acceptance Testing (UAT), allowing your team to test functionality with actual data as you build out the system. *** ### 1. Pre-Migration Planning (Weeks 1-2) The first step in any data migration project is planning. This phase sets the foundation for everything that follows. You'll want to get your team aligned and make sure you're clear on what data needs to be migrated, who's responsible for what, and how data from your old system will map into NetSuite. #### Key Tasks: * **Define the scope:**\ Start by figuring out exactly what data you need to migrate. This usually includes financials, customer and vendor information, and inventory details. * **Assign roles:**\ It’s important to identify who will be handling each part of the process. You’ll need people for data extraction, validation, IT support, and user testing. * **Set up data mapping:**\ Begin mapping fields from your legacy system to NetSuite. Use **External IDs** to track records and make reconciliation easier. Start by creating a data migration template that maps fields from your old system to NetSuite. * **Sandbox setup:**\ Get your sandbox ready early so you can use it for testing. This is where you’ll load data first, and it’s important to have it set up properly from the start. * **Finalize the Chart of Accounts and tax setup:**\ Before migrating any data, make sure your **Chart of Accounts (COA)** is finalized and ready to go. Similarly, ensure your **tax nexus** and **tax codes** are set up correctly. #### Deliverables: * Completed Data Mapping Document * Sandbox Environment Set Up * Finalized Chart of Accounts *** ### 2. Initial Data Loads in Sandbox (Weeks 3-5) Once your planning is done, it’s time to load some data into the sandbox. This step is all about testing the waters—identifying any issues with your data before the final load. #### Key Tasks: * **Trial load of static records:**\ Load your static records like **Chart of Accounts**, **Customers**, **Vendors**, **Items**, etc., into the sandbox. The earlier you do this, the more time you have to identify issues. It’s also helpful for functional UAT testing. * **Data validation:**\ After loading, cross-check the data with your legacy system to make sure everything came through correctly. This includes checking customer/vendor details and balances. * **Refine data mapping:**\ If you notice any issues (and you probably will), adjust your data mapping. This phase is about catching errors, like missing fields or mismatched data formats, before the final load. #### Deliverables: * Static Records Loaded in Sandbox * Data Validation Report * Updated Data Mapping *** ### 3. Trial Load for Transactions (Weeks 6-7) Now that static records are in the sandbox, it’s time to move on to transactional data like **open AR/AP**, **inventory balances**, and **trial balances**. #### Key Tasks: * **Load transactional data into sandbox:**\ Perform a trial load of **open AR/AP**, **inventory**, and **historical trial balances** into the sandbox. This is where you’ll check how well your system handles transactional data. * **Reconcile the data:**\ Run reconciliations between the sandbox and your legacy system to ensure that everything matches up. Use **External IDs** to help with tracking and reconciliation. * **Fix issues and update scripts:**\ Like before, identify any problems with the data. Adjust import scripts or data formats where needed to fix issues. #### Deliverables: * Transactional Data Loaded in Sandbox * Reconciliation Reports for AR/AP, Inventory, and Trial Balances * Updated Import Scripts *** ### 4. Final Testing & User Training (Weeks 8-9) With data in the sandbox, now is the time for final testing and to get your team trained on the system. #### Key Tasks: * **Final static data load in sandbox:**\ Load the latest version of static data into the sandbox to ensure everything is clean. Make any last-minute updates before the final production load. * **Code Freeze:**\ At this stage, your implementation should be complete (2 weeks before go live), not code changes should be take place. * **User Acceptance Testing (UAT):**\ This is where your team checks that the system is working as expected. They’ll test processes like creating sales orders, processing invoices, and running financial reports. * **End-user training:**\ Make sure your team knows how to use the system. Focus on their specific roles, like entering customer orders or processing payments, so they’re comfortable with the new data and system setup. #### Deliverables: * Final Data Validation in Sandbox * UAT Completed * User Training Documentation *** ### 5. Live Data Migration & Cutover (Weeks 10-11) Now comes the real deal—moving your data into the live NetSuite environment. #### Key Tasks: * **Freeze data in the legacy system:**\ Stop entering new transactions in your old system during the final migration. This prevents mismatches between the systems. * **Final static data load into production:**\ Load the final static records (COA, Subsidiaries, Customers, Vendors, Items) into NetSuite. **Customer and vendor addresses** should be loaded at **go-live**, but make sure the import is **tested and prepared** ahead of time. If addresses have already been loaded, a full replacement of addresses can be performed at go-live. * **Transactional data load into production:**\ Load open AR/AP, inventory balances, and historical trial balances into the production environment. * **Final reconciliation:**\ Perform a final reconciliation between NetSuite and your legacy system. Ensure that all opening balances, open AR/AP, and inventory match. #### Deliverables: * Final Static and Transactional Data Load in Production * Reconciliation Reports (AR/AP, Inventory, Balances) * Cutover to NetSuite Live *** ### 6. Post Go-Live Support (Week 12+) After go-live, your system is up and running, but that doesn’t mean the work is done. You’ll need to keep an eye on things and make adjustments as needed. #### Key Tasks: * **Monitor the system:**\ After go-live, check for any discrepancies or errors. Address any data issues, especially with transactional data. * **Ongoing reconciliation:**\ Keep reconciling data with your legacy system for a few weeks after go-live to catch any lingering issues. * **End-user support:**\ Provide ongoing support to your users. This might include additional training or helping troubleshoot any workflow issues. #### Deliverables: * Post-Go-Live Reconciliation * Support Documentation and Issue Tracker *** ### Summary Timeline (Key Milestones) 1. **Weeks 1-2:** Pre-Migration Planning (Scope, Roles, Data Mapping) 2. **Weeks 3-5:** Trial Data Load for Static Records (Sandbox) 3. **Weeks 6-7:** Trial Transactional Data Load (Sandbox) 4. **Weeks 8-9:** Final Testing, UAT, and User Training 5. **Weeks 10-11:** Live Data Load & Cutover 6. **Week 12+:** Post Go-Live Support & Monitoring *** ### Important Notes: * **Timelines vary by project.** This guide assumes a 12-week schedule, but your project may take more or less time depending on complexity. * **Concurrent Implementation:** Remember, data migration and NetSuite implementation happen at the same time. Get your data into the sandbox early to support UAT. * **Customer and Vendor Addresses:** Plan to load customer and vendor addresses at go-live. If addresses are already loaded in sandbox, perform a full replacement at go-live to ensure accuracy. # Reconciliation templates ## Reconciliation Templates Reconciliation templates are often more important than the initial import templates. Pre-designed reconciliation templates can save significant time when checking and correcting any mistakes during data migration. They enable efficient and repeatable comparisons between the old and new systems, allowing for quick data extraction and rechecking after corrections are made. Below are examples of essential reconciliation templates: ### 1. Mapping Document for Account Codes This spreadsheet serves as a mapping document between the account codes in the old system and those in the new system. It ensures that each account is correctly aligned, making it easier to identify discrepancies and ensure accurate financial reporting after the migration. * **Purpose**: Map account codes from the old system to the new one. * **Usage**: Use this template to quickly verify that each old account has a corresponding account in the new system, reducing the likelihood of errors in financial statements. {% embed url="" %} ### 2. Line-Level AR and AP Reconciliation Templates These templates reconcile Accounts Receivable (AR) and Accounts Payable (AP) at the line level. Amounts in foreign currencies should match for each transaction at the line level in both systems. This template ensures that both the base currency and foreign currency amounts are reconciled. * **Expected Results**: * Foreign currency amounts should match exactly. * Minor differences in base currency are expected due to currency rounding at the line level. * **Usage**: Use this template to confirm that each line item from AR and AP transactions matches the corresponding data in the new system. {% embed url="" %} ### 3. Balance Sheet Extract and Reconciliation This template allows the finance team to extract the balance sheet from NetSuite in a format they are familiar with, making it easier to verify the data. The template should be populated by extracting the internal ID from a standard balance sheet in NetSuite. * **Purpose**: Simplifies the process of comparing balance sheets between the old and new systems. * **Usage**: Extract the balance sheet data from NetSuite, map the internal ID fields, and use the template to compare the results with the old system. To use this template customise the standard NetSuite balance sheet as follows
Note: account type and internal id have been added above as columns to the balance sheet report ### Recomciliation template: {% embed url="" %} ### Benefits of Pre-Designed Reconciliation Templates 1. **Repeatable Process**: The reconciliation process becomes streamlined and repeatable, saving time and reducing errors when correcting any discrepancies. 2. **Quick Re-checking**: After correcting mistakes, the templates can be reused to quickly extract data and ensure all corrections have been made accurately. 3. **Familiar Formats**: The templates are designed in formats familiar to finance teams, making it easier for them to review and confirm the accuracy of migrated data. By using these reconciliation templates, you ensure a more reliable data migration process, with fewer errors and a faster reconciliation period. Reference the example templates linked below to get started. *** These templates will help facilitate the reconciliation process between systems. Once you have finalized your reconciliation templates, you can upload them for quick access here in GitBook. # 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. # Handling Historical Data in NetSuite Migrations ## Handling Historical Data in NetSuite Migrations #### **Avoid Loading Full Historical Data (If Possible)** In my 20 years of experience with data migrations, I have yet to see a scenario where the cost and effort required to load **full historical data** outweigh the benefits. While it's technically possible, especially when migrating from simple systems like Xero or QuickBooks, the cost and complexity usually outweigh the advantages. *** ### **Recommended Approaches for Loading Historical Data** If you need to load historical data, here are three efficient options to consider: *** #### **1. Load the Balance Sheet Only** The simplest approach. This is ideal when a company has acquired another business and needs to bring over its **assets** and **liabilities**. **Use Case:** * Reflect the **balance sheet** at a specific date, such as an acquisition. *** #### **2. Load P\&L Year-to-Date** Load the **Profit & Loss (P\&L)** data for the current financial year up to today. You can also include **departments** for detailed reporting. **Use Case:** * Report on **year-to-date P\&L** without needing detailed historical transactions. *** #### **3. Load Balances with P\&L Year-to-Date + Historical Journals** For more detailed records, load **year-to-date P\&L** balances plus a journal entry for each historical period. **Use Case:** * Retain historical records by posting **journal entries** for each financial period while avoiding cumulative errors. *** ### **Summary of Best Practices:** * Avoid loading full transaction histories unless absolutely necessary. * Consider using the **Balance Sheet** or **P\&L year-to-date** approaches for simplicity. * Use **journal entries** for each period if you need more historical detail while maintaining efficiency. # Use of external IDs ## Using External IDs in NetSuite for Data Migration #### **What is an External ID in NetSuite?** In NetSuite, an **External ID** is a unique identifier that creates a reference to a corresponding record in an external system. It acts as a link between NetSuite and other systems, ensuring that records can be easily matched and tracked across platforms. This makes **External IDs** highly useful for data migrations. If you have a unique reference number for a record in another system, you can use it to load or update records in NetSuite. External IDs allow you to match and update data in future imports, making them invaluable in maintaining data integrity during migration. *** ### **Why External IDs are Ideal for Migrations** During migrations, having a unique reference to the corresponding record in your legacy system means that you can easily update data in NetSuite if needed. Instead of relying on NetSuite's **Internal ID** (which only exists after data is loaded), External IDs allow you to match records consistently across systems. For example: * If you import customers from a CRM into NetSuite, the External ID will let you **update** customer data in future imports without creating duplicate records. * It simplifies the process of syncing data between NetSuite and other systems, such as an ERP, CRM, or Ecommerce platform. However, when using External IDs for migration, there are important considerations based on the **type of record**. *** ### **Considerations by Record Type:** #### **1. Vendors / Customers / Items** In many cases, External IDs for these records are already being used by other systems, such as a CRM or Ecommerce platform. This could cause conflicts if NetSuite is integrated with those platforms, as the External ID might already be in use to link the two systems. **What to Watch For:** * **Pre-existing integrations:** If an integration with another system (like a CRM) already uses the External ID, it could overwrite or conflict with your migration data. **Best Practice:** * Instead of using the External ID field, consider using a **custom field** to store your external system's unique identifier during the migration. * This approach ensures that existing integrations won’t be disrupted, especially if you plan to test those integrations before go-live. **Tip:** * Even if you don’t use the External ID field, it’s essential to maintain a **mapping table**. This table should map the **target system’s internal IDs** (the ones generated by NetSuite after data import) to the corresponding **external database key** from the source system. This helps with reconciliation and future updates. *** ### **2. Loading Opening Balances (AR/AP Transactions)** When loading **opening balances** and **AR/AP transactions**, using the **Internal ID** or **database key** from the source system as the **External ID** is common. This works well in most cases, as each transaction typically posts a single line to AR/AP. However, there are some cases where **journals** or other multi-line transactions are used to post data to AR/AP. In these situations, using the **Internal ID** may not be sufficient. **Best Practice:** * Use the **Line Unique Key** when loading multi-line transactions. This ensures that each line on the transaction is tracked individually and avoids issues during reconciliation. **Why It’s Important:** * Using the **Line Unique Key** makes reconciliation faster and more accurate, especially when dealing with journal entries or transactions that span multiple lines in AR/AP. *** ### **Summary of Key Points:** * External IDs create a direct link between NetSuite and external systems, making them ideal for migrations and future updates. * For **Vendors, Customers, and Items**, avoid using External IDs if they are already in use by other system integrations. Instead, use a **custom field** to store the unique identifier during migration. * Maintain a **mapping table** between NetSuite’s internal IDs and the source system’s external keys for easy reconciliation. * When loading **AR/AP transactions**, use the **Line Unique Key** for multi-line transactions to avoid issues and make reconciliation smoother. # Foreign Currency Revaluations - AP and AR accounts ### Background When migrating financial data, accurately handling foreign currency revaluations for open Accounts Receivable (AR) and Accounts Payable (AP) balances is crucial. At the end of each month, companies often have outstanding balances owed by customers or to vendors in multiple currencies. The exchange rates used to calculate the value of these outstanding receivables and payables may have changed since the invoices were originally issued. To account for these fluctuations, a foreign currency revaluation process adjusts the value of all outstanding receivables and payables based on the current month-end exchange rates. This results in adjustments to the AR and AP balances and corresponding postings to the Profit and Loss (P\&L) statement under unrealized foreign exchange gains or losses. ### Best Practices for Migration When loading transactions into a new system during migration, it's best practice to: * **Load Transactions with Original Dates and Exchange Rates**: This ensures that AR and AP reports in the target system remain consistent with those from the source system. * **Match Month-End Exchange Rates**: Ensure that the month-end exchange rates in the target system match those in the source system. * **Run Foreign Currency Revaluation in the New System**: After loading the outstanding balances, run the foreign currency revaluation process to verify that the results match exactly with the source system. ### Challenges This approach can present challenges, especially in scenarios involving company acquisitions or mergers. Combining multiple subsidiaries makes it difficult to confirm that the foreign currency revaluation process has produced the correct results due to the complexity of merged financial data. ### Alternative Approaches Depending on whether the situation involves an acquisition or a merger, different strategies can be employed: #### Asset Acquisitions In acquisitions where only assets are purchased, open balances can be loaded with each invoice using the **month-end exchange rate**. This simplifies the reconciliation process, making it easier to confirm that balances match between the source and target systems. However, it's essential for the finance team to confirm that this method aligns with accounting policies and regulatory requirements. #### Mergers For mergers involving the consolidation of P\&Ls, consistency in postings to unrealized gains and losses is necessary. Running the foreign currency revaluation in the target system for imported transactions can be challenging because it's difficult to isolate which revaluation entries relate to the migrated transactions. In this case, consider the following steps: 1. **Create a Saved Search in the Source System**: Generate a report that details what the revaluation amounts should be based on the source transactions. 2. **Load Transactions at Original Exchange Rates**: Import transactions into the target system using their original dates and exchange rates to maintain historical accuracy. 3. **Adjust AR/AP Balances with a Journal Entry**: The AR and AP account balances on the imported opening balance trial balance will differ from the imported transactions since the trial balance reflects month-end rates. Load a journal entry to adjust for this difference, similar to the reversal journal the system would create if you had run the foreign currency revaluation process. ### # Data Import Templates ## Import Templates for Opening Balance AR and AP | **Field** | **Import Notes** | **Example Value** | | ----------------- | ----------------------------------------------------------------------------------------------------------------------------------- | ------------------------- | | **Entity ID** | NetSuite internal id for the customer / vendor | 123243 | | **Currency** | ISO currency code for the transaction (e.g., USD, EUR) | USD | | **Date** | Original transaction date | 2023-09-30 | | **Memo** | Any relevant information for AR/AP teams (e.g., line or header-level details) | Opening balance migration | | **Due Date** | Original due date of the transaction. The date format used should be consistent with the settings in your user account in NetSuite | 19/03/2024 | | **Exchange Rate** | Original exchange rate from the source system | 1.15 | | **Subsidiary** | (If using OneWorld) The subsidiary for the customer/vendor | Subsidiary1 | | **Item** | Hard-coded to the opening balance item (e.g., "Opening Balance AR/AP") | Opening Balance AR | | **Amount** | Amount of the opening balance bill in foreign currency | 5000 | | **Rate** | Set to the same amount as the balance | 5000 | | **Quantity** | Hard-coded to 1 | 1 | You can download the CSV version of this template for use in your migration process [here](https://docs.google.com/spreadsheets/d/1EsDOMNWoM9LjHZTjaLsHo1XWWymQfYDoviBQmVQAeic/edit?gid=313157799#gid=313157799). [View the Google Sheet](https://docs.google.com/spreadsheets/d/e/2PACX-1vQMy3e3KLj__oCoQndSOXjQ8nP9YvvOgf3l1028EbvH_lB7t5hpso3584no4uXhTBA_jE-pvJRHd1iX/pubhtml) ## NetSuite Customer Import Guide ### Standard Fields for Customer Import The table below outlines the standard fields required for customer data import into NetSuite. These fields cover the essential details needed for each customer, including contact information, addresses, and preferences. | **Field** | **Notes** | | --------------------------- | -------------------------------------------------------------------------------- | | **Category** | Category of the customer | | **Comments** | Additional comments on customer | | **Company Name** | The legal or company name of the customer | | **Credit Hold** | Indicates if the customer is on credit hold | | **Primary Currency** | Primary currency used by the customer | | **Email** | Email address of the customer | | **Email Preference** | Customer's preference for receiving emails | | **Email Transactions** | Whether to email transaction details | | **Name** | Customer's name | | **Internal ID** | Internal ID of the customer | | **Phone** | Customer's phone number | | **Print Transactions** | Whether to print transaction details | | **Ship Complete** | Indicates if shipments must be complete | | **Taxable** | Whether the customer is taxable | | **Web Address** | Customer's website | | **Tax Number** | Customer's tax or VAT number | | **Terms** | Payment terms for the customer | | **Addressee** | Addressee for the customer (default billing/shipping address) | | **Address 1** | Customer's primary address line 1 (default billing/shipping address) | | **Address 2** | Customer's address line 2 (default billing/shipping address) | | **Address 3** | Customer's address line 3 (default billing/shipping address) | | **Attention** | Person to whom the communication is addressed (default billing/shipping address) | | **Town/City** | Town or city of the customer (default billing/shipping address) | | **Country Code** | Country code of the customer (default billing/shipping address) | | **Address Label** | Label for the address (default billing/shipping address) | | **State/Province** | State or province of the customer (default billing/shipping address) | | **Default Billing Address** | Indicates if this is the default billing address | | **Postal Code** | Postal code for the customer (default billing/shipping address) | A spreadsheet template is as follows: # Overview Migrating data between two NetSuite accounts is a complex process due to the unique configurations of each account. Unlike transferring data within the same system, NetSuite accounts often differ significantly in terms of their setup, including: * **Chart of Accounts**: Each NetSuite account may have a different structure for managing financial accounts. * **Subsidiaries**: The use of subsidiaries and associated configurations can vary between accounts. * **Custom Fields and Records**: Each account may have custom fields, forms, and workflows that do not exist in the other account. * **Data Structures**: Variances in transaction types, reports, and saved searches can also add complexity. As a result of these differences, there is no direct or automated way to transfer data between two NetSuite accounts. Instead, the data must be treated similarly to a migration from any external system. The process typically involves: 1. **Data Extraction**: Extracting the relevant data (e.g., ledger transactions, customer data, or inventory records) from the source NetSuite account. 2. **Data Transformation**: Aligning the extracted data to match the structure and configuration of the target NetSuite account. This can include mapping accounts, adjusting subsidiary relationships, or modifying custom fields. 3. **Data Loading**: Importing the transformed data into the target NetSuite account using tools like CSV imports, SuiteScript, or APIs. # Trial Balance Data Extraction An example search showing the extraction of the trial balance is:
``` var transactionSearchObj = search.create({ type: "transaction", settings:[{"name":"consolidationtype","value":"NONE"}], filters: [ ["posting","is","T"], "AND", ["account.internalid","noneof","@NONE@"], "AND", ["subsidiary","anyof","8"], "AND", ["accountingperiod.enddate","onorbefore","31/07/2024"] ], columns: [ search.createColumn({ name: "formulatext", summary: "GROUP", formula: "case when {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income') then case when {accountingperiod.startdate} < to_date('01/01/2024', 'dd/mm/yyyy') then 'Retained Earnings' else case when {accountingperiod.startdate} >= to_date('01/01/2024', 'dd/mm/yyyy') then '3013 Legal Entity Restructuring' else {account} end end else {account} end", label: "account" }), search.createColumn({ name: "formulacurrency", summary: "SUM", formula: "case when {accountingperiod.enddate} <= to_date('31/07/2024', 'dd/mm/yyyy') then NVL({debitamount},0) - NVL({creditamount},0) else 0 end", label: "amount" }), search.createColumn({ name: "formulacurrency", summary: "SUM", formula: "case when {accountingperiod.enddate} <= to_date('31/07/2024', 'dd/mm/yyyy') then NVL({debitamount},0) else 0 end", label: "debits" }), search.createColumn({ name: "formulacurrency", summary: "SUM", formula: "case when {accountingperiod.enddate} <= to_date('31/07/2024', 'dd/mm/yyyy') then NVL({creditamount},0) else 0 end", label: "credits" }) ] }); ``` #### Criteria This search extracts all transactions from the ledger up until the cut over date: * Posting: The search should include only posting transactions, ensuring only finalised ledger entries are retrieved. * Account Internal ID: All transactions should relate to valid accounts (i.e., exclude transactions where the account is @NONE@). This avoid * Subsidiary: Filter transactions to those associated with the specific subsidiary being migrated (e.g., Subsidiary ID = 8). * Accounting Period: The search should include all transactions posted on or before the cut-over date (e.g., 31 July 2024). #### Results The results of the search will include the following columns: 1. Account:\ Transactions are grouped by account. However, for transactions that occurred before the current financial year (e.g., before 1 January 2024), they are grouped under "Retained Earnings". This allows for the proper treatment of prior-year balances. 2. Amount:\ The net balance of transactions (debits minus credits) up to the cut-over date. This balance will be used when loading data into the target system. The “Balance” column will be used to load the balances into the target system, the “Debit” and “Credit” columns are included for reference only.
# Subsidiary Mergers vs Asset Acquisitions When migrating data in NetSuite, the key difference between **Subsidiary Mergers** and **Asset Acquisitions** is whether the **P\&L** is combined. In a merger, the P\&L is consolidated, while in an asset acquisition, only the **balance sheet** is affected. **Subsidiary Merger (P\&L Consolidation)** In a **subsidiary merger**, both entities’ P\&L must be merged, requiring full alignment of financials, including the chart of accounts and intercompany transactions. This is more complex, as the entire financial structure needs to be integrated. **Asset Acquisition (Balance Sheet Only)** An **asset acquisition** focuses solely on transferring assets, affecting only the balance sheet, with the P\&L of the acquired company remaining separate. In either case as we are combining balance sheets, ensuring that foreign currency revaluations are correct can be challenging. See separate cheat sheet article on approaches in this area. **Key Takeaways:** * **Mergers**: Involve combining both the P\&L and balance sheet, requiring more extensive financial integration and careful foreign currency handling across all accounts. * **Acquisitions**: Focus on the balance sheet, making the process simpler, though foreign currency revaluation on asset accounts still requires attention.