in this video 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 video 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 video 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 video 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
video 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