Google Sheets Invoice Tracker Template for Nonprofit Organizations

Google sheets invoice tracker template for Nonprofit

hello and welcome to this video today i'm going to walk you through a more detailed example of how to use these google sheets in voice tracker i have a separate video in which i give you a quick tour of the whole thing so i recommend that if you already purchased the template you watch both of them and if you haven't purchased the template i recommend you maybe watch the other video i will leave the link to that in the description down below because this is more detailed and the other one is just a quick walkthrough of the whole thing so let's get into it the first thing you're going to do is you're going to set the year you will be working with now you have this section right here if you are working in a different language you can just change this to your own language the sheet names i recommend that you don't change the names of the monthly sheets if you really want to change them i'm going to show you how you do that in the end of this video so you have to do it in a certain way to keep everything connected so let's move on to the client section so this section is going to be used to customize your clients right here so the first thing you need to do before you create your invoices is to create your clients and you're going to do this in this column right here so each of your clients has to be unique if it isn't it's going to be highlighted in red so you need to find a way to uniquely identify each of your clients let's create a new client my new client example now another thing to remember is that you should never use quote symbols inside your client's names so if you want to write maybe for example this it's going to give you an error because you're using quote symbols so adding quotes might mess up some search functionalities and then everything here is completely optional you can customize these three sections so you have status purchase plan custom column and each of those has different drop downs so you can customize that here you can change the title here and you can change the drop down elements down here if you don't want to use those columns you can just select them right click and delete them and then you have these date options if you double click a calendar will appear so if you have used to have a client but he's not with you anymore you could set an end date but that's also entirely up to you and then if you want to add more columns you can just right click here and insert column to the right or to the left so the only thing that you should never ever touch are these fixed columns right here and then your clients must be unique so once you added your clients you can move on to creating invoices so i already filled out january and february so let's start in march so as you remember i created a new client so this is my new client example i started naming my invoices a for january and then b for february so let's do c for march so this is going to be invoice c1 for my new client example it's going to be due on the 4th and as you can see once i entered the due date only the day the date was automatically created for me here so you can actually see the weekday the month and the day and the year is going to be the year that you entered here so that's automated so you don't have to enter the full date you can just enter the day of that corresponding month and then as you can see it is highlighted in yellow because it's detecting that i already entered some information but i haven't entered the amount too so let's do 1 000 due and then as you can see my balance is now minus one thousand dollars the status was automatically set to unpaid and i have zero for amount paid and as you can see this is also updating automatically so these are my totals and these are my number of invoices for each of these tattoos and then the total number of invoices are shown right here and then this is just for your own reference so you can once you actually send that invoice to your client you can just check this box but it really doesn't do much it's just for your own reference you can ignore it you can use it or you can name it something different now all of these labels if you want to change any of them you have to go to the customize here sheet you have to find the as an invoice payment section and then where it says your translation that's where you can change those labels so you have to do it here because there's a lot of functionalities connected to this table so you must never change them directly here you have to come to the customize here sheet and change whatever label you want to change you have to change them here now i don't see a reason why this would happen but it's important to note that whatever you enter here must be unique in this specific table so you can't have two labels named invoices for example or two labels named clients they have to be unique the same goes for this table i don't see a reason why you would duplicate them but i just wanted to mention it because this will break some functionalities then you have your status right here paid over paid partially paid or unpaid so if you want those to be named differently you change them here in these white cells so you will only edit white cells gray cells are for reference white cells are for you to set your own translation or a different way of naming those labels so i'm gonna come back to march and as you can see i have my invoice ready so let's create a second invoice and then again i'm gonna use my new client example and now they're gonna owe two thousand dollars and that is due maybe on the 21st and as you can see it's also marked as somebody now i have two invoices the total is updated automatically now let's say i already received the payment for this invoice i received one payment of 500 even though they owe a thousand dollars so now i'm going to set the date paid so let's say today and they paid 500 for my invoice c1 for my new client example so now my amount paid is 500 they owe 500 and the status was updated automatically to partially paid now let's say somewhere in the future they paid another 500 and then i add them to my new client example and now the total amount paid is 1 000 for that invoice and my balance is zero and the status is updated automatically to paid and as you can see this progress bar has been moving automatically so let's say i'm gonna pay now this invoice i'm gonna pay it in one transaction and i'm gonna pay it in full so let's say i'm gonna pay those two thousand dollars to that invoice and now my bar is completely full and both of my invoices have been paid invoice number client and amount due are mandatory for the invoices an amount paid an invoice client are mandatory for the invoice payments you will only edit cells that have a white background within this invoice sheet you might think that these are empty they look empty but they're actually not so right here i have a formula that's hidden that's affecting every single row down here i have a formula down here i have a formula here i also have a formula here and here and then down here if you add something it will mess up the lookup functionality so only edit cells that have a white background never add any columns so now this is what we ended up with this is our total amount to our total amount paid and our balance so we have two paid invoices and one on paid invoice so if you move on to the dashboard and we go to march you're gonna see that same information right here we have a total of three invoices and one is unpaid now it's important to note that an invoice will be considered unpaid even if it's partially paid so this number represents any invoice that is not fully paid now you also get your progress bar here which looks the same as this one just a tiny version you get your amount due and your amount paid that match your amount doing your amount paid you get your balance right here and that same thing repeats for every single month and then you can easily access your sheet by clicking on this link you also get totals for every single sheet right here so this is adding up the balance for every month and then here you get that exact same information just in a smaller table that is accompanied by a graph so you get your amount due in these blue bars and then you get your amount paid in these green bars and now if you move on to the invoice sheet this is where you will find every single invoice within your spreadsheet no matter on which month you created it so this is useful to search for invoices whenever you receive a payment so let's say i receive the payment i'm going to sort it by invoicemonth in ascending order so now it's sorted by this column and then let's say i only want to see invoices for kim's corner so i have all of these invoices i have two invoices in january so let's go look at them i have these two invoices and then i have four invoices in february so i have one two three and four and let's say i only want to look at paid invoices so i'm going to select this checkbox and now i'm only looking at paid invoices so if nothing's selected it will show everything and if you select one then it will get rid of the rest and if you select all of them then it will again show everything so selecting all of them and selecting none of them is pretty much the same thing so let's say i only want to see the unpaid ones i just select this now i have january and february but let's say i only want to see february so i'm going to filter by february and now i'm only seeing invoices for kim's corner that's my client for february and i'm gonna select paid so i'm only seeing paid invoices for kim's corner for february so whenever there's no filter selected you will get this message that says showing all invoices and if you filter it then you'll get this message that says filters have been applied so if you get the showing all invoices it means you're seeing absolutely everything and these totals should match the totals in the dashboard now let's say you are interested in jumping to this invoice you want to jump into this b1 invoice for kim's corner for one thousand dollars so i'm just gonna click here and now i automatically jumped into the b1 invoice for games corner in february for one thousand dollars so this entire sheet is read only the only thing you should do is you can play around with these filters and then you can click on the links but other than that you should never edit anything in this sheet you shouldn't add cells rows nothing and then the same goes for these payments sheets so it's pretty much the same logic as the invoices the only thing is that instead of taking this information it's taking this information and it's joining it together in one single sheet now you can also filter by client here you're filtering payments so you can right now we're seeing everything i have no filter selected and then i can filter payments for kim's corner and i can sort them by invoice month in ascending order for example i can sort them by amount paid in descending order and then if you want to see that payment transaction then you can just select it so let's say we're selecting the payment for invoice b1 that's located in february for one thousand dollars so i'm just gonna click on the go link and then i jumped into the invoice payment table into this transaction that is paying one thousand dollars to the b1 invoice now finally i'm going to show you how to properly update the sheet names for the monthly sheets so if you want to change the sheet names the first thing you have to do is you have to change the name here so if maybe you want to name them like this as soon as i change that it's going to give me an error because that is no longer properly connected and you're also gonna see an error here in the dashboard so what you do is number one step one you're going to change the sheet name here and you're going to see this error step number two you're going to go ahead and change the sheet name here so that it matches the one that you entered in the table now you have to make sure that it matches perfectly or there will still be an error so now that i did that the final step is to refresh you can see it but i'm using the refresh button on my browser so i'm going to reload the page and then once you reload it it's going to update the status to ok so every single time that you want to change a sheet name you have to change it here first you have to change it here afterwards so it matches exactly and then you have to refresh the page and then make sure that everything here says okay and that there are no errors in the dashboard so that's it for this video i hope you found it useful even if you've already watched both videos this one and the quick tour i still recommend that you read the entire pdf that you received with your download just so you know absolutely everything there is to know about the spreadsheet's functionality so if you have any questions please feel free to message me on etsy or contact me via email and i will be happy to help you thank you for watching