How to import historical consolidated financial statements in NetSuite

I recently assisted a client with migrating 15 legal entities (with 15 separate QB Online accounts) to a single NetSuite account. The ability to generate historical consolidated financial statements was a project requirement. My approach for meeting this requirement was to import historical transactions to "import only" accounts and reclassify the eliminating balances with monthly advanced intercompany journal entries. This blog post will discuss the detailed steps taken.

Before starting this process, confirm how the legacy system tracked eliminating balances between entities. The two requirements needed to prepare the advanced intercompany journals are:

• Ability to identify the counter subsidiary - ideally, this is included in the account name.

• Ability to identify the transaction type - this is the type of intercompany transaction. Here are a few examples:

  • A due to/due account for transactions between two entities

  • An investment/equity transaction where a parent invests in the child

  • Recording intercompany rent income/expense between two entities

Side note - see my article on best practices for tracking intercompany balances in QuickBooks for more details.

First, create the "import only" intercompany accounts not configured to eliminate in the chart of accounts. This will reduce the data required for posting transactions to these accounts and prevent transactions from accidentally being eliminated. Also, configure any "import only" due to/from accounts as 'Other Current Asset' or 'Other Current Liability' account types. We want to do this because the 'Accounts Receivable' and 'Accounts Payable' account types have more data requirements that might complicate the data import.

Map the historical activity to the "import only" intercompany accounts and validate that the balances tie to the legacy system. I'd recommend completing subledger tieouts before transferring any intercompany balances to an AR or AP account. NetSuite will include the intercompany balance on the subledger, which will come up as a variance when tying out these reports. Regardless, this is an easy variance to explain.

Once the initial data is loaded and validated, we can start to prepare the advanced intercompany journal entries. We will prepare these in batches based on the transaction type noted above. For each batch, create a transaction saved search by the activity type with the "import only" accounts selected in the filters. The result columns should include the date, subsidiary, account, the debit amount, and the credit amount. See the below example:

Prepare the map file by getting the unique combination of account and subsidiary. From here, identify the offset subsidiary and account. The offset account should be the correctly configured intercompany account. The offset subsidiary can be identified based on the account name. Here is an example map based on the transaction saved search above:

Using the map file, compile the advanced intercompany journal entries by summarizing the debits and credits by month, offsetting the "import only" account balances to zero, and reclassifying the actual balance to the elimination account.

Shameless plug - OptimalData consulting has a tool that does this automatically, saving you the hassle of manually preparing these advanced journal entries. If you are doing this alone, check out my post on tips for importing advanced intercompany journal entries.  

Validate that the "import only" balances are zero by running the subsidiary's balance sheet and income statement. If this is the case, you've successfully loaded the intercompany balances. The last step is to run the "Eliminate Intercompany Transactions" on the Period Close checklist for each period. A quick tip is to enable the 'Close Multiple Periods,' close all the periods through your go-live date, and then go back to the first period and start to run the 'Eliminate Intercompany Transactions,' reducing the clicks to go through all of the close steps.

Sound overwhelming? OptimalData consulting can complete this complex task as part of your NetSuite implementation. We have the tools and experience necessary to onboard your historical data. Maximize your NetSuite investment with the ability to run historical consolidated financial statements. Contact us today to find out more.