In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
With any system implementation, there comes a time when the closing balances from the legacy system become the opening balances in the new system. In today’s post, we’ll explain various methodologies that are available for this stage of the implementation and explain the pros and cons of each. Enjoy!
To set the framework – let’s say that we have the following balances/information to bring into the new system:
1. Open Customer Invoices (Accounts Receivable or AR)
2. Open Vendor Invoices (Accounts Payable or AP)
3. Closing Stock Quantity and Cost Value (Inventory)
4. General Ledger Balances (The closing balances of all the G/L accounts. The total balance must follow the accounting equation, which is Assets = Liabilities + Equity)
Items #1, 2, and 3 have their own sub-ledger as well as the total represented by the G/L account.
The objective is to create the sub-ledger for customers, vendors, and stock, plus import all G/L balances. When we import the sub-ledger, it will impact the AR, AP, and Inventory Control accounts. The balances of these accounts will also be included in the G/L balance – otherwise, the accounting equation will not be balanced. This means that the balances in these control accounts may double up, as these accounts will be affected once for the sub-ledger import then a second time for the G/L balance import.
1. Create a new G/L account as a balancing account in this method, a new G/L account is created (let’s call it a suspense account). When sub ledger balances are brought in, the balancing side is posted to this suspense account. When G/L balances are brought over, the balances in the control accounts are also sent to the suspense account, bringing the balance back to zero.
2. Using the same control account as the balancing account in this method, the balancing entries for the sub-ledger are also sent to the same control account. This means that when the sub-ledgers are imported, the G/L balance in the relevant control G/L accounts will still be zero. The G/L balances can then be brought over as per normal.
Both of these methods have their advantages and disadvantages.
Let’s say that the new G/L account is called “Suspense Account.” The sub-ledger balances will be brought over in this manner:
When bringing in the G/L balances, the AR, AP, and Inventory balances will be posted to the Suspense Account, bringing it back to zero.
This method gives complete control and validation of the balances and is considered our best practice. If there are any reconciliation differences between sub-ledger and the G/L account balance, the balance in the suspense account will not be zero, forcing reconciliation.
Unfortunately, the G/L account may never be used again and will just use up space in any accounts-based reporting.
Using this method, this is how the entries appear:
The balances in these control accounts will be zero and you can easily bring in G/L balances without any need to create a separate G/L account, which is of course an advantage. But the disadvantage is that extra entries (the balancing ones) are posted to the same account, making it very hard to read if you need to refer to the opening balance entries.
We always use Method I, as it compels each client to ensure that their balances are reconciled and accurate. Thus, the risk of future issues is mitigated.