Collaborate on Payment Template Excel for Finance with Ease Using airSlate SignNow

Payment template excel for Finance

welcome to this tutorial on how to create the ultimate personal budget in Excel a personal budget is one of the most fundamental and Powerful tools for actively taking control over your personal finances as it helps you to understand and to proactively plan where your money goes while many people already use Excel to build simply expense trackers for themselves I want to bring this to an entirely new level and show you how to build a full-blown personal budget management tool in Excel that lets you proactively plan your income allocation to custom expense and savings categories for every single month then lets you track and assign your actual money flows to these defined categories and based on that automatically generates a well structured and interactive dashboard analysis with a detailed breakdown of your budget performance for each individual month and year let me quickly state that nothing said in this video is financial advice I'm not a financial adviser and this tutorial is solely about the creation of a powerful personal finance tool in Excel with that being said let's take a deeper look at the final Excel template that we're going to build today the budget planning worksheet is where the journey starts it's designed for creating a so-called zerob based budget plan which essentially means that every dollar that comes in has to be allocated either to your expenses or to your savings so that the planned income minus the planned expenses and savings will equal zero with the the template being fully empty at the beginning the first thing to start with is defining custom categories in the income expenses and saving section to add a new custom category in any of these sections you can simply unhide these prepared empty rows Define the category and it automatically gets added to this table and will now be considered in all further calculations and worksheets every additional Dollar in income that you plan for a month will increase the amount to be allocated in that same month just like in the income section you can add as many custom expense categories as you need by using those prepared empty rows the expenses section should cover both your needs like housing utilities groceries and also something like minimum loan payments and your wants like entertainment fun vacation donations and many more every dollar allocated to an expenses category will decrease the amount left to be allocated so that after you have planned all your expenses you know exactly how much will be left for your savings the savings section can then be used for planning your contributions to both long-term savings Investments or extra payments on debt but also short-term savings Ambitions in the form of syncing funds that help you to systematically save towards a future expense or down payment the goal should be to plan the contributions based on your saving priorities until the amount left to be allocated is exactly zero which means you now have a plan for every single dollar of disposable income and your budget plan for that month is completed but in real life not every month is the same and for many categories planning the exact same amount each month is not realistic this template does not force you to create one static budget plan that has to be used for every month but instead allows you to create a realistic multi- period budget plan on one single worksheet in basically no time let me show you how you can expand this budget plan for the whole year in case all income positions will stay the same in each month planning the remaining months is done in a second using the autocomplete handle but if you have a bit more variety in your monthly income so that for example you know there will be a level shift for your salary through a promotion then you have an income category that you expect to stay on the same level each month and a third income Source you know will be different every single month all of this can be accurately portrayed in your budget plan for the expenses you can plan regular expenses ahead just as easy in case you have an expense category that will be paid only once a year year or once a quarter like insurances for example you don't have to break it down into 12 equal monthly amounts but you can budget the actual expense whenever it's expected to be paid obviously there might be regular categories like clothing or medicine that in reality are a bit more volatile than other regular categories like for example rent yet setting a fixed monthly budget for these positions will give you a good Benchmark for each month in case you cannot exactly predict the distributions over the year and finally you can also have mixed categories like fun and vacation for which you want to plan a base level each month for things like going out eating in a restaurant buying birthday gifts Etc but in specific months this amount will Peak when you plan to go on vacation or plan to spend more money for gifts and celebrations around Christmas every expected variation of expenses over the year can be accurately represented in this budget plan for the savings section you can now plan the allocation of the remaining amount available in each month here it makes sense to plan ahead with ance with your personal priorities if for example your emergency fund is still 3,000 short of the amount that you feel safe with you can prioritize that and allocate 1,000 for the first 3 months since the plan budget for this position goes to zero after that you will have more Capital available for other savings categories based on your priorities you can then go through all further positions and decide if you want to have a constant amount allocated each month if you want to increase a position once more money is available for saving or even start allocating to a new category once you have additional free resources to do so what's crucial in the end is that for each month you have a plan for every available dollar from your income streams so that the amount left to be allocated is exactly zero and the green check mark at the top is visible when we jump to the dashboard worksheet all the defined categories and planned budget amounts are instantly available on the left side you see a sorted breakdown of the categories that dynamic Al grows and shrinks as you add or remove categories in the budget planning sheet on the right side in the summary section the track versus budget chart beautifully visualizes our budget plan over time and even allows you to single out two or one specific types moreover using the drop down at the top you can easily switch between a total year View and monthly views of the budget plan in the budget tracking sheet you can then start tracking your actual money flows in this smart table for a new entry you simply need to enter the ing date select the type as either income expenses or savings and then based on which type you have selected all the previously defined categories of that type will become selectable in that category dropdown finally you can enter the amount as an absolute number and enter the details for that record whatever type you select the category dropdown will always automatically adjust its list of values as you add records for different types you will notice that the amounts are automatically formatted to visually differentiate between income on the left and the allocation of income to either expenses or savings on the right when we add the dashboard breakdown in split view you can perfectly see how every new record instantly updates the respective track amount of that period in that breakdown the categories are automatically sorted by Tracked amount as the primary and budgeted amount as the secondary sorting Criterion and you always instantly know which percentage of the budgeted amount you have already tracked and how much of the budget is still remaining for each category and type the balance column will always keep track of all the track money flows up until the date of the respective record a positive or negative amount in that tracking balance column should be reflected in the main bank account through which your money flows from income to expenses and savings this balance is calculated in a way that is completely robust against any sorting or filtering in the table that means you can easily add a new record that chronologically should be placed right before these existing records the balance of this new record and all later records is still calculated correctly we can then bring this record to the chronologically correct position by sorting the date column from oldest to newest and anytime you want to take a closer look at certain transactions in a specific period for example the expenses only is sorted by amount you can easily do that and the Integrity of the balance calculation based on each individual date is still guaranteed the effective date column right next to that is responsible for another great optional feature that solves a real issue for many people when it comes to tracking their income because for many people it's the usual case to receive their income for example in the form of a paycheck rather towards the end of a month instead of the beginning of a month and if that's the case for you you might want to treat that income not as the income to make up for all the previously tracked expenses and Savings in that same month but rather in a forward-looking way as the disposable income for the next month so that you first earn it and then you spend it and save it and not the other way around if that something you want to apply you can simply jump to the settings worksheet and activate the late monthly income option for which you can also specify the day in each month from which on track income is considered for the next month so with 20 in here every tracked income with a date of the 20th or later in a month is assigned to the next month just another great feature to better cope with the realities of life after entering all data to bring your budget planning and tracking up to date so that the lag between your latest record and the current date is as small as possible the budget dashboard is a great place to understand analyze and evaluate your personal budget performance with the current month as the selected period you can take a look at how much of that month has already passed in order to better evaluate the percentage of your budgeted amount that you have tracked so far for each type and category in addition the period tracking balance shows you exactly how much of your track disposable income is still left to be allocated in that month and down here that balance value is then perfectly broken down into the remaining budget amounts and the amounts that have exceeded the budget by type and category over here in this track versus budget chart your track budget performance for the income expenses and Savings in that current month is visualized and highlighted with an exact breakdown by category for the income the expenses and the Savings in these beautiful circular charts to analyze your budget performance for a completed month you can select that month's name in this period drop-down and then conduct a conclusive evaluation of where you have under or overperformed against the expectations from your budget plan on a higher level you can take a look at the total income expenses and savings numbers and then dive deeper to identify which categories have been the driving factors into one or the other direction if you want to analyze the individual transactions for a specific category in a given period to better understand the overall category performance you can display the budget tracking sheet in split view right next to the breakdown then filter down these records to the respective period and category and then simply sort by amount to identify the main contributors back in the dashboard you can also find the savings rate of that completed period up here and in addition you now have the opportunity to analyze the exact distribution of the track categories for the income expenses and savings now that you know that every transaction for that month has been included as we go through the different months you'll notice how the savings rate category distributions anti overall budgeted and tracked amounts can be highly volatile for that reason the total year view provides a way more evened out and thus significant representation of basically every inside in this dashboard in this total year viiew any single category can now be evaluated against the period progress even better as the exact day of a single record within a month has way less impact on these percentage numbers when compared to a monthly view while in this totally year viw most visuals display aggregated numbers over all months the track versus budget chart still breaks down the track and budget amounts by month and lets you easily explore the development of income expenses and savings over time for comparing the development of only two types or exploring during the development of one specific type this chart allows you to fully customize which types to display with a few simple Clicks in addition for a pure focus on the track amounts only you can hide the light colored budget values at any time with the track amounts for every category aggregated over the total year these category distribution charts now provide even more meaningful insights about the composition of your money flows since one time and period iCal effects within single categories are getting leveled out over a longer period of time and eventually this totally R viw is also the one view that gives you the most significant indication about your actual savings rate because while the savings rate might heavily fluctuate between single months the savings rate over the total year provides an accumulated and leveled out feedback about your long-term savings performance the whole template is just an amazing tool for setting up tracking and analyzing your personal budget it is created with a focus on lightweight design outstanding user experience and fast performance we're going to build the whole ultimate Excel personal budget with all these amazing features step by step the final template file is available for download on Excel find.com link for that is in the description and now without further Ado let's get right into it as the initial step we're going to build the budget planning worksheet in total the whole template will have six worksheets which we have already created as empty worksheets with header only besides the budget planning sheet there are two other main sheets budget tracking and budget dashboard then there are two supporting worksheets for calculations and drop- down data which will be hidden at the end and the settings worksheet which will allow the user to set the most important parameters for the template and this is where we're going to start off by creating an input field allowing the user to set the most crucial parameter of the template the starting year of your budget for this input field let's create a new input section by merging these six cells together for a section header aligning the text to the middle setting the fill to this dark blue and the text to wide and bold this section will be for the general settings that concern the whole template and to set the section borders let's select the intended content range and click on more borders to open this format cells window we set the Border color to this mid-light gray and add a left bottom and right border to the selected range as we want to use the left and right column as padding we're going to reduce their size and in return increase the size of the content columns the label of our only input field here will be starting year and we set it to bold and for the input field itself we set the fill color to this light blue reduce the font size and align the text to the middle and Center let's enter 2022 as the value for our budget starting year and make this Fields value globally accessible through the name starting uncore year for those of you unfamiliar with defining names in this upper left Name Bar every time we Define a name for a cell or range in this upper left name bar this cell or range will be reprehensible in the whole workbook through that name and can always be checked edited or deleted in this name manager before we make use of that starting year value let's not forget to add a short description for this input field in column G which we will make a lot wider to fit in some text for this description let's reduce the font size to nine set the font color to this dark gray and also make it italic the description text itself should be brief and on point letting you and potentially other users know what how and when to enter or change values here with this set up let's jump over to the budget plan planning sheet and create the header bar for the first year in this budget plan going from E5 to Q5 covering a total of 13 columns it's 13 columns because we need 12 for the months in a year and one for the total year let's merge these cells align the text to the middle make it bold and also adjust the height of that row as the value for this header bar we're going to reference the starting year that we have just defined in the setting sheet the two rows below that header bar is where we're going to summarize the budget to make sure that the budgeted income is in balance with the budgeted expenses and savings let's open the advanced border options in the format cells window set the color to this mid-light gray and this time we're going to add every horizontal and vertical border so that every individual cell is surrounded by a border as the next step we increase the size of column C which will be a super crucial column for the whole template as this column is where we will be able to Define our custom categories that then later will be made available in both the budget tracking and the budget dashboard worksheet before we start setting up the budget input section for the income let's quickly add a small reminder that this starting year is supposed to be defined in in the settings worksheet for the income section now we start by entering income as the header in cell C9 and then in the rows below we add placeholder values like enter income category for all the potential custom income categories the exact number of placeholders here doesn't matter as later only those rows with entered categories will be considered below the placeholders we add one final row with the label total and then we're going to start formatting these values starting with adding an indent to all the values then selecting the header and total cell to make the text bold and eventually for the category cells in between we going to reduce the font size to 10 after that we select the header row plus a number of category rows and insert a table the key idea of this table is that all the categories that are inside the table will be considered for the budget plan budget tracking and the dashboard and all other category values outside of the table will be ignored and held as reserve for adding more categories in the future so for those category cells inside a table let's enter some example income categories such as employment side hustle and dividends from a stock portfolio since the default table design is not what we want to have let's create our own custom design by going to the table design tab open the full selection of available table designs and click on new table style at the bottom this opens this new table style window and as you see we're going to start with a clean white design from scratch let's set the name to income table style and start by formatting the whole table element at first all we want to set on that level is the Border style and here again we go with this mid-light gray as the Border color and add all horizontal and vertical borders so that every individual cell has a border around it the second element we want to format is the header row and for this header row we set the fill color to this green tone and the font color to white and bold and that's already it let's click okay and as you see the new custom table style is not automatically applied to our table instead it is now available in the selection of table designs and we have to manually select it eventually we also remove the filter button of that table as sorting these values is something that we don't want to allow even without the filter button it is still recognizable as a table through this small blue shape in the bottom right corner that lets you increase or decrease the table size to make the total row match the Border style of the table let's add a similar border to this one cell and now we can start setting up the budget plan input area for the budget values let's select the cells in this header row and add the same fill and text color as we already have for the table header in addition we reduce the font size to 10 align the text to the middle and center and finally make it bold in those header cells we want to see the respective period which is the month's name from January to December in the first 12 cells but instead of entering this as plain text we want to use this as lookup row to find the correct budget for each period in our dashboard later so what we're going to do is we insert an actual date consisting of the year in the upper header bar here we have to make sure that the row is fixed through a dollar sign for when we copy that header bar down later then for the month we're going to enter the respective month number which is a one for January in this First Column and for the day we simply take a one for the first day in that month and that's it now when we autofill this to to the next cell you will see that the year reference is lost somehow and that is because we haven't fixed the column through a dollar sign and for a merged cell area you always have to reference the top left cell to get the value we haven't added this dollar sign for a good reason as we're going to add more years on the right at the later point so in this case we now need to manually readjust the reference to E5 and change the month number to two for February to make life easier let's just copy this formula and paste it for the remaining months so that we only have to adjust the month number and the reference to the year will always be correct once we have done that for all the 12 months the final column is for the total of that year and for this header cell we're going to reference the year only and also make sure the dollar sign is set only in front of the row the amazing advantage of having the months as full dates in here is that each cell now has two crucial information saved in it which are the month number and also in a dynamic way based on the selected starting year the Year number and both these together can be used later as lookup value to identify the correct budget plan column for the dashboard calculations at the same time we can display this date as its month's name only in a clean and pretty Way by opening the number format options go to custom and enter mmm as a type this will display this date as the three initial characters of the respective month and now it looks clean and beautiful for the value range we start by adding the same border design as we already had for the category table with this mid-light gray and all the horizontal and vertical border elements and after that let's enter an example number value and select the whole range to adjust the font size and set the number value to the counting format with no decimal places as the next step we're going to generate some Dynamic positional information about the elements of this table and the total row because for many calculations in this template we will need to know in which Row the table header is in which Row the first and last defined categories are and also in which Row the total for each period is calculated in this budget planning sheet specifically knowing these rows allows us to visually differentiate the rows with defined categories from the placeholder rows using conditional formatting and for the total calculations in each period it helps to ensure that only values are considered if the category label is part of the table let's start by selecting a cell in the table going to the formulas Tab and opening the name manager here here you can see that the table that we have inserted got assigned the generic name table one and we want to change that name into income so that we can always access the table and its values using a name that makes sense once we have renamed this table we can now reference it with ease in any calculation using that name great and as already indicated there are four positional information we want to know for this category input section the first is the row second is the row of the first element in the table which I call Min row and the row of the last element in the table which I call Max row and eventually the total row calculating the header row is quite simple we're going to use the row function and reference the cell of the table header and as you see Excel automatically fills in in a reference to the table header instead of a simple cell reference so even if that table would move down in the worksheet the row will always be calculated correctly right now the row is nine and for the row of the first and last element let me quickly demonstrate what happens if we apply the row function to the total table content range as you see this returns an array of the same size with the row function applied to each element in the table returning the correct row numbers of 10 to 12 when we enlarge the table to include another element the return array will include the row of that element as well and when we now feed this array into the Min function it will return us the smallest row and thus the row of the first table element the same logic can then be applied to dynamically calculate the row of the last element by using the max function instead so adding two additional elements here will change the max row to 14 and removing them back to 12 eventually for the total row we simply appli a row function to that specific cell and get returned the correct number of 20 as we want to use those four Dynamic row calculations multiple times in many other calculations in this template we going to create so-called named calculations in the name manager so that we can always reference these calculated rows by a defined name as this is for the income input section we're going to call the header row calculation income header row we leave the scope at workbook so that it can be accessed from any worksheet in the workbook and then we create the row calculation just like I have done in the worksheet before applying the row function to the table header cell in a similar manner we then going to create the income Min row calculation with the formula min of row of income and the income Max row calculation with the formula Max of row of income and eventually the income total row calculated as the row of the total label cell great let's close the name manager and test if those named calculations work as intended the income header row is correctly calculated as nine the income Min row as 10 the income Max row as 12 and the income total row s 20 that looks perfect so we can make use of of these row calculations the first time for setting up a dynamic conditional formatting that visually highlights those rows with a defined category in order to make all other rows visually move to the background let's set the default font color for all the category rows in that column and also the value input columns to this gray tone and now with the same range selected we can add a conditional formatting rule that that uses a formula to determine which cells to format we want to format all the cells for which the row of the cell and here we need to reference the top left cell of the range and remove all the dollar signs to make it a relative reference is smaller equal the income Max row so right now as the income XX row is 12 this rule will check for each cell of the range if the row of that cell is smaller or equal to 12 so this will return true for every cell in these first three rows and false for every cell in the rows below and the format we want to apply is a PL font color instead of automatic and for the fill of those cells we take this light green let's confirm this and as you see the conditional formatting rule seems to work as it should let's insert some example budget values and as you can see the font color is set to gray once it has no defined category in the table and as soon as we add another income position though it gets automatically included into the table and the conditional formatting expands correctly in the same manner manually reducing the table to those top two elements works just as fine next let's take care of the total calculations in this total row we're going to calculate the total budgeted income per period while in this total column we're going to calculate the total budget over the year per income category as we want to make both stand out as totals let's set the font to be bold the total per category over the year can be calculated quite easily as the sum over those 12 cells in the same row with the range reference being Dynamic without any dollar signs we can autofill this formula to the other rows using the autofill handle for the income total per period however the calculation needs to be a bit more advanced as we want to only consider values from rows with a defined category that means instead of referencing the full range above the total cell in the sum calculation we now need to reference a dynamically Cal cated range that only covers those highlighted cells the solution to this is a combination of the address and the indirect function the address function takes a row number and a column number as input and returns a cell address in the form of a text the row of the first cell in our dynamic range is the income Min row and the column is the same as the column of the total cell from which this formula is called we can simply enter the column function function with no arguments which then Returns the column number of the total cell itself let's hit enter and we see this returns E10 as an absolute cell address in the form of a plain text now to expand this one cell address text to be ar range address text we need to add a colon as text in quotation marks and then add another address statement this time with the income Max row as the input as this is the row of the last category element in the table there we go now we have the full range address returned as text and when we expand the table the range address updates ingly what we now need to transform this text range reference into an actual range reference is the indirect function so we pass the whole expression to the indirect function hit enter and you see it now dynamically references the relevant range and Returns the dynamic damic array of all range values based on the table size to add all these signal values together we eventually feed the statement into the sum function and there we have the final result for the January income total let's autofill this formula to the other total cells in that row and then test all total calculations by expanding those sample values to all months and then adding a new income category as well works perfectly great so we can clean those sample inputs up for now and as the final step hide all those unused placeholder rows which gives us this beautiful condensed view that only covers the defined income categories with this initial section for the income we have created a great foundation for the other two budget plan sections that will be dedicated to expenses and savings for the expenses section let's copy down the income section with one spare row between obviously we need to do a few adjustments starting with renaming the table header to expenses also we're going to rename those three sample categories into expens categories like housing utilities and groceries then we change the placeholder value to enter expenses category for all those rows and next let's change the table design by duplicating the income table Style then renaming that duplicate table style to expenses table style and the only adjustment we need to do is basically changing the fill color of the header row we're going to go with this red tone as the color for expenses after defining this new table style we still need to manually assign it to this table and just like for the income table we're going to remove the filter button to avoid any Distortion with these category definitions for these period headers then we're going to align the fill color with the table header color and that's it for the basic adjustments just like for the income section we want to generate those four crucial row calculations so that we always know the position of the header the min max and total row of this expensive section let's open the name manager and start by renaming this new table into expenses after that we can generate the equivalent named row calculations for the expenses section starting with the expenses header row as the row function appli to the table header cell then the expenses M row as the Min of the row function apply to the expenses table and in a similar manner the expenses Max row as the max of the row function applied to the expenses table eventually the expenses total row as the row of the total label cell great let's do a quick testing if all those named calculations work properly and as you see all of these return the correct row value now when we click into the category range and open the conditional formatting rules manager we see that the conditional formatting rule has been copied over and refers to the correct range of cells in this expanses section when we change this filter up there to show all the rules in this worksheet we can see that the original rule for the income section is still in place and obviously working correctly what we need to change for this new rule in the expenses section now is adjusting the formula to not compare the row of a cell against the income Max row but instead against a newly defined expenses Max row and of course we also need to adjust the fill color that goes along with this rule to be this SL red instead of green let's apply these changes and it works perfectly so we can close that window and put it to the test by expanding the table everything works just fine and now the only thing left to be adjusted for this section is the calculation formula for the totals for those totals per category over the full year no adjustments are required as the relative range references have been transferred over correctly but for the calculation of the total expenses in each period we need to replace the income Min and Max row calculations with these new expenses Min and Max row calculations so that they now reference the correct elements let's autofill that adjusted formula to the other total cells in that row and do a similar check as before or by entering some sample values for all the months and adding a new category as you see the calculation perfectly adjusts to always consider the correct range of values for the total calculation that's amazing and that's already it for this expenses section so let's remove those sample values and hide those unused placeholder rows to get the same condensed view as above we now going to repeat this process one more time to create the savings section so let's copy over the expenses section change the table header to savings and the example categories to emergency fund retirement account and stock portfolio and in addition the placeholder value to enter savings category for all those rows below then we're going to create another duplicate table style that we name savings table style and the only adjustments to do here is changing the header row fill to another color for the savings this beautiful blue will be the color we choose let's apply this new table style and a of course don't forget to remove the filter button from this table as well the period header will get the same blue fill color as the table header and then we have already reached the point where we're going to rename the table and Define the forame calculations for the key rows of that section in the name manager let's first change the table name to savings and then create a named calculation called savings header row as the row of the table header cell then the savings Min row as the Min value of the row function appli to the savings table and in a similar manner the savings Max row as the max of the row function applied to the savings table and eventually the savings total row as the row of the total label cell again equip quick testing of all those named calculations is recommended before proceeding and since all four seem to work properly we can proceed with editing the conditional formatting rule for this section again it has been copied over correctly referring all the potential category rows so let's jump into the rules formula and replace the expenses Max row with the savings Max row and of course the fill color for this rule needs to be changed to the appropriate color tone which is a light blue in this case let's confirm and apply these changes and on first side it seems to perfectly work when we close that window and expire a table that first impression is confirmed amazing again for the calculation of totals the formula in the last column does not need any adjustments but the formula in this bottom total row needs to be slightly changed in the form of changing those row references to savings Min row and saving Max row beautiful let's autofill this formula to the other to cells in that row enter some sample values and change the scope of the table as a quick test since everything works properly as expected we're going to clean this up and also hide those unused placeholder rows now let's take a look at the summary section at the top the way we want to approach budgeting here is called zerob based budgeting which means that every dollar that comes in through one of the income categories needs to be allocated to either expenses or savings so that you're basically going to make a plan for every disposable Dollar in each period for that reason we want to keep track of the amount that still has to be allocated in each period while setting up our budget plan calculating this amount is as simple as taking the total planned income and subtracting the total planned expenses and savings so if we expect to have an income of 1,000 for this first month then the amount to be allocated increases by that amount let's autofill this formula to all the other cells in that row decrease the font size to nine just like we did for the input sections below and also make the total value for the whole year bold with those 1,000 available to be allocated let's allocate half of that to expenses and the other half to a savings category as the whole amount of disposable income is allocated now the planned budget balance is back at zero which is the target state of our zerob based budget plan in the upper row of those two we want to display the respective period for each column but in a slightly different way compared to the input sections below as we will not use this row as a lookup row later we don't need to put actual dates in here but instead we can use it to display if we have reached the target state with our budget plan so what I want to do is inserting an if statement that checks if the amount left to be allocated equals zero and if that's the case I want to display the month's name with a check mark As Unicode character which I going to put into the description of this tutorial otherwise I simply want the month's name to be displayed without any additional character let's reduce the font size align the text to the middle and center and make it bold just like we did before for the other period headers then we're going to fill this formula to all the other cells in this row but of course as we have hardcoded the month's name here we need to manually adjust it for the other 11 months and the year's total as well that will take a bit of effort I know but it will definitely be worth the result for the year total header then we're not going to reference the year number as this is already displayed directly one cell above instead here we're going to call this total to not be totally redundant to unleash the full potential of these summary rows let's add a few conditional formatting rules that will visually help us to understand in which state the budget plan for each period is let's start with the case that not a single amount has been entered in any of these three sections for a specific period so the income total the expenses total and the savings total all are zero make sure that each reference has a dollar sign in front of the row but not the column in that case even though the amount left to be allocated is zero the a plan for that period has not been touched yet and for that reason we want to set the font color to a default mid-light gray once we apply that rule we see that only those columns are shown in color once they have underlying values in any of these three input sections the second rule we're going to create covers the case that there is more allocated to expenses and savings than we have in disposable income which means the amount to be allocated is smaller than zero in this case we want to change the font color for both rows to be a warning red let's click okay here and here and now in the rules manager we see that this new rule has been placed at the top but since we want that rule to be checked and applied after the other rule we're going to move it down and also set the check mark for the stop if true option which makes sure that the rule after that first rule is only checked if that first rule proves to not be applicable eventually with the third rule we want to cover the target state which is that we have values entered in either of these three sections and the amount left to be allocated equals zero since this initial rule with the end statement only lets through those columns for which at least one of the input sections has entered values we only need to ensure that the amount to be allocated equals zero so let's just duplicate that second rule move it right below the initial rule change the formula to E7 equals 0 and change the font color to this stunning green tone now as soon as we click apply we see that both the January and the total value are colored in green as they fulfill all the target State criteria of a zero based budget plan and to improve the performance let's also set the check mark at stop if true for this second rule because there is no need to check the last rule in case the second rule is true with this we have now completed a setup of the budget planning section for this first year let's quickly test the setup by adding 1,000 of disposable income to all the other months as you see all the months are now in color but do not display any check mark as the available amount still needs to be allocated to expenses and savings once we allocate the full amount the color changes to green and the check mark becomes visible indicating that the planed budget is in perfect balance now if we allocate more than available however the amount allocated turns negative and the color changes to Red indicating that this is not a sustainable budget plan for that period let's quickly bring all the periods into perfect balance and just as intended all the periods are now displayed with a check mark and in green color in the summary section beautiful now we can remove all the input values and change to a more macro level view because we don't want to use this personal budget template for only one year we want to make this a multi-year template and thus we need to find a way to display the budget plan for one year in a compressed way and then add similar budget planning sections for the subsequent years to display the budget plan for this year in a compressed way we can select all the month columns then go to the data Tab and click on that group button this will allow us to collapse all the monthly budget plan information while the yearly totals are still visible let's expand it again and decrease the size of column r as we want to use this as a separator column between this year's and the next year section then we're going to select column e to R and use the autofill handle to add another section for the next year instead of referencing the starting year in this top header cell we now going to reference the previous year's value and add one to it that not only updates the year number in the top header cell but also the underlying year values in all those period headers of the input sections when we open the conditional formating rules manager and make it display all the rules in this worksheet we can make another great discovery because by using this autofill option to create the second year the conditional formatting rules have not been duplicated but instead the new ranges have been automatically added to the already existing rules which heavily improves the work sheeet performance and the manageability of those rules in general based on the second year section we can now add as many additional years as we wish for this template we're going to go with a total number of 10 years so with 2022 set as the initial year the last year should be 2031 perfect of course we don't want to have all these years expanded so let's collapse all the years by selecting all these columns going to the data Tab and clicking on that hi details button which collapses all the groups finally to ensure that the categories are always visible even when we scroll to the right with multiple sections expanded let's select column e go to the view Tab and use the freeze paints option which makes every column to the left of the selected column always stay in place when we scroll to the right awesome that's the complete setup for this budget planning worksheet so all that's left to do here is entering some generic sample data for this first year so that we have something to work with especially in the budget dashboard later for the income section we're going to go with these three already defined categories for the employment category let's assume at the start of the year the net salary from our job after taxes is 3,500 but we already know that a few months in to the year we'll get a raise so that we end up with a th000 more in net disposable income from that job changes in salary or income are obviously part of real life and that's the great thing about the way we have set up this budget plan it allows you to accurately represent each month individually for the S hustle let's say we expect to earn about 1,000 each month in net income after putting away payable taxes and eventually for the dividend payments from our stock stock portfolio we can look up which company plans to pay what dividend in which month of the year so here we're going to plan the income for each month individually and for Simplicity let's assume the dividend payment amounts repeat every 3 months once we have entered the expected income for all the defined categories the total row at the bottom accurately sums up those relevant positions for each period while the total year column on the right provides a perfect overview of over all months per category as we have not allocated any of the income to expenses or savings so far the amount to be allocated in each month up here is exactly equal to the budgeted income let's start planning the expenses for the year and at first we need a few more expenses categories so let's unhide these available placeholder rows the expenses section is probably the one section that in general requires the most number of different categories so let me show you how you can easily add more potential input rows to a section simply select a number of unused category rows right click on the row number and click on insert this will insert new empty rows on top of the selection and by selecting the row above and dragging down the autofill handle you can add the placeholder values and the total formula in the year total column to these new rows just make sure that these newly inserted rows are somewhere in between the other rows so that they are automatically included in the conditional formatting rules range in addition to the already defined expense categories housing utilities and groceries let's also add a few more the level of granularity you choose for these expenses categories is of course totally up to you still I want to recommend to rather use higher level categories and not to get too granular as this could negatively impact the interp credability of your data in the budget dashboard later as a quick side note for those of you who want to consider loan payments in their budget as a rule of Thum I think it makes sense to include your minimum loan payments in the expenses section because this is something you have to pay to not get into trouble with the lender and anything beyond those minimum payments like for example extra payments would rather go into the saving section as those are the amounts that heavily contribute to what actually improving your net worth with that being said let's hide those unused rows and set up the budget numbers for these expense categories for the first four categories let's plan a consistent amount so we can simply autofill these numbers to all the periods for the insurances however we're going to assume that here we have to do quarterly payments that means there are always two months with no payment at all and one month that covers the whole quarter that's another great example how this budget plan right here enables you to set the right expectations in each month because with a simple budget that's reused every month we would need to budget the monthly average of $50 each month and then when tracking the actual payments we would fall below that amount in two of the months and exceed that amount massively in the month of the actual payment now for the next three categories we again want to plan a constant amount each month which we can easily do by autofilling these numbers to all subsequent periods and the last category then combines two types of expenses fun which refers to more regular expenses and vacation which is a rather irregular expense that means we're going to plan a base level amount for each month and which which we don't plan to be on vacation and then for April for example that budgeted amount increases for the first vacation putting the total budgeted expenses in that month to a way higher level than usual then another 3 months where we're going to plan the base amount and then in August we plan a second even larger vacation pushing the total budgeted amount in that month to over 4,000 and eventually after another three months of base level for December we budget twice the base level to account for everything that is related to Christmas celebrations this yields us a plan for our expenses for every month individually based on how we actually plan and expect our life to be throughout the year for every month we are able to set the expectations as realistic as possible and more importantly the top section up here now tells us exactly how much we will be able to save based on the planned income and expenses in each individual period and for that reason enables us to set up a sustainable and realistic allocation of the remaining money to our custom savings categories we have already set up a few important savings categories with emergency fund retirement account and stock portfolio but now let's also add a syncing fund for a future down payment so this is an actually pure savings position where we're going to save towards a specific future investment or transaction and also a syncing fund for other future purchases it's important to understand that these syncing funds don't necessarily have to be tied to individual savings accounts it's also possible that they only exist on paper or in this case on the spreadsheet and actually are located in one mutual savings account for planning your allocations to the different savings categories you should strictly plan ing to your priorities let's assume those SA savings categories are already ordered by priority and we still need $3,000 to reach the target value for our emergency fund then we can take a look at the available amounts for the first months see that we can afford to allocate 1,000 each month to the emergency fund for the first 3 months and after that we can budget that position as zero for the rest of the year next on the list is the retirement account and here we probably prefer a con monthly contribution and an amount that can easily be covered each month would be for example $400 so let's set that amount for each month of the year next we also want to plan regular contributions to our stock portfolio but the amount left to be allocated for those three months is pretty low so we decide to allocate the minimum amount of 150 in those first 3 months which makes us hit the budgeting Target of zero for March and as we want to start saving towards the down payment not before we can make significant contributions to that position we're going to allocate the remaining amounts to the syncing fund rest for these periods due to the increase in salary and the no longer required contributions towards the emergency fund starting in April the amount left to be allocated for those three savings categories has taken a huge and sustainable jump that that gives us more room to increase the monthly amount that goes into our stock portfolio and as we have at least 1,000 available in each of these remaining months we can easily cover an amount of 750 for this and all subsequent periods and for the remaining amount we decide to put the full amount minus $100 into the syncing fund for the down payment so that we have $100 to allocate to the other syncing fund for all these months as I already mentioned this is just a random example but the important aspect here is that this budget plan now perfectly fulfills the requirements of a serob based budget as the budgeted income versus the budgeted expenses and savings are perfectly balanced and we have a plan for every disposable dollar as The Next Step we're going to set up the budget tracking worksheet and this is where we're going to keep track of our actual income expenses and savings allocations and the budget planning worksheet that we have just set up provides a crucial input for this budget tracking sheet in the form of the defined custom categories which we now going to use to accurately categorize each of the actual transactions and we're going to track these actual transactions in an input table so let's start by defining the table column headers as date type category amount and details then we select those five cells press contrl T to insert a table and set a check mark for my table has headers let's enlarge this table to get a better view of the overall table design and also increase the size of those columns as the current Table style does meet our expectations let's again create a custom table style and we're going to start with a duplicate of the savings table style for the name we're going to go with tracking table style and for the whole table element we then adjust the Border setup by removing those in between borders only for the header row we're going to add all horizontal and vertical borders in there and for the fill we select this dark blue for this table style in particular we want to add one additional element which is the first row stripe for which we set the fill color to this light gray and as you see the first and second row stripe basically allow you to Define alternating fill colors for the table content rows and for a transaction tracking table like this this seems to be an appropriate design let's apply this new table style and then take care of the general text formatting in the table for the header bar we want the text to be aligned to the middle and the left but with a small indent and for the table entries we're going to decrease the general font size to 10 align it to the left and also add a small indent after that let's start setting up the input columns starting with the date column technically you can enter dates in any form you want so for example in this clean and readable format but also month SL day slash year and whenever you enter a value into the cell right below the table the table will automatically be expanded to now include that row and applies the defined text formatting as we want to have all dates being displayed in a uniform format independently from which format they have been entered in we going to define a custom and clean date format like this in addition we want to add a data validation to this column to ensure that only date values will be accepted as input for the validation criteria we can select to only allow dates and as we want to allow basically any date let's set this to greater than the smallest available date in Excel which is the 1st of January 1900 in case a value is entered that doesn't match this validation Criterion we want to show a custom error alert that simply tells you what you have done wrong let's confirm this and put it to the test when we enter a valid date everything is fine and when we enter a non-accepted value like a text for example Excel will not accept that value and shows our custom error alert perfect the type column is where we going to choose if the entry is of the type income expenses or savings for the best user experience let's create a drop down list that contains those three options so with the table column selected we open the data validation window and to get a drop- down list we have to allow a list of values and you see by default the inel dropdown option is already activated then in the source field we can type in our three items separated by a comma and as you might see the values we type in exactly match the names of the three category tables that we have defined in the budget planning sheet and that is crucial even with this drop- down option activated you can still simply type in values into those celles and when someone types in a value that is not in the list we want to show a custom error alert that points to the fact that only values from the list are allowed let's click okay and test it for the first record as you see we have all three options away available and when we select the value it will be written directly into the cell and whenever we type in a value into the cell that is in the list it will be accepted just as fine but as soon as we type in something slightly different that's not defined in the list the error alert is triggered and the input value gets rejected the category input column is where we now going to dynamically inject the custom categories that we have defined in our budget planning worksheet in this column we want to have a drop- down list that depending on which type has been selected in the type column offers all the custom categories of that type that we have previously defined now in the source field we're going to insert a formula that will make this a dependent drop- down list since the values that we can select in the type column perfectly equal the table names that we have set up for the three category tables in the budget planning worksheet we can now apply the index direct function to convert the simple text value from the type column into an actual reference to the respective table content to make this work row by row we have to make sure that this is a relative cell reference without any dollar signs and in case a value is manually typed into the cell that is not contained in the respective category table we also want to show a custom alert similar to the one that we have defined for the type column great let's take a look at the first row and as you see since we have selected income for the type the category dropdown automatically contains all the defined categories from the income category table let's select the value and increase the size of that column so that all the categories will be displayed in full length with expenses selected as type the category drop down now contains all the defined expense categories and eventually for the savings type we get all the defined categories from the savings category table that is already pretty amazing and it ensures that later in the dashboard the budget plan and track values can be perfectly matched together one drawback of drop- down list in Excel is that once a value has passed the data validation and is written into the cell the data validation will will not be repeated anymore when the underlying list of allowed values changes if we for example change the type to expenses for this first record this updates the available dropdown list to now show all the expenses categories but it doesn't affect the value that is already written into the cell as long as we don't actively override it with a value from the updated list the same is the case if we would delete or rename a category in the budget planning sheet there is no automatic indic indication that the value that is already written in there might be outdated and needs to be replaced so to solve this issue I want to add a conditional formatting rule to this category column that checks if we get an NA error which basically means no such value found when we try to look up the category value in the category table that has the same name as the select the type for the lookup we use the match function and provide the category value as the lookup value then a reference to the relevant category table as the lookup array and as the final argument is zero which means it has to be an exact match now if that category value written into the cell right now is not found in the category table of the selected type the match function will return an NA error and thus the is na function would return true in that case we want to set the font color to a warning red indicating that the category value is no longer part of the relevant category list and probably needs to be replaced with another category and as you see the first records category is now colored in red as employment is not found in the list of defined expense categories however once we change the type back to income the red color disappears as now both the type and category fit together again this is just a clean and userfriendly way to ensure the consistency of type and category in this worksheet at any time the next column is the amount column where we're going to input the actual value of the transaction so let's start by entering some example values so that we can properly set up the number formatting and as a general number format we're going to apply the accounting format but with zero decimal places of course you're always free to display the two decimal places if you prefer it that way but without the decimal places the focus is more on the actually relevant digits before The Comma just like for the other columns we also want to apply data validation to ensure that only valid values in this case only numbers can be entered into this column the only suitable option from this allow section would be decimals but since we don't want to specify any further conditions for the value range um we're going to choose custom instead and enter the is number formula and inside that function we're going to reference the first cell as a relative reference without any dollar signs this will absolutely do t