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 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. A customized template considering the basics of Account payable ledger can be downloaded here.
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 Due date you can set a function which automatically calculates number of days for you and show the exact of payment. For this you need to set a function like this = 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 cells below this cell, this will apply the same settings to all cells below.
Balance Amount here is calculated by deducting 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.