Accounts Payable Ledger
First Six Columns
When you first download the spreadsheet, save the document to wherever it is you save all your documents (see computer filing system for useful tips on filing electronic documents).
When you receive a supplier/vendor invoice enter in the following details to your spreadsheet starting in Row 3:-
- Date: Enter the date that is shown on the vendor invoice
- Invoice Number: Enter the invoice number that is shown on the vendor invoice
- Vendor's Name: Enter the vendor's name.
- Description: Enter a brief description of the type of purchase.
- Total Amount: Enter the total that is shown at the bottom of the invoice.
- Due Date: Enter the date that the invoice is due to be paid by - this should also be shown on the invoice, however some suppliers - more notably, very small businesses - sometimes leave this date off because they don't realize they should put it on there, in this case just enter the date you are going to pay it by (be reasonable - payment is usually due within the next 30 days or so).
Accounts Payable Ledger:
- Total Accounts Payable: The total of 5,710.00 is automatically calculated by Excel - it adds up the total from the 'Balance Due' Column underneath it (the screenshot is not showing all the amounts in that column).
- Balance Due: For example, the Balance Due of 5,000 is made up of the total of the Bank Business Loan of 20,000 (which you can see in First Five Columns screenshot further up this page) less the payments as seen in columns title Payment 1, Payment 2 etc.
- Payment Columns: Every time you make a payment to your supplier you need to enter it into the payments columns and the dates of the payments into the Dates columns, this is particularly useful if you have a large bill that you are paying off over several months.
In my example screenshot, you can see six payments of 2,500 which equals 15,000 - so 15,000 taken away from 20,000 leaves a 5,000 balance due to still be paid to the bank.
- Current Date: this is auto-generated by your Excel software and it should show you the current date that you are on (it will use the date that is entered to your computer so make sure your computer is dated correctly!). If you have problems with this, simply manually over-type the date to what you want it to be.
Accounts Payable Ledger
Using from Month to Month
Every time you enter new data to the spreadsheet, remember to Save before exiting.
The recommendation is to save the spreadsheet once a month with a new name.
For example, if it is the month of May and you are entering all May dated invoices then save your spreadsheet with the name Accounts Payable - May 20xx.
When June starts save a fresh copy of this ledger and start entering your June bills and any payments you are making. Call it Accounts Payable - June 20xx
Keeping copies like this ensures that if you make any huge mistakes in your current spreadsheet you always have last month's version to start afresh again.
You may also like Accounts Payable Procedures which will give you more information on the process of managing your business bills.