Two ways to prepare historical monthly financials in NetSuite

Introduction

The best way to load historical monthly financial statements to NetSuite is through a journal entry. Two methods exist to prepare the journal entry import templates for your NetSuite data migration strategy. This article discusses the methodology, pros/cons, and when I’d recommend using each method.

Example

For illustration purposes, let's assume that Company ABC is migrating data from QuickBooks to NetSuite. Their NetSuite implementation go-live date is 5/1/2022. The organization has QuickBooks data going back to 2019 but decided only to load historical monthly financials from 1/1/2021 through the NetSuite implementation go-live date.

How NetSuite Calculates Retained Earnings

Users should understand how NetSuite calculates retained earnings before selecting an approach to prepare the journal entry import templates.

NetSuite automatically rolls the prior fiscal year's net income balance into a system-generated retained earnings account. Unlike other systems, no year-end net income close entry is required.

This concept is critical when reversing journal entries across a fiscal year-end. NetSuite’s journal entry reversal functionality will post the reversal amounts into the original account it was posted in, regardless of whether it's a fiscal year-end or not. This is an issue when loading historical financial statements. The legacy system report will reflect the adjusted retained earnings balance. This issue is applicable to approach #2 below.

The Two Methods to Import Financials into NetSuite

Approach 1 - load opening balances and net change entries (the net change approach)

Under this approach, the client would load journal entries for the opening balance sheet and the monthly incremental change to each account balance. See the graphic below as an example:

The first step is to load the opening balance sheet as of 12/31/2020, the day before loading net change entries. This journal entry reflects the balances for the activity from 2019 - 2020. Run the QuickBooks trial balance report as of 12/31/2020, combining all the income statement accounts with the retained earnings account, and importing this journal entry into NetSuite on 1/1/2021. 

After that, prepare a journal entry to post the incremental change in the account balance. Each monthly journal entry should reflect the net change between the month-end balances. 

To prepare the net change entries, run the QuickBooks transaction list from 1/1/2021 - 4/30/2022 and calculate the monthly balance using a SUMIFS formula. You want to calculate the unique combination of the included segment values (GL account, Department, Class, and Location). Once the segment combination balances are calculated, map the NetSuite segments to the QuickBooks segments.

Here is a breakdown of what I like and don’t like about this approach:

Pros:

• Segment data can be added to the QuickBooks transaction list and included on the NetSuite import template for the journal entry.
• You avoid creating a manual journal entry to reclassify the prior year’s P&L activity to retained earnings.
• You only post one journal entry per month instead of two.

Cons:

• Exporting the transaction list will be challenging if you have a high volume of transactions. QuickBooks Online cuts off the report by around 30,000 lines. If your transaction volume exceeds this limit, you might need to export several reports and manually combine them in Excel.
• Requires intermediate Excel skills to prepare the SUMIFS formula to prepare the journal entry import template.

Approach 2 - load balances and reverse (aka point-in-time approach)

Under this approach, the client would load the monthly account balance as a journal entry. Each journal entry would be reversed the following month. Users do not reverse the go-live date balances, since these represent the beginning balance. Also, a special reversal entry is required for each entry crossing a fiscal year-end date. See the graphic below:

Use the QuickBooks trial balance report to prepare each monthly journal entry import template. Map all legacy accounts to the corresponding NetSuite account. When you load the journal entry, populate the reversal date for all entries except the fiscal year-end entry and the entry at the go-live date.

You must prepare a separate reversing entry for the entries that cross a fiscal year-end. Your legacy trial balance report’s retained earnings balance reflects the prior period's net income balance. When using NetSuite’s journal entry reversal functionality, it will not reflect that adjustment.

Flip the debit and credit amounts on the template to properly prepare these reversal journal entries. Any income statement account should be mapped to the retained earnings balance.

Here is a breakdown of what I like and don’t like about this approach:

Pros:

• This approach is simple to execute if your legacy system, like Great Plains, includes all segment data in the account string, and/or you can export this data in a single report, like a SmartList extract from Great Plains.

Cons:

• If your legacy system is QuickBooks Online, QuickBooks Desktop, or Xero, you must export multiple trial balance reports because you can’t include multiple periods in a single report. Additionally, these systems don’t allow you to include department, class, or location segments on the trial balance report. 
• Users need to prepare the fiscal year-end reversing journal entries correctly.

Other Considerations When Preparing the Journal Entry Import Templates

• Regardless of the selected approach, always include a fictitious AP Vendor and AR Customer on all lines impacting any account configured as an “Account Payable” or “Account Receivable” account type. Otherwise, you will have a “No Vendor/Customer” line on the detailed subledger reports that cannot be removed.
• You must prepare a separate journal entry for each subsidiary.
• If you have multiple subsidiaries with different reporting currencies, you can either:
  • Load the historical financials in the parent-subsidiary currency and set the exchange rate to 1:1 - this approach is simple and will ensure the financials tie.
  • Load the historical financials in the local reporting currency. In this case, you must update the consolidated exchange rates to match the legacy system. In addition, you might need to post additional equity entries if the historical exchange rate is different by equity account. This approach is extremely difficult.
• Load the data in two batches. This will:
  • Minimize the downtime of your accounting system at the implementation go-live date
  • Give you experience preparing these entries before the go-live date.
  • Opportunity to validate the historical data

Conclusion

If your legacy system is QuickBooks Online, QuickBooks Desktop, or Xero, I recommend using the net change approach. I recommend the point-in-time approach if your legacy system is Great Plains (or any other system with a single string representing all segment values).

If you are preparing the NetSuite data migration templates, check out my other resources, such as my guide on loading financials and open transactions or my transactional CSV import guide.

Finally, contact me if you are overwhelmed by the data migration requirements or want to hear about loading your full transactional history. OptimalData Consulting has helped over 60+ clients migrate their data into NetSuite.