What is Accounts Payable?
As per Wiki
Accounts payable is money owed by a business to its suppliers shown as a liability on a company’s balance sheet. It is distinct from notes payable liabilities, which are debts created by formal legal instrument documents.
Or in very simple terms accounts payable ledger consists of the list of suppliers along with details like invoice number, date of invoice, date wise payments made and outstanding balance.
Excel is a powerful tool which helps us to create/design a tailored Account payable ledger.
Let me share the basic of how I created this template.
Row 1 = Enter Company/Business Name. Cells A1 to AD1. Merged cells.
Row 2 = Heading of sheet. Cells B2 to BD2. Merged cells. Headings can put as Accounts Payable Ledger or Creditors Ledger.
Row 3 = Multiple headings as numbered below
- Sub heading for Current Date in A3.
- Next cell B3 which will reflect the current date of system using the formula =TODAY(). Date will automatically change according to the system date. You don’t need to change it every time and this saves your time.
- The next column for Total Due till date. The cell next to it reflects total of column F which is the balance of each supplier. Formula applied here is =SUM(F5:F23).
Row 4 = This row consist of sub headings for data input. For example see table below:
Date, Invoice number, Supplier name, Amount of Bill, payment 1 and payment 2 and their respective date are details to be put while entering data.
For the Due date, you can set a function which automatically calculates number of days for you and will show the exact date of payment. For this you will set the function as shown below:
= cell address which contains date + Number of days for allowed.
Here in our sheet, it is 45 days. So the formula entered is =A5+45. Copy this to all the cells below this cell, this will apply the same settings to all cells below.
Balance Amount here is calculated by deducting the total amount of bills – Sum(Payment1+payment2) etc. In this template formula used is =D5-SUM(H5+J5+L5+N5+P5+R5+T5+V5+X5+Z5+AB5+AD5).
If you have any queries or questions, share them in comments below and I will be more than happy to help you.