NetSuite, Implementation, Data Migration, QuickBooks
December 6, 2022
4 min Read
Two ways to prepare historical monthly financials in NetSuite
There are two approaches a user can take when preparing monthly journal entries for historical financial statements. There are pros and cons to each approach that depend on different circumstances. This article will discuss both methods and when to use each.
For illustration purposes, let's assume that Company ABC is implementing NetSuite on 5/1/2022 and is planning to load historical monthly financials for 2021 and 2022.
Approach 1 - load opening balances and net change entries after that (aka net change approach)
Under this approach, the client would load the opening balance sheet as of 12/31/2020 as a journal entry dated 1/1/2021. Then, the following journal entries post the incremental change in the account balance. See the graphic below as an example.
I'd recommend using a trial balance report to prepare these journal entries for the opening balance sheet. Then, map the balance sheet accounts to the corresponding NetSuite accounts and the income statement accounts to retained earnings. (Note - I'm assuming the first month represents a new fiscal year). Next, run a transaction listing and calculate the journal entry balances using SUMIFS on the unique combination of the included segment values (GL account, Department, Class, and Location).
Pros:
- Segment data is not available on the trial balance report in QuickBooks.
- A reversing journal entry at year-end is not necessary.
Cons:
- Requires intermediate Excel skills to prepare the journal entries from a transaction listing.
- This approach might not be feasible if there is a large volume of transactional data.
Approach 2 - load balances and reverse (aka point-in-time approach)
Under this approach, the client would load the monthly trial balance amounts as a journal entry. Each journal entry would be reversed the following month and replaced with the next month's trial balance. Users would not reverse the balances at the go-live date.
Read how the SmartLabs team imported detailed transactions from 15 QuickBooks files to NetSuite
A critical consideration under this approach is understanding how NetSuite handles rolling prior period income statement accounts into retained earnings. When you run the next period's financial statements, NetSuite automatically moves the previous period's net income into retained earnings. So, users must account for this when reversing the period-end journal entry. Users should adjust this reversal entry by changing the income statement accounts to the retained earnings account. Otherwise, the next period's income statement accounts will start with a reversal balance, leading to incorrect financial statements.
I'd recommend using a trial balance report for each monthly journal entry to prepare these journal entries. Map all legacy accounts to the corresponding NetSuite account. When you load the journal entry, populate the reversal date for all entries except the year-end entry. You'll need to prepare a separate reversal journal entry with all the income statement accounts mapped to retained earnings. This approach works incredibly well if there is no additional segment data besides GL accounts to migrate or if the transaction listing is too large to export.
Pros:
- QuickBooks does not include segment data on the trial balance report.
Cons:
- Users need to adjust the period-end reversing journal entry
Other Considerations
- Regardless of the approach, always include a fictitious AP Vendor and AR Customer on all lines that impact Account Payable and Account Receivable accounts.
- Situations with multiple subsidiaries in different reporting currencies will need to factor in the exchange rate for the individual journal entries and the consolidated exchange rates. Variances will occur in consolidation because exchange rates will differ each month.
- Minimize the downtime of your accounting system by preparing and loading closed periods before the go-live date. Users will gain experience and practice preparing these entries before the go-live date.
Conclusion
Preparing the historical monthly journal entries is critical for a successful NetSuite implementation. Check out our implementation and data migration solution page for additional guidance. Or, contact us to get a quote for OptimalData's data migration service.