Purchase Ledger (Accounts Payable)
- 8 hours ago
- 5 min read
00:00:00
Track who owes you how much and for how long and also how much you owe to others. This is the latest version of my accounts receivables and payables tracker with a bunch of powerful new features. So let's check it out. Hi everyone. Welcome to this latest version of my accounts receivables and payables tracker. This tracker now has dashboards to give you a bird's eye view of who owes you how much and for how long. You have a main dashboard which shows you Accounts Receivables and Accounts Payables.
00:00:40
You have a Customer Dashboard and you have a Vendor Dashboard. You now also have a Customer List into which you can enter all your relevant information regarding each customer and this becomes an automatic drop-down list when you're entering your invoices. Similarly, you also have a Vendor List and this becomes a drop-own list in your Accounts Payables tab when you're entering invoices there. Let's go to the Accounts Receivable tab. This is where you would be entering all your invoices from your customers.
00:01:22
You would choose your customer name from the drop- down list over here. You can enter the customer ID if you have one. Enter the invoice date, the invoice number if you need to, the invoice amount, the amount that's already been paid. The balance due gets automatically calculated. You would enter the due date of the invoice over here. And here are two new columns that I've added in this latest version. This is for post dated checks. So if you take post dated checks from your customers as a security, you can enter the amount you've received over here.
00:02:04
Going to the right you can enter your terms like net 30, net 60, the product or service and any notes. If you go further to the right, you will see the number of days outstanding for each invoice, the current amounts 1 to 30 days, 31 to 60 days, 61 to 90, 91 to 120, and greater than 120 days. And these are two new additional columns I've added in this new tracker. Let's enter an invoice. Choose the customer name. Enter the customer ID if you would like to enter an invoice date. Say it's June 1. Enter the invoice number if you would like to enter the invoice amount.
00:03:06
Enter any amount you have already received. Enter the due date. If you have received any post-dated check, you can enter that and then you can enter any terms, the product and any notes. If you go to the right, you will see that the number of days outstanding is six because I'm recording this on July 23rd and the due date of this invoice was 17th July. and the amount outstanding of 3,000 is showing in the 1 to 30 days category. This spreadsheet also has an Open Invoices tab and over here all of your invoices which have
00:04:05
a balance due are listed. So say for example this last invoice we entered is also shown over here. It has a balance due of $3,000. If we were to go back into the accounts receivables and change the amount received to the full amount of $15,000, the balance due becomes 0. And if we go to Open Invoices, we'll see that - that invoice has now disappeared. Going to the main Dashboard, you get to see your total outstanding over here. You also get to see your current receivables 1 to 30 days, 31 to 60 days, 61 to 90,
00:04:52
91 to 120, and greater than 120 days. You get to see your top 5 customers who owe you money. You get to see your total number of invoices due and the total overdue. And this graph also shows you by percentage how much is owed in each of these categories. To the right you get to see the Accounts Payable information. You get to see your total outstanding, your current 1 to 30 days, 31 to 60, 61 to 90 days, and so on. You get to see your top 5 vendors by amount due and the total number of invoices overdue and your total overdue.
00:05:42
If you come to the Accounts Payable tab, this is where you would be entering all the invoices that have been raised to you by your vendors. You will choose your vendor name, vendor ID, invoice date, invoice number, the amount you owe, any amount you've already paid, the due date of the invoice. If you've issued any post-dated checks, you can enter that and the net payable gets listed over here automatically. You would enter terms, product, service, description, notes, and to the right again you would see the total number of days outstanding, what the current is,
00:06:22
amounts that are between 1 to 30 days, 31 to 60, 61 to 90 days, and even greater than 120 days. We now come to the Customer Dashboard. This is a great new feature of the spreadsheet. Over here, all of your invoices for each customer get summarized. You get to see the total balance due for each customer as well as a breakdown of their invoices by aging, current 1 to 30 days, 31 to 60, and so on. You also get to see all of your post-dated checks you received from each customer and what is still due from them.
00:07:07
Similar to the customer dashboard is the vendor dashboard. Again, this shows you by each vendor the total balance due as well as the aging for each vendor, any post-dated checks you've issued to them and any net payable. The spreadsheet also automatically creates Accounts Receivables and Accounts Payables Aging Reports. These are great to print out and hand to your banks or investors because they can be used as collateral. All of the fields in the accounts receivable and accounts payable tab can be sorted and filtered.
00:07:52
You can sort it by invoice date or by customer name. Say we want to sort it by invoice date. Select oldest to newest and you'll see everything is sorted according to date chronologically. Say you want to sort it by customer name alphabetically and it does it for you. Now say you want to choose one customer and see all the invoices for that particular customer. Again just go to the filter section and choose that one customer. Say it's Blue Oak Collective. Say OK and you get to see all the invoices for that
00:08:32
one customer. You also get a total of the total invoices, total paid, total due, and so on. Going to the right, you also get to see the aging for each of the invoices and the totals. This spreadsheet accommodates 500 customers, 500 vendors and is available both in Excel and Google Sheets. You can get this spreadsheet at my website MoneyUsage.com. I will also be leaving a link in the description and comment section below. I hope you found this useful.
Comments