Enhance Your Nonprofit Operations with the Client Payment Tracker Excel for Nonprofits

Client payment tracker excel for Nonprofit

hey everyone today I'm going to show you guys how to make an easy quick and automated Finance tracking spreadsheet in Google Sheets by the end of this video you're going to have a sheet that looks something like this and there's going to be a few different tabs I'm really excited to get started so let's go so here we have our blank spreadsheet in our first sheet we're going to want to track all of our transactions that we make so we're going to rename this first sheet our Journal this is where all the journal entries go and for each journal entry we may want to know the date in which it occurred the account it is in whether it is the gas expense account or revenue from work account the amount of the transaction and any other notes you may have on it and as we go through the spreadsheet we're going to want to format it in a certain way to make it look good so I'm going to click on this select all section here and I'm going to select Montserrat as the font I just like it as the font for a spreadsheet like this and then in Row 2 we are going to format our date so say a transaction occurs on January 1st 2023 we are going to highlight this row or this column column a click on format number and format it as a date so that if we were to delete this and type in one slash two it knows to include the year as well next as we fill in the account in which this transaction took place and we want to recognize that every account we're going to use in the spreadsheet is either a revenue or an expense so for example in this transaction we received a paycheck from our job so we're going to call that account work Revenue making sure it has the word Revenue in it also we're going to resize this column B by going in here resizing it we're going to make it size 200. as for the amount if our paycheck was say 540 dollars we would type that in and I'm then going to select column C and format number we're going to format it as accounting I'm also going to want to resize this column to 150. as for the notes section you don't need to write extensive notes about each transaction but it's available if you need it say in this example we work as a pizza delivery guy we will say pizza place for the notes and then this we want to allow more room so we're going to resize column D to 600. I also want the date to be centered so I'm going to highlight this column and horizontally align it in the center then I'm going to return the date back to left aligned so now we can do another example transaction I'm going to click on this most recent transaction insert one row above so that we have the most recent ones at the top as we go and the date is January 4th the account is gas expense because we bought gas and the amount comes to 24.31 cents and for a note we're just going to say gas don't need anything too fancy there we now have a whole set of sample transactions onto our Journal sheet but let's make it look a little bit nicer first I'm going to go to row one and insert one row above to make a header for this header I'm going to want to merge all four of these cells together by merging here next I'm going to type in income and expenses 2023 because that's the year I'm also going to Center this next I'm going to highlight that header and all these column headings here and make them bold I'm also going to change the color up here to I mean you can make it whatever you want but I'll make it this dark blue and I'll make these a different dark blue and then to make this pop more I'm going to make this text color white I think that looks a little bit nicer but we're not done yet next we're going to create alternating Colors by highlighting all of our data going to format alternating colors removing the header option here hit done and now every time we insert rows above here it will also work with the alternating colors and one last thing we're going to highlight this entire range of data go to the borders section and select all borders I just think this makes everything look a lot nicer and cleaner one more measure to make this look better is also functionally important we're going to select the column B go to format and conditional formatting as I explained earlier every account is either an expense or a revenue we want the expenses to show up as read and the revenues to show up as blue so we can do that by format cells if text contains if it contains the word expense we're going to make it this red it's the second lightest red okay done add another rule do the same thing format if text contains if it contains Revenue then we're going to make it this second lightest blue and hit done and now we're really getting somewhere let's X Out of conditional formatting and test it so this most recent transaction was a gas expense if we delete it the cell is now white we type in if we type in just gas nothing's going to happen we have to type in the word expense and now it becomes red however we don't necessarily want to type the name of the account every single time because if you make a typo or type in the account differently than you've typed it in before it's not going to work with the whole functionality of the spreadsheet and for that reason we want to create a drop down menu so that when I click on one of these cells instead of typing gas expense I can select gas expense from a list of all our accounts and that is where the accounts page comes into play we are going to add a sheet and rename it accounts this is where we list all of our accounts so I'm going to put that in in one second now we have what can be considered a completed list of our accounts all the accounts here you will find on the journal except for other revenue and other expense save these accounts for things you may not know how to categorize and that may be infrequent our accounts list will really be used back on the journal tab so like I explained before we want to create a drop down menu to do this we are going to highlight cells B3 to b19 the entire range that needs a drop down menu for the account select data then data validation and once we get here we have to add a rule criteria is dropped down from a range and we're going to select data range here then we go back to the accounts page we highlight this entire range and hit OK now you can see here that the drop down list has all of our accounts both revenues and expenses but one more change I would like to make to make this look a little bit better um I don't really like this gray area here so we can do that by scrolling down under data validation rules selecting Advanced options and then the display style is currently a chip we want it to be an era and I just think that looks way better the journal sheet as well as the accounts sheet are now both completely done unless you're adding accounts or transactions of course but they're all set up now you have everything you need to record your transactions and next is the cool part where we see how to use this data to do this we are going to create a new sheet and we are going to rename it net income this is where you track your total revenue total expenses and revenue minus expenses your total net income this sheet is just going to pull all of this data from the journal and total it in here to start creating your net income page we are first going to set up a revenue table so our title will be revenues 2023. and then under this we're going to have each month so starting with January then we will drag down all 12 months to December next to it is where we are going to display the totals so for now we'll just put the number zero for all of them and we're going to also format as accounting again as for making our revenues table look a little more presentable I'd first like to select all the cells and make them the font monster once again I'm then going to select our revenues heading and then merge it with the cell in C2 right next to it by merging cells then I'm going to Center I'm then going to also make this size 14 font to make it a little bigger and easier to see and I'm going to bold it as well for coloring I'm going to select the third lightest blue down as the fill color and make the text color white now I'm going to make the dollar value cells font size 12. and make the month cells font size 11. we can also Center and bold the month to make them stand out a little bit more and for column C we can resize it to make it 160. finally I'm going to put borders around all these cells by going back into borders and selecting all borders and now that we have this all settled we can start creating our actual sumifs function for our sumus function we want each of these months to return the revenue totals for the month in question so to do that we need the sumif function to know the start day of the month and the end day of the month you'll see in a second what I mean but back on the journal slide when we type in a date that is how we will know over here which month to categorize the transaction into so I'm quickly going to set up a little reference table with all the start dates and end dates for each month all I need to do off to the side here is Type in January for the month one slash one for the start date one slash Thirty One for the end date of course we don't like how these are formatted so we will highlight these two columns go into format number and date I'm also going to Center all of these and then if you just select these three cells and drag them all the way down they will autofill to the correct data now we are finally ready to input our sumifs function to do this we're going to click in our January revenue cell and start typing equals sumifs open arm parentheses and as you can see Google Sheets opens up a list of what the function should look like here the first part of our function is the sum range which we have to click back onto the journal sheet to collect we are going to select column C in its entirety as our sum range next we are going to need our first criteria range our first Criterion so our first criteria range will be column B but first we need to add a comma and then we can select column B remember that we are looking for January revenues so in column B we want to sum values that have the word Revenue inside it so again we'll select a comma to go to that part and then usually in this kind of function you would put in quotations the word Revenue what this function now does is it sums everything in column C if the corresponding cell in column B is the word Revenue but we want it to be only if it contains the word Revenue not if it equals Revenue exactly to do this we're going to use the asterisk which is a wild card character so by entering the asterisk here and here this now means that if Cell and column B just contains the word Revenue it will sum column C the only issue now is that this sums everything for the entire year regardless of the month so we want one more criteria range to determine the appropriate month so we will add another comma and then criteria range 2 is column A now we can go back to our net income sheet at a comma and criteria two in quotations we will write greater than or equal to by the greater than and equal to signs and our quotations write an and symbol and then select the cell January 1st we will then add a comma and move on to the final part that is saying that we sum everything less than or equal to January 31st so again we're going to go back to the journal page select column A add another comma head back to net income and then in quotations write less than or equal to end quotations and then write the and symbol again and select the end date January 31st then when we hit enter we find that our January revenue equals 1088.84 and if we go back to our Journal that adds up with 540 plus 540 plus 884 and since we've done this from one month we've now done it for every month if we go back to the net income tab I'm noticing that the start dates and end dates are off so we're going to fix that by doing February 1st and February 28th highlighting these and dragging them back down now we have the proper start dates and end dates so if we select this cell and drag it all the way down all this is going to do is change the start dates and end dates so let's drag it all the way down we see February fill in with 1080 which if we go back to check makes sense 540 plus 540 and none of the other months have filled in yet so we're perfect we'll also want to know the total for the year and we can do that with the sum function underneath the December total we can type in equals sum and then highlight this entire range here we will track our Revenue total for the entire year of 2023. and I'm quickly going to format this a little bit better 2023 we can Center that add borders bold and size 12. change the background color and change the text color and to prepare copying this over to the expenses table we're first going to add a couple more Columns of room and go back into our January function and notice that we want all of these columns to stay as the same letter when we copy this function over if we copy it over two columns every column up here will also move over two columns so to keep that from happening we need to put a dollar sign in front of all of these letters for Journal C Journal B journal A the J here journal A here and K here and that creates a proper reference so none of this will change we hit enter and then we're going to want to drag this down so that every month has the same dollar sign added and then I'm just going to highlight these two columns B and C copy move them over to E and F and paste the only thing we have to do now to change this to expense is go back into January and change this word Revenue to expense once we copy all of this down we now have the total revenues and total expenses and once again we're going to format expenses a little bit differently obviously we need the word expenses here as for the color we're going to make it red and for the color down here we are also going to make it red last but not least we're going to want to copy in a net income page so we will add a few more columns I'm also going to want to shrink these columns in between just so we can fit more on the page at once size 25 and then like before I'm going to copy columns E and F paste them to H and I and then the net income function here is real easy go to January delete this hit equals the revenue total minus the expense total and then when we copy all this down we can now see the net incomes for each month to format I'm just going to change expenses to net income and then I will also change the color from red to green and down here I will use another green like this and with that you now have a perfectly functioning Revenue expense and net income tracker based on all these transactions you could say that your spreadsheet is done here which it may be if that's what fits you best but I'm going to add one more part where you can track each revenue and expense by account so this totals all your revenues and all your expenses but what if you want to know all your dividend revenues for the year all your gas expenses to do this we're going to need to make one last new sheet let's duplicate our net income sheet by right clicking on it and selecting duplicate and we're going to rename it reports here's where we can run a report on any one of our expense or Revenue accounts let's highlight columns H and I and delete them since we don't need net income for this sheet now let's say we want to run a report on our dividend revenue for the year I'm going to go into this title heading here and rename it dividend Revenue then all I have to do is go into the original January function and change this word right here Revenue to The Heading cell instead to do this we'll delete the word revenue and instead click on this cell B2 I'm also going to want to use dollar signs in front of the B and the two to make sure that when we drag this function down this does not change hit enter and you can now see after we drag this down that it displays 884 for dividend Revenue in the month of January which when you check the journal turns out to be correct now let's go back to the sheet to finish it up by inserting a drop down list once again so you can do this a little easier a reminder from when we did this earlier you select data data validation then we're going to add our rule select drop down from a range then we can select data range go back to the accounts page and make sure that we select all of these accounts hit OK done and then go back to reports and you see you have the proper drop down menu here but again we're going to go back into Data validation rules and scroll down to Advanced options click on Advanced options and then change the style to Arrow hit done and that's it real quick let's do it with expenses we'll X out of this type in gas expense up here then we can go into the January function switch out this expense for this cell E2 put in our dollar signs in front of the e in and from the two hit enter and then drag all the way down and then last we'll insert a drop down menu here as well click on this data data validation and all we have to do here is click back on this same rule we made before and then in the apply to range box we just add a comma and then E2 E2 to add this as another spot where we have a drop down list if you want you could separate revenues in their own drop down list and expenses in their own as well which you are now capable of and one last note if you want to see multiple revenue or expense Accounts at a time you can simply copy and paste this over and add some more space for it and with that our spreadsheet is done all four sheets are complete and you now have everything you need to complete an automated income and expenses tracker I worked very hard on this video so I would appreciate a like maybe a subscribe and I'll see you in the next one thanks for watching