How to 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.

These steps are also demonstrated by Solution 7’s Co-founder, Simon Miles, in our latest webinar, available to watch on demand here. 

For more quick guides, you can explore the full playlist here: Ask the Expert Webinar Series.  

Step 1: Create the Balance Report

1. 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).

2. You can now create the balance report to see local currency amounts.

   
   

Step 2: Change to a Different Currency

1. Copy the formula across to replicate your balance data.

   
   

2. You can now use the “lookup” option to change the subsidiary context.

   
   

3. Set up the context to the consolidated notional subsidiary.

   
   

4. 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

1. Go into the FX function and add a second option, in any order to update the result basis.

   
   

2. Go into the result basis and you will find multiple currency options.

   
   

3. 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.

   
   

4. 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

1. Change the main subsidiary to a consolidated subsidiary.

   
   

2. Remove the subsidiary context.

   
   

3. 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

1. Select individual subsidiaries for consolidation, e.g., Europe, Mexico, UK.

2. Click “OK”.

   
   

3. 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.

For the full walkthrough, watch Simon, the Co-founder of Solution 7, demonstrate how to create a Year-to-Date Formula for Excel, in the demo here.

To learn more about Solution 7 for NetSuite reporting in Excel, book a demo or start a free trial.