Discover the Best Client Payment Tracker Excel for Financial Services

Client payment tracker excel for Financial Services

hello and welcome to this video today i'm gonna give you a tour of these google sheets in voice tracker it's available for purchase on my etsy shop i will leave the link to that in the description down below this is just gonna be a quick walkthrough of the entire spreadsheet and then if you want a more detailed explanation of each section i have a separate video on that so i will leave the link to that in the description down below so let's get into it first i want to explain the logic so what i did is i have 12 different sheets right here one for each month and then within each sheet you have one for each month as you can see up here and then for each sheet you have two separate tables so you have the invoices table and then you have the invoice payment table so what you're going to do is you're going to enter your invoices here you will enter how much they owe you and the due date within this corresponding month and then whenever you receive a payment for any of these invoices you will enter that payment in this table right here and amounts will be added up so for example i have a payment for invoice a1 that's the invoice number i gave it for the client kim's corner so it's this invoice right here they owe me a thousand dollars and they paid a thousand dollars so it was automatically marked as paid and then i have a second invoice for kim's corner invoice a2 and she owes two thousand dollars and she has paid two hundred dollars towards that invoice in two separate transactions so i was able to enter the day that was paid and the amount for each of those transactions and as you can see these two hundred dollars were automatically added up here to this amount paid for this invoice and if i wanted to pay it in full so i owe eighteen hundred dollars i would just do [Music] eighteen hundred dollars and now this invoice was paid in full so that's the basic functionality of this so you add your invoices here and whenever you receive a payment for any of these invoices you enter it here it doesn't matter when these transactions were made what matters is the invoice that they're paying so this pretty much replaces the just changing this manually to paid unpaid or partially paid you will only edit cells that have a white background within this invoice sheet so as i mentioned you have one sheet per month and then you have this invoices sheet what this does is it's going to pull every single invoice from every single month and it's going to join it together in this one table so you're going to be able to filter this table you're going to be able to filter by client you're going to be able to filter by month and then you're also going to be able to select and unselect whatever payment status that you want to see for any specific filter and then you can also sort by any column in ascending or descending order and then if you want to quickly jump into a specific invoice you can just click this link and it's going to bring you to the corresponding row of that invoice within its corresponding monthly sheet and then you also have a payment sheet so it's pretty much the same logic it's going to join this payments table this invoice payments table from every single month and it's going to join it together in this one table that you can also filter and sort and then you can also jump into the corresponding payment transaction by clicking on this link so i quickly jumped into this transaction right here then you also get this dashboard that is going to pull totals for each month and it's going to show them on these small tables and then you also get that same information all together in this table that is accompanied by this graph right here that is showing your amount due for the month and how much has been paid and then each month gets this small progress bar showing what percentage of the amount due has been paid and what percentage is still owed and then finally you get this clients table so this is where you are going to save all your clients so you can create your drop downs so those clients that you enter in that table are creating these drop downs automatically and then you also have this sheet where you can customize your entire template it looks like a lot but you really don't have to change anything unless you want to maybe change the way one label is named so instead of going to every single sheet let's say you don't want this to be called status you want it to be status payments so instead of manually going to every single sheet and changing that you will just change it here and it will automatically change everywhere else so you get your clients your dashboard your invoices filter your payments filter and you get 12 sheets one for each month it might look like a lot but it's actually really simple because most of it is automated so all you have to do is just add your clients right here and everything else is optional and then each month you're going to add your invoices here and then you're going to add your payment transactions here and that's all you're going to edit everything else is read-only these two sheets are just meant for you to filter and just look up information but never really touch anything here other than the filters the same goes for this one this is just for you to look up information you're gonna filter and you can sort but you should never really edit anything here other than playing around with the filters and then you have the dashboard which is entirely read-only you should never ever touch anything here so that's it for this video i hope you liked it if you want to purchase the template you can find the link in the description down below and if you want to watch a more detailed walk through a more detailed example of how to use this template you can find the link to that video in the description down below as well if you have any questions feel free to message me on etsy or send me an email i will be happy to help you thanks for watching