Easy Excel PivotTable Profit & Loss Statements

creating an Excel pivot table profit and loss statement is surprisingly easy and because it's a pivot table you can team up with slicers to make it interactive and while you're at it you might as well add some conditional formatting to make reading what is usually a drab report at least quick and easy let's have a look this is the data I'll be using notice it's already in a tabular layout and each account is classified into an account group and these account groups represent the different sections of a profit and loss statement now I've also got regional and financial year columns that I can use in slices to filter my P&L and in this case I have actual and budget data but you might only have actuals alright let's insert a pivot table I'm going to pop it on this sheet here called P&L and we'll put it in cell b3 so I want my account group and account in the row labels and the actual and budget and the values areas let's apply some number formatting these are currency values and no decimal places and we'll do the same for this on currency with no decimal places now the revenue accounts should be listed above cost of goods sold so I'm just going to hover my mouse until it displays the four-headed arrow and then left-click and drag into place I'm also going to right-click and remove the grand total now profit and loss also have subtotals for gross profit and net profit and we can add those using calculated items I want the calculated items at the account group level so I need to select any of the row labels at the account group level and then on the pivot table analyze tab I'm going to go fields items and sets calculated item we give our calculated item a name the first one will be gross profit and the formula is simply revenue double click to add it minus cost of goods sold and I'll click Add and then let's add one for net profit this one's going to be made up of gross profit minus expenses and I'll click Add and okay now you can see they've been added to my pivot table but we also have all these accounts underneath them so I'm just going to collapse these two so that we can't see the underlying accounts and I need to also move gross profit up underneath cost of goods sold alright let's do some formatting I don't want to see the expanding collapse buttons and I don't want the field header that's this row labels here so let's turn that off we'll get rid of some off before actual and budget now I need to leave a space before actual to differentiate this label from the actual field in the field list and I need to do the same for budget so we're just going to leave a space you can't even tell the space is there let's write align those labels and back on the design tab I want to have the subtotals at the bottom of the groups and I want to add a blank line after each item just to make it look more like a traditional profit and loss report now I'm just going to give the columns a bit more space and I want to make sure that this pivot table isn't going to resize when we refresh it or use slicers so I'm going to right click go into pivot table options I'm going to remove autofit column widths on update and make sure preserve cell formatting on update is checked it should be by default but best to check anyway I'll click OK the last thing I want to do is remove the default styling that the pivot table has so on the design tab in the pivot table Styles you can choose this on here that's none if you look at the peer now you can see there are still lines in it so I've created my own custom style that has absolutely no formatting and you can create your own custom style by right-clicking on one that's close to what you want and then choosing duplicate and then you can go ahead and modify the settings in there I've already done that here so we're going to apply the no formatting style let's add some borders we'll repeat the same for this one and the expenses gross profit can have a top and bottom border and net profit can have the double bottom border now if you have actual and budget like I do then you probably want to display the variance and we can do that on the analyze tab by adding a calculated field call this one variance and the formula is simply actual - budget click Add while I'm here I'm going to add a percentage variance and that's just actual divided by budget - 1 click Add and ok you can see they're automatically added to the pivot table let's get rid of some of like we did with the other labels out of space I'll format this as a percentage with one decimal place and then you have a pivot table profit and loss now profit and loss statements make for dry reading but we can make it quicker for our audience to interpret with the help of some conditional formatting to visually indicate whether the variance is positive or negative using traffic lights now positive income variances are good but the opposite is true for expense variances so we need two conditional formatting rules I'll start by setting on up for the income items so revenue gross profit and net profit and then on the Home tab conditional formatting icon sets I'm just going to go with this on here we'll repeat that for the cost of goods sold and the expenses now I need to modify the rules so in conditional formatting manage rules this on here is for the expense lines I'm going to double click to open the Edit formatting rule dialog box here I want to reverse the icon order remember negative variances here are good because this is expenses I want to change this to number and this onto number so when a value is greater than or equal to 0 it's going to be an egg give variance for the expense when it's less than zero and greater than or equal to zero then it's going to be neutral and that's highly unlikely and when it's less than zero then that's good so click OK we need to make a change here to change this to number and likewise here I don't want to reverse the icon order though it's correct as it is so click OK and ok now I can see that negative variances for revenue are displayed with a red indicator whereas for expenses they're green now if your source data has fields that you'd like to filter your profit and loss by like I do here with region and financial year then we can do that with slices so let's insert some slices I have one for region and financial year and I'll just bring them over here and we'll squeeze them into this space let's move that one down and we'll make them a bit smaller I don't want to spend too much time formatting the slices I think you get the idea of that will make them grey and now when I select a region in my slicer you can see the pivot table automatically updates as does all the conditional formatting I can show all regions if I want likewise if I select a financial year it all updates accordingly so there you have pivot table profit and loss with conditional formatting to give visual indication of the performance and slices to give that interactivity and allow the user to select what region or financial year that they want to display I hope you found this technique useful you can download the excel file for this lesson from the link here or the video description if you like this video please give it the thumbs up and subscribe to my channel for more and when I let's share it with your friends who might also find it useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 69,776

Rating: 4.9770889 out of 5

Keywords:

Id: 5kUQSxBVlZ8

Length: 8min 47sec (527 seconds)

Published: Wed Jul 29 2020