Simplify Your Billing Process with Bills Template Google Sheets for Technical Support

Bills template google sheets for Technical Support

hello and welcome to this video today I'm going to give you a tour of my new Google Sheets build tracker template this is the second version that I create it's the extended version this is a very detailed walkthrough if you prefer to watch a shorter video a quick tour you can find the link to that in the description so let's get into it you're going to start off with a blank template and the very first thing you have to do is you have to come into file settings and select a country from this dropdown and then a Time Zone from this dropdown and this is so that dates are updated properly and then you're going to click on save settings so once you do that you're going to come into the customize here Tab and you're going to follow these steps so Step One is optional and it's just so if you're working on a different language you're going to have to set your translation here for each of these labels if you are working on a different language you have to do that before you move on to step two so in step two you're going to select a month from the dropdown and you're going to set a year in sell and this is going to be the first month of your template and the rest of the months are going to be updated ingly so if you enter January and I come in here my first month is January if I enter March and I come in here my first month is March and it goes all the way over to February 2024 and then you're going to select if you want your calendars to start on a Sunday or on a Monday and then you have these two lists so throughout the template you're going to be able to select a payment method for your transactions and you're going to be able to assign categories to your bills I suggest you fill just a few out so you get an idea of how it works and then you can come back later and add more and then you have a few more translations over here if you want to change it to a different language you do that here if you don't like how One label is named and you want to change that for something else you just come into this table and change the way this is named in the your translation column and it's going to get updated across the entire template so you never edit any labels directly in the template you edit them here so once you did that and you customize your template you're going to move on to the monthly transactions and this tab is divided into two sections in the fixed section you're going to enter everything that you pay the same amount every single month and then in the variable section you're going to enter all of your bills that have a different amount do every single month so you're going to be able to set a budget but that's not necessary exactly how much you're going to pay so you're going to have a different amount due each month so you have three things that you're going to enter to get started on both sections so you're going to enter a default due day just the day then you're going to enter the bill name and then you're going to enter a default budget so I'm going to do rent and I pay that on the 15 and as you can see as soon as I entered that 15 this due date got updated on every single month so every single month I get a specific date for the 15th and then if I add a budget so let's do 1500 then you get a budget every single month and you get that same amount due every single month because this is fixed I'm always going to be paying this every single month I'm actually going to bring this back to January just so you can see the whole 12 months from January to December and since January already passed this is going to be highlighted in this blue color that means that this payment is overdue so once you check the box it gets marked as paid and you get an amount paid up here and then February is also overdue today is February 28 so that's also overdue and if I check this box then it gets marked as paade now there's other options that you can add here but first I'm going to jump into the variable section just so you can see how that's different so for variable I'm going to do my water bill so that comes on the third and that's water and that can be $30 so once I did that I got a due date every single month once again and once again this is highlighting blue because it's overdue so what you have to do is you have to enter an actual amount due so I have a budget right here that's the 30 the default budget that I entered for every single month but when the bill comes I realize that it's actually 34 so I can enter that 34 but it won't be marked as paid until I double click on the date paid and select a date and once I did that then it gets added up here so if I move on to February then this came for maybe 29 again this is not moving until I add a date paid so that's how these two are different since this is fixed all you have to do to Market as paid is check the box and to mark this as paid you have to set a date paid so you can enter an amount due once you realize what that amount is and it won't be considered as paid until you double click here and select a date from the calendar now there's a couple other things that you can do so you can assign a category to this so let's do housing for rent and then for water I'm going to assign utilities and this list was created using the elements that I entered right here and then you can also set a default payment method so this is a payment method column and if I select a default payment method then it's going to get assigned every single month so I can do the same down here I can do Fred card and it's going to get assigned every single month and then there's one extra feature that I added so let's say you're going to move out in August so right now my bill payments are going all the way to December but if I'm moving out in August then I can select end date I can set it as August 14 and since my due day my default due day is a 15 if if I come in here I'm going to see that August has an X and that means that there's no payment expected here there's no budget there's no due date so let's let's not have this as paid I can also choose to start on February for example maybe on February 14 and then there's no payment expected here and then there is a payment expected here already paid it and you get that every single month so you can choose your start date and end date and your budget will only be assigned to the range contained between your start date and your end date and you can set just one of them and it will run through the end of the 12 months so it's starting on February and since I didn't set an end date it's running all the way to December this also has the start date and end date features so let's say it ends in May so I come in here and I ended it in May 8th so May 3rd will still be expected but then June July August that shows an X it means it was cancelled and it's no longer expected so this is pretty much all you have to input in here so white cells are expecting an input these white cells are expecting an input you check this box you add an amount due you enter a date paid by double clicking and that's pretty much all that is expected and then if you scroll all the way to the right you get this summary so you get a summary for your total total budget for each bill a subtotal for each section down here and then the total budget for both fixed and variable sections total paid for both fixed and variable sections and then you get a summary of your payments for every month and in here you can also choose to add bills that you pay twice a month so let's say you have a debt payment and you pay that twice a month so let's do that in the variable section so you have your student loan and then you have your student loan again so one of these you pay on the first and then you pay the second one on the 15th and each one is of $250 and then the amount is not always exactly the same so you set a date paid and we do the same here so maybe for some reason it was more or less you mark that as paid and this is how you can handle more than one payment a month you can just set the exact same bill name and set different due days and then the template is going to be expecting those two payments and then the things that you pay annually every other month every six months quarterly you're going to enter those in the annual transaction sa and the way this works is let's say you have a gym membership and then you have maybe a car loan and then something else and then you get a default due they so let's do 23rd the car loan let's do the 15 and then something else let's do the fifth and then let's enter a budget let's say I pay my gym membership in January so I'm going to check this box and as you can see that payment is going to be expected in January right now February is empty but if I checked this box for February as well then a payment is also going to be expected here and what's Happening Here is a due date is getting created using the default due date that I entered a budget is getting assigned using my default budget and then I'm expecting an actual amount do so this works similar to the way the variable section works in the monthly tab so it's expecting an amount due so let's add 30 extra dollars and it's not going to be marked as paid until you double click and set a date paid once you do that it gets marked as paid and you can also manually assign a payment method and you can also choose to assign a category to each of these bills in any month that you activated the default due date and the budget is going to get assigned and again if it's overdue today February 28th if it's overdue it's going to highlight in this blue color so let's pay those bills then if I come in here this is also overdue so I could also pay this here double click and then all those are paid and then nothing else is expected so if I was expecting all of these bills you can check this box then I'm going to be seeing those bills get activated on this month and as you can see whenever a payment is expected this is going to highlight in this jellow color for both the monthly transactions and the annual transactions tab you're going to get a lot of default information added for you based on what you enter here and you can of course override these formulas these default formulas and enter manual information so the way you do that is let's say we want to change these three rows so for some reason this is going to be different so the first thing you can do is you can select either one cell or multiple cells and you can delete them so once I did that the background color changed so when you get this dark gray cell it means you deleted the formula but you did not add any information either so that's something to bring your attention to so if I wanted to add a different due date I can just double click and select a different due date for the calendar and in this case it's highlighting in yellow because this due day doesn't belong to this section so if I were to add it in May the background turns white so if I added another one in May for this then the background is white so this has a formula and the background is light gray this does not have a formula it's a manual entry and the background is white now I can continue filling information here I can also change my budget so that can be maybe six 78 changed my budget and then I can also change my default amount do so originally this column is using these Valu so if I were to change this whenever there's a formula here what I change here updates my amount do as well because this is a fixed one and it's supposed to be the same each month so it's based on the budget but if I delete the formula I can also override this manually so that can be six that can be seven that can be eight so as you can see these are the things that I changed manually and the background is no longer light gray the same thing happens here can overwrite this and I can select different values and the background changed to White now if you want to make it really obvious whenever you have a manual entry where a formula used to be you can select this checkbox and everything that you changed manually is going to highlight in this color now if you did this by accident if you messed it up you want it back all you have to do to bring the formula back is click on any other row that belongs to that same section and column so in this case it would be any of this that is still automated you should not touch this ones just just it has to belong to the same column and the same section you copy and you paste and you can also select multiple at the same time and paste and then the background turns light gray again that means your formula is back and you can do the same thing here so I'm copying cells that are still automated in that same column and section and I'm pasting them and this is how I am bringing the automation back and it works the same way down here so you can change your due date your budget and your default payment method you can delete this and then you can change it manually and if you don't choose to highlight your manually edited formulas you can just uncheck this box and manual ones turn white these are empty and have no formula so they are gray so you can also find a guide to all these coloring in your instructions pdf so in case you just want to plan at that very quickly so same thing if you want to bring it back you copy one that is still automated from the same column in section and then you paste it on one cell or you paste it on multiple cells at the same time and remember for due days if you delete them and want to edit them manually you can double click and select a date from the calendar and then the annual transaction section works exactly the same way so you have this gray cells right here that you can choose to override so maybe I'm going to take a bit longer on this one I highlight it in yellow because it doesn't belong in this section but it's fine if you want to keep it in there it's just to bring your attention to it and then I can delete this value and add a different one and I can also choose to highlight my manual edits in this purple color just so you know when you Chang it manually and it's not going to be tied to the default values anymore so so if I change the budget here it's not going to change here because it's no longer tied to a formula but if I were to select this to bring the formula back then it's tied again and again to bring it back you copy a cell that is still automated and you paste cell that is still automated and you paste make sure you are copying and pasting when you do this and not cutting and pasting because you're going to mess things up if you do that so these are the only things that you're going to edit and then you have three automated sections so you have your calendar and the way it works is you're going to come in here you're going to select a month from the dropdown and that's all you have to do everything else is automated and you can also choose to sort your table by any of these values so you could sort it by Bill and then your table gets updated by Bill you can sort it by due date and you can also choose to sort in ascending or descending order and then you're going to see all of your bills in this calendar and you can choose to only show bills that are pending so if you uncheck these box you're not going to see the bills that are already ping paid in the calendar so this is fully paid and you're not going to see anything so let's move to February and then maybe let's unpay something so let's remove the date paid and if I come into the calendar I'm missing the gym membership and it's going to highlight in blue because it's overdue so things are going to be overdue if they are due today or before they're going to be highlighted in this blue color and you're going to see it highlighted in here as well and if I choose to show the paid ones as well then you get all the ones that were paid this is overdue and today is highlighted in Orange and if you want your pending ones to show at the top you can sort by status in ascending order and the pending ones are going to be shown at the top or in descending order and they're going to be shown at the bottom and then if you scroll to the right you're going to see two summaries one is by category and the other one is by payment method in case you're interested in that information and you can choose to keep just One calendar tab or you can choose to duplicate the tab I just right clicked and selected duplicate and you can name that something different so you can do February and that can remain as February and then this one I could rename it to January and then I can select January and I have a calendar for January and a calendar for February that are completely independent from each other so you can choose to do that and then you have your 12 month summary so this is completely read only you don't have to edit anything if you see a drop down you can play around with it so you can order your tables using those drop downs or you can highlight a certain row using those drop downs as well but that's all you need to do and that's optional so you get a 12- Monon summary a budget and amount paid and difference for each month you get a summary for each section you get a summary for categories so you get a small table showing the budget get the amount paid and the difference for each category and then you get a larger table that shows a payment summary for each month and each category and you can choose to sort these Tables by any of these options just know that whenever something is not assigned it's always going to be showing at the top so this not assigned option it's not going to move these are the ones that are going to move so if I do it in ascending order then these options below those are the ones that are moving and this table is moving as well so if I did it by amount pay in descending order so I can get my highest payment at the top then everything changes except for the not assigned ones then you get a payment summary so if you wanted to highlight for example utilities you can select it from the drop down and then the utilities row is going to get highlighted just so you have better visibility and then you can come into the payment method section and it works pretty much the same way you get your drop downs and you get your highlight and again the not assigned one stays at the top always and then you get a Bild summary so as you might recall we did student loan twice so if you come into the monthly transactions we have two rows for student loans and in the end the budget for both of those is $6,000 so if I come into my 12 month summary you're going to see student loan budget $6,000 all together in one row and I have already paid paid $1,031 so if I come in here and I select these two you're going to see down here $1,031 which is the amount that you see here then you also get this table showing the payments for each month and you can choose to highlight a specific one so you can highlight student loan for example and if you come back you're going to see you pay 51 and 530 January and February so if I come to the student loan 51 in January and 5:30 in February so that's how that works and then finally you have an all transactions stab and this one is pretty much just gathering information from the entire template and just dividing those transactions into two Separate Tables the pending table and the paid table so if you want to sort it by due date in ascending order these are all of my upcoming payments I have this one for February that's actually overdue and then my next payment payment is on March the 1st March 15 and then these are all my payments so you can sort them by amount paid by amount due by date paid and then the section for the current month is going to get highlighted in Orange so this is just for your own reference you don't have to enter anything here and if you are interested in quickly jumping into the corresponding section so this payment is expected in the section February of my annual tabs so I can click for or hover over this link and then I click on this blue one and I'm going to jump into the corresponding section and then I can enter a date paid and then if I go back into those transactions that one is no longer there because it was moved over here now important to note if you manually change a due date for example let's say this is due March but for some reason I want to just get ahead of it pay it in January you can delete this automated value double click and I'm going to select January 7 so this is actually going to be due January 17th and if I come into January you're going to see that payment appear here and then you're going to see that it actually is found in the March section so that's something that you can do if you want to override manually for some reason and assign it to a different month it is going to be shown in the calendar in its proper month the section is going to be highlighted just for your own reference that doesn't mean that's wrong if you choose to do that it's totally fine and then then you can hover over here and click on this link and you can jump into that section so that's it for this video I hope you liked it if you have any questions please feel free to contact me and I will be happy to help you and if you could give this video a thumbs up and subscribe I would be really grateful thank you so much for watching