Collaborate on Billing Statement Sample Excel for Accounting with Ease Using airSlate SignNow

Billing statement sample excel for Accounting

creating an Excel pivot table profit and loss statement is surprisingly easy and because it's a pivot table you can Team it with slices 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 all right 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 in 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 one 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 foreheaded arrow and then left click and drag it into place I'm also going to right click and remove the grand total now profit and 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 all right let's do some formatting I don't want to see the expand and 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 of 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 SPAC 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 isn't going to resize when we refresh it or use slices so I'm going to right click going to 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 and I'll click okay 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 one here that's done if you look at the p&l 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'll 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 minus budget budget click add while I'm here I'm going to add a percentage variance and that's just actual divided by budget minus one click add and okay and 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 and we'll add a space we'll format this as a percentage with one decimal place and there 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 one up for the income items so Revenue gross profit and net profit and then on the Home tab conditional formatting icon set and I'm just going to go with this one 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 one here is for the expense lines I'm going to double click to open the edit formatting rule dialogue box here I want to reverse the icon order remember negative variances here are good cuz this is expenses I want to change this to number and this one to number so when a value is greater than or equal to zero it's going to be a negative 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 okay 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 okay and okay now we 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 and we can do that with slices so let's insert some slices es I'll 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 but we'll make them gray 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 ingly 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 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 [Music] watching