Collaborate on Commission Bill Format in Excel for Sales with Ease Using airSlate SignNow

Commission bill format in excel for Sales

hi this is Seth David from the world famous nerd Enterprises Incorporated bringing to you another special screencast this time we're talking about sales reps and commissions and how to get QuickBooks set up so that you can quickly and easily report on your sales by sales and calculate your commissions so that you can go back and enter your bills to pay the commissions to your salespeople now as you may well know especially if you've gotten to this point where you're watching this video QuickBooks has a limitation there it will not do the calculation for you it'll go as far as showing you giving you what you need to be able to report on sales by sales rep so that you can then look at the sales total and multiply by the sales rep's commission rate so that you can go enter the bill once you figured out how much you owe them of course this isn't always practical especially if I've got several sales reps and especially if they're at different commission rates so what I've done is of course I've created a template in Excel that lets you finish the process very quickly and the first thing you'll need to do in the previous tip on this you saw it was very short and simple uh just how to set up the sales rep so if you haven't already watch that video you'll learn how to set up the sales rep so once I've done that I can create this sales rep list report which is just a list report going into reports and lists which I've customized to include a custom field which I've created here as as well as of course you know the name email and phone it's just information that might be handy to have even when you're working in the Excel template so once we've got this in place this is the first thing we're going to need because the template has to be able to look up the sales Commissions in order to do the calculation and I think it's better to keep track of it in QuickBooks so that again as you're about to see you can export this and update the list yourself so what we want to do and instead of using the keyboard shortcuts I'll do it directly you click the export button click export again and QuickBooks will talk to Excel and it'll do its thing so now we've got the sales rep list in Excel and all we have to do is take this section press contrl C we've just highlighted the sales reps and then I want to come over to the template and over here you're looking at the title page so just click get started and come over here to the top right and click sales reps of course you can use the tabs at the bottom too I've just made it easy so that if you're not that familiar with Excel you can still navigate this thing notice the commission rates come in with an error warning here because it's formatted as text when you create a custom field in QuickBooks it's going to be formatted as text so if you click down on this message you'll see it says number stor is text and Excel is great it makes it easy just click convert to number make sure that you've done so successfully by hitting the percent formatting and increasing the decimal by two just to be sure that the information is coming in you know in the correct format once you know that you've got a numeric value here then you know you're looking Up's going to work so now we can go back to the sales commission report and let's go back to QuickBooks here and that's right here and again I've created a custom field here this commission wire n the purpose of this is to enable you to say there might be line items on an invoice that I don't want to pay commissions on maybe you don't want to you know credit the the sales rep for for the shipping because that's really incidental to the sale it's not really what the sales rep earned per se it's it's a cost you have to incur in order to get the product to your customer and you're just billing that back to the customer so in case you have things like that that you don't want to pay commission on I've set up the infrastructure for you here that enables you to specify a specific line item within an invoice that should not be paid commissions on and this is a custom field that you set up in association with your items it's not a custom field for the customers it goes it gets set up with your items and if you download my full length tutorial on this it walks you through how to set all of this up step by step I don't have time to cover it all in a short free tutorial tutorial so once again we're going to click our export button and we're going to export this and the trick of course is to make sure that the columns line up now I've customized this report of course intentionally to make sure that it did in your case if you're not buying the full length tutorial that teaches you how to do it the assumption is then you'll know how to go do this on your own there's still a little additional customization required because when QuickBooks formats these reports it doesn't put the sales rep on every line and we do need that so you will have to just go down and you can do a quick Copy control shift down arrow will copy down the range and hit enter to copy down the names then you can get rid of these extra lines that you don't need because all you need for the templates purpose is just the information itself and we have to get rid of this extra column that comes in here too there's nothing you can do about that it just creates it and we don't need the balance that's out here so let's get rid of that now once I know I've got my columns in place and lined up properly I press a contrl c and I come over to my template and you want to paste values always paste values on these because if you paste if you just paste it it'll take the formatting from its source and you'll overwrite the formatting here when we paste values I want you to pay attention over here to the commission rate and commission columns all the way to the right because as I paste the values in as you can see everything gets looks looked up in an instant and all the calculations are done notice the two line items that have NS under commission yre n do not get calculated we have one more step in this process which is I'm going to make a copy of this tab by right clicking choosing moover copy create a copy and I can just put it right after it or right before the next one click okay and it makes a copy and the reason I want to do that is I want to preserve the source once I've created it but now I can highlight the range of data that I've got in here come over to my data Tab and I want to subtotal it and I want to subtotal it by sales rep so at each change in sales rep I want to use the sum and I want to sum on the amount so I know how much they're being how much uh sales have been associated with that sales rep and I want to sum the commissions when I click okay Excel does the subtotaling for me but we still need some a little more formatting in order to make sure that it's easy enough to read so I can zip up by clicking on the number two here highlight the range and now I've got a special button up here called select visible cells which I've created and you can add that by just right clicking here on the quick access toolbar yours may be on top I've changed the setting so that mine appears below the ribbon so just click on that quick access toolbar and you'll find it in the options it's called select visible cells so if you choose to show all options it's under the s's for select visible cells so we select the visible cells only and now I go to my formatting options which is control and the number one is a fast way to get there I want to go to my fill tab here to my fill effects and I'll just stick with the same formatting I've used for the header come back over here here we want the font to be white so that it can be red against the black click okay it updates the formatting the other thing is sometimes it part of it comes in bold part of it doesn't so I just click and unclick the Bold real quick now I can click on my three over here and it unzips everything and as you can see I've got nice neat subtotals that really stand out so it's very clear to see what's going on and now what I can do is make additional copies of this for each sales rep so I can send it out to that sales rep and then Joe P daily is going to get a summary report that shows you your sales total is 20,25 based on that you're getting paid 5,000 in commissions and you can see very clearly here that he's not getting credit for the shipping portion of the sale he's only getting credit for the actual products that were sold again that's an option you may say you know what I'm going to pay them Commission on the shipping too that's up to you but at least you have the infrastructure and there may be other things that for whatever reason you don't want to you know pay commission on but you still want them to be on the same invoice because you're not going to send your customer two separate invoices one for stuff that's commissionable and one not the customer is going to want to know what's going on so that's pretty much what I can give you in a brief uh free screencast what you can do if you're interested in taking this further is come over to my knowledge Center and you'll find and I'll post the link wherever this video is posted of course where you can download the full-fledged 1our video tutorial that walks you through stepbystep how to create all this in QuickBooks of course the templates included it'll teach you how to run the reports you need in QuickBooks customize them so you can calculate your commissions and minutes and it'll eliminate questions when you do this which saves you time and money because people aren't calling in and asking you how'd you come up with the commissions that you calculated you've sent them a report already that tells them exactly how and obviously if you stick around in the video for the last bonus segment that teaches you how to write the formulas in Excel then that information is going to not only teach you how to create a template like this but it's powerful information you can use and apply in other contexts so that you can start learning how to create some of the stuff you need in Excel that's going to make your life easier and certainly it's going to help you run your business business more efficiently because I'm sure you can see just from what you've just seen here how quickly and easily you can take a process that manually might take an hour or two to do and it cuts it literally down into minutes so email me with any questions Seth at nerd enterprises.com one of the thing I also have just a template you can download the template if you're not interested in the whole video course you can download just the template but I only recommend doing this if you really know QuickBooks well and you know how to set up and customize those reports on your own this is the wrong template I'm show showing you here here's the sales commission template so uh you that's available you can just download the template but uh again I want to stress I would only do this if you really know QuickBooks very well and you know how to customize those reports and set up all those custom Fields the full length tutorial walks you through every little piece of this how to set it up and it's got logical breaks so you can conveniently pause the video go work on some of what you've learned and then go back to it so again Seth at nerd enterprises.com if you have any questions email me or call 866945 8070 and I look forward to seeing you on the web for