How to Make a Vendor Ledger Reconciliation Format in Excel

In business, it is quite urgent to create vendor ledger reconciliation. Are you searching for a good vendor ledger reconciliation format for Excel? Then you have come to the right position. We’ll show here a proper vendor ledger reconciliation format in Excel with vivid illustrations.

What Is Vendor Ledger Reconciliation?

Vendor reconciliation identifies the reconciliation of a vendor’s balance with a statement served by the vendor. The reconciliation process matches vendor invoices with the system of entities. It is also a way of detecting the entity’s payables to vendor account balance. Reconciling statements helps a business to ensure accuracy between what the vendor charges and the services achieved by the company.

Reconciliation identifies issues within the system and vendors’ ledger accounts. The reconciliation report can help to increase vendor relationships, minimize vendor’s mistakes, and boost control over the vendor’s expenditures. With many regular transactions and different statements to reconcile, it ensures less time and validity throughout the process of reconciliation.

How to Make a Vendor Ledger Reconciliation Format in Excel: Simple Steps

Now let’s follow the steps properly to make a vendor ledger reconciliation format.

Step 1: Form a Basic Template

At the very first we need to form a basic template to input the data. Stating the company name, period, setting the columns are the tasks to complete in this step.

Firstly, insert the time period in a cell for that you want to make a vendor ledger reconciliation. I inserted 1-July-2020 to 31-Dec-2021.

Next, insert the required headers in a row like Date, Debit, Credit, etc. I used row- 6.

Later, insert the ledger dates in the date column. But in the first cell of the column, there will be no date, we’ll keep it for the term- Opening Balance.

If we apply data validation in the date column, it will help us detect any wrong date while inserting dates. Select the range of the date column and click as follows: Data > Data Tools > Data validation > Data validation.

After that, select Date from the Allow drop-down box, between from the Data box, and lastly, just insert the start date and end date.

Step 2: Provide Input Data

Now we need to insert the values into the corresponding fields. Let’s start.

Insert the corresponding values in the Description, Debit, Credit, and Dr or Cr columns according to the dates.

According to the date need to insert the values. These values will differ from yours so do the number of rows.

Step 3: Calculating Closing Balance

Now, we’ll calculate the closing balance for every date with a simple manual formula. We’ll add debit and subtract the credit from the previous closing balance to get the new closing balance. So, insert the following formula in Cell G8–

=G7+D8-E8

Then just hit the ENTER button to get the closing balance for the first date.

Lastly, drag the Fill Handle icon to copy the formula for the rest of the closing balance.

Here’s our complete format of vendor ledger reconciliation.

Things to Remember

Make sure you have inserted the right date range.
Be careful while inserting the debit or credit account in the right place.
You can use the SUM function too instead of the manual formula.
Any mistake in the vendor ledger reconciliation process can make excess payments that a company may not be able to find out.

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

Vendor Ledger Reconciliation Format.xlsx

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to make a vendor ledger reconciliation format in excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.

<< Go Back to Excel Reconciliation Templates | Accounting Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!