KB22: Uploading Customer and Vendor Balances with Open Invoices and Payment with Template

Overview

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.

Introduction:

 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.

Step 1

Create General Journal at General Ledger > Journal Entries> General Journal.

Step 2

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
3	Company	TPL	Company Code
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
11	Offset company	TPL	TPL
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
15	Currency	USD	Transaction Currency
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.

Step 3:

Add Required fields in the Excel Import which are not available in the above required fields.

• Download the excel
• Open it
• Add fields:

Step 3:

Insert the required data as per Step 2. Above.

Step 4:

Publish the Excel. Verify in the System with Same Journal and post from Dynamics 365 Finance and Operations.

Disclaimer:

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 enquiry@dhyey.com and our team will be glad to set up a free demo for your brand!