Ready-to-use Accounts Payable Template in Excel, OpenOffice Calc, and Google Sheet to easily monitor and manage your vendor/supplier payments.
Furthermore, this template displays the outstanding amount against each invoice. The best part about this template is that you can generate vendors ledger at the click of a button.
With the help of this template, you can record your trade payables as well as non-trade payables. Moreover, you can generate vendor-wise, date-wise, status-wise, etc reports.
The template consists of multiple payment columns. Thus, the user can record partial payments or full payments against each invoice.
Table of Contents
Download Free Accounts Payable Template (Excel, OpenOffice Calc, and Google Sheet)
Additionally, you can also download other templates such as Accounts Receivable Template, Salary Slip Excel Template India, Salary Slip Format UAE, Simple Salary Sheet, and Employee Salary Sheet depending on the company requirement.
You can also download a fully automated Payroll Template With Attendance to easily perform all your payroll tasks.
In case, you want to customize any of the above templates feel free to contact us. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.
How To Use Accounts Payable Template?
Accounts Payable Template is a fully automated AP Ledger to record the invoices for trade payable as well as non-trade payables.
3 simple steps to record invoices in this fully automated Accounts Payable Ledger:
- Record the invoice received in the Accounts Payable Ledger.
- Insert Credit Period.
- Record date-wise payments.
That’s all the user needs to do. As the template consists of recorded macros, you can fetch any vendor/supplier report with a click of a button.
Moreover, the template consists of predefined formulas. It automatically calculates the following for you:
- Due date of the invoice.
- Outstanding amount against each invoice.
- The number of days the invoice is outstanding above the due date.
- Displays a call to action against each invoice.
If the invoice is outstanding above the credit period, the remark column will show “Aging” and if the invoice is outstanding below the credit period, it displays “Due”.
You can download this sheet in three different spreadsheet programs; Microsoft Excel, Google Sheets, and OpenOffice Calc. Instructions to use each type of spreadsheet program are given in the below section.
Components of Accounts Payable Excel Template
The template consists of 2 files: Accounts Payable Ledger and Vendor/Supplier Report Generator.
Accounts Payable Ledger
In the header, insert your company name. The next cell displays the Total Accounts Payable outstanding till date.
The accounts payable template sheet consists of two sections Vendor/Supplier Details and the Payment Section.
Vendor/Supplier Details
Invoice Date: Insert the invoice issue date in this column.
Month: This column consists of predefined formula. It displays the month using the TEXT Function. This column is useful for generating month-wise reports.
Invoice #: An invoice number is a unique number series issued by vendors/suppliers.
Vendor/Supplier Name: Insert the name of vendor/supplier. Accounts payable records both trade as well as non-trade invoices.
Invoice Amount: Enter the invoice amount as per the invoice to avoid discrepancies.
Credit Period: Insert Credit Period. The credit period exists in both accounts payable as well as accounts receivables. In accounts payable, we get predefined credit days to pay a particular invoice.
Usually, the credit period differs based on relations. Hence, enter the number of days allowed for credit.
Due Date: This column consists of pre-defined formula. Hence, it auto-calculates the due date based on the credit period.
Balance: This column displays the outstanding amount against the invoice. It consists of predefined formula. Hence, it is auto-populated.
Days: Days column displays the number of days passed above the due date. The cell is blank when the invoices are completely paid. Or else it will calculate the number of days.
Remark: Remarks column also consists of a pre-defined formula. It is blank if there is no outstanding. If the days are below the due date it displays “Due”. But, if the days are above the due date, it displays “Aging”.
Furthermore, the template displays the “Due” invoices in green and the “Aging” invoices in red.
Payment Section
Date: Date of payment.
Payment Amount: Amount paid/transferred to the vendor/supplier against the respective invoice. There are 4 payment columns with their respective dates. Hence, you can record partial payments accordingly.
Customized Reports
You can generate month-wise, date-wise, amount-wise, and client-wise reports using the Filter data option under the Data tab.
All you have to do is to click on the down arrow key beside each heading, select your criteria and click “OK”. The sheet displays the desired output.
For example, if you want a month-wise report, click on the arrow beside the “Month” heading. Select the month for which you want the report and click “OK”.
Similarly, you can prepare customer-wise, credit period-wise, and due date-wise reports.
Moreover, these same features are also available in OpenOffice Calc and Google Sheet files. Thus, you can easily make all these reports in those file formats also.
Vendor/Supplier Report Generator
You can print a vendor-wise report using this sheet which can be helpful during the preparation of Final Accounts. Additionally, this report can be helpful to reconcile vendor/supplier invoices from time to time.
Insert the name of the Vendor/Supplier in the top Criteria box. Click on the Fetch button. Using a prerecorded macro, the sheet fetches the data of the respective vendor.
Furthermore, you can also generate vendor reports alphabetically. For example, If you insert in criteria “A”, then the sheet generates all vendors starting with the alphabet “A”.
In the OpenOffice Calc format of the Accounts Payable Template, you can generate the vendor/supplier report using Advanced Filter Option.
The Google Sheet Format of Accounts Payable Template automatically transfers data based on given criteria. Change the criteria to your desired vendor and it displays the respective data.
It uses the Filter Function of Google Sheets to transfer data. You can also use the Query Function available in Google Sheets.
If you want to make different files for each debtor, you need to configure using a macro with Advanced Filter. We can do it for you as a paid job. You can contact us at info@msofficegeek.com.
To know more about Accounts Payable, check out the FAQ section below.
Frequently Asked Questions
A ledger that records Trade Payables as well as non-trade Payables of vendors/suppliers. In simple terms, Accounts payable are amounts due to vendors/suppliers against the unpaid goods or services. Trade invoices or trade payables are those that a company purchases for further processing or selling. These include the purchase of raw material, specialized services, etc to produce a product or provide a service. Non-trade invoices or payables are those which are not part of a product/service but are necessary to run a business smoothly. These include office supplies, taxation, utility bills, etc. Usually, every vendor/supplier allows a credit limit. The company sets this limit is based on relationships. The payments delayed upon this credit limit are our long outstanding invoices. It is known as aging. Outstanding invoices of accounts payable for a longer period depict the bad financial health of a company. Hence, it is advisable to make timely payments to vendors/suppliers. Moreover, it can disrupt supplies and also hinder production. Mainly, the duties of the Accounts Payable department are to report and pay external/internal business payments to vendors/suppliers and ensure timely payments. Moreover, in large companies, the Accounts Payable department manages travel arrangements for staff which include booking airline, car rental, hotel reservations, etc. Furthermore, it also includes making payments for AMCs and other maintenance expenses that help in running the business smoothly. In addition to that, it is also responsible for distributing internal payments such as reimbursements, controlling, petty cash, and distribution of corporate taxes. Apart from that, this department undertakes verification of purchases against POs and analyzing aging reports to maintain vendor relations. Accounts payable is a 4 step end-to-end process: 1. To receive the invoice Following proper process helps to prevent payments of fraudulent invoices. Additionally, proper reporting saves from multiple payments for the same invoice to vendors eventually ensuring the safety of the company’s financial assets. Vendor Account Dr Vendor Account Dr Accounts Payable Account Dr Accounts Payable Account Dr PO stands for Purchase Order. A "PO invoice" consists of the order number and details of the goods/services agreed between the company and the vendor. Invoices without a purchase order are known as Non-PO invoices. Such invoices are also known as expense invoices. Companies use such invoices for various indirect purchases. Payments made in excess to vendors no pending invoices of a vendor/supplier, it is known as Debit Balance. Recoveries of such amounts are known as Debit balance recovery. For regular vendors, such invoices can be settled through Credit Memo or can be adjusted against future invoices. In case of no future transactions, such recoveries can be made through cash or bank transfer. Failing to recover such debit balance, the business needs to write off such balances. The average payment period refers to the number of days to pay off its credit purchases. To calculate the average payment period, use the following formula:What is Accounts Payable?
What are Trade Payables and Non-Trade Payables?
What is Aging in Accounts Payable?
What is the role of Accounts Payable in the business?
What is Accounts Payable Process?
2. Check the invoice against the PO
3. Report the invoice in the respective vendor/supplier account
4. Timely payment to vendors.What is the journal entry for recording an invoice in Accounts Payable?
To Accounts Payable Account CrWhat is the journal entry for making payment under Accounts Payable?
To Cash or Bank Account CrWhat is the journal entry for purchase return?
To Purchase Return Account CrWhat is the journal entry for cash purchase with discounts in Accounts Payable?
To Cash or Bank Account Cr
To Purchase Discount Account Cr
The total of debit and credit columns should be equal.What is a PO Invoice?
What is a Non-PO Invoice?
What is a debit balance recovery?
How to calculate Average Payment Period?
Accounts Payable / (Total Annual Purchases / 360)
If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.
We also design customized templates according to your needs. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.
Please send us your queries or suggestions in the comment section below. We will be more than happy to assist you.
Pingback: Ready-To-Use Excel Inventory Template - MSOfficeGeek
Pingback: Ready-To-Use Marketing Budget Template - MSOfficeGeek
Pingback: Ready-To-Use Expense Report Template With Chart - MSOfficeGeek
Pingback: Ready-To-Use Straight Line Depreciation Calculator Template - MSOfficeGeek
Pingback: Ready-To-Use Petty Cash Book Template - MSOfficeGeek