Import Historical Transaction and Trial Balance (Data Migration)

2 methods for Historical trial balance import.

Year to date(Historical trial balance)

Net activity balance load(Historical Trial Balance)

Detailed Historical balance/transaction import:

• Above are the methods mentioned by Netsuite. We will use the second method but with transaction details in it(not just the total of each account). 
• To go with this method we will use the General ledger report from QB. Ideally the GL report will match with the Trial Balance amount.
• Report download from QB
  • Export the GL report from QB month wise. This will help us to reconcile the transaction and trial balance monthly.
  • Also download month wise Trial balance.
  • Required fields from report
    • Transaction number
    • Date
    • Transaction type
    • Account
    • Debit
    • Credit
    • Memo line
    • Memo header(if applicable)
    • Entity Name(Customer/vendor/Employee)
• Pre-requisite in Netsuite
  • All the required COA or its mapping should be available.
  • All the required Customer,vendor,employee should be there.
  • The corresponding accounting period should be open.
  • Overwrite entry number should be enabled for JE.

• General Ledger Import(Detailed balance)

2 methods are described ,the recommended method is the second one.

• Single Journal per month
  • In this method we will import a single journal entry for every month.
  • The JE lines will have different transaction lines.
  • The memo field can have the more detail like date,memo line,transaction type,header memo,transaction number.
  • Before import we will have to confirm the JE debit and Credit balances or not.
  • Advantage
    • Easy to import.
    • Easy to correct or delete the journal entry for us.
    • Not much JEs are created in the system.
  • Disadvantage
    • More details are in the memo.
    • Client can not search the transaction based on the document number.
    • Difficult to locate the transaction.
    • Auditing wont be easy.
    • Date of JE will be a single date.
    • Currency of JE will be subsidiary currency, transaction currency is not considered.
  • Field Mapping for Journal Entry
    • External id: GL-Jan-2019
    • Entry Number: System Generated Transaction number(leave blank)
    • Date: 1/31/2019
    • Period: Jan 2019
    • Account : New COA
    • Debit :Debi
    • Credit: Credit
    • Memo (header): GL for Jan 2019
    • Memo (line): Transaction type+Memo line+ tran date+Memo header+Transaction number.
    • Name: Entity Name(Customer/vendor/Employee)
    • Currency: Subsidiary currency
• Each transaction as each journal entry(Recommended)
  • In this method we will import a journal for each transaction.
  • Each JE represents a transaction and lines will be each transaction lines.
  • Since same document number can come for different transaction types, we will have to generate our own unique id.
  • Unique id= Document number+Transaction type+date+Entity name
  • Net amount= Debit-Credit.
  • Document number if blank, doc numb=Entity name+date.
  • Before import we will have to confirm each JE debit and Credit balances or not. Make use of Unique id,net amount generated and You can use Excel pivot to do this.(Pivot the unique id and net amount)
  • Remove total lines from GL report.
  • Advantages
    • Easy to locate the transaction.
    • More accurate details like date,memo etc.
    • Easy for Audit.
  • Disadvantages
    • Difficult to manage the import and do corrections for us.
    • Many JEs are created in NS.
    • Currency of JE will be subsidiary currency, transaction currency is not considered.
  • Field Mapping for Journal Entry
    • External id: Unique id
    • Entry Number: Document number
    • Date: Transaction date
    • Period: Transaction period
    • Account : New COA
    • Debit :Debit
    • Credit: Credit
    • Memo (header): Memo Header or some custom memo
    • Memo (line): Memo line
    • Name: Entity Name(Customer/vendor/Employee)
    • Currency: Subsidiary currency.
• Year opening balance import
  • Before importing the GL for 2019 we have to have an opening balance for Jan 2019.
  • We will have to import the trial balance as of 31th Dec 2018. This will be the opening balance of the Jan 2019.
  • This will help in reconciliation of trial balance for month Jan 2019 and when we close the 2019 year we will get the correct ending balance for year 2019.