QuickBooks Online 2023 Sales by Product and Service graph, get ready to start moving on up with QuickBooks Online 2023.
00:13
We’re gonna be using the free QuickBooks Online test drive searching in our online search engine for QuickBooks Online test drive selecting the option that has intuit.com and the URL Intuit being the owner of QuickBooks,
00:27
we’re going to be picking the United States version of the software. And of course, verify that we’re not a robot, Joey, take the professor in the back and plug him into the hyperdrive. Sometimes I just didn’t understand human behavior.
00:41
Zooming in by holding down control up on the scroll wheel currently after one to 5% on the zoom in 19. In the car dropped down, we’re in the accountant view as opposed to the Business View will try to toggle back and forth between the two views so you can see where stuff is. And both of them are right clicking on the tab up top to duplicate it as we do every time.
01:02
Right click it again to double duplicate the tab. And then we’ll tab to the middle as the tab to the right things go into the reports on the left hand side open up one of our favorites, that being the balance sheet report.
01:14
As that’s thinking we’ll tab to the right go to the reports on the left this time opening up the other favorite report that being the period of the hour, the profit and loss the income statement, closing up the hamburger otherwise known as the hand bogey.
01:26
So we could change the range from a 101 to two tab 2031 to two tab, run it to refresh it, tab it to the middle closing up the bogey scrolling up to the top changing the range in from a 101 to two tap 12 At one to two tab and run it to refresh it. That’s the setup process we do every time I know that I did that fast.
01:46
But that’s because we do it every time and we want to get in the flow of things. So you gotta get kind of the rhythm of going through that in any case.
01:54
These are the financial statement reports all of the 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.
02:18
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,
02:39
the accounts receivable by customer, then we moved over to the sales items. And sales are always a big winner. For us. Those are the popular stuff, because sales is the important stuff, right?
02:52
So we broke out the sales line items last time by 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.
03:14
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.
03:28
And then on the expense side of things, you could break out the expenses by category as comparison 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 can break out expenses by vendor.
03:43
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,
03:54
we’re going to duplicate it, and then open it up in a new tab once again, go into the reports on the left hand side, close it up the bogey,
04:03
holding control scrolling down a bit to one to five 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.
04:22
And so there it is, let’s run it 4010122 tab 1230 122 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 different columns up top,
04:38
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.
04:47
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,002 80 which doesn’t tie up exactly to what’s on the income statement, which ties out to 10,277 cents for total income. That could be the case.
05:10
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.
05:26
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.
05:40
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. If, however, you have a a cache based business where you have a cash register, you’re using the sales receipts,
06:00
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.
06:19
So that’s the 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.
06:37
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 subgroups. So now we’ve got items and subgroup of items that will be showing up on our items, reports as well.
06:52
Okay, so let’s go back on over to our items reports. So so when I export this, maybe I don’t want maybe I just want to group this stuff by category. If that was the case, I can collapse the tabs here.
07:07
And I can export just by the categories, and then make my make my pie chart by the category. Or possibly I want all the items and not just by category.
07:18
So if I want all of my items this way, then then I can do it this way, maybe I just removed the sub category. So now I’ve got a category and another sub subcategory. So I’m gonna minimize these ones.
07:31
And I’m just gonna 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.
07:40
So when I export, this gets a little bit messy, because now I’m going to have to remove these kinds of subtotals. So I just have a set of data in a total at the bottom, not too bad, but it’s a little bit a little bit more of a challenge to deal with it to get our pie chart. So let’s let’s do our pie chart based on this information.
07:59
So I’m going to export it now by going to the dropdown 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,
08:10
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 gonna hit the triangle, right click copy,
08:26
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 clicking on the tab down below.
08:40
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.
08:51
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 CTRL down and scroll up to zoom in to check out the data.
09:06
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.
09:21
So these ones in here, are formatted at Arial 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.
09:44
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 for sales. And I’m going to make this currency bracketed numbers for negatives.
09:56
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.
10:17
And then I don’t need any of these other columns. But I gotta 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 just to make sure, I’m going to select all of this data, right click on it and copy it.
10:38
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.
10:48
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, I 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.
11:10
Now now we’ve got this added issue here, where we’ve got this kind of subtotal information, and it still gave me this kind of double subtotal thing.
11:20
So you’ve got the, you know, the design information up here, and then you’ve got the fountains that has the subtotals to get down to the to the total fountains down here.
11:31
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 gonna 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.
11:51
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.
12:03
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.
12:16
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.
12:33
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,
12:56
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 to 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.
13:13
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’s still popped in here. So we’ve got this, and this add up to that 198 Right there.
13:32
So I don’t need the subtotal from the sprinklers down to here, I just want them just in the total sprinklers, right click and delete, double click in here, get rid of the total.
13:43
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 landscaping.
13:58
And then pest control, I’m just going to get rid of the these two, 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.
14:18
And then just to see if I’ve messed anything up, I’m going to sum it up now just to double check my number, so it still comes out the 10 to 80. And I’m going to delete, I’m going to I’m going to go back to QuickBooks here, delete this.
14:33
Go back to QuickBooks, and it should add up to the 10 to 80. 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.
14:51
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 it. chart which again is somewhat of a tedious process to do.
15:05
And 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.
15:25
Okay. So now I can delete the added columns up top, I’m going to 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, go into the Data tab, adding filters, but I like to add a table.
15:52
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. And 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, of the revenue.
16:14
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 in 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, I’m going to say maybe just down to like right there.
16:36
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.
16:50
Right click, get rid of it get out deleted, at still adds up to 10 to 81. So it’s $1 difference because of 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.
17:11
And there we have it, I can then change it. I’ve been choosing this one, I think something like that, well that one looks and 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.
17:22
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,
17:36
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.
17:49
And I say give me a recommendation QuickBooks, see there give 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.
18:08
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 Ctrl, 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.
18:31
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 scape 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.
18:48
And landscape. I took out my hoe and my shovel and landscaped it. So now I’m gonna go down and make this larger, so something like that. So So then again, you could format these, you could put a title on the page, you can make it look nice. That’s just the general idea.
19:10
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 kid, I spelled it wrong. Okay, and then I’m going to go on to 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.
19:39
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 P D F with the PDF printer.
19:52
And then the graphs show up without the data tabs and that’s a way you can kind of work some graphs into your stuff.
19:59
And then Making it look a little bit more professional, or, you know, making you 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, OK, so everything is unhidden.
20:21
I don’t like to have stuff hidden unless I have to like to be transparent round here. This is how things are done.
20:29
So I’m gonna go to the first tab and then switch this to the business view just so we can see where stuffs 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.
20:46
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
20:57
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.