in this video we'll create an interactive budget versus actuals dashboard that's going to
dynamically update based on our inputs and it's going to tell us the variance with supporting
charts and visuals and thank you to the daily upside a free business and finance newsletter
for sponsoring this video for the dashboard we're going to need two input tabs one being the actuals
and the other one being the budget so let's look into it so over here we've got the three different
sheets and we've got the budget one out here which is basically all of the income that you might have
that being your salary your bonus and potentially a side hustle of sorts that's a total income and
just below that we have all the different expenses which are pretty much the standard expenses you
might have in your day-to-day below that we've got the savings over here which is simply the
total income minus the total expenses and just below that we've got the cumulative savings
which is essentially the current month savings plus any of the previous months in the year
that's a budget side which looks nice and clean because well it's just a plan for the actuals
it's a bit messier let's look into it over here we've got all of the actuals and we've got sorted
by the date the month the category which are all of these categories that we've got over here the
description so what it actually was and then the amount after that if you go all the way down to
the bottom by pressing ctrl down arrow you'll see we have up until may over here now you'll notice
that we can't actually see the titles anymore so let's go back all the way to the top here and
what we're going to do is we're going to freeze panes so we'll just select this column over here
by pressing the shift space from there go under the view tab under freeze panes we want to just
click on freeze panes there the idea now is that when you scroll down all the way to the bottom
here you're still going to have the title so that you can see what thing is going to be for what
now that we understand the different inputs let's start working on the output dashboard so go to
control page up all the way to the top here and firstly we want to have a drop down that's going
to allow us to select which month we're in so for that we'll just type query month over here
and then we can just put a sign like so and for here we're first going to format it so go to alt
hba which is going to give you the all outside borders like so ctrl b to bolden it and then
let's highlight the box so go to alt hh and i'm just going to select this yellowish color over
here like so then what we want to do is a data validation which is going to be the drop down list
go to alt avv for that that's going to be the data validation we want to select a list and
the source we want to select all the different months and we actually have them laid out over
here under the budget so let's go ahead and select them ctrl shift right arrow there and we'll select
all the months up until december hit okay and now over here when we look at it you can see that we
have all the different months let's put the month of april for now now to get the budget versus the
actuals we need all the different line items so for this go to control page down and we're just
going to copy and paste them from up here so we've got all the way to the total expenses control c
and we're just going to paste it like so then we want to add two more titles up here one that's
gonna say that's the budget and the other one that's gonna say that's the actual now you can't
see them it's probably because they're in white font so go to alt h we're just gonna go ahead and
select a dark blue color as the background from there we're gonna need some sort of a formula
that's gonna say that hey if it's currently the this line item so the base salary under the budget
and if it's currently in the month of april then we want the number for that so for this we're
gonna do an index match so we'll go equals index press the tab key the array go to control page
down it's gonna be this whole range over here let's go ahead and select everything up until
the total expenses press the f4 key which is gonna allow us to lock it meaning that when we copy down
it's going to stay fixed on that table which is what we want press the comma key then it's going
to ask for the row number so for us that's going to be the base salary side we'll go match that's
the next function that we're going to use press the tab key the lookup value go to control page up
it's going to be the base salary because that's what we're interested in press the comma key
the lookup array is going to be the whole range where you have these which is going to be
this range over here up until total expenses press the f4 key there comma and the match type we want
the exact match so put a zero there close those brackets press the comma key and now we need to
match by the month date in this case we want the month of april so we'll go match again the lookup
value in this case like we mentioned is going to be the current month of april press the f4 key
comma and then the lock-up array control page down is going to be this whole range over here
from figures all the way to december press the f4 key comma and we want an exact match so that's
just going to be a zero close those brackets and then close the brackets again and hit enter
nice that's giving us a base salary of three thousand in the month of april if you go control
page down to try to verify this so in the march of april it's up three thousand for the base salary
and this is the month so it's looking like it's correct now looking at the actuals and this is
going to be slightly different that's because some line items are actually duplicated in the actuals
for example if you go to control page down all the way to the actions here you'll notice that we have
leisure three times in the same month of january and that makes sense after all maybe you had some
fun activities three times in a row and so we need to do some sort of a different formula because
a match is not gonna work because it's only gonna match for one instead we're gonna need to sum them
so for this we can use a sumifs equals some ifs press the tab key the submarines go to control
page down all the way to the bottom here is gonna be this whole f column the reason we
want the whole column instead of just some of the numbers or all of the numbers inside it
is because whenever we want to add a new row like we have a new actual new expenditure or something
we want it to be able to account for that so that's why we select the whole column there
press the comma key the criteria range number one first we want to select it by the category
so that we want to make sure that this category press the comma key and the criteria 1
control page up is actually going to be equals to this figure that we've got over here or this item
in this case it's a base salary for now press the comma key and the criteria range number two
go to control page down is the month so we want to select that column c here press the comma key
and the criteria number two go to control page up is that it's in this query month
that we've got up over here press the f4 key for that one to lock it close those brackets
and hit enter nice from there we can go ahead and copy these so ctrl c just drag them down all
the way to total expenses ctrl v then we want to sort some of these so we'll delete these over here
delete and we'll delete delete these two over here as well because for these they're just going to be
the sum so it's just going to be total income is the sum of all three income select these two
first so we'll go shift right arrow and then alt equals that's gonna do the formula for us
same thing over here select the two then alt equals that's going to do it for us let's
reformat them so go to control b and then alt h b p that's going to give us the top border
same thing over here control b alt h b p for the top border with these formulas it should be fully
dynamic so let's suppose we change the current month to say the month of january then you'll
notice that all the figures change for us and same thing if you go to any of the other months too
and speaking of budgeting if you're looking to learn more about business and investing
the daily upside is a free newsletter with over 330 000 subscribers myself included it was founded
by a former investment banking vp with the goal of educating and empowering individual investors
every weekday before the markets open they deliver a morning brief followed by more detailed stories
that are shaping the business world directly to your inbox plus on the weekends they publish a
deep dive newsletter going over business trends in more detail it's actually what i enjoy reading
the most for example they recently did a piece on activist investing explaining what it is who
the major players are and the companies that are currently under pressure from activist investors
so if you're looking to stay on top of the latest business and investing news click the link in the
description to sign up it's completely free and if you don't like it you can always unsubscribe
alright back to budgeting now that we've got both the income and the expenses let's go ahead
and add the savings which we forgot to do so so we'll go down over here and we're just going to
type savings and it's just going to be equals to the total income minus the total expenses
and same thing over here so ctrl v and we're just going to reformat this so go to alt h b d for the
top and the bottom border and then alt h and we'll select the yellowish color that we've got over
here one thing you'll notice though is that it's a bit hard to tell between whether the budget is
higher or the actual is higher and by how much for this we can do a variance over here so we're gonna
do a variance dot ops meaning for an absolute and then we're gonna do a variance percentage as well
here and we're gonna put this in blue alt-h and let's go ahead and click over here press enter
and for the variances it's just gonna be once just gonna be the regular difference while the
second one is going to be on a relative basis so it's gonna be one figure divided by the other
minus one so let's look into it firstly for the absolute we'll go equals the actual
minus the budget hit enter and it's obviously no difference here because it's just
both three thousand same thing with the variance percentage we'll go actuals divided by the budget
minus one that's going to give us a percentage for us then we can just drag all of these down
all the way to the bottom over here for these that don't have anything we can just delete them
so just press delete there same thing over here just press delete for the expensive side you'll
notice under utilities here that our actual is actually higher than the budget and it's showing
a positive figure which doesn't quite make so much sense it means that we spent more than we had
anticipated but it seems like it's a good thing here so instead we're gonna flip the signs
so we'll go to press the f2 key here and we're gonna go c first and then d after that hit enter
same thing over here press the f2 key we're gonna put ac first and then a d after that hit enter
we'll go select these ctrl c and just drag them down all the way till here this way if we have um
we spent more than we actually anticipated it's a negative value and if it's the opposite it's
positive which i think is more intuitive let's go ahead and format these so ctrl b and then alt h
bp for the top border same thing over here and down over here we're gonna format it in this other
method so alt h b d and then alt h and we'll select the yellow color here hit enter ctrl b
for the variances we can take it a step further and do some kind of a conditional formatting
so it stands out more if it's a positive figure or a negative one so for this first
let's go ahead and select this whole area so ctrl shift down arrow and then ctrl shift right
arrow and you'll notice there's this conditional formatting table here and you've got a ton of
different options so you can do a data bar like so you can see there what it kind of looks like
um we can do icons so you can do up down arrow etc but we're gonna do instead just highlight
cell rules which is quite a simple thing to do and so if it's greater than if it's a positive
figure so we'll put greater than zero then we're gonna wanna just go ahead under the custom format
to the color here and we're gonna select the green color hit okay nice and then we want to
go okay there and we want to do one for smaller than as well so highlight cell rules less than
zero and we're going to want a red color for that one so we'll go under this here then custom format
we're going to want a say sharp red color like so hit okay hit okay again and now for the zeros
we're going to have it in black for the gree for the greens the positives and then for the
reds is going to be the negatives this makes it a lot easier for people to interpret now that
the dashboard is looking better let me show you how you can add new items and how that's going to
dynamically change the model so let's go under the actuals tab and over here go to the very bottom by
pressing the control down arrow and let's say under the 29th so we're going to go 5-29-2822
press the tab key and it's going to be for the month of may you'll notice that we have a formula
here the text side is going to give us essentially reformat it however we want and then we're going
to select what we want formatted which is the date and we want to change it to month format so for
that we put these four m's like so hit escape to get out of that then for the category let's
say this is a new side hustle that we found we'll select that and this is gonna be for say lemonade
we made a lemonade stand so let's go ahead and put that here and let's say we made one thousand one
thousand dollars of that so now that we have all of this inputted we'll go back to the dashboard
we'll change the month to the month of may hit enter there and you'll notice that under side
hustle now it's showing 1366. if we go back down control page down there that's going to be the sum
of the 366 we had already plus the 1000 that we just added one final automation we could do to the
dashboard is that whenever you open up the file it's going to be updated to the current month so
the month that you're actually in for this we can use the today function so we'll go equals today
i'm just going to show you here close the brackets hit enter and that's gonna give me today's date
if i open the file tomorrow it's gonna be updated to tomorrow's day hit press delete there and we're
gonna have to combine these functions so we'll go equals we'll type text press the tab key and
the value here is gonna be the today function hit enter hit tab sorry close those brackets press the
comma key and here we're gonna put in quotations the m four times which is gonna represent the
month close the brackets and hit enter now you can see that it's in the month of april
this way whenever you open the model it's going to be updated to the current month
once we have this working the way that we want it we can actually start adding some charts over here
so let's say we call this something like budget versus actual income hit enter there and we're
just gonna go under the insert let's go ahead and select these over here so we're gonna select these
three these three line items and we're gonna go on the recommended charts and we're fine with
the first cluster column hit okay from there we're gonna reformat it such that this is actually the
title so we're gonna select these three go to alt h m c that's gonna merge and center for us
then we're gonna go ctrl b alt h and we wanna select a dark blue background like so hit enter
alt hfc and we want to select a white font color we want to delete that chart title because this is
going to be this title up here we want to get rid of those grid lines as well so we'll click on them
hit delete for the legend we want to label this so go right click and we're going to go under select
data the first series is actually going to put edit there and we want to name that series the
budget hit ok and then for the second series go to edit we want to name that one the action
hit okay and then hit ok if you want to change the colors it's as simple as just selecting the
columns there and we're going to go down over here select the dark blue first and then for a
second one let's say we go with a light blue color like so refit this so we're just gonna bring it
down a bit kind of like that and let's also get rid of these borders so we'll right click format
chart area and under the border we're gonna say no line so that's gonna give no no outside border
as you can see over here lastly it'd be nice to add some labels so go to right click
and we're gonna go at labels same thing over here right click and add data labels you'll
notice that the labels seem to overlap with each other so we can change the gap width there
we can select something like say 100 hit enter and now you can see that things look a bit better
now this is just one type of chart maybe for the expenses it makes more sense to do some
kind of a pie chart say so we might select all of the items here so pressing shift down arrow
and by pressing the ctrl and you can click on all of these actual figures over here
we can go to the insert say we insert a 2d pie chart now here you could be able to see the the
breakdown of where the expenses are going and here it seems like the rent is actually the highest
expenditure that you have while for this example we focused on a personal finance budget
this is also applicable to a company's budget but instead of having groceries say you would
change the line item to marketing for instance for more on excel you can check out our free
playlist over here or you can see some of our courses over here that's all for this video
hit that like hit that subscribe if you liked it and i'll catch you in the next one