馃攳
Build a Dynamic Budget vs Actuals Dashboard on Excel (Variance Analysis) - YouTube
Channel: Kenji Explains
[0]
in this video we'll create an interactive聽
budget versus actuals dashboard that's going to聽聽
[4]
dynamically update based on our inputs and it's聽
going to tell us the variance with supporting聽聽
[9]
charts and visuals and thank you to the daily聽
upside a free business and finance newsletter聽聽
[14]
for sponsoring this video for the dashboard we're聽
going to need two input tabs one being the actuals聽聽
[20]
and the other one being the budget so let's look聽
into it so over here we've got the three different聽聽
[24]
sheets and we've got the budget one out here which聽
is basically all of the income that you might have聽聽
[29]
that being your salary your bonus and potentially聽
a side hustle of sorts that's a total income and聽聽
[34]
just below that we have all the different expenses聽
which are pretty much the standard expenses you聽聽
[38]
might have in your day-to-day below that we've聽
got the savings over here which is simply the聽聽
[42]
total income minus the total expenses and just聽
below that we've got the cumulative savings聽聽
[47]
which is essentially the current month savings聽
plus any of the previous months in the year聽聽
[52]
that's a budget side which looks nice and clean聽
because well it's just a plan for the actuals聽聽
[56]
it's a bit messier let's look into it over here聽
we've got all of the actuals and we've got sorted聽聽
[61]
by the date the month the category which are all聽
of these categories that we've got over here the聽聽
[66]
description so what it actually was and then the聽
amount after that if you go all the way down to聽聽
[71]
the bottom by pressing ctrl down arrow you'll see聽
we have up until may over here now you'll notice聽聽
[75]
that we can't actually see the titles anymore聽
so let's go back all the way to the top here and聽聽
[79]
what we're going to do is we're going to freeze聽
panes so we'll just select this column over here聽聽
[83]
by pressing the shift space from there go under聽
the view tab under freeze panes we want to just聽聽
[88]
click on freeze panes there the idea now is that聽
when you scroll down all the way to the bottom聽聽
[92]
here you're still going to have the title so that聽
you can see what thing is going to be for what聽聽
[96]
now that we understand the different inputs let's聽
start working on the output dashboard so go to聽聽
[101]
control page up all the way to the top here and聽
firstly we want to have a drop down that's going聽聽
[105]
to allow us to select which month we're in so聽
for that we'll just type query month over here聽聽
[110]
and then we can just put a sign like so and for聽
here we're first going to format it so go to alt聽聽
[115]
hba which is going to give you the all outside聽
borders like so ctrl b to bolden it and then聽聽
[120]
let's highlight the box so go to alt hh and i'm聽
just going to select this yellowish color over聽聽
[126]
here like so then what we want to do is a data聽
validation which is going to be the drop down list聽聽
[131]
go to alt avv for that that's going to be the聽
data validation we want to select a list and聽聽
[137]
the source we want to select all the different聽
months and we actually have them laid out over聽聽
[141]
here under the budget so let's go ahead and select聽
them ctrl shift right arrow there and we'll select聽聽
[146]
all the months up until december hit okay and now聽
over here when we look at it you can see that we聽聽
[152]
have all the different months let's put the month聽
of april for now now to get the budget versus the聽聽
[157]
actuals we need all the different line items so聽
for this go to control page down and we're just聽聽
[161]
going to copy and paste them from up here so we've聽
got all the way to the total expenses control c聽聽
[167]
and we're just going to paste it like so then we聽
want to add two more titles up here one that's聽聽
[171]
gonna say that's the budget and the other one聽
that's gonna say that's the actual now you can't聽聽
[175]
see them it's probably because they're in white聽
font so go to alt h we're just gonna go ahead and聽聽
[180]
select a dark blue color as the background from聽
there we're gonna need some sort of a formula聽聽
[185]
that's gonna say that hey if it's currently the聽
this line item so the base salary under the budget聽聽
[190]
and if it's currently in the month of april then聽
we want the number for that so for this we're聽聽
[195]
gonna do an index match so we'll go equals index聽
press the tab key the array go to control page聽聽
[201]
down it's gonna be this whole range over here聽
let's go ahead and select everything up until聽聽
[206]
the total expenses press the f4 key which is gonna聽
allow us to lock it meaning that when we copy down聽聽
[211]
it's going to stay fixed on that table which is聽
what we want press the comma key then it's going聽聽
[216]
to ask for the row number so for us that's going聽
to be the base salary side we'll go match that's聽聽
[220]
the next function that we're going to use press聽
the tab key the lookup value go to control page up聽聽
[226]
it's going to be the base salary because that's聽
what we're interested in press the comma key聽聽
[230]
the lookup array is going to be the whole聽
range where you have these which is going to be聽聽
[234]
this range over here up until total expenses press聽
the f4 key there comma and the match type we want聽聽
[240]
the exact match so put a zero there close those聽
brackets press the comma key and now we need to聽聽
[244]
match by the month date in this case we want the聽
month of april so we'll go match again the lookup聽聽
[249]
value in this case like we mentioned is going to聽
be the current month of april press the f4 key聽聽
[254]
comma and then the lock-up array control page聽
down is going to be this whole range over here聽聽
[260]
from figures all the way to december press the聽
f4 key comma and we want an exact match so that's聽聽
[266]
just going to be a zero close those brackets聽
and then close the brackets again and hit enter聽聽
[270]
nice that's giving us a base salary of three聽
thousand in the month of april if you go control聽聽
[275]
page down to try to verify this so in the march of聽
april it's up three thousand for the base salary聽聽
[280]
and this is the month so it's looking like it's聽
correct now looking at the actuals and this is聽聽
[284]
going to be slightly different that's because some聽
line items are actually duplicated in the actuals聽聽
[289]
for example if you go to control page down all the聽
way to the actions here you'll notice that we have聽聽
[294]
leisure three times in the same month of january聽
and that makes sense after all maybe you had some聽聽
[298]
fun activities three times in a row and so we need聽
to do some sort of a different formula because聽聽
[304]
a match is not gonna work because it's only gonna聽
match for one instead we're gonna need to sum them聽聽
[308]
so for this we can use a sumifs equals some ifs聽
press the tab key the submarines go to control聽聽
[315]
page down all the way to the bottom here is聽
gonna be this whole f column the reason we聽聽
[319]
want the whole column instead of just some of聽
the numbers or all of the numbers inside it聽聽
[323]
is because whenever we want to add a new row like聽
we have a new actual new expenditure or something聽聽
[327]
we want it to be able to account for that so聽
that's why we select the whole column there聽聽
[331]
press the comma key the criteria range number聽
one first we want to select it by the category聽聽
[336]
so that we want to make sure that this聽
category press the comma key and the criteria 1聽聽
[341]
control page up is actually going to be equals to聽
this figure that we've got over here or this item聽聽
[347]
in this case it's a base salary for now press聽
the comma key and the criteria range number two聽聽
[352]
go to control page down is the month so we want聽
to select that column c here press the comma key聽聽
[357]
and the criteria number two go to control聽
page up is that it's in this query month聽聽
[361]
that we've got up over here press the f4 key聽
for that one to lock it close those brackets聽聽
[366]
and hit enter nice from there we can go ahead聽
and copy these so ctrl c just drag them down all聽聽
[371]
the way to total expenses ctrl v then we want to聽
sort some of these so we'll delete these over here聽聽
[376]
delete and we'll delete delete these two over here聽
as well because for these they're just going to be聽聽
[381]
the sum so it's just going to be total income聽
is the sum of all three income select these two聽聽
[385]
first so we'll go shift right arrow and then聽
alt equals that's gonna do the formula for us聽聽
[390]
same thing over here select the two then alt聽
equals that's going to do it for us let's聽聽
[394]
reformat them so go to control b and then alt聽
h b p that's going to give us the top border聽聽
[400]
same thing over here control b alt h b p for the聽
top border with these formulas it should be fully聽聽
[406]
dynamic so let's suppose we change the current聽
month to say the month of january then you'll聽聽
[410]
notice that all the figures change for us and same聽
thing if you go to any of the other months too聽聽
[415]
and speaking of budgeting if you're looking聽
to learn more about business and investing聽聽
[419]
the daily upside is a free newsletter with over聽
330 000 subscribers myself included it was founded聽聽
[426]
by a former investment banking vp with the goal聽
of educating and empowering individual investors聽聽
[432]
every weekday before the markets open they deliver聽
a morning brief followed by more detailed stories聽聽
[439]
that are shaping the business world directly to聽
your inbox plus on the weekends they publish a聽聽
[444]
deep dive newsletter going over business trends聽
in more detail it's actually what i enjoy reading聽聽
[450]
the most for example they recently did a piece聽
on activist investing explaining what it is who聽聽
[455]
the major players are and the companies that are聽
currently under pressure from activist investors聽聽
[461]
so if you're looking to stay on top of the latest聽
business and investing news click the link in the聽聽
[466]
description to sign up it's completely free and聽
if you don't like it you can always unsubscribe聽聽
[472]
alright back to budgeting now that we've got聽
both the income and the expenses let's go ahead聽聽
[477]
and add the savings which we forgot to do so so聽
we'll go down over here and we're just going to聽聽
[481]
type savings and it's just going to be equals聽
to the total income minus the total expenses聽聽
[487]
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聽聽
[493]
top and the bottom border and then alt h and we'll聽
select the yellowish color that we've got over聽聽
[499]
here one thing you'll notice though is that it's聽
a bit hard to tell between whether the budget is聽聽
[503]
higher or the actual is higher and by how much for聽
this we can do a variance over here so we're gonna聽聽
[509]
do a variance dot ops meaning for an absolute and聽
then we're gonna do a variance percentage as well聽聽
[516]
here and we're gonna put this in blue alt-h and聽
let's go ahead and click over here press enter聽聽
[523]
and for the variances it's just gonna be once聽
just gonna be the regular difference while the聽聽
[527]
second one is going to be on a relative basis聽
so it's gonna be one figure divided by the other聽聽
[532]
minus one so let's look into it firstly聽
for the absolute we'll go equals the actual聽聽
[537]
minus the budget hit enter and it's obviously聽
no difference here because it's just聽聽
[542]
both three thousand same thing with the variance聽
percentage we'll go actuals divided by the budget聽聽
[548]
minus one that's going to give us a percentage聽
for us then we can just drag all of these down聽聽
[553]
all the way to the bottom over here for these聽
that don't have anything we can just delete them聽聽
[557]
so just press delete there same thing over here聽
just press delete for the expensive side you'll聽聽
[561]
notice under utilities here that our actual is聽
actually higher than the budget and it's showing聽聽
[566]
a positive figure which doesn't quite make so聽
much sense it means that we spent more than we had聽聽
[570]
anticipated but it seems like it's a good thing聽
here so instead we're gonna flip the signs聽聽
[575]
so we'll go to press the f2 key here and we're聽
gonna go c first and then d after that hit enter聽聽
[581]
same thing over here press the f2 key we're gonna聽
put ac first and then a d after that hit enter聽聽
[588]
we'll go select these ctrl c and just drag them聽
down all the way till here this way if we have um聽聽
[594]
we spent more than we actually anticipated it's聽
a negative value and if it's the opposite it's聽聽
[598]
positive which i think is more intuitive let's go聽
ahead and format these so ctrl b and then alt h聽聽
[604]
bp for the top border same thing over here and聽
down over here we're gonna format it in this other聽聽
[610]
method so alt h b d and then alt h and we'll聽
select the yellow color here hit enter ctrl b聽聽
[618]
for the variances we can take it a step further聽
and do some kind of a conditional formatting聽聽
[622]
so it stands out more if it's a positive聽
figure or a negative one so for this first聽聽
[626]
let's go ahead and select this whole area so聽
ctrl shift down arrow and then ctrl shift right聽聽
[631]
arrow and you'll notice there's this conditional聽
formatting table here and you've got a ton of聽聽
[635]
different options so you can do a data bar like聽
so you can see there what it kind of looks like聽聽
[640]
um we can do icons so you can do up down arrow聽
etc but we're gonna do instead just highlight聽聽
[645]
cell rules which is quite a simple thing to do聽
and so if it's greater than if it's a positive聽聽
[651]
figure so we'll put greater than zero then we're聽
gonna wanna just go ahead under the custom format聽聽
[656]
to the color here and we're gonna select the聽
green color hit okay nice and then we want to聽聽
[662]
go okay there and we want to do one for smaller聽
than as well so highlight cell rules less than聽聽
[668]
zero and we're going to want a red color for that聽
one so we'll go under this here then custom format聽聽
[675]
we're going to want a say sharp red color like聽
so hit okay hit okay again and now for the zeros聽聽
[681]
we're going to have it in black for the gree聽
for the greens the positives and then for the聽聽
[685]
reds is going to be the negatives this makes it聽
a lot easier for people to interpret now that聽聽
[690]
the dashboard is looking better let me show you聽
how you can add new items and how that's going to聽聽
[694]
dynamically change the model so let's go under the聽
actuals tab and over here go to the very bottom by聽聽
[699]
pressing the control down arrow and let's say聽
under the 29th so we're going to go 5-29-2822聽聽
[706]
press the tab key and it's going to be for the聽
month of may you'll notice that we have a formula聽聽
[710]
here the text side is going to give us essentially聽
reformat it however we want and then we're going聽聽
[716]
to select what we want formatted which is the date聽
and we want to change it to month format so for聽聽
[720]
that we put these four m's like so hit escape聽
to get out of that then for the category let's聽聽
[725]
say this is a new side hustle that we found we'll聽
select that and this is gonna be for say lemonade聽聽
[731]
we made a lemonade stand so let's go ahead and put聽
that here and let's say we made one thousand one聽聽
[736]
thousand dollars of that so now that we have all聽
of this inputted we'll go back to the dashboard聽聽
[741]
we'll change the month to the month of may hit聽
enter there and you'll notice that under side聽聽
[746]
hustle now it's showing 1366. if we go back down聽
control page down there that's going to be the sum聽聽
[752]
of the 366 we had already plus the 1000 that we聽
just added one final automation we could do to the聽聽
[760]
dashboard is that whenever you open up the file聽
it's going to be updated to the current month so聽聽
[765]
the month that you're actually in for this we can聽
use the today function so we'll go equals today聽聽
[770]
i'm just going to show you here close the brackets聽
hit enter and that's gonna give me today's date聽聽
[774]
if i open the file tomorrow it's gonna be updated聽
to tomorrow's day hit press delete there and we're聽聽
[779]
gonna have to combine these functions so we'll聽
go equals we'll type text press the tab key and聽聽
[784]
the value here is gonna be the today function hit聽
enter hit tab sorry close those brackets press the聽聽
[789]
comma key and here we're gonna put in quotations聽
the m four times which is gonna represent the聽聽
[794]
month close the brackets and hit enter now聽
you can see that it's in the month of april聽聽
[800]
this way whenever you open the model it's聽
going to be updated to the current month聽聽
[804]
once we have this working the way that we want it聽
we can actually start adding some charts over here聽聽
[808]
so let's say we call this something like budget聽
versus actual income hit enter there and we're聽聽
[815]
just gonna go under the insert let's go ahead and聽
select these over here so we're gonna select these聽聽
[820]
three these three line items and we're gonna go聽
on the recommended charts and we're fine with聽聽
[824]
the first cluster column hit okay from there we're聽
gonna reformat it such that this is actually the聽聽
[829]
title so we're gonna select these three go to聽
alt h m c that's gonna merge and center for us聽聽
[835]
then we're gonna go ctrl b alt h and we wanna聽
select a dark blue background like so hit enter聽聽
[842]
alt hfc and we want to select a white font color聽
we want to delete that chart title because this is聽聽
[848]
going to be this title up here we want to get rid聽
of those grid lines as well so we'll click on them聽聽
[853]
hit delete for the legend we want to label this so聽
go right click and we're going to go under select聽聽
[858]
data the first series is actually going to put聽
edit there and we want to name that series the聽聽
[864]
budget hit ok and then for the second series聽
go to edit we want to name that one the action聽聽
[873]
hit okay and then hit ok if you want to change聽
the colors it's as simple as just selecting the聽聽
[879]
columns there and we're going to go down over聽
here select the dark blue first and then for a聽聽
[884]
second one let's say we go with a light blue color聽
like so refit this so we're just gonna bring it聽聽
[890]
down a bit kind of like that and let's also get聽
rid of these borders so we'll right click format聽聽
[895]
chart area and under the border we're gonna say聽
no line so that's gonna give no no outside border聽聽
[900]
as you can see over here lastly it'd be聽
nice to add some labels so go to right click聽聽
[904]
and we're gonna go at labels same thing over聽
here right click and add data labels you'll聽聽
[910]
notice that the labels seem to overlap with聽
each other so we can change the gap width there聽聽
[914]
we can select something like say 100 hit enter聽
and now you can see that things look a bit better聽聽
[920]
now this is just one type of chart maybe for聽
the expenses it makes more sense to do some聽聽
[924]
kind of a pie chart say so we might select all聽
of the items here so pressing shift down arrow聽聽
[929]
and by pressing the ctrl and you can click聽
on all of these actual figures over here聽聽
[933]
we can go to the insert say we insert a 2d pie聽
chart now here you could be able to see the the聽聽
[939]
breakdown of where the expenses are going and here聽
it seems like the rent is actually the highest聽聽
[944]
expenditure that you have while for this聽
example we focused on a personal finance budget聽聽
[949]
this is also applicable to a company's budget聽
but instead of having groceries say you would聽聽
[953]
change the line item to marketing for instance聽
for more on excel you can check out our free聽聽
[958]
playlist over here or you can see some of our聽
courses over here that's all for this video聽聽
[963]
hit that like hit that subscribe if you聽
liked it and i'll catch you in the next one
Most Recent Videos:
You can go back to the homepage right here: Homepage