Please read our Privacy Statement and Standard License Terms. By downloading any of the documents below, you are agreeing to our Standard License Terms.
Consolidate Monthly Results for Multiple Subsidiaries from Local Currency to a Budget FX Rate in Excel for NetSuite
When you are managing multiple subsidiaries in Excel financial reporting for NetSuite, you may find that you need to consolidate results from local currency to a budget FX rate. (A budget FX rate is a predetermined exchange rate used to plan a company's finances when dealing with multiple currencies.) In the following how-to guide, we’ll walk you through the steps to do this with Solution 7 using advanced balance functions.
STEP 1: Create the Balance Report
Use the ‘Advanced Balance’ function from the ‘Function’ drop-down in Solution 7. From here you have the ability to report any company in any other company’s currency. Select the relevant subsidiary and the relevant time period (for example, Jan 2023).
You can now create the balance report to see local currency amounts.
STEP 2: Change to a Different Currency
Copy the formula across to replicate your balance data.
You can now use the “lookup” option to change the subsidiary context.
Set up the context to the consolidated notional subsidiary.
This will now convert the subsidiary’s local currency to the consolidated currency at the actual FX rate.
STEP 3: Applying the Budget FX Rate
Go into the FX function and add a second option, in any order to update the result basis.
Go into the result basis and you will find multiple currency options.
You’ll see here that you can report in the actual consolidated rate which means that you can choose the exchange rate based on the account type.
If the budget exchange rate matches the actual rate, this will be because the rates are identical, but this step will ensure that all future conversions will be done at the budget rate to start creating consolidations.
You can learn more about the current or historic rate for budget and net accounts in the demo, here.
STEP 4: Consolidate Across Subsidiaries
Option 1: Full Organizational Consolidation
Change the main subsidiary to a consolidated subsidiary.
Remove the subsidiary context.
Now apply the result basis setting to ensure all conversions use the budget FX rate. This creates a consolidation across the entire organizational structure.
Option 2: Ad-Hoc Aggregation
Select individual subsidiaries for consolidation, e.g., Europe, Mexico, UK.
Click “OK”.
It will then aggregate the balances and show them in the currency of the first listed subsidiary.
Although there are multiple ways to do this from within Excel using Solution 7 for NetSuite, these are the two main methods. The key is to use the result basis to change the fundamental way that we calculate the intercompany currency amount and switch over to the budget rate from the actual rate.