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 that helps us to create/design a tailored Account payable ledger.
Let me share the basics of how I created this template.
Row 1 = Enter Company/Business Name. Cells A1 to AD1. Merged cells.
Row 2 = Heading of the sheet. Cells B2 to BD2. Merged cells. Headings can put as Accounts Payable Ledger or Creditors Ledger.
Row 3 = Multiple headings as numbered below
- The subheading for the Current Date in A3.
- Next cell B3 will reflect the current date of the system using the formula =TODAY(). The 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 to date. The cell next to it reflects the total of column F which is the balance of each supplier. The formula applied here is =SUM(F5: F23).
Row 4 = This row consists of subheadings for data input. For example, see the 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 that automatically calculates several 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 the comments below and I will be more than happy to help you.