QuickBooks Online 2021 sales by item reports, let’s get into it with Intuit QuickBooks Online 2021. Here we are in our free QuickBooks Online test drive file, which you can find by searching in your favorite browser. For QuickBooks Online test drive, we’re in Craig’s design and landscaping services, we’re going to go to the reports on the left hand side, scrolling down to the reports that are going to be related to the income statement, including the sales and customers types of report.
00:32
And we want to take a look at the sales price product and services which we have two reports up, we will first be taking a look at the summary reports. So we’re in the sales and customers sales by product slash service summary. This would be similar to a sales by item report, if you were in the desktop version, recalling that we kind of have that difference in terminology by the things that we sell being called either product slash services, which is a little harder to say, but more descriptive, or inventory items or service items, which would be used on the desktop version.
01:03
So we’re going to be opening them that up. Let’s do a range change up top range change from a 101 to zero to 1231 to zero, run that report. And then we’re going to do some tab duplicating. So we’re going to go back up top and right click on the tab duplicated, and then we’re going to do it again. So I’m going to go on that tab again. Let’s go to the first tab. Well, that one’s thinking, right click again and duplicate again, on the tab to the far right, I’m going to want the income statement.
01:29
And then we’ll have that one report that we are working on. And then we’ll have the other report here in case we want to open up anything else which we will in the future. So let’s go to the tab to the far right, let’s open up a normal income statement profit and loss, P and L type of report. So here’s going to be our profit and loss type of report, we’re going to do a range change up top for that one, that’s going to be going from a one a one to zero to 1231 to zero, let’s go ahead and run that report.
01:56
Then I’m going to close the hamburger up top, hold down Control, scroll up just a bit to get to that one to five, which is my preferred viewing range, then we’re going to go to the sales by product. And we’re going to then close the hamburger there as well. So let’s first analyze the income statement and see how this will fit into the income statement. So if I go back to the tab to the right, we’re looking at the income line item. Now there’s a lot of different things in the income line item for this particular company.
02:25
Because of the type of industry they are in, I’m going to collapse the income line item, just to recognize that generally, you’re not going to have a whole lot going on in income as far as a line item goes. Although in terms of dollar amount, you hopefully will have a whole lot going on, you can have a hopefully a good dollar amount going. But the number of accounts should be fairly small, for the most part. Now, like we saw with the customers, you might have a tendency to try to want to put more information in the profit and loss or income statement.
02:56
So in last time, we thought about how you might have a tendency to think about doing that by customer. And then you kind of want to resist that urge so that you can then run another report that provides you with that detail so you don’t muddy up your income statement. Same kind of thing that you can think about by product or service. So these products or services are the things that we sell inventory items, service items. So we might have a tendency to want to have our biggest, like things that we sell as a line item on the income statement.
03:26
Or you might have a tendency to say hey, every time I sell a particular thing, I want a different line item for the thing that I’m selling. But you don’t really need to do that you don’t want to do that to any big degree. Because if you do, you’re going to end up with a huge amount of income accounts, which is kind of defeating the the point of the income statement, which should give you a pretty good quick summary of what is going on. Instead, what we want to do is have a couple line items up top on the income statement, and then be able to go to another report that’s going to break us out that more detail that of course being the sales by product and services.
04:01
So instead of adding another line item on the income statement, for every thing, every product we have, or every service we do, we have another report that will kind of summarize those items. So there could be deviations to this rule remember, like if I go back to the first tab here on the income statement, you might have like two line items if you sell goods and you have services will basically break out two line items and that means that you’ll you’ll have a line of income that’s all breaking up all the income line items in that line might be worthwhile to do right that is pretty good breakout and then you’re gonna have the other side will be all of your all the things you sell like inventory type of items.
04:41
So you could you could break it out like that you may also use classes to do a breakout like that which could be you know, interesting or useful to do as well we’ll talk more about classes in a future presentations you know, after the whole practice problem. You also might have some major categories so if you have you so inventory you have major categories of inventory that you want to group in, then you might have some accounts that are going to group group by category, but you wouldn’t want to break out every inventory item that you sell.
05:09
So so there are times when you want more groupings of income, but you want to be careful on those groupings of income. And when you do group income, you want to keep in mind that you have this other report, which will break out the more detail and then think about how you can integrate what you want on your actual income statement, which should be more consistent, and concise information, and then how you can then use the supplemental reports over here to break out the more detail. So this of course, is going to be the sales by product report.
05:37
So we have in the the in the information and things in terms of things that we are selling here. So we’ve got this information. Now this information is going to tie out to the products and services that we have created. In other words, if I go to the first tab, and I go down to the sales tab, and we take a look at then the products and services, these are the items that we set up. And these are the things that we set up that we actually sell when we create, say, invoices and sales receipts.
06:06
So now we’re basically creating them, you can see how QuickBooks creates this report. When we sell stuff, if we’re using a full QuickBooks, you know, inventory system, or we’re using a full sales cycle, whether we have inventory or not, if we use service items as well, we would be making then for those sales, invoices and sales receipts. And we’re going to be making those invoices and sales receipts with the use of the items or the product and service items on them.
06:32
Now note that if you do not use these items, for example, if you’re taking your information directly from the bank statement or from Bank feeds, and you’re just taking the deposits, and putting them right into then the bank, then you’re not using the items in that case. And so these items won’t be able to be used to drive this information.
06:51
So if you’re using a kind of a cash basis system, or relying on the bank, and not creating these reports, the invoice and the sales receipt, then you’re not going to have this added kind of level of detail. Because this is being generated through the full service accounting cycle with the help and use of these reports. Note that if you are in a cash basis system, you’d still be using these reports. If you’re using the whole, you know accounting cycle, because you you would be using the sales receipt report.
07:20
But it would if you’re going a step further than that, and just simply recording the deposits as they go through like with bank feeds and just recording them to income, then you may lose the level of the detail here on the items. And if you do not add the customers as you do that, which you may not because you might not know the customers, if that’s your system, then you’ll you’ll lose some level of detail on the other added report as well that we saw last time with the customer, the customer balance detailed report by the sales by customer report.
07:48
Okay, let’s go back to our report then. So we have the quantity here we have the amount, and then this is going to be the percentage of the total sales. So the total sales are going to be down below 100%. So the percentage of sales is just taking this line item divided by the 10,002 80. That’s gonna give us our percentage, we have the average price, and then we got the cost of goods sold the gross margin and the and the gross margin percent, which would only really be applicable if you are using inventory or if you’re tracking inventory items for those items.
08:19
Now if we go down to the bottom of this report, then of course, we see that we have that 10,000 to 80.05. That then ties out to or should tie out to the the line item for income it’s a little bit off for the same reasons we talked about before, because it’s possible for you to hit an income account or record something to an income account with a deposit that you put directly into an income account that you didn’t use a form for such as such as an invoice and a sales receipt.
08:47
However, if you are only using those forms to record your income, then they should basically tie out but it’s not forced to tie out in the same way that a sub ledger for like accounts receivable basically is or accounts payable, those sub Ledger’s being the customer, the accounts receivable by customer or the AR aging detail, and the accounts payable aging detail tie into the balance sheet accounts of accounts receivable and accounts payable almost every time because QuickBooks is way more stringent in allowing you to record anything to those accounts.
09:19
Without using that they don’t do that same restriction to the income line. Because there are times when people you know may want to post something to income without a customer. So they don’t they don’t force you to have a customer every time you do. So let’s open up the other report. If I go back to the first tab here and go down to the reports again, and then we go to the other one. That’s by item that detailed reports. So we’re going to go down to the sales reports once again. And now we want the sales by product service detailed reports. Let’s open that one up.
09:53
I’m going to right click on the tab up top and duplicate it again. And then we’ll do a range change on the date range. So we’ll have a date range change, we’ll take it from, let’s see, oh 101 to zero to 1231 to zero, go ahead and run that report. Let’s close the hamburger. So we have the same kind of information, these are the things we sell. And now of course, we can see the more Doc, the documentation or the forms related to them. When we sell these items, we sell them with sales, receipts and invoices.
10:24
So we have that detail, we can drill down on this detail if we want to use the zoom feature. So notice that this report, once again can only include items that were recorded to the income line items, income accounts, with the use of invoices and sales receipts. Again, if you recorded something without an invoice, or sales receipt, just with a journal entry, or deposit directly to the sales line item, you’re not going to have this detailed report with it because we didn’t get to assign an item for it in that case. So that’s going to be this information.
10:54
And of course it will, it’ll tie up or an add up to the same information at the bottom. Let’s go ahead and then just print out this report. I won’t do the other one. Let’s customize it. So this is going to be the sales by product, we’re just going to then do our standard process customizing. And hopefully that should have been my standard process. We think we’ve skipped customizing a few times. But now it should be the standard process, we’re going to customize it, then we’re going to download it as a PDF and Excel document and put it into our extended Excel worksheet.
11:24
So let’s go ahead and customize, we’re going to say let’s remove the pennies. Let’s make some negative numbers here and bracket ties those negative numbers and make them red as well. Going down to the header and footer, we’re going to be removing the date prepared time prepare report basis, go ahead and run that report. So there we have it, that’s going to be our standard report information. Once adjusted, remember, you might want to customize or memorize the report, save the report.
11:50
I won’t do that now though. So I’m going to then hit the drop down, we’re going to export to a PDF file, then we’re going to save the PDF file. So there it is down here in Chrome, I’m going to hit this box, which is is going to make the window smaller, I’m going to look up the name of that what that’s called again, cuz it’s kind of bugging me that I can’t But anyway, then I’m going to drag down, I’m going to pull this over and drag and drop it, drag it and drop it.
12:15
There we have it going to go back up top again, we’re going to close this back out, we’re then going to be opening up our our Excel worksheet because we have this Excel worksheet. This is the one I want to save it to with all our information down at the bottom. So I’m going to make another another worksheet on this one. And then I’m just going to copy and paste it into this item. Let’s go ahead and download the Excel worksheet as well the Excel and then we’re going to go ahead and open that up. Enable the ending up top, we’re going to select the entire worksheet by hitting the triangle.
12:50
And then I can say Ctrl C, if I so choose Ctrl C, going to minimize this tab, I’m going to paste this in cell A one gonna put my cursor in a one Ctrl V and paste it there we have it, then I’m going to double click on the tab down below and rename it. And I’m just going to call it sales by Prado, something like that. And let’s see if it fits on one page. Now we should test that out by going to the Page Layout View, does it fit on one page? Yes, one page wide, it fits on it could be more than one page long, but it’s not. It’s on one page wide and one page long.
13:24
So we are okay, they’re gonna then save it. And then let’s go ahead and print this as a PDF file by going to the print or the File tab on down to the printing information. And then I want the entire workbook to be printed. So I’m going to print the entire workbook, which is now at 20 pages. We have a 20 page workbook, let’s scroll through it. Make sure everything is looking appropriate. Here, it all fits on one page, we don’t have to tape anything together or anything like that. So scrolling down the new report down at the bottom here, and it fits on one page looks good.
14:03
Let’s print it going to be printing it which is going to save it to the cute PDF printer. So it’s going to save it as a PDF. Because we’re using the cutepdf printer, I’m going to overwrite this file, double clicking on it, saving it to that file. And then let’s open that file up and just double check it, they’re gonna put two checks next to the checking with doubling up the check. So then we’re gonna scroll down.
14:28
And so this is this is everything we have so far. So it only it only said I only have one thing here, hold on a second. This is the wrong report. This is the report I want. This is the one. That’s why and then I’m gonna now it’s gonna scroll through this thing. We got the balance sheet. I’m going to scroll all the way down to the 20 pages down to the new stuff.
14:54
So we’ve got all this stuff on one report because of the cute PDF print And exporting to excel combination fairly easily done without the need for Adobe Acrobat. Fancy reader, we just need a normal Adobe. So we can do this and then our new report down sales by customer. That’s what we did last time. Sales by income by customer. We did that last time. And then here’s the new ones sales by product and services. We did that this time.