QuickBooks Online 2021. Now, accounts payable graphs with the help and use of Excel. Let’s get into it with Intuit QuickBooks Online 2021. Now, here we are in our QuickBooks Online test drive practice file, which you can find by typing into your favorite browser, QuickBooks Online test drive, we’re in Craig’s design and landscaping services. Going down to the reports on the left hand side, we’re looking for reports that represent or support the accounts payable.
00:28
So we’re going into the category of who you owe, or what you owe what report, we’re looking for the vendor balance summary. So let’s open up the vendor balance summary. Let’s open up the balance sheet as well. So I’m going to duplicate some reports up top, we’re going to go up top right click on the report, duplicate the report, going to duplicate again, right click and duplicate again. And then I’m going to open up the balance sheet for the report all the way to the right, keep all the report for the AP report in the middle, and then have another tab just in case we want to do other stuff without messing up the stuff that we’re currently in.
01:02
So we’re in the tab to the right, we’re going to go down to the reports down at the bottom left, again, opening up our favorite report that being the balance sheet report going up top for the range change, oh 101 to zero to 1231 to zero range has been changed. Let’s go ahead and run it. And then we’ll close up the hamburger on the top left, hold down Control, zoom into that one to five percentage. And then if we go down to the liability side of things down here in the accounts payable, that represents money that we owe to somebody else now.
01:32
And if we imagine a scenario that we would talk about, like with a client or with a supervisor or whatever, and they ask, you know, how much money do we owe other people and we say we owe other people 1602 67, then they’re gonna say, who do we owe? When do we have to pay it? Can we not pay it and not be penalized for some period of time and like pay it later, or something.
01:51
And to do that, we need to break out the report by the sub ledger. So that’s what this is, if we go to the second report here, closing up the hamburger range change up top, which is just one date, not really a range, this time doesn’t sound as good point in time change point in time update 1231 to zero. And then we’re going to say run that report. So this is the people that we owe, so we can break down who we owe in a simplified fashion here, that’s going to come out to the 160 267, which should tie in if I go back to the balance sheet to the 160 267 on the balance sheet, back to the profit and loss.
02:28
Let’s use this data to make another chart again, we can this is a nice chart, makeable type of report, many of them make great, great graphs, you can imagine good graphs that you can make for visuals depending on what you’re doing. And again, it’s a really good skill to have this on any kind of database program, whether it be QuickBooks, or you’re doing some other kind of any work that you do, you’ll probably have a database. And you’ll probably have the ability to export to Excel if they if they give it to you. Otherwise, it’s you know, your work might restrict the export.
02:57
But if they allow you to export it, then that software will typically have that ability. And if you have that, then you can run reports like this and put them into your, your reports. And it’s really good. So we’re going to be then saving this to our our data tab we have over here, I’m going to add another sheet that we’re going to add a sheet to I think I added to on accident, I just want one. So I’m going to add this sheet. And then let’s go ahead and export this one to excel and export to Excel. It’s going to show up down here because I’m in Google Chrome.
03:30
And that’s how Google Chrome does it. But you can it might be up in the top right if you’re in like Firefox or like Explorer. And then let’s enable the editing. Let’s hit the triangle up top, as we see in the past, I’m just gonna say Ctrl C, this time, keyboard shortcut, name it, making it simpler. And then I’m going to put my cursor on, you were on sheet three I’m in so a one I’m gonna say Ctrl V on the cable keyboard to paste it holding down control zooming in a bit. That’s you that’s zooming down here to the 145. So we can see that. And this is going to be I’m going to call it the AP graph data, graph data data. And then I’m just gonna cut this thing down to what we need. So what I want to do is format this whole thing.
04:18
So it doesn’t have this kind of different formatting. So what I’ll do is I’ll put my cursor on some cell to the right that didn’t get affected by funny formatting. And then I’m going to go to the Home tab, and then format paint and then I’ll format paint the entire worksheet. So there’s like standard formatting, then I’m going to format it the way I like to do it, which is to select the whole thing, right click, format, the cells, go to currency, bracket ties the negatives, and then remove the decimal.
04:49
Or mean sorry, remove the removed the dollar signs. We’ll keep the decimals. And there we have it. So that looks good. And now let’s just get rid of the excess data we don’t need I don’t need I don’t need this whole header thing because it’s just data So I’m going to just delete that stuff, delete. And I don’t really need the total right now put the total back later. Let’s just delete this whole thing, delete. And then if I want to make a pie chart out of this, I might just say, let’s put it into a little table first. So I’m going to go highlight the data, go into the Data tab, you could then add the filters.
05:22
But like I say, I like to insert a table. So I go to Insert, make a table, so that I don’t mess anything up, I’m less likely to mess stuff up this way. And then, and then I’m going to format or sorted by top to bottom on the on the number side of things. And there we have it. So now we got our data, we could just simply make a good old pie chart out of this, selecting that information. And we could say I want to go and insert myself a pie chart. And so there’s the pie chart. And then hold on, I just dragged the data, let me do it again. Cuz something funny happened.
06:02
So we’ll do it again, we’ll go to Insert, pie chart, pie chart, there it is, okay. And then you can, you know, adjust, adjust what you want. This time, let’s put the legend here. So I’ll say we’re gonna put the legend maybe on the left. Let’s put it on the right this time. And then let’s add the title, this time, this is the accounts payable, do something like that. And then, of course, we can change the coloring to like the standard format of the coloring like these items here, make them all kind of similar color, or the different colors.
06:44
And then we can change what we want in terms of the format in this format as well. So I won’t go into any more of that now. But let’s make this a little bit smaller. Now again, if you wanted to save this data or print it, you could just put this right over the data, and then it’ll kind of print then I can see Okay, then it prints basically on one page there. So that looks pretty good. You know, I could do that last time we copied it. And then we tried to hide the data. That’s the other way you can kind of do it. So then you can also say that I’m going to maybe you want to select this and create another graph like, like a bar graph. So we create the bar graph again.
07:23
And let’s make like a sideways one like that. Yeah, the numbers are kind of squished. So we’ll just do a standard one like this. And so there we have that looks good. And so a couple ways you can kind of show the data. So this is accounts payable, accounts payable. And then again, if you want to copy this into a into like PowerPoint, you can copy it into a PowerPoint if you if you want to do that, or if I want to print it and I don’t want to see the data, then like I said, we can create another tab, I can create a tab down here and say, I’m going to call this the AP graph.
08:07
And then we could just grab our graphs and copy the graph over here and say I want to copy that thing, and put it over here. And then I’m going to go to see if it fits on a page. So it fits on a page there. And then we can make this a little bit larger, we can do that for this one too. Just grab that, put it on this side. And then when I print everything onto my PDF file, if we’re printing it as a PDF, we can hide this one again. So I can hide that sheet just right click and hide it. so high that so no one even knows, it’s like I made the graph. Without any data at all, I just kind of made it hide this one.
08:47
So then we can print these two with the rest of the data. So let’s do that. Let’s go to the File tab, and then we can print it, let’s print it. And we’ll print it the entire workbook. It’s not letting me do it again, I got to go off the graph before it won’t let me do I’m going to go over here on the journal, then it lets me do it. file. Print did last time, let me do it last time, and then the entire workbook. So we want to print the entire workbook, scrolling all the way down to the 39th page. Page Number 39. There it is. So there it is. And the one above it is our prior graphs.
09:27
And we don’t and we get we hid the data. So we can successfully print this with a nice, you know, pretty graphs without having the data thing showing up as well. So we could spend a lot more time to kind of make these look nicer. But with Excel, you have a ton of options. But just to do a basic graph, it’s really quite easy to do. And just realize also that if someone asks you about the graph, I mean, oftentimes when you make a graph like this, people are not, you know, don’t do what they’re supposed to and just be impressed by the colors and whatnot, not ask you any questions about it.
10:00
Anything but they might ask you the questions about it. And you want to make sure that this ties in You say, Well, this is the accounts payable graph that ties into this report that we have generated here that’s breaking out who we owe by, by vendor. And that ties into, of course, the the balance sheet report here. So that ties into the balance sheet report here. And if you wanted to take a look at the percentages, if I was just to unhide, these, I’m gonna, I’m going to select this one and this one, right click and unhide. Just so we can see the data.
10:32
And I just want to see the AP graph right now it’s the only one I really, so if you want to see the percentages over here, then it would be if I put a total column is equal to some, this is the total, then you can always do this calculation, it’s going to be this divided by this. And then I’m going to f4 this one absolute referencing it, and then copy that down, make this a percentage, Home Tab number of percent defy it, and then hold on a second. This should be one more time this divided by this, and then I’ll try to f4 that it’s not like because it’s in a table, I can’t do the f4 thing. f4. Well, there we have it. So there we have it.
11:26
Okay, and then if I sum this up, we have our 100%. And if I if I show you the percentages on this side, then we take a look at our table that has been created. And that was to say plus, and we want to see, say, the, the data, and let’s see the data with the percents. So then you can you can kind of see the actual percents there. And that’s how the percents are basically calculated. So you have a good idea that so you might want to actually, you know, make your table and have the table ready for someone who’s gonna, you know, actually want to know what the thing means and might ask you questions about it.
12:06
Okay, so now let’s hide this again, let’s right click and hide. And then let’s go, I’m going to go back to the journal tab, and then file up top and print this thing. And we’re going to print it to the cutepdf printer, the entire workbook. And then we’ll just say print. And then it’s going to tell us where do we want to put it. So I’m going to say says, Where do we want to put it? Right there. And then I’m going to overwrite this one, and say yes, and then let’s, and then we’re looking for our, our, here’s where we put it. So I’m going to open that up, and do the double check, double check.
12:45
And then I’m going to scroll all the way down, so that I can put the second check on this one and complete it. So there it is, the accounts payable looks nice. And then right above it counts receivable, graphs, and then right to the data, all that data, the data tabs are now gone, because we hit them. And if you want to unhide them remember, and this is good to know like if anybody has, they might have hidden worksheets in your thing.
13:08
And you wouldn’t even know but if you like select the first one and then go to the end, and then right click and unhide then they will be revealed. And you can you can select those and then bring them back to two out of the from wherever the place that they hide them in. And there they are. So there’s that one and there’s that back.