By: Merine Tulloch, FCCA
Keeping track of your accounts receivable is vital, since you need funds to make sure that your business remains in operation. Not giving your payables the same level of vigilance will result in excess debt and penalties, damaged credit and possibly the lack of supplies to carry on your business. You can monitor accounts payable by creating an accounts payable aging file in a spreadsheet application. This will assist in figuring out which bills are to be paid first as well as how many bills are past due and by how long.
Step 1
Open your spreadsheet application such as OpenOffice, Microsoft Excel or Google Spreadsheets. Make sure that this is a new, blank spreadsheet. You may insert a page header and give it a title such as “Accounts Payable Aging.”
Step 2
Use the first row at the top of your spreadsheet as your header row and name the columns from left to right. The first column is “Vendor” or “Supplier”, then name the next four columns as “Last Invoice Date”, “Last Payment Date”, “Last Payment Amount” and “Reference/Check#”.
Related Reading: Accounts Payable Ledger
Step 3
Name the next four columns as follows “0-30 Days”, “31-60 Days”, “61-90 Days” and “Over 90 Days”. Based on the invoice date, the vendors balance will fall into one of these columns and therefore will help you determine which bills are past due should be given priority.
Step 4
Name the next column “Total Invoices.” The figure in this column is the sum total of all invoices regardless of which period they fall in. The formula might be, “ =G2+H2+I2+J” or “=SUM(H3:J3).”
Step 5
The next column “Total Due/Owed” will provide information on the amount due to the supplier. To arrive at this figure the formula is “Total Invoices” minus “Total Payments to Date” or “=+K3-E3”.
Step 6
The bottom row of the spreadsheet will display the Total of each column. Enter the formula to add each column separately. You will be able to see at glance how much is payable for each period and the total overall accounts payable. Please see sample below.
Update the information on a weekly or monthly basis with new purchases or payments made. You may also create a new worksheet for each month and track the activity in a single workbook if the spreadsheet application that you are using allows it. All that you would need to do is copy over the report in to a new spreadsheet and update the aging position as needed. You can name the bottom of each worksheet as you go along.
© Merine Tulloch -2015
All Rights Reserved
No part of this website or any of its contents may be reproduced, copied, modified or adapted, without the prior written consent of the author, unless otherwise indicated for stand-alone materials.
You may share this website by any of the following means:
1. Using any of the share icons at the bottom of each page
- Providing a back-link or the URL of the content you wish to disseminate; and
- You may quote extracts from the website with attribution to www.merinetulloch.comFor any other mode of sharing, please contact the author at the email: merine.tulloch@yahoo.com
Commercial use and distribution of the contents of the website is not allowed without express and prior written consent of the author.