Build a Dynamic Personal Finance Budget Tracker in 15 minutes #Finance

in this CashNews.co we’ll build a dynamic budget tracker like this which will tell us our Income our expenses and our Savings alongside some supporting visuals and all of this is going to be automatically calculated using our inputs tab where

you’ll be able to input your Income or your expense sources so let’s get into it looking at the Excel file which you can download in the CashNews.co description for free you can see here under the dashboard tab that we’ve got the Income breakdown

by each specific month alongside the the year-to-date total on the very end and the same thing goes for the expenses you can see we have different sources here and lastly we have the Savings towards the bottom alongside some supporting visuals that we’ll add later here

let’s take a closer look at the inputs tab so over here you can see that we have all of these categories which are basically some the Income While others are going to be the expenses that we can record and so over here we have some dummy data where you’ve got say in the

month of January some of the typical expenses that you might have in this case it’s a metro card and you can select that drop down and pick up the one that you you prefer most now if you go to control down arrow all the way to the bottom here I’m just going to show you how you can add a

new item so first we’ll just copy the one from above so Ctrl C there and then just paste it down over here Ctrl V like so now let’s suppose this date is actually in September let’s say I picked the 2nd of September like so then you can see this is gonna Auto populate and we can

select whatever category we want let’s say this is our Investments and say this is our stock Dividends and from here let’s say we just put the amount that we want at 300. now one thing to note is that sometimes you might have duplicates for instance in

September maybe you went to a grocery store more than once that’s okay that’s something you can add multiple times as well and if you don’t really want this Navigator you can always go ahead and start deleting it by hitting shift space then control minus like so now that we know

how to add an Income or an expense let’s move up to the dashboard tab as you can see right now we don’t have any of the amounts so let’s work to make it Dynamic so we’re just gonna go equals some IFS hit the top key when you find that and this is going to

calculate all of the inputs that we want and more specifically we want them to be in this case in the month of January for the base salary as we move along we wanted to update to the month of February base salary and as we move down we also wanted to update say February for

Investments and so on and so forth across the whole sheet so some apps formula is going to allow us to do that now the sum range for us is all of the amounts so under the inputs tab we’re simply going to select a whole column so just click on that F there and you can see that

that’s going to start to show up then one important thing we need to do is set the F4 key you’ll notice that’s going to put some dollar signs in front of the letter that basically says that hey as we move this along across all of the different months this column is going to stay

fixed here which is what we want we don’t want it moving along or there’s not going to be any numbers hit the comma key there and here it’s going to ask for the criteria range so the first criteria is that we want of this one to be equal to the month that we’re seeing in the

dashboard tab so this is the whole range for us the column C hit the F4 key again comma and then the criteria number one let’s go back to the dashboard there is first that we want it to be in the month of January now for this we actually do want it to move to the side but just we don’t

want it to go down when we copy the formula across so we’re actually going to press the F4 key not once but actually hit it twice there like so hit the comma key and now we have the second criteria range which we want it to be under the Income right so in this case the base

salary so under inputs we’re gonna go to the categories which is what we want here hit the F4 key again comma and then the criteria number two for us let’s go back to the dashboard tab it’s actually just gonna be the base salary and again we need to lock this one correctly so

press the F4 key three times on this occasion such that it’s only on the column then we can just close that formula and hit enter and so we have for January a base salary of 3500 and let’s move this across so go to shift and then right arrow all the way to the very end there and

you’re just going to press Ctrl r that should fill it across and then shift down arrow and just press Ctrl d we’ll do the same thing with the expenses so let’s just copy that first one Ctrl C and then from here go to shift down arrow and shift right and then go to control V and

you can see that all the data is gonna start to Auto populate and you remember in September we went ahead and added um 300 in Investments in stock Dividends now you can see that that’s showing over here now that we have all the numbers updating dynamically

let’s work to calculate the totals so for the total Income it’s just gonna be the sum of these so we’ll use a shortcut by hitting shift right arrow all the way to the end there and ALT equals like so same thing goes for the expenses below so shift right arrow and

hit alt equals and for the Savings it’s simply the total Income minus the total expenses hit enter and let’s press shift right arrow to move that across and then Ctrl r Aid then we gotta work on the totals over here which are simply the same thing so

it’s just some hit the top key there and it’s going to be the sum of all of these and hit enter let’s drag that down so Ctrl C and then shift down arrow and we only want to paste the formula not the format because it’s going to mess up this bottom format here for this just

go to control alt V and we only want to paste the formulas so we’re just going to select that second one as you can see you can just go down arrow for that hit okay there that’s looking like it this last part for the month for month growth we don’t want so we’ll just delete

same thing goes over here let’s copy that one again and then shift down arrow all the way to here and then Ctrl alt V and formulas again so down our ones and hit enter there and this very last one we’re just gonna equal sum and let’s just select them like so close those brackets

