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

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

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


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.

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.


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!