 QuickBooks Online 2023, Sales by Product and Service Graph. Get ready to start moving on up with QuickBooks Online 2023. We're going to be using the free QuickBooks Online Test Drive searching in our online search engine for QuickBooks Online Test. Support Accounting Instruction by clicking the link below, We've also given you a free month membership to all of the content on our website, broken out by category, further broken out by course. Each course then organized in a logical, reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources such as Excel practice problems, PDF files and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. Drive selecting the option that has Intuit.com and the URL Intuit being the owner of QuickBooks. We're going to be picking the United States version of the software and of course verify that we're not a robot. Julie, take the professor in the back and plug him into the hyperdrive. Sometimes I just don't understand human behavior. Zooming in by holding down control up on the scroll wheel currently at the 125% on the zoom and noting in the cog dropdown. We're in the account view as opposed to the business view. We'll try to toggle back and forth between the two views so you can see where stuff is in both of them. Right click it on the tab up top to duplicate it as we do every time. Right click it again to double duplicate the tab and then we'll tab to the middle as the tab to the right things. Going to the reports on the left hand side, open up one of our favorites that being the balance sheet report. As that's thinking will tab to the right, go to the reports on the left this time. Opening up the other favorite report that being the P to the L, the profit to the loss, the income statement. Closing up the hamburger otherwise no one has the hand boogie so we could change the range from a 1-0-1-2-2 tab to a 1-2-2 tab. Run it to refresh it, tab it to the middle, closing up the boogie, scrolling up to the top. Changing the range from a 1-0-1-2-2 tab to a 1-2-2-2 tab and run it to refresh it. That's the setup process we do every time. I know I did that fast but that's because we do it every time and we want to get in the flow of things so you got to get kind of the rhythm of going through that in any case. These are the financial statement reports, all other reports basically given more information about one or multiple line items within it. We've been exporting reports to Excel in order to make nice colorful graphs that we might want to put in our presentations and or our package of reports that we give to clients at the end of the month quarter year or to our supervisors for example. So we could anytime we see something that has a set of numbers and a total such as the total assets here we can make a pie chart, we can make a graph out of it, we can make a graph out of the assets breaking out by category, the liabilities and equity breaking out by the category, the GL we took a look at making graphs, breaking out the accounts payable by vendor, the accounts receivable by customer. Then we moved over to the sales items and sales are always a big winner for us. These are the popular stuff because sales is the important stuff, right? So we broke out the sales line items last time by customer who we sell most of our stuff to. We can also break the sales out by GL account, which could be significant here given the fact that we have a few GL accounts, but oftentimes there's not that many GL accounts for revenue. So it's not that significant, but you could also break it out by what you sold. Even if you have few GL accounts, you might have a lot of different items that you sell, service and inventory items. That is, that's how we'll, that's what we'll focus in on this time. And then on the expense side of things, you could break out the expenses by category as compared to the total expenses. That would be a useful way to format it because there are typically a lot of GL accounts by expenses or you could break out expenses by vendor. Now let's go to the report that we will be using. We're going to break out our income by what we sold service items, income items, right clicking on the tab up top to get to that sub ledger. We're going to duplicate it and then open it up in a new tab. Once again, going to the reports on the left hand side, closing up the buggy, holding control, scrolling down a bit to 125. So I can go down to my sales areas. There they are. I'm looking for the sales by sales by item. So I want the sales by item and I just want the summary. There it is. Sales by product service summary. And so there it is. Let's run it for 010122 tab 123122 tab and run it. Now this one's a little bit more detailed because there's more stuff in it because we've got a couple of different columns up top. What we're really focused in on is just the amount of the sales is what I'm focused in on to make our pie chart and our graphs. So I could just delete the other stuff once I export it to Excel. We also have some subcategories we'll have to deal with before I get into that, though. Note that the total down here adds up to 10,280, which doesn't tie out exactly to what's on the income statement, which ties out to 10,277 cents for total income. That could be the case. And remember that this sales by item will only work if you're entering your data into the system in such a way that it's going to populate the sub ledger accounts. Quick recap. If I hit the plus button, your revenue cycle could differ depending on the type of industry you're in. If you're in gig work, you're getting paid by YouTube, you're getting paid by Amazon, some platform. Then as you get the money, you might just be using a deposit form with the use of the bank feeds possibly to record the revenue. So that's great, but you're not going to have the same detail for the service sub ledger item because you're not entering items that way because the deposit form doesn't have the capacity to do so. Because it's not usually the form we use for the sales side of things. However, you have a cash based business where you have a cash register. You're using the sales receipts. And as you populate the sales receipts, you're going to be populating the inventory and revenue items, service items in there. And therefore the sub ledgers should be generated. Same with the invoices. If you're using invoices, the sub ledger should be generated because you're going to be using the items within the sub ledgers. So that's the general idea. Also just remember, if I go to this first tab over here, we're going to go to the sales tab. And then you can find your items and the products and services. And these are the things that we sell that we populate the invoices for. So these would be our inventory items, our service items. Now we have a bit more complexity because here they set up their items to have sub groups. So now we've got items and sub group of items that will be showing up on our items reports as well. Okay, so let's go back on over to our items reports. So when I export this, maybe I just want to group this stuff by category. If that was the case, I can collapse the tabs here and I can export just by the categories and then make my pie chart by the category. Or possibly I want all the items and not just by category. So if I want all of my items this way, then I can do it this way. Maybe I just remove the subcategories. So now I've got a category and another subcategory. So I'm going to minimize these ones and I'm just going to have the major categories. So I'm going to minimize this. So I have like two layers, the major categories and then everything within it. So when I export this, this gets a little bit messy because now I'm going to have to remove these kind of sub totals. So I just have a set of data in a total at the bottom. Not too bad, but it's a little bit more of a challenge to deal with it to get our pie chart. So let's do our pie chart based on this information. So I'm going to export it now by going to the drop down up top and I'm going to export to Excel as we've done in prior presentation. You do need Excel in order to open stuff in it. There it is. I'm going to enable the editing. I'm going to copy this and put it on our other worksheet that has all of our statements on it. You don't have to do this. If you don't have the other worksheet, you can work here, but I'm going to show you how to do it. If you want to do that, we're going to hit the triangle, right click, copy. I'm going to go into my other Excel worksheet that has all our reports on it. Go to the last tab within it. Add another tab and then put this in a one or select the triangle and paste it down right then and there. Double click it on the tab down below. I'm going to call it sales by item data. This will be my data tab because I'm going to put the actual graphs on another tab and then hide the data tab. So I can print out all the reports without the data tab printing out and print them to a PDF form. So that I can get them all on one PDF form. Okay, so now that it's there, I'm going to hold control down and scroll up to zoom in to check out the data. Let's see what it looks like. So there we have it. Okay, so I'm going to go to the tab to the right and then back to the left. I usually like to just see where the page screen ends right there. And then I'm going to do my formatting thing on this. So these ones in here are formatted at aerial eight. And these out here are formatted at Colibri 11. I'm going to make them all Colibri 11 by putting my cursor out here. Go into the paintbrush and brushing the format on the entire sheet. And then I'm going to start to delete the stuff that I don't need. Well, let's let's first format the cells the way I'd like to see it. I'm going to right click on them format the cells. And I'm going to make this currency bracketed numbers for negatives, no dollar sign. Let's get rid of the decimals. And okay, so there we have that. And then I'm going to get rid of what I don't need. I'm going to put my cursor on row one. I don't need anything from here down to down to let's go to here for now. And right click and delete. And then I don't need any of these other columns, but I got to be careful on these columns that I don't remove any formulas. It looks like this is all hard coded numbers. I'm checking to see that it doesn't have a formula in it. But I just to make sure I'm going to select all of this data, right click on it and copy it. And then I'm going to paste it right back down on top, but paste it 123 hard coding the numbers. So everything is just hard coded now, no formulas. And then I can be fairly confident getting rid of the quantity. I don't need that. So I'm going to select column B, right click and delete it. I'm going to select this percent, all this stuff. Don't need any of that. I just want one set of numbers and a total right click and delete. Boom. Okay. So there we have it now. Now we've got this added issue here where we've got this kind of sub total information and it still gave me this kind of double sub total thing. So you've got the, you know, the design information up here and then you've got the fountains that has the sub totals to get down to the to the total fountains down here. So what I want is just to have the total fountains. So I don't want any of this stuff up top. So what I'm going to do is I'm going to have to delete from, from row five to row eight. And I'm just because this sums up if I add that, that amount sums up. Now notice this particular report had some funny formatting where it's still on the left. So like if I double click on these cells, then it, it moves it over to the right to a normal formatting. I don't know why sometimes the reports do that because they're not. So I have to double click on them and have them move over so I can see the normal formatting for it. So I'm just going to do that all the way down so that it formats it properly. There probably is an easier way to, to do that. But I'll just do it this way for now. It's kind of an annoying little thing. And then if I select these three, this adds up to 2670. So I can, I want to keep that total and delete from here down to here. I'm just going to delete that and then I don't need the total. So I'm going to get rid of the total and then the lights that looks good. And then we've got the total design. I don't need the total because this should add up to the 5853. So I'm going to get, going to get rid of the total here and get rid of that. I'm going to get rid of the designs up top, delete that. So I'm just getting rid of the subtotals. And then down here, we've got, we've got these landscaping items that add up. And then I've got this, again, the subtotal I tried to get rid of, but it still popped in here. So we've got this and this add up to that 198 right there. So I don't need the subtotal from the sprinklers down here. I just want them just in the total sprinklers, right click and delete. Double click in here, get rid of the total. And so there is that. And then we've got the total landscaping. I shouldn't need that because the sum of these 4407 should be that. So I'm going to get rid of the total and I'm going to get rid of the landscaping and then pest control. I'm just going to get rid of the, these 2 because I just want the total right click and delete. Double click on the total, delete that stuff and then not specified. Okay, there it is. So then I'm going to, I'm going to get rid of this total and then just to see if I messed anything up. I'm going to sum it up now just to double check my number. So it still comes out to 10, 280 and I'm going to delete. I'm going to, I'm going to go back to QuickBooks here, delete this, go back to QuickBooks and it should add up to the 10, 280. So I didn't mess anything up. So that's a little bit messy of a process because you have those subtotals. If I didn't have those subtotals, then it would be, you know, a fairly easy, easy process to deal with. So it takes a little bit of cleaning up. But now that I have this, I should clean this up a little bit further by getting rid of these spaces because that could mess up the format of the chart, which again is somewhat of a tedious process to do. There probably is an easier way to do this like I could. Well, I won't do it. I won't show you right now. Just this isn't too bad with this many numbers. We'll just delete the spaces. And then I'm going to do that two more times. Okay, so now I can delete the added columns up top. I'm going to, or rows one and two, right click, delete. I don't need this total column because I'm going to make a table out of it. Right click, delete. And so now we've got our raw data. So, so now I need to sort this data. So I could do this by selecting the data, going to the data tab, adding filters, but I like to add a table so I can put my cursor anywhere in the data. I don't have to select the whole thing, insert table, and then I'm going to say, okay, I'm going to add a total column. So it totals it up down below. So now I need to filter it from Z to a drop down Z to a. That's what that's what we sold in terms of the revenue. So, so now I can make a pie chart out of it, although there's too many slices, most likely, if I select the meat, the stuff in the middle, the data, not the total and the headers, insert pie chart. There it is, but it's a little too, too messy up here, too many little slices. So I'm going to close this out and I'm going to say maybe just down to like right there and then everything below. I'm going to put into other that adds up to 1178. So I'm going to say this is 1178. I'm going to call it other and then everything underneath from eight to 15 right click, get rid of it, get out and delete it. It still adds up to 10281. So it's a dollar difference because a rounding than it was before, but then I'm going to select these. And now I can insert a pie chart and it should be appropriate pie size. And there we have it. I can then change it. I've been choosing this one, I think something like that. Well, I haven't been choosing that one, but I kind of like it. So there we have that. So that's one way you can design. Now, remember those percentages are being formatted like this. All we're doing is taking each number divided by the total. So anytime you have a list of numbers and a total, you can make it into a pie chart. Right. That's the idea because this is going to add up then to 100%. And that's where the pie happens. And then I could make the chart into any other kind of bar chart by inserting. And I say, give me a recommendation quick books. See there, it gave us a pie chart that time. Let's do this one again. And so there we have that. I can make it fancy colored or something orange, whatever. And then I can put that on a new tab selecting another tab. I'm going to call it sales by item graphs. And then pull these graphs. I'm going to hold control copy them, put them in my sales by items page. I'm going to go to the layout to see where the endpoint is. I like more space. So I'm going to landscape it so I can fit more stuff on the land. I want to fit more stuff on the land. You've got to optimize the escape of the land. So I'm going to make it landscape. You got to be off the thing off the chart so I can landscape it. And landscape. I took out my hoe and my shovel and landscaped it. So now I'm going to go down and make this larger. So something like that. So then again, you could format these. You could put a title on the page. You can make it look nice. That's just a general idea. But if I wanted to print all this stuff out, I can then hide the data tabs, right click and hide the data tab. So the data won't show up. I'm going to hide this data tab. I'm going to hide this data tab. I'm going to hide this data tab. Data. I spelled it wrong. Okay. And then I'm going to go onto some other tab so that I can then go to the file, print it using the cute PDF printer, which is a free PDF option. You can check it out. Look, look it up. You don't have to use it. I'm not advertising them. I'm just saying. And then if I scroll down, I can print all this stuff on one PDF with the PDF printer. And then the graphs show up without the data tabs. And that's the way you can kind of work some graphs into your stuff and making it look a little bit more professional or, you know, making just stand out from the crowd. So I'm going to put my cursor on this one, hold shift and then select this one, selecting all of them so I can right click and unhide. And then I'm going to hold shift and select all of them and say, okay, so everything is unhidden. I don't like to have stuff hidden unless I have to like to be transparent around here. This is how things are done. So I'm going to go to the first tab and then switch this to the business view just so we can see where stuff's located under that view too. So we'll go to the business view and we've only been working in the get things done page, which is otherwise known as home. And then we have the reports in the this tab, which was the business overview tab. So there's where the reports are. I think we've just been hanging out on the reports. No, we went into the, the products and services, which is under the get paid and pay area. And then we've got into the get pay. There's your products and services under the business view format.