Build a Dynamic Budget vs Actuals Dashboard on Excel (Variance Analysis)

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

Channel: Kenji Explains

Views: 722,982

Keywords: excel dashboard, budget vs actuals excel, budget vs actual excel, budget vs actual, variance analysis, variance, budget variance, excel budget dashboard, excel dynamic dashboard, forecast vs actual, personal finance budget dashboard, excel budget template, automate your budget, index match budget, sumifs budget, budget vs actuals comparison, create a professional budget tracker, budget tracker, conditional formatting, track budget on excel, excel budget vs actual

Id: 9uFKAwIa3V0

Length: 16min 8sec (968 seconds)

Published: Sun Apr 17 2022