Create a Template Invoice Google DOC for Financial Services Effortlessly

Template invoice google doc for Financial Services

today I'm showing you guys how to track your income and expenses with this automated sheet here's how it works first on a blank spreadsheet we're going to create our accounts tab this will list off all of our different expense and revenue accounts as an example I'll create the accounts of work Revenue dividend revenue and other Revenue we'll also need some expense categories such as gas groceries and whatever else you might need throughout this spreadsheet our Revenue accounts will be colored blue and our expense accounts will be colored red just so we can visually understand it a little bit better also as we go through this sheet I'm just going to make things look a little bit nicer which includes changing the font the spacing and other things such as the borders later if if you want to add a new account all you have to do is go into this accounts list and insert a row but for now we're just going to leave it with these eight accounts next we're going to make our Journal Tab and this is where we're going to enter in all of our individual transactions you're going to want columns for the date account amount and any notes for the given transaction to help create this portion I'm going to enter in a sample transaction where on January 1st of 2024 we had a gas expense of $50 and for notes we're just going to write that it was gas for some simple formatting I'm going to start with the date I'm going to select column A and Center this whole column and then additionally I'm going to select format number and date this way if I entered another transaction on let's say March 15th I can then click off of the cell and it will know March 15 15 2024 but we'll delete that for now I also want to format the amount section by selecting column C going into this more format section here and then Select Accounting next and quite crucially I want to format the account section so that every time we don't have to type in the account name but we can select a drop-down menu from our list of accounts here so what I'm going to do is Select gas expense and hit data then data validation and then I can add a rule and where it says criteria we're going to select drop down from a range and then if we select this little button where you can select data range here we're going to click on it go back to our accounts tab here and then just highlight all of our accounts and hit okay so what this now means is we can go back to journal and you see this little drop-down menu we can click on it and then select any one of the accounts here also what you could do we'll delete gas expense you could just start typing in the account and you'll see it will pop up as well now in terms of formatting we're going to scroll down to the right here and select Advanced options and then what I want to do is not a chip because the chip format kind of cuts off the whole text and arrow plane text we want arrow and then we can hit done X out and we're also probably going to want to make this column a little bit wider and now that I'm looking at it we want to resize all of these columns so what we're going to do is Select column B rightclick and then resize the column to size 200 that should fit most uh we're going to select column C resize it to 150 and notes we're going to resize to 600 in case there's any special notes we need to make then for some little formatting I'm going to change the font again I'm going to take all the headers and Center them to the left I'll merge this top these top four cells here merge them together and then you can write something like income and expenses 2024 you could title that whatever you'd like um and then additionally we can bold all of that we can create borders around all of this um in terms of colors I think giving the headers some color makes them look a little bit better we'll make this one a dark blue this one an even darker blue and to make those words pop out a little better we can color them as white and this part feel free to play around and make whatever color scheme you'd like and then once you want to add a new transaction all you have to do is insert a row above and then you can keep doing that sometimes what I I like to do is just have a bunch of open rows and then say I have a transaction on January 2nd I'll just put it here and work from the bottom up that way your most recent transactions will always be at the top and there's one final thing I want to do on this tab remember earlier I mentioned that our Revenue accounts will be blue and expenses will be red so I want to format it so that the expenses appear red here and revenue appears blue how I do that is selecting column B and then I'm going to select format and conditional formatting so format sells if let's say text contains so if it contains the word revenue and then we can change the color to that blue we wanted we can then add another rule format cells if text contains expense and then change this to that red color then we can hit done on X out and then if you see let's go back to making a new transaction January 15th and we want work Revenue so salary we can write that in the notes it's our salary let's say it's $500 now all of this is going to flow through to our net income Tab and this is the third and final tab that we are creating today the goal of this tab is to pull all of the transactions from the journal so that we can see everything grouped together by type of transaction and by month as well so first things first we're going to start in cell B6 I'm picking that just so I have more room to fit everything for later and all we're going to do is put our 12 months going down you really just have to type in the first two and then drag all the way down to looks like the 17th row and then once again we want headers in these cells that have all of our different accounts so what I'm going to do is Select that first cell and type in data data validation add a rule again and we're going to do drop down from a range select data range once again go back to our accounts and select all of our accounts hit okay again we're going to come back to next that income then scroll down Advanced options and change it to the arrow display style once we select done now we can start entering all of our accounts and what I'm going to do is take our drop down menu here copy it and paste it over the reason why there are five currently is because we have five expense accounts I'm going to start with the expense and then do the revenue off to the side so we have our gas expense groceries expense clothing expense utilities expense and other expense these don't fit perfectly so what I'm going to do for all of them is make them three cells high so I'm just going to merge these three cells together and then what I'm also going to do is you see this vertical alignment button I'm going to Center into the middle and then for text wrapping I'm going to wrap around so that it now fits in the whole cell I'm actually just going to drag this gas expense all the way across now and then change these back to the proper account it's just a little easier this way and now for some simple formatting I'm going to merge all these cells and just title it expenses I can even Center that I'm going to highlight the whole sheet and change the font once again off to the side here I'm going to Total the expenses so I'm going to create a header that says that and then I can even merge these four cells together and like we did before vertically align it to the center and wrap the text as well another thing I'm going to do real quick is highlight the whole sheet again and Center all the text I'll also make a total section here and create a cell here merging all these cells that just says that it's the year 2024 again I'm going to Center this text okay and now I'm going to highlight this whole range give it borders and then I'm going to start changing some things Bolding here making the expense headers the red color we wanted and making the subtotals a slightly lighter red I generally use this kind of color scheme and then finally I'm going to make this 2024 text a little bit bigger fill in this cell in Gray make this total bold and fill it in with a lighter gray and then column A I'm going to resize to 25 just to give us a little bit more room okay and now a little quicker I'm going to kind of speed through this but I also want to make a little table off to the right here for uh Revenue accounts so I'm just going to copy these drop downs over I believe we had work Revenue dividend revenue and other Revenue I'll copy over the total as well rename this toal total revenue and create a header here that says revenue for formatting again we can make this bold give it a border and change the color of all this to that blue color we've been using then I'm just going to use borders once again and for our totals here use a slightly lighter blue so it's formatted similarly to the expense table and there's one final table I wanted to add so we're going to add another size 25 column in between and this will be our net income table so we're just going to take these eight cells merge them together and write net income we can make this bold wrap it and Center it and I think we're also probably going to want to make this a little bit bigger in size INF foret income I guess I'll use this green color um and then down the side we'll use a lighter green color missed one spot use this lighter green color create our border all the way around and for this all we're going to do is copy over the 12 months into this First Column can copy over total as well and the simple formula we're going to use for net income is net income equals total revenue minus total expenses so sell M6 minus H6 hit enter we can drag that all the way down and we're going to want to format this going to more formats and then accounting all these Revenue cells will also be the accounting format and all these expense cells are going to be the accounting format as well now as far as the totals are concerned total expenses is going to equal the sum so we're going to type in equals sum open our parentheses and highlight all of the January expenses and hit enter once we do that we can drag this formula all the way down and we can do the same thing on the bottom here so we can type in equals sum open our parenthesis and highlight all of the gas expenses for every month and hit enter we can then drag this all the way across and we're going to do the same thing for Revenue use our sum formula drag across hit enter copy this all the way down and then do the same down here equals sum parentheses highlight everything hit enter and then drag this formula across to carry over so the final part here is we're going to automate so that if we enter a January gas expense in our Journal like we did it will pull it from here so that we see a $50 figure in this cell the formula we're using to create that is going to need to distinguish each month from each other so to do that I'm just going to make a little month chart off to the side we can do it in cells RS andt or in columns R S&T we're going to need three columns for it and we can write some little headers here month start date and end date and all you have to do is again enter in the 12 months of the year so I typed in the first two and I can drag this down get the rest of them and then the start date and end date columns we're going to need to highlight in format again format number date format as a date and just just write the first date of each month or the first day of each month in the start date column we can drag this down and then the end date of each month in the end date column for 2024 you're going to need to write February 29th because there were 29 days as this was a leap year Google Sheets knows the final day of each month and they can already tell what we're doing here so we just have to highlight these first two cells and drag all the way down and it accurately displays the final date of each month okay and now we have everything we need to create our final formula so what I'm going to do is start by going into cell C6 which is the January gas expense cell and we're going to type in equals sum ifs and open our parentheses if you hit the little question mark off to the side here it tells you which part of the formula you're in and when you're creating a longer formula I recommend opening this up every single time so while you're in this parenthesis here you have to make sure you're in here for this to work you're going to go back to the journal Tab and all we're going to do is click on this C header so it's going to highlight all of column C on this tab we're then going to do a comma and do the same thing but with column B one one more comma and then we go back to the net income Tab and select on gas expense C3 so it already says $50 that's the preview of the formula here because this formula what it's doing if I go back to the journal we are summing column C here so we are summing all the amounts in here given that column b equals the same thing as C3 so so to go through that again we are summing all the amounts here if the account equals the same account we've selected in cell C3 what we also want to do is make sure that the date is appropriate so we're going to add another comma and keep going with this formula so I'm going to go back to the journal page and select column A now the date I'm going to go back to the net income tab add another comma and then in quotations I'm going to type in greater than or equal to just by typing in the greater than and then the equal symbol we'll end our quotations and then what I need to type in is the and symbol and then I'm going to scroll over to the right here and select cell S6 this will make sense in a second just stick with me for now I'm then going to add another comma go back to the journal and select column A once more go back to net income comma and then in quot quotations less than or equal to end quotations type in the and symbol again and now select the end date for January T6 we can now close our parentheses and that's our formula so if I scroll over here and click back into this formula what I did there is if I highlight this last section of the formula I am summing if the go back to the journal section if this column a is greater than or equal to January 1st and less than or equal to January 31st so basically what it's saying is any date included from January 1st to January 31st will be summed in this January row okay I'm going to make a couple changes to the formula that are key so that we can easily copy all of them over without having to type in the whole formula a million times I'm just going to need to place some dollar signs in this formula so that the references don't get all screwed up so going into this formula the first dollar sign is going to be before the letter c here and then another one before the next letter C we're also going to place dollar signs before each letter B here one1 dollar sign in between the C and the three here dollar signs before each letter A here dollar sign before s here more dollar signs before the a again and then a dollar sign before the letter T here and hit enter and now we have our final formula so let's select cell C6 we're going to copy it and all we have to do is paste in this entire range here and then we can also paste it in this entire range of Revenue here so let me now select this copy paste this whole Revenue range you can see that $500 popped up in the cell j6 for January work Revenue because this was already displayed in our journal and for an example today is July 9th so I could type in 79 let's say we bought a new shirt today so we'll do clothing expense and we'll type in $30 new shirt for notes we go back to net income and you see that July clothing expense of $30 is displayed here and if you go to net income you can see for January we have a net income of 500 because we have 5 or 450 rather because we have 500 in Revenue minus 50 in expenses then we have a negative $30 net income in July because we only displayed one transaction and that sums to our total net income for the year and that's it for our sheet really now it's automated all you have to do is again enter in your accounts and then anytime you have a trans trans action enter in here and the rest is automated for you as a thank you for watching to the end I'll also drop the template so you guys can download that and make your own copy if that's a little easier than creating the whole thing if you don't want to miss new useful spreadsheets like this then subscribe and hit the Bell so you can stay updated for all the new content thanks for watching hope you guys enjoyed