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 NameExample ValueValue Description
1Date24-08-19This is Posting date = Invoice date or Payment posting date
2VoucherGNJL000805Auto Generated system number sequence
3CompanyTPLCompany Code
4Account typeCustomerSelect Customer or Vendor as the case may be.
5AccountUS-004Select Customer Account number of FnO
6InvoiceUS-12345Insert actual Invoice number of Sales Invoice or Purchase Invoice
7DescriptionInvoice US-12345Invoice Description upto 50 Character
8Debit126,987.001. 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
9Credit0Balance amount to be recovered or paid out of total Payment Amount. Either insert Debit amount or Credit amount as the case may be.
10Terms of paymentNet45Insert Terms of payment on Invoice
11Offset companyTPLTPL
12Offset account typeLedgerSelect Ledger
13Offset account300160-001-Retained Earning Account for the Opening balance of 2019
14Offset transaction text Opening balance Description
15CurrencyUSDTransaction Currency
16Exchange rate1Exchange rate as on Dec-31-2019
17DocumentINV-AB_12345Invoice Number reference
18Document date24-08-19Invoice Date
19Due date08-10-19This 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!