Whenever we want to move from the legacy system to the new system, we also need to move the opening balances as well. There are a couple of methodologies available to upload and migrate the opening balances. Here are methods to move Opening balance of Vendors, Customers, Inventory, Bank, Fixed assets and Other Ledgers from old system to the Dynamics 365 Finance and Operations.
This guide to upload the Customer and Vendor Opening Balance with Open Invoices and Payments. Here Open Invoice means, Remaining to be collected and paid to Customer and Vendor Respectively. Open Payment means Advance Payment which is not adjusted/settled against any Invoice or credit note to Vendor or from a customer.
Master Data required for Opening Balance
The first step is to upload all the master data in the system. Make sure the respective department head and Finance needs to verify and approve all the following masters:
- Chart of account A chart of accounts is a listing of the names of the accounts that a company has identified and made available for recording transactions in its general ledger
- Financial dimensions – Financial dimensions are data classifiers that are used for financial reporting. Financial dimensions identify information such as the purpose, cost center, and department.
- Vendor master
- Customer master
- Bank master
Notes for Business
- All opening balances will be posted on a cutoff date let’s say 1st Jan 2018.
- We will use a unique journal and voucher series to upload and post the opening balances so that we can easily identify and verify these transactions.
- Bank balance, Open Checks, open vendor invoices, open vendor advances, open customer invoices, open customer advances and fixed asset acquisition and accumulated depreciation will be posted through sub-ledger(Module)
- Asset account(Debit balance) – As we know that asset account will have a debit balance, so when we migrate balances for asset, bank, AR and inventory we will offset with retained earning account account.
- Liability account(Credit balance) – For liability accounts AP, loans, accumulated depreciation and equity we need to offset as Retained account.
Reason behind taking Retained earnings as offset account is, the balance of all Assets and Liability type of accounts is comes to retained earnings.
Following Steps required to be Followed.
Create General Journal at General Ledger > Journal Entries> General Journal.
Create a Journal Line as per below details and open in Excel Import Template.
|#||Field Name||Example Value||Value Description|
|1||Date||24-08-19||This is Posting date = Invoice date or Payment posting date|
|2||Voucher||GNJL000805||Auto Generated system number sequence|
|4||Account type||Customer||Select Customer or Vendor as the case may be.|
|5||Account||US-004||Select Customer Account number of FnO|
|6||Invoice||US-12345||Insert actual Invoice number of Sales Invoice or Purchase Invoice|
|7||Description||Invoice US-12345||Invoice Description upto 50 Character|
|8||Debit||126,987.00||1. For Invoice: Balance amount to be recovered or to be paid out of total Invoice amount. Either insert Debit amount or Credit amount. E.g. Total Invoice amount is $ 500000 and $ 373013 is paid then balance 126987 would be amount to be inserted as it is part of the Balance|
|9||Credit||0||Balance amount to be recovered or paid out of total Payment Amount. Either insert Debit amount or Credit amount as the case may be.|
|10||Terms of payment||Net45||Insert Terms of payment on Invoice|
|12||Offset account type||Ledger||Select Ledger|
|13||Offset account||300160-001-||Retained Earning Account for the Opening balance of 2019|
|14||Offset transaction text||Opening balance Description|
|16||Exchange rate||1||Exchange rate as on Dec-31-2019|
|17||Document||INV-AB_12345||Invoice Number reference|
|18||Document date||24-08-19||Invoice Date|
|19||Due date||08-10-19||This would be calculated from Terms of Payment code. User can Manually insert the same if not updated.|
Make sure to record the description, sales tax group, item sales tax group, invoice number, due date, document, and document date.
Add Required fields in the Excel Import which are not available in the above required fields.
- Download the excel
- Open it
- Add fields:
Insert the required data as per Step 2. Above.
Publish the Excel. Verify in the System with Same Journal and post from Dynamics 365 Finance and Operations.
There might be errors while publishing the excel template. Generally it will be related to updating Automatic fields e.g. Line Number, invalid combination of the Financial Dimensions etc. Solve it and post it again.
If your company is looking for ERM services in Vadodara or anywhere around the globe, drop an email to firstname.lastname@example.org and our team will be glad to set up a free demo for your brand!