Pivot Table - Actual vs Budget Analysis - Part 1

thank you for joining me on my pivot table tutorial in this video we are going to replicate the pivot table report you can see on screen at the moment this is a fairly typical sales performance report it's got actual budget the difference between the two and the achievement rate the analysis service for salesperson and region we are comparing two years 2009 against 2010 we do have a month filter at the top which we can use to restrict the data by a given month or a combination of months we've got totals of the rows to the right and at the bottom here we've got the totals for the columns since we are replicating this report we will need to grab the source data of it so if I go into the data area highlight the whole lot do a ctrl C on my keyboard to copy it create a new workbook do a ctrl V to paste that detron what I tend to do is to label my sheets so I know I can see at a glance what they contain in this case I know this is data so I go ahead and call this one data I would like my pivot table to be on too so I will name this one double click on that and name it pivot I then move it to the left so it becomes the first work it then I go back to the data as you can see here this is basically a simple database and the key thing about it is that there isn't any gaps between the columns or the rows the data range itself is actually from a1 through to G 4 5 8 to begin creating a pivot table if I go back up here we can place a cursor anywhere within that date range from a1 to G 4 5 6 8 but our plasma cursor on a 2 which you can see at the top there so that's a 2 I go into insert click once on the pivot table icon we can see here that Excel has determined the correct date range for us I could have used an external debt source such as the Microsoft Access database or a text file or indeed any other ODBC or database below here we've got the where we need to put the people place the pivot table we can either put it a new worksheet or an existing one because of prepared the other one earlier I would be choosing the existing worksheet I'll use this button to find it click on the pivot click on a one that's where I would like my pivot table report to start press ENTER and then okay now we've got a shell for the pivot table this left hand side here is where the pivot table will yeah to the right we've got the pivot table field list this area is effectively a window into the source data so these items you can see here are the column names if I go back in here dis column names of our data source below that are some boxes that help us place the items where we want them so for region as we saw on that report the region was a row label so we would drag the region to the row levels unless we do that we can see there that the report is beginning to take shape to the left in pivot table is usually more than one way of doing exactly the same thing some instances gonna at least three ways you've seen me drag and drop which is the method I prefer however I could also place a tick against the salesperson and the default behavior in Excel is to place that as a row level so I can demonstrate that now if I click on that we can see Andy's coming to the cells the sauce person is gonna 0 level and it's appeared on the pivot table there as well we had year as the column level so would drop the year to the column levels area and we can see it up there for the month it was a future place that in there and for actual we need the values we also need the values for the budget currently Excel is telling me that's count of actually sewing it up as a count which is really not what we want it's got it right for the budget as a summation but this one is giving me account so we can change all that if I click once or use this little arrow but I prefer to click once on there I can see value field settings if I go in there I need to use some and then give it a custom name with custom names you can't give a name like actual because that is a name that exists already we can see this actual there we've got budget etc we can't give it any of these names it would actually complain if I try that now okay we can see there that it tells you that name already exists so what I tend to do is put an S against it but of course it can be any name we want once we've got a custom name for it can go for the number formats I use a custom format because these numbers are in millions for a large number I'd tend to women round them up in thousands so in this case here I would have there will be one second and the negatives would be in bracket and then just put semicolon the family should be semicolon if I can find it right so that gives me that 65 days actually 65 million as I mentioned previously is more than one way of doing exactly the same thing so for the budget if I place my cursor on the budget and right-click on it I can also access the value field settings from here if I go in there we've got a sum already in this case I want to actually give it a custom name of budgets and then a number format which would be similar to what we had for actuals so I would choose what is in there already then okay the next stage is to do the calculations for the difference between actual and budget of course we could easily if I go back to the data we could easily have added the difference here but in some cases we may not have control or access to the source data especially with the database so it's useful to be able to do calculations based on the field list items we could do this calculations on the pivot table report itself to do that we go into the pivot table tools and to the right we've got a number of options here and one of them is formulas while we're here we've also got changed data source so if you were to add a new column this is what we would use to change the date range for the pivot table let's go back to the formulas and click on calculated field in this case yeah I want to calculate something SCT for actually for versus bu d for budget what we see here this fuel is there so what I want is for the formula take that zero off I can even double click on this here and if I double click would put in about a value field in here or I can actually type it in or is this - where instead the field for the actual I will double click to put that in there then I do a minus and for the budget I would actually just highlight it and use insert field to put that in as well so when I press add that goes in here which clearly would appear on here as well now that we've got that in we can also create the achievement calculation as well so I would over type that name and call this achievement and for this we need a simple formula which is a simple formula partly an equal to if I do if it's error that is if we take these and divide that what you just calculate and divide that by the budget if that is an error then put a zero else but a one plus we're using a 1 plus because it's an achievement it's not a variance I would then use actual divide that by budget then atlas of this calculation and that as well again it's appeared on days appear here as well at the very bottom if I press ok now just to call well-known appear on the pivot table report so we concede us to their for the sum of actual business budget I need to rename that I could easily just over type on the south so I can just go se T - bu D and that's exactly as but that's exactly the same thing as doing these and going to the valueless and giving it a custom name we don't need to specify a number format because it should pick it up from the previous one since it's it's a calculation for the achievement we expect to see percentages so we need to format is slightly first or just give it my custom name abbreviated achievement percent and then I right-click on it going to the value field settings good number formats go to percentage and do one point percent then okay one decimal place badly if I do ok again we should now see a legitimate of 118 so that is telling me that that over that is actually one hundred and eighteen percent so the report a report nun is beginning to look fairly similar to what we saw at the beginning of this video there are other things we can go ahead to do now and one of them is to if I were to for example if I wish to we'll see the details of these we can let me one second so actually I think I would probably stop the video here and continue because the there is a time limit of 15 minutes I would continue with these in part two so please do join me in part two

Channel: akparawaNET

Views: 123,647

Rating: 4.8457832 out of 5

Keywords: pivot, table, excel, actual, vs, budget, sales, analysis

Id: F_MJdL5jcqM

Length: 14min 21sec (861 seconds)

Published: Fri Jul 15 2011