and hit enter and speaking of excel models if you’re liking this CashNews.co you can also check out our range of courses which include Excel PowerPoint Finance

eValuation as well as bundle packages and what makes our courses different is that they’re all applied to the real world so aside from teaching the theory our lessons also offer case studies that simulate the type of work you might be assigned in our day-to-day ranging from

creating a financial model on Excel to creating a slight deck presentation on PowerPoint and if you get stuck along the way you can easily ask us questions in the discussions forum so if you’re interested in checking it out go to the link in the description below alright back to the

CashNews.co another thing that would be nice to know is our growth Trends so is the Income progressing as a year goes on how about the expenses Etc now to do that we’re going to calculate the month-to-month percentage growth over here so for February we’re just gonna go

to equals the total Income in February divided by the total total Income in January minus one hit enter there and then go to shift right arrow as we drag that along and Ctrl R but you’ll notice here towards the end that we start to get this error sign

that’s basically because we have no data so let’s actually set up a conditional formula here that’s going to allow us to to still work with all of this stuff so at the very beginning here we’re just going to create an if statement once you see that if hit the top key there

The Logical test is basically if this cell over here equals zero meaning that there’s nothing in it hit the comma key if that’s the case then we actually don’t want anything so we’re just gonna put two quotations like that that basically tells Excel that we don’t want

anything and if it’s not the case meaning that there is something then we just want to leave it as is because we want to calculate the percentage close those brackets and then just hit enter there and then when we go to shift right arrow and Ctrl R you’ll notice that we don’t have

all of these error signs here anymore then we can just copy this formula down so Ctrl C down over here we’re just gonna go to control alt V and again we only want to paste formulas same thing over here so shift right arrow and then Ctrl alt V formulas only hit enter moving on to step 3 where

we’ll focus on the visuals so first up under the month-to-month growth we can see here that maybe for the positive percentages we can color them in green and red for the negative ones to do so first let’s select them so select the entire row Ctrl shift right there then we’ll just

go to conditional formatting and then we’re going to create highlight cell rules greater than so whenever it’s greater than zero we want it to be in green so go on the drop down go to custom format we want to select the font color to be green so let’s go for this bright green like

so and then just hit OK there hit OK again now you can see that we have it in Green for the positives and let’s work on the negatives now so conditional formatting highlights and rules less than so less than zero we want them to be in Red so the red text is actually this one over here and hit

OK now we just need to replicate this for the Savings and the total expenses so let’s select it Ctrl C then we’re just going to paste data over here under the Savings first so Ctrl V there and let’s change the coloring there so go to alt page H

we’re just going to select the yellow color that I used to have and hit enter there now for the expenses the reason I didn’t do it just yet is because it’s slightly different in this case if it’s a negative that’s actually a good thing in that you spent less money

right so we’re actually going to create some custom conditional formattings so go to Ctrl shift right there under conditional formatting this time it’s going to be the reverse so for greater than it’s going to be when it’s greater than zero that’s going to be a

negative thing so we’re going to put that in red text hit okay and then when it’s less than zero it’s a positive thing so conditional formatting again let’s head rules less than zero and we’re gonna put those in green so under custom format font we’re gonna

select the green like so and hit OK hit OK again and that makes sense in that whenever the expenses go down that’s gonna be a positive thing now for the rest of the visuals if you keep scrolling down over here you can see that we have the Income breakdown and the expenses

breakdown so we want to essentially create a pie chart for it first we’ll select all of the Income types so shift down arrow make sure you press the Ctrl key there and we’re just going to select the year to date totals so all of these here then go to insert and then we

can go on the recommended charts and it should be that second one for you hit okay there let’s work on reformatting it a bit don’t really need that title so we’ll remove that the legend here let’s say we want to put it on the left hand side so go to right click format Legend

and let’s say we put that towards the left and for the colors it’s as simple as selecting one so make sure only one is selected there and under format we’re just gonna change the shape fill to say a dark blue like so let me fast forward how I change the colors of these other ones

let’s suppose we’ll help you with these colors then we want to remove that border as we’re going to put it to the bottom there so Phil we actually don’t want a fill and we don’t want a boiler either so no line let’s go ahead and drag that down and one final thing

you can do here is once you select them go to right click and add data labels that’s going to give you the labels like so you can’t really see some of them you can go ahead and select it change the color of it to White say same thing over here let’s say we change that to a white

let me fast forward how I do the same thing for the expenses as you already know how to do it great now with this budget tracker we just built in the inputs we can always add any Income or expense and then over on the dashboard we’re gonna be able to see the monthly breakdown

alongside the growth Trends and some visuals to support it for more on Excel check out this CashNews.co over here to create a stock Portfolio dashboard or this other link over here to take some of our courses hit the like and that subscribe and I’ll catch you in the next

one