Can you consolidate legal entities in Dynamics 365 Business Central without a 3rd party solution?
Yes.
Turns out you can.
Much to my surprise, consolidations are a standard feature in Dynamics 365 Business Central. I always assumed you needed a 3rd party to help, but nope! There’s some decent documentation explaining how it works at Learn, but there’s a couple of little tidbits I found as I was setting up multi-company consolidation in Business Central.
Consolidate Data from Multiple Companies – Business Central | Microsoft Learn
Example organization structure
Consider a situation like the below:
In this organization, there are three companies that need to be reported on in a single currency from a consolidated view. In Fabrikam Sprockets’ case, the accounting currency (or LCY in Business Central parlance) is GBP. The three companies shown with a white fill above are operating companies; the grey-filled companies are reporting companies created just for consolidations purpose.
Consolidations overview
The consolidations process essentially makes a copy of transactions in the operating companies and duplicates them into the consolidation companies. The overall approach is something like this:
- Create a separate company for consolidation transactions
- This company can be basically empty. It needs your chart of accounts, a single number series, and a currency record for each of the currency’s that your subsidiary companies use as LCYs
- This keeps the transactions separate from operating companies for auditing and reporting
- Also makes sure it’s a lot easier to reverse and re-do the consolidation processes as transactions are added or changed in the subsidiary company
- Set up each of the operating companies as “business units” in the consolidation company
- Run the consolidation process in the consolidation company
I’m pretty familiar with the general process, as it’s similar to how consolidations are processed in Dynamics 365 Finance and Operations, but there were a couple of little quirks that took me a second to troubleshoot.
Exchange rates
As seen in the example organizational structure above, you might have a subsidiary that’s in a currency different than what you want your consolidation company to be in. There’s probably a couple of different ways to arrange this, but the way I did it was by creating a separate consolidation company that’s essentially used to do a currency conversion before the final consolidation. I run a consolidation in the “Fabrikam Sprockets USD” company to convert the GBP transactions to USD, and then use the “Fabrikam Sprockets USD” as a business unit in the consolidation for “Holding company”.
Where do the exchange rates come from? You may think it’s from the normal Currency table and the exchange rates associated with those currencies. You’d be wrong. The exchange rates are actually entered during the consolidation process. When you try to consolidate a company that has a currency different than the consolidation’s company LCY, a button on the “Run consolidation” form will appear.
This will pop up a form that allows the user to manually enter two currency rates – one for the profit and loss accounts and one for balance sheet accounts.
This rate is used only for the consolidation – nothing else. Best I can tell, it doesn’t really even persist anywhere. It’s entered on the form, used for the consolidation, and then evaporates into thin air.
That said, though, as I mentioned above, you WILL need a record in the standard currency table for each of the subsidiary company’s LCYs. If you don’t, you’ll get sort of an obscure error message saying, “There is no Currency Exchange Rate within the filter.”
Year-end close
Now, let me again preface this with the fact that I don’t know much about business central, but the “Close income statement” function tripped me up a little bit. Similar to Finance and Operations, there’s a process that you run at the end of the fiscal year to transfer your profit and loss account balances to retained earnings. What happens in this process is a pending journal is created with all the transactions necessary to zero out the P&L accounts and post the offset to retained earnings. This journal posts in a very specific way – it prefixes a C to the front of the posting date and applies a source code of CLSINCOME. For reporting purposes, it’s important to understand both that these transactions are posted and how to identify them to either include or exclude then, depending on the report you’re running.
The weird thing is, though, that these closing transactions don’t appear to be included in the consolidation process. Since I built a bunch of Power BI reports on the consolidated company’s data, I had to run the “Close income statement” in the consolidation company. That created basically the same transactions that were in the subsidiary companies.
Not sure if I feel like if that’s how it should work, but now you know how it does work.
Reports
I mentioned it in one of my other posts – the one about personalization – but the “business unit” field is of paramount importance when reporting on a consolidated company’s data. There’s a standard “Consolidated Trial Balance” report that’ll help you ensure that all of various business units were included properly in the consolidation, but it’s kind of crappy. If you want to use the Financial Reports reports, then you’ll need to make sure you personalize your form to have the business units available in your column definitions.
So far, I’ve not been a fan of the reports in Business Central. I do, however, like how easy it is to pull the data into Power BI. The same things apply, though. If you’re reporting against GL entries in Power BI, you’ll need to be very clear about how you’re filtering your data. You’ll notice that the data corresponding to your consolidated company (“Holding company” in our example above) has data in the “Business_Unit_Code” column that reflect the subsidiary that the data came from. The exception to this is the data that comes from the “Close income statement” function referenced above.
Conclusion
There will probably be more to this story. I’m guessing after a year or so, I’ll realize I made some sort of mistake that’ll cause me problems, but that’s future Jake’s issue. Love to hear any comments you might have.
Leave a Reply