Excel Dashboard - Plan vs Actual Variances - FREE Download

hi i'm minda tracy from my online training hub i'll be taking you through how to use excel's built-in tools to build this actual versus plan dashboard you see behind me before we get started i just want to set your expectations for this tutorial first it's going to be at a fast pace you won't have time to follow along step by step the first time you watch it but there's a link in the video description where you can download the workbook and data and of course you can re-watch the video as many times as you like pausing and rewinding as required now i'm using microsoft 365 but this dashboard can be built in all versions of excel from 2010 onward now some of the steps may appear advanced but i promise you none of it's difficult and there's absolutely no programming involved okay let's get started i'll be using a modified version of some it spend data from obvious you can see it's in the perfect tabular layout which is suitable for pivot tables and is broken down by month business area location t area cost elements and then we have values for actual forecast and plan now there are over 81 000 rows of data representing 12 months for the plan and forecast now the actual values are currently only up to august but on the future data sheet you can see we have data for september through to december and i'll use that at the end to demonstrate how easy it is to update your dashboards when you use the techniques that i'm going to cover in this tutorial now the first important step is ensuring that the source data for all your analysis and charts is formatted in an excel table and we can do this with the keyboard shortcut ctrl t it's asking me if my table has headers that's the column labels and yes it does so i'll click ok you can see now that it's formatted in a table because it displays the default banded row formatting and the column headers now have filter buttons and if we look on the table design tab you can see the table name is table 1. i'm going to rename that data and just press enter now before we start the analysis let's take a look at the dashboard so we can see what pivot tables we need at the top of the dashboard we have some headline figures that provide the overall view of actual year to date and the full year forecast versus plan let's build the pivot tables for those figures so back in the file and we'll insert a pivot table we can do this from the table design tab and then summarize with pivot table i'm going to put it onto a new worksheet and i'll call this sheet analysis let me bring the field list into view and we'll make it a bit bigger now i recommend that you use one sheet per pivot table so that you never have errors caused by them overlapping one another but for this tutorial i'm going to put them all on one sheet just to save me time so my headline figures are my actual and then plan versus actual and plan versus forecast i'm going to start with a pivot table just for the actuals and i need to calculate the variance to plan and we can do that on the pivot table analyze tab and then fields items and sets and then i'm going to add calculated fields so the first one is plan versus actual variance and the formula is simply plan minus actual now i should point out that because the data is all cost related an adverse variance will occur when the actual spend is higher than the plan spend that's why my formula is plan minus actual of course if you were doing sales variance analysis it would be the other way around all right that's the one and only accounting lesson for this tutorial so that's my first calculated field i'm just going to click add i also want to calculate the percentage variance so that's just plan divided by actual minus one so let's add that and i'll click ok they get added to the pivot table you can see them behind i'm going to move the values down into the row labels and let's right click and format the numbers so go into number format i should point out that it's important you always format your pivot table numbers via this dialog box rather than using the number formatting on the home tab of the ribbon this way the number formatting is attached to the field so that when the pivot table grows the number formatting is automatically applied to any new cells for that field i've already created a custom format and you can see it here at the bottom it formats the numbers into millions and it puts any negative values in parentheses so click ok let's repeat that for the plan versus actual just click end and then okay and let's also format the percentage custom and and there's one i've already set up for percentages click ok now i need a slicer for the period because remember i only have actuals up to august so i'm going to right click add as slicer and then let's bring it over here i'm just going to do a little bit of formatting we'll give it two columns and i'll make it a bit smaller so i'll select up to august in my slicer and now my actuals are for august year to date let's give this pivot table a name we'll just call it headline year-to-date and i need another pivot table the same so i'm just going to copy and paste it below this one is my forecast and plan v forecast so let's add some calculated fields fields items and sets calculated field so this one is plan versus forecast variance and the formula is plan minus forecast click add i also want the percentage change so we'll just modify this minus one and click add and okay now i don't want this pivot table linked to the slicer so i'm going to right click the slicer report connections i'm going to deselect this new pivot table that i've just added it's reminding me that i need to give it a name so click ok and let's give it a name before we format the values so this one is headline full year and now let's format the numbers and okay we need to repeat it for this field and okay and lastly the percentage and okay all right so there's the two pivot tables for our headline figures and we can put them into the dashboard so let's go to sheet one i've got a little bit of formatting here just to save some time but i want to just make it clear that there aren't any special add-ins or sheets required for the dashboard itself it's just a regular sheet so with that said i'm going to rename it dashboard now the dashboard has a blue background so i'll set that up first under page layout background i've already created an image and we'll just go and grab that from my folder there is there insert we'll turn off the grid lines now we've got a nice clean slate ready to populate with our dashboard so all i need to do now is start linking the cells here to the pivot tables that contain the values i've already formatted them and that's why we see the number formats feeding through this is my plan versus actual variance and if you remember on the dashboard we've got a symbol to indicate the direction of the variance and the percentage and it's color-coded so to do that we're going to insert the symbols on the insert tab on the far right symbol now under the font arial and the subset geometric shapes there's a up and down triangle just double click them to insert them now we've got them in the cell i'm just going to write the formula it's going to determine whether we need the up or down triangle so it's just an if formula that says if this value is less than zero then it's a negative variance so we want the up arrow to indicate it's over budget otherwise we'll have the down arrow close my if and then i want to concatenate that symbol to the value returned in the pivot table so let's go and grab it it's ten percent variance now you'll see when i press enter that we get the value but it's not formatted so what we need to do is wrap it in the text function and that allows us to specify the number format so here this is just a percentage variance i want zero percent for the positive and if it's a negative i'm going to put it in parentheses or brackets as we say in the uk and australia and that reminds me of when i used to work in finance in london and my cost center managers would often ask me whether brackets meant the variance was good or bad and i'd tell them to remember that brackets are bad news of course parentheses doesn't quite have the same ring to it okay i digress a little close the parentheses on the text formula and press enter and now we get it formatted correctly now we're not always there to remind our users that brackets are bad news so another thing we can do is use conditional formatting to format the percentage red or green obviously red for bad green for good so let's do that on the home tab conditional formatting new rule i'm going to use a formula and all we need to do is say if this value here is less than zero then we're going to format the font now normally we do it red but red on this blue green background hurts your eyes so we'll use a shade of orange which is pretty close to red actually that one i think is a little bright let's go one shade lighter okay and okay now this is good news it's under budget so we need another rule for the green format and again we're using a formula if this is greater than zero then we're going to format the font in this bright green click okay and okay and now we have our percentage variance conditionally formatted all we need to do now is repeat for the full year forecast so let's go and grab that and the variance and the percentage now for the percentage i'm going to copy this because it's already got some of the work done for me so i'm just going to replace this getpivotdata formula with the correct reference press enter now i also need to modify the conditional formatting so in manage rules i'm going to double click and instead of checking the values in column m we want to check the value in column t and click ok and repeat for this one place that with t and click ok and ok i'm also going to insert some icons from the insert tab icons if you have excel 2019 or onward you have icons you do need to be connected to the internet to download the icons so i'm just going to grab an icon that represents money we'll go with the coins and one for the forecast let's say binoculars and we'll add those two in let's format them in this pale blue color while they're selected i'll resize them and we'll drag them up into place okay so that's our headline figures done i'll grab the slicer from this sheet ctrl x to cut and i'm just going to paste it in there now i want the slicer to blend into the background and if we go to the slicer tab we have some built-in styles and this one's in keeping with our colors but it's still standing out like a sore thumb so i'm going to right click and duplicate this style and then modify it to suit my needs so for the whole slicer format i want the fill to be the same as my background color and i don't want any border i'll click ok i also want to modify the font color on the header just to give it this pale blue color and okay and okay now we just need to apply the format and now our slicer looks more in keeping with my dashboard i'm going to right click go into the slicer settings and change the display header to same month and click ok let's take a look at the completed dashboard and see what's next the first chart is this actual versus plan and forecast let's go ahead and build the pivot tables for that one back on the data tab i'm going to insert a new pivot table i'm going to put it on the same worksheet remember don't do this at home always start a new sheet for each pivot table so you don't get any errors now i want the plan the forecast and the actuals the order of these columns is very important because i want the plan columns to sit behind the forecast and the actual so keep that in mind make sure you add them in the correct order i also want the date in my rows let's apply some formatting this is going to feed through to the chart so it's just going to save me some work and obviously i've already set up the custom number format so i just quickly apply those now i can insert the pivot chart cluster column is fine let's bring it over here and we'll just move the field list over here a little all right i'm going to right click hide all the fill buttons on the chart and then i want to add a chart title and i want to get rid of grid lines i'm going to put the legend at the top it's just going to give my chart some more room and i can now make the plot area bigger okay with one of the columns selected i'm going to ctrl 1 to open the formatting pane let me drag it over here so we can work with it closer to the chart so i want the series overlap to be 100 and i'm going to change the gap width to 30 just makes the columns wider all right i'm going to format the color of my plan to this gray and then the actual i'm going to have this bright blue color i'm going to make it 50 transparent and that's going to allow the plan columns to show through if the actual is higher than plan and for the forecast i want no fill i'm going to give it the same blue border as the actual let's make it a bit thicker but we'll give it a dashed line effect before we move it to the dashboard we'll give it a title actual versus plan and forecast and i'll just ctrl x to cut and ctrl v to paste it in now with it selected i'm going to hold down alt and just drag it slightly larger with alt held down it snaps to the grid behind so it's easy to resize all right going back to the dashboard the next chart i need a pivot table for is the cost elements year-to-date let's go ahead and build that pivot table i'm going to copy this pivot table and paste it below because this has the same actual plan and forecast figures that i need except i don't want them by date these ones i want by cost element group now i want the cost element to be included even if there are no costs so i'm going to right click go into the field settings and on the layout and print tab i want to show items with no data so in the event that there's no plan forecast or actual these labels will still appear in the pivot table and they'll feed through to the chart i also want to show the actuals from largest to smallest so let's sort them we're going to give this pivot table a name cost element bar which reminds me i didn't give this one a name so let's go back and fix that column chart and we're ready to insert the pivot chart now this is a bar chart i'll click ok and let's just bring it over here into view and get rid of all these massive field buttons they just take up way too much room let's give the chart a title and we'll get rid of grid lines bring the legend to the top and we'll move that across there to the left this just gives us more room for the plot area now notice that the axis labels are in the reverse order to the pivot table this is just one of those things that you get with a bar chart they're all a bit back to front we'll go in and format it so that the categories are in reverse order which means now my access labels are at the top so let's move them so that they're at the bottom so we'll set them to high now it looks like they're high but remember this is a bar chart everything's back to front now so high makes them at the bottom now we also want this one to show the plan forecast and actuals overlapping so let's select one of the series and overlap by a hundred percent make the gap width smaller and we can go ahead and format each of the series consistent with the other one so remember the plan is gray the actual is this bright blue color but set to 50 transparency and the forecast is going to be no fill but we're going to have this blue border 1.25 and dashed all right let's give it a title cost element year to date and i'm just going to control x to cut and ctrl v to paste it in now let's get rid of these task panes and holding down alt i'm just going to snap it to the grid okay let's go and take a look at what the next chart is it area year to date variances so we'll go back i'm just going to insert a new pivot table but we're going to pop it on that same sheet underneath click ok so this one is it area and i want the plan versus actual variance and the plan versus forecast variance let's set up the number formatting end and enter and rinse and repeat and now i just want pivot chart this is another bar chart click ok let's bring it over here get rid of those fill buttons we're going to have a chart title now i'm going to have labels on these bars so i don't need my horizontal axes let's turn that off get rid of grid lines add the labels and we'll bring the legend to the top all right let's just move this over here and that over there make this a bit bigger now notice that these axis labels are almost overlapping the number labels for the bar so let's fix that ctrl 1 to open the formatting pane on the labels i want the position to be low i also need to set the categories in reverse order let's go and make these bars a bit wider so we'll change the gap width to 30. let's give the chart a title i t area you today variances now this legend's a bit big don't worry about that i'm going to fix all that at the end let's give this pivot table a name so we know what it's for this is the it area bar chart and we'll cut and paste it on the dashboard hold down alt to size it okay let's go back and see what's next business area year-to-date variances this is similar to this chart here so let's go ahead and all we need to do is copy this one paste it and instead of it area we want the business area i'm going to right click and go into the field settings we're going to set it to show items with no data so they're always present let's give this pivot table a name business area bar and we'll insert the pivot chart remember it's another bar chart let's drag it over here get rid of those we'll get rid of the primary horizontal axis remember we're going to give it labels get rid of the grid lines put the legend at the top move that over there and over there all right so again with this one we need to do some formatting of the vertical axis categories in reverse order and we want the labels to be low so that they're out of the way let's give it a title business area year-to-date variances and we'll make these bars a bit wider and then cut and paste onto the dashboard get rid of these task panes holding down alt like i said don't worry about these overlapping we'll fix them later all right what is next we've got a bar chart here that shows the plan versus actual variance here to date by country so let's go and insert that so we'll insert another pivot table on the existing worksheet underneath the last one this is our country bar so we want to see it by country and then plan versus actual variance let's give it some number formatting and i want to see this sorted so right click sort by smallest to largest i also want to make sure that all countries are always present so we're going to go down into field settings on the layout and print we want show items with no data let's insert the pivot chart this is another bar chart bar charts are great when you have long axis labels that way they're always easy to read we're going to get rid of the horizontal axis get rid of grid lines and we don't need a legend here we only have one series and our chart title is going to provide the information about what values are represented in the chart so this is plan the actual variance here to date you need to add some data labels and i want them inside the base let's make these bars a bit wider so we'll give the gap width 30 percent i also need to make sure these axis labels are positioned low because sometimes there's negative variances as you can see there and now we're ready to control x and ctrl v to paste it in holding down alt actually this one should be over here further okay now i just need to make this label wider for the usa and let's go back to the dashboard the last visuals are these sparklines notice they're aligned to the vertical axis labels of the charts to the left and this saves having to duplicate labels for the sparklines and by color coding the cell fill to match the chart it makes them appear as though they're part of the chart itself okay let's build the pivot tables for these sparklines on the analysis tab i need one for this data so i'm just going to copy this pivot table and paste instead of having plan and forecast we just need actual and we need to see it broken down by the date let's get rid of the grand total so i'm just going to remove that and that's the data for our sparklines so let's go and insert them just going to select the cells insert this one's a column sparkline the data range that's our pivot table here i'm going to select them and i'm selecting right through to december that way i don't need to update these sparklines when i add my new data click ok can't really see them because they're a bit similar to the background color so i'm going to color them this bright blue which is in keeping with the color for the actual we'll give it a heading and let's go back to the pivot table let's give this one a name so this is my cost element sparkline so we'll copy this pivot table can't quite see it make sure i've got it all copy and we'll paste it in here now again we don't want forecast we just want to see the actual and we want it broken down by date i'm going to get rid of the grand total now this i want to see the cumulative variance so i'm going to right click show values as running total in and here we want to see it by date click ok now we have a cumulative variance so i'm ready to insert my sparklines select the cells insert this one we're going to do as a line sparkline the data range is my pivot table right through to december click ok let's give the sparkline a color i'm going to go with the same color as plan versus actual variance that's this one here let's give it a heading cumulative plan versus actual now notice that this variance starts to go up and that's because it's including the period september through to december which we don't have actuals for yet so what we need to do is link this slicer to the pivot table that's feeding these sparklines in fact we need to link this slicer to multiple pivot tables let me go back here and just check i don't think i've given this one a name yet no i haven't so let's do that first so we know which pivot table is which this one's business area sparkline and we'll go back to the dashboard right click the slicer report connections now i can choose which pivot tables and therefore pivot charts should be filtered by this slicer so i want the business area bar and the sparkline i don't want the column chart because i want all of the months always present in the chart so i don't ever want to filter that one i do want to filter the cost element bar and the sparkline and the country bar chart i don't want the headline for you filtered because i want it to always show the four year forecast so we're going to skip that one but i do want the it area bar chart filtered so click ok and you can see now my sparkline is truncated to only show up to september so while we're on the topic of slices let's insert the other two so with any of the pivot charts selected i'm going to go on the insert tab slicer i want region and i t area click ok let's bring them over here and drag this one up above let's resize them while they're both selected let's apply our custom slicer format and we'll make the buttons a bit bigger and then let's just resize them so we can see both okay i won't waste too much time on the slicer formatting something's gone wrong with this one let's give the axis labels a bit more room and this one here okay while we're doing a little bit of formatting let's fix all of these legends so they're not so big one of the problems is they all contain some of before the item that they represent so we can get rid of that sum of text go back into the analysis remember the pivot table is feeding the pivot charts so i'm just going to select all of the cells containing these pivot tables i don't want to change the headline ones so i'm just going to control h for find and replace what do i want to find some of and the space and what do i want to replace it with well i need to replace it with a space because i can't have any of these fields with the same name as a field in the field list of the pivot table so putting a space in there just tricks excel into thinking that it's unique so we'll replace all and if we go back now to the dashboard you can see all of the legends require a lot less room so we'll just resize them so that they're out of the way and this one this font here needs to be smaller so the last thing to do is to fix the formatting now i'll select the first chart what i want to do is make the fill color a slightly lighter shade of green background and i don't want any outline so i'm going to repeat that for each chart and then we'll come back and fix the font so f4 to repeat the outline and then let's do the shape fill f4 for the shape fill and let's do the outline f4 for the outline and then shape fill and last one all right with that one selected i'm just going to set the font color to this pale blue and f4 to repeat f4 and f4 so that's quite quick to set all the formatting up in one go what i want to do is make these sparklines appear to be part of this chart so let's change this fill color to this blue as well and this one so now the sparkline and the bar chart appear to be one chart what i didn't do is link all of the slices to the relevant pivot tables so let's do that quickly this is the it area so i want the cost element bar sparkline the country bar the headline and the headline year to date i don't want the it area bar to be filtered by this it area slicer because there's no point it's already broken out if i filter it's just going to end up with one item showing in the chart so better to leave them all broken out then we can compare them in context to one another likewise at the business areas they're actually very closely aligned to the it area so i'm not going to filter them with this slicer either i'll click ok let's do the same for region report connections this one's easy it's going to filter all of them i'll just select them all click ok now when i select some it areas you can see the relevant charts update likewise for the regions you can see our conditional formatting is being applied and everything's updating allowing us to filter and focus on specific areas of our business now that our dashboard is all set up let's simulate what happens when next month comes along and we get the new actuals for september so on the future data tab i've got the data here for september to december just going to right click filter and i'm just going to filter for september and then i can copy that data on this sheet that contains the source data for all of my charts and pivot tables on the very next row of the table i'm going to paste in the september data and you'll see the table will grow and you'll see that this pull handle down here in the bottom right of the table will now be at the new bottom we can see now our rows go up to 84 600 and there's the end of our table so i haven't had to update any ranges and if we go back to the dashboard i'm just going to select up to september and then when i refresh keep an eye on this column here in the actual versus plan and forecast chart to refresh the dashboard all i do is go to the data tab and click refresh all you can see in the blink of an eye my dashboard is completely updated and that's it i don't need to edit any formulas or update any of the charts and the reason the dashboard is easy to update is because i formatted the data in an excel table remember it's called data and then when i built my pivot tables they referenced that data you can see there so any new data that's added to that table is automatically picked up by the pivot tables when i click refresh and because i use pivot charts they automatically include all of the data in the pivot table i hope you enjoyed this tutorial if you'd like to get up to speed quickly with dashboards please consider my course in the meantime you can download the excel file for this lesson from the link here if you like this video please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 574,305

Keywords: excel dashboard, excel dashboards, dynamic excel dashboard

Id: _PlBRWc0RlI

Length: 35min 28sec (2128 seconds)

Published: Mon Aug 10 2020