Effortless Management with Bills Template Google Sheets for NPOs

Bills template google sheets for NPOs

hey what is going on you guys today i'm going to be showing you how to make an interactive monthly budget tracker in google sheets but before i show you how to make this tracker i'm going to quickly show you how it works so we can see we have two tabs here we have a transactions tab and a monthly total tab all of our transactions are going to roll into our monthly total tab where we can see our spending by category the total amount spent and our amount budgeted and then we can see the difference between the two which is all going to roll into the amount over or under budget right here and then we can see our total income and our total expenses and the amount left we have to invest at the end of the month and you can see here we just have a visual where we can see where most of our spending is going into so housing is taking up about 48 in this example utilities 19.4 percent and some other metrics as well so let's say that on december 31st i went to the movies with my family i would come here and type out the date and i will go here and i will select entertainment and then i can just type out movies right here and let's say that i spent about 20 dollars at the movie when i put this in you're gonna see our entertainment tab here change so i'll hit enter here and we can see our entertainment total amount spent has gone up and we are over budget for the month by 22 dollars our total expense has gone up and our amount left to invest has gone up as well and our chart has automatically updated so if you'd like to be able to download the spreadsheet then you can head over to my patreon page at the link in description so with that being said let's go ahead and dive into the tutorial okay so we are ready to begin our budget and the first thing we'll need to do is we need to come up here to the top and title this spreadsheet and i'm just going to go ahead and title it monthly budget tracker and hit enter and the first thing we'll need to build out is our transactions tab so if we come right here we are going to highlight rows a through d and two and three and we're going to want to merge these cells here and if we come here we can give it an outer border as well and let's go ahead insert our text so we want to make this the transactions tab so we'll type out transactions and let's go ahead and increase this font size to about 18. okay and if we click on it again we can come right here and we can center this text we're going to want most of the text on this spreadsheet to be centered it just will make it look nicer if we come below here we have four categories under our transactions we need a date category we're going to need a category called category and then we need a description and then we need to know the amount and let's go ahead and highlight these as well we'll come up to borders and we're going to select all borders on this one and so let's go ahead and make our transactions tab go down about 50 rows so what we're going to do is we'll highlight starting on our transactions right here and we'll scroll down to about 50 and we will give it outer borders and if you feel like you're going to have more transactions than 50 in a month then i suggest going a little bit farther maybe 60 or 70. and so let's scroll back up so we can see we start to have our transactions coming together now let's come over to the right and we can start building out our monthly total so we'll start right here in column g and we will go ahead and highlight rows g through j and two through three again and we'll do the same thing we just did with transactions we'll merge this cell and we will give it an outer border and let's go ahead and insert our text and type out monthly total and we will increase our font size and we can go ahead and center this text as well and we are going to want to put a few categories below this as well so we need to know what category we spent in we need to know the total amount we spent in that category we need to know how much we budgeted for that category and then we need to know the difference between the two so i'm just going to title this one remaining and we can see here amount budget is a little bit large for this cell so let's go ahead and increase the cell size on this one and we can come here select this row and we can center the text for all of these as well and let's go ahead and give this all borders as well so now we want to come back to our category tab and we want to list out all the categories that we will be spending in so i'm just going to make some example categories so we're going to have an income category where we will track all of the income we make over the month we'll have a housing category we will have a groceries not combined groceries and gas so i'll have groceries and gas listed here we'll have an entertainment tab we can have a tab for eating out as well and we will probably need a medical tab we'll say utilities and we'll have a miscellaneous tab as well and if you think there's any other categories that you'll be spending in then you can go ahead and add them under your category but i'm just going to put anything that's out of the ordinary under my miscellaneous tab so let's go ahead and come here and we will go ahead and give this borders as well and if we come under here we need something that will total all of our spending and our income so if we come down below right here let's go ahead and merge this cell and give it an outer border and i want this to be the amount over or under budget and we'll come here and we will make an area where we can have that listed and now we need to do what's called a data validation on our category in our transactions area so what we're going to do is we're going to come over here and click right here on the first row under our category tab in transactions and we are going to go to data and if we come down here near the end we can see where it says data validation we are going to click on this and you're going to have this screen come up what i'm going to do is i'm going to go ahead and move this down and we're going to click right here where it says to enter a range or formula and what we're going to do is we're going to come here and highlight everything in our category under our monthly total so i'm just going to select these cells here and then i'm going to click ok and when i come here and click save we can see we now have a little arrow standing under our first row in our category tab if we click on this we can see that all of our categories in our monthly total are listed now under our transactions tab so let's go ahead and grab this right here and drag it all the way down to the bottom of our transactions and we can see we now have our arrow all the way down so let's go ahead and start listing some sample transactions so let's say on january 1st of 2021 i received a paycheck so it'll go under our income category and for description maybe i'll just say paycheck and let's say it was for three thousand two hundred dollars so that is what an example of our transactions will look like what i'm gonna do is i'm gonna go ahead and jump forward and list some more sample transactions for the sake of this tutorial okay so i have listed some sample transactions here and now we're ready to start listing our totals for each category under our monthly total and to do this we are going to use one formula that's just a little bit more advanced and it's called the sum if formula it's a very useful formula and i'm going to show you how it works right now so what we're going to do is we're going to come to this cell right here and put in an equal sign and type in sumif and then do an open parenthesis and we want to sum anything that is under the income category so what we're going to do is we're going to come over here to our transactions and highlight this entire column and we're gonna do a comma and we want anything that is listed as income in this category column to be added together so we're gonna come over here and select income and we're gonna do another column and then we're gonna highlight our entire amount column and we'll close this parentheses and hit enter so we can see now that our total amount of income for the month was three thousand and four hundred dollars and the great thing about this formula is this is an interactive formula so if i take this here and drag it all the way down we now have the totals for each category so now that we have our monthly totals let's go ahead and list our amount budgeted and this will be different for all of us so really just put whatever you think you need to budget for each of these categories so for income let's say i budgeted 3500 housing will say 8.50 groceries and gas maybe i budgeted 200 entertainment let's say i budgeted about 65. eating out we'll say i budgeted 60 medical maybe 85 utilities 100 and miscellaneous 50. so now we have our amount budgeted and we want to know the amount we have left over and this will tell us whether we are under or over budget so for our income all we're going to do is equals and take our total minus our amount budgeted and we can see here that i was a little under budget for income i needed to make about a hundred dollars more in order to meet my amount budgeted for income for the rest of these these are expense items so what we're going to do is we're going to do equals and we're going to take the amount budgeted and then subtract that by our total and hit enter and we can now take this and drag it down through our other expenses and we can see the difference and what items we are over or under budget on now let's come down here and list the amount we are over or under budget so all we're going to do is do equals and we are going to sum all the values above us in this column and we'll close this parenthesis and hit enter and we can see for the month we are a hundred and eleven dollars and 25 cents over budget so now let's come down here and i want to have an area where i list my total income and my total expenses so i'll type out total income right here and hit enter and below this is where we can list our total income so for this i'm going to do is equal and i'm going to come up here and make this equal to this cell right here and hit enter so let's go ahead and highlight this and give these borders as well and i'm going to come over here and do the exact same thing except for expenses now so we'll type out expenses and we'll come here and merge these cells as well and for our expenses what we're going to do is we're going to do equals and then we're going to highlight all of our expenses right here excuse me we need to have the sum formula first and then highlight our expenses and we will close that parentheses and hit enter now we can see what our total expenses were for the month so let's go ahead and highlight this and give this outer borders as well so one of the last things that i would want to see is the amount i have left over to invest and all that would be is our total income minus our expenses so let's come below this and we will make an area for that so let's go ahead and merge these cells here and i'll just title it amount left to invest and we'll come right here below it and we'll merge these cells and we'll make a formula we'll just do our total income minus our expenses and hit enter and we can see we have almost two 000 left to invest for the month so let's highlight this and give this borders as well so now i have all the tracking that i need but i want to add a visualization to go along with this and i want to track all of my expenses so what i'm going to do is i'm going to highlight all of my expense categories along with their total expense if we come up here to insert you'll see the option to click on chart right here so let's click on chart and we can see it comes up as a bar chart some of you may want to use a bar chart but i actually want to use a pie chart for this scenario so i'm going to click on pie chart right here and we can quickly see at a glance what expenses are taking up the most of our income and what percent it's taking up as well so we can see housing is by far our biggest expense and then we have our groceries and gas and our other expenses as well so let's go ahead and resize this chart a little bit and we'll bring it down here and kind of line it up with our other categories and the next thing i want to do is i'm going to go ahead and highlight this entire page and let's go ahead and center the text on all of this and that's going to make it look a lot cleaner and now let's go ahead and remove these borders right here so we'll have to come up here and fix this one [Music] so now we're very close to finishing our budget there's just a couple more things that i want to add to the spreadsheet and one of those things is i want to add conditional formatting to our remaining column here so what we're going to do is we're going to highlight these cells here and we're going to go up to format and if we come down here near the bottom you'll see conditional formatting we're going to click on this column and what we want to do is we want anything that is greater than 0 to show up as green text so we can see it's currently highlighting the whole cell we want to switch this to the text showing up green so we'll make the text green and we will make our cell white and let's go ahead and make this a color that'll stick out just a little bit more okay and now let's add another rule and we want to say anything that is less than zero we want to show up red and we want the text to show up red so we'll come here and click on this and click on red and click on done so now we can quickly see at a glance based off the colors which of our categories we've overspent or we have under spent for the month so now i'm just going to add some finishing touches to this spreadsheet so for example maybe i want to center this text vertically one of the things we're going to want to do is we want to make all of our dollars show up is a dollar amount on our sheet so we can highlight this and click on the dollar sign up here maybe something that we want to do is bold this text for our headers and then of course we want to go ahead and color everything in as well so i'm going to go ahead and jump forward and color everything in so this is what our finished monthly budget tracker looks like you can see i've colored everything in bolded some text and our chart is colored in as well so let's go ahead and place an example transaction to make sure everything is working properly so let's say that on december 31st or excuse me january 31st i ate out at chick-fil-a so we can see eating out is currently at 69 and we're nine dollars over budget so let's come here and place a transaction and make sure it is working correctly so we'll come here to our category and we will select eating out in our description we will put chick-fil-a and let's say chick-fil-a cost us ten dollars so when i hit enter we can see our total spent changed our amount remaining slash over budget changed our total expenses changed amount left to invest change and our chart changed ingly as well so our budget is officially complete everything seems to be working properly and let's say that now that our budget for january is complete we now want to start one for february all we would have to do is come down here to this tab if we double click we see we can duplicate this tab so let's click on duplicate and all we need to do now is we'll just highlight these here and click delete and now we have a brand new budget ready for february and what we can do is we can come down here and we can rename this and make this a february budget and we can do the same for our transactions tab here we'll just rename this to january and you can see that can be done for each month of the year i hope this tutorial was a huge help to you guys if you want to have the ability to download this spreadsheet then you can head to my patreon page at the link in the description so with that being said thank you guys so much for watching the video and please don't forget to like and subscribe to the channel