This article is relevant if you are working to upload a trial balance into NetSuite.
Background
During a NetSuite implementation, many times we are asked to constitute the historical balances to allow for comparative previous year performance. I find it rare these days for clients to want transactional history in their new implementation. While it is completely possible to do so, the effort usually outweighs the benefits.
I wrote an article, Sequencing Data Imports to Go Live on NetSuite, which can help you organize the work required to get NetSuite ready for ongoing use. Getting the Trial Balance in typically can happen late in the project. But the most important step is to get the Chart of Accounts stable.
General Preparation Concerns to Upload the Trial Balance
Much of the work required is to manipulate financial information to fit the new Chart of Accounts model. This may be simple or complex depending on the nature of the original information and target chart structure. Most of our clients want to restructure the Chart of Accounts to optimize financial reporting as part of their implementation. The most significant challenge is to take information that is summarized in the source but is now to be split into two or more accounts in the new target chart model. However, the opposite is usually easy to handle: multiple source accounts to be summarized into a single new target account. There is no simple answer to data transformation requirements – except to say that it is a good idea to produce a way to trust your data manipulation work; the most common technique is to compare new balances to old.
I highly recommend that preparation work be done in a spreadsheet to organize the data prior to import. Here are key concepts to consider:
- Use CSV Journal Entry Import: A journal entry is needed for each trial balance period. Use a CSV “Transactions” type with a “Journal Entry” record type. A single file approach with all our data is generally easier than a multi-file linked approach.
- Import Balance “Debit”: Target a single column of balance data. If the balance is positive, it will be a debit; if the balance is negative, it will be a credit.
- Avoid Retained Earnings Account: NetSuite automatically produces the retained earnings account as a derivative of income statement accounts in previous periods. To minimize trying to explain retained earnings balances, I recommend any actual journal entries to Retained Earnings be to a separate account, such as “Retained Earning – Adjusted”.
- Avoid Control Accounts: To help explain account balances that are generally derived from subledgers, such as Accounts Payable or Accounts Receivable, I recommend avoiding posting directly to the these accounts. Just like Retained Earnings, use “Accounts Payable – Historical” or other to illustrate that this information came from your Trial Balance import work.
- Use Account Internal IDs: In general, it is easier to import related data using internal IDs versus names for cross reference information. While it is more work to setup, it saves time during the import process– the CSV File import tools needs pointers to the related lists and table data and the internal ID is precise; otherwise, you may find that NetSuite throws errors when it can’t quite lookup on name / text values.
- Import Multiple Journal Entries: In general, it is easier to perform one import versus 12 imports to constitute a year of balances. The key to make this happen is to organize your data to have a different entry number for each period. Name each entry period distinctively. See example import file.
- Use a Saved CSV Import Map: Using NetSuite’s CSV import tool can be a trial and error endeavor. Save your CSV import definition to make it easier in subsequent attempts to import. See image for a map used in this example.
- Confirm Import Data Foots to Zero: All trial balances structured into a single column of data representing both debits and credits, including data import files containing multiple journal entries, must foot to zero.
- Close Periods once Satisfied: Once your data is imported without error, run financial statements (Balance Sheet and Income Statements) to confirm expectations. If good, close the periods to prevent changes.
Don’t Go it Alone – Get Expert Help
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
Thanks for the information.
Please share the best practice to upload the opening balances also .
Thanks .
Regards
Shekar
Hi Marty,
How do you prevent NetSuite from giving an error when you put a negative value in the Debit column?
Thanks!
Hi Jimmy,
I have found that if you are going to do an upload via CSV files to NetSuite, instead of using the amount column, only target the debit column, not the credit column. Then, you can enter negatives because these effectively are treated as credit. Naturally, you entry must balance before it can be saved.
Marty
Hi Marty,
I found your article researching how NetSuite handles importing prior history during a migration.
I am an accountant who works mainly with QuickBooks Desktop.
If you have clients migrating from QuickBooks, you may find my TBX Trial Balance Exporter tool a fast and simple way to export monthly history.TBX exports 12-months of period history at a time to a csv file.TBX will also export P&L activity by class name, which may represent departments.I added a class name replacement table. I find this useful when appending a department number to a general ledger number.
More info: TBX Trial Balance Exporter
Please delete if inappropriate.Happy Migrating!Sara Laidlaw
Thanks Sara. This looks like it would be helpful for the community.
Marty
Hi Marty,
How do you keep your journal entries in balance when uploading years of historical balances and removing retained earnings? Netsuite keeps doubling the retained earnings line. Thanks!!
As I mentioned in this article: “NetSuite automatically produces the retained earnings account as a derivative of income statement accounts in previous periods. To minimize trying to explain retained earnings balances, I recommend any actual journal entries to Retained Earnings be to a separate account, such as “Retained Earning – Adjusted”
Remember, you can post that either as an equity account or you can make an account on the income statement that will derive the retained earnings natively for you. I recommend a bit of trial and error to observe the effects until you are satisfied.
Marty
Marty, great advice as usual!
My team recently created a subsidiary that rolls up directly to our parents company, but then decided after a few months that they would like to roll it up under a different child subsidiary instead.
We are familiar with getting the trial balance imported, but they also want the historical transaction data. I suppose the open AR items wouldn’t be too bad to do but creating a new item and importing the invoices, and then reversing the GL impact, but I wonder if there is a better way. This will already require adding a new subsidiary to all of the current customers with multi-subsidiary feature. Consolidation is a big worry, especially elimination, CTA, and consolidated rates. What would you suggest as a best practice to get historical transactions over to a new subsidiary?
Hello James,
Your questions are complex. This article by NetSuite provides good guidance that is not well documented. It probably will clue you into important practices.
https://static.rainfocus.com/oracle/sw19/sess/15466278045960017BTw/PDFPF%20/FIN1514SES_THURS_1553176981281001vNgi.pdf
If you want to discuss working together more closely, contact us at
https://www.prolecto.com/contact-us/
Marty
My question is, if I am uploading TB’s as JE’s for each month. Won’t that make my balances at the end of the year extremely high? Do I need to create a reverse JE at EOY to bring balances to actual at YE?
Hello Emily,
This is all about the practice you decide to employ. A simple approach can be to use a reversing journal entry (as you suggested) so that the balances do not accumulate each month. That allows you to upload the current trial balance as-is. An alternative is to compare this month’s trial balance to last month’s and upload only the deltas (differences). I found I use both techniques depending on what I am trying to achieve. As a Systems Integrations Practice, we are often in NetSuite historical load situations so the source information often drives my preferred practice.
Marty
If I use the net change method to import 2 years historical data of the trial balance, do I need to calculate the differences from Dec 2021 to Jan 2022?
I’m importing from Jan 2020-Nov 2022. And I calculated the deltas starting from Feb 2020. But wasn’t sure if I needed to continue using the delta moving from Dec to Jan. Or would I just be able to use Dec’s ending balance and then start calculating the differences between periods Feb-Nov again.
Hello Dara,
It all depends on how your data is shaped. Assuming that in year 2, for January, you have just the first months (of year 2) of income statement data, you do not need to do the delta. In your case, you are likely showing growing balances; thus, you need to calculate the deltas.
NetSuite automatically handles all the work to “close the income statement” to Retained Earnings. It really does not move the data. It’s a reporting capacity to get the values for Retained Earnings depending on the context for which you are viewing financial information.
Hi Marty,
Thanks for this great article. I am uploading historical T.B’s with multi currency. Do you have any tips to upload these.
Hello Akileshwara,
Yes, our Accounting Practice Leader, Meir Bulman, is an expert in multi-currency trial balance concerns. Please see his article here:
https://meir.prolecto.com/2022/11/21/netsuite-currency-translation-ii/
Marty
Hello Akileshwara,
A multi-currency trial balance that is also consolidated is tricky to upload. I recommend reading Meir’s article here:
https://meir.prolecto.com/2023/07/23/netsuite-currency-considerations/
Marty