QuickBooks Pro Plus desktop 2022 accounts payable grass with the Help and Use of Excel Get ready because we bookkeeping pros are moving up the hilltop with QuickBooks Pro Plus desktop 2022 Can we are in our QuickBooks sample file sample Rockcastle construction going through the setup process with the view drop down to open windows list on the left hand side company drop down home page in the middle,
00:24
maximize in that home page to the gray area, then go into the reports drop down company and financial take a look at that balance sheet standard report customizing the reports up top changing the end date to 1231 to three fonts and then the numbers change in that font to 14. And okay. Yes, please. And okay. Same thing with a P and L reports drop down profit Profit and Loss report changing that in data 1230 123. Customizing the report fonts and numbers change in that font to 14.
01:05
And okay, yes, please. And okay, so now we’re going to be taking a look at that accounts receivable report. Once again, let’s go to the balance sheet, we got the parent account on the liability side of accounts receivable representing what we owe to vendors for goods and services we’ve received but have not yet paid for entering the bills for them that have not yet been paid. We now want to take a look at the graph. And then try to think about how we can construct our own graph in Excel by going to the reports drop down. Let’s take a look at the Report Center.
01:37
Going to maximize the Report Center. We’re in the vendors and payables on the left hand side, we looked at in a prior presentation, the accounts payable graph, let’s run it again. The accounts payable graph dates up top 1230 123. We got our pie chart down below. And we’ve got our aging up above the pie chart, however, quite restricted. Let’s see if we can reconstruct it by exporting data from Excel from QuickBooks to excel and do it in Excel giving us much more flexibility. Go into the Report Center, the report that we can use is down here.
02:16
And the vendor balance summary gives us a nice clean report. Let’s go ahead and run that report. There it is changing the dates from 12, a 123 to 1230 123. And the endpoint adding up to that 26 636 92 that amount, then also on the balance sheet 26 636 92. Back to the vendor Balance Summary. What we want to do now is export this to Excel and simply use this data to create a part pie chart which will be much easier than when we did it with the accounts receivable given the fact that we have a nice clean set of data here.
02:54
So I’m going to go to the Excel drop down we’re going to go to create a new worksheet, I’m going to be putting this to the existing workbook we set up in a prior presentation. If you don’t have an existing workbook, you can create a new workbook, you do of course need excel in order to complete this process. We’re going to this Excel workbook here, opening it up and export into it. Let’s do it.
03:17
So we’re going to pull this to the right hand side now. So there it is, I’m going to call this double clicking on the A are no, this is going to be the AP graph data data. Okay, I’m going to increase the data on the right hand side to like 140 or so. And then I’m just going to adjust it as we did in the past. So it’s a nice straight set of data, I don’t need the total down here. So I’m going to remove the total, selecting the total row right clicking on it, deleting it, we don’t have any other formulas.
03:51
So I don’t have to get rid of the formulas because these are all basically hard coded numbers meaning they’re in essence just typed in there without a formula. I don’t need this top area too. So I’m going to select the column or row one, right click on that row and delete it. We don’t need row or column this time column A. So I’m going to delete it, right click on it, delete it.
04:13
I’m also going to format these because I’m in Arial eight, and over here I have Calibri 11. I’m going to make everything Calibri 11 By putting my cursor over here, Home Tab clipboard paintbrush into that cell, selecting the entire sheet paint, crushing it to the entire sheet. Then I’m going to put the number format in this cell by right clicking on the sheet right clicking on the sheet if it will let me and then we’re going to say format, format this cells and then I’m going to format the entire sheet currency brackets, no dollar sign, no decimals, and then
04:54
Okay, so there we have it. Then I’d like to format this data from Hi number two low number, which you could do by going to the data and adding filters, but I like to add a table to it. So I’m going to go to the Insert tab, add a table to it, and then enter, we’ll add a total row at the bottom. So there we have that. So we could check our number down there. And then I got my little drop down up top, and I could just hit the drop down and say I want it to go from Z to A Z to A, there we have it, the top one being the 6000 705. So next thing I want to do is trim down the number of slices I’m going to have so in the pie chart in QuickBooks selects 10.
05:39
By default, let’s do something less than 10. Let’s say we want the first like six or 7123456. And then everything seven down. Let’s say that we’re going to put that into its own category, meaning I add these up, that adds up to 7002 30. To delete this, and this is going to be 7232. And this one’s just going to be named the total at this point. So now I’ve got my data. And we can just add the pie chart by selecting that data. And going up top to insert. I’m going to select the pie chart right here. And just I like the flat pie chart because I don’t feel like it’s as deceptive as the 3d pie chart can be.
06:25
And then we could select our data however, we want to see the data within the pie chart. So that we have that we got a lot more options. Of course, with our with our data in terms of the pie chart have been the name or the key or the key or no key, I’m going to delete the name up top. And that’s the general the general idea, then you can customize your pie chart, you have a lot more customization capacity with it. If I wanted to put it on another tab and print it along with all of this other data and not print the data on the left hand side, I could cut this pie chart, add a new tab, called this AP graph without the data, paste it right here.
07:07
Gonna check how large this sheet is going to bring that sheet and see if I can rotate it to landscape. See if it lets me do that. And then I’m going to maximize this pie chart to the sheet. And then if I want to print this out, I could hide this sheet for example, right click and hide that sheet. And that sheet is still there. But it’s hidden, I’ll do the same for my data sheets. And that way, I can print my entire workbook out with the sheets that I want and then hide the Data tab, I go into the file up top, going to the printing, I’m going to print it as a PDF using the cute PDF printer.
07:46
I’m currently on a pie chart. If I wanted to print the entire workbook, I can select the entire workbook, thusly 28 pages. Let’s go out here to page 25, for example, page 25 loading up and then page 26 was our report and our report on 27. Then we’ve got our pie chart with no data tab in front of it. And then 28, we once again have our pie chart with no data tab, basically in front of it in that format, which is nice. So if I go back on over, we can then unhide the data cells, I’m going to select this tab, and then hold Shift down over to here. So I have all those tabs highlighted right click on them. And then I want to unhide the tabs, I’m going to do them one at a time.
08:41
So I’m going to unhide that one. And then so that’s the that’s the data graph here. And then I’m going to do it again unhide, this one Unhide. And there’s our data graphs. Now you could do the same thing for the other, I’m going to save this, I’m going to close this sheet, close this sheet out. If I wanted to do this to do a similar process for the other graph, which was this one up top, we could say okay, that’s coming from the report, Report Center, that’s just a bar graph.
09:15
And that’s going to be our aging detail. So this aging detail or run that one, run that one, and then change the date to 1230 123. And then we’ll export that graph to our Excel sheet in a similar fashion, exporting it to Excel, put it in the existing workbook, it should be going to the same one because that’s the one we had opened last time. I won’t even check it because I live dangerously like that it might open the wrong sheet. And if it does, we’ll deal with it. That’ll be okay. And then I’m going to maximize this out and then I’m going to say pull this over. And this is going to be I’ll call it the the AP aging graph. A P ageing graph.
10:03
And so what I really want is this data basically down at the bottom of the graph, it might be the easiest thing to do to just basically rewrite this information. So I might try to basically change the the cells from having the headers, the columns and the rows, you could do that with like a tricky method, you can copy and paste it and change them. Well, let’s try it, let’s take a look at it.
10:26
What I’m going to do first is I’m going to take this data on the middle, I’m going to copy it because I want to make it a hard coded number with no formulas, copy it, and then I’m just going to paste it right back down, using the same format using the 123 values only. That makes it so there’s going to be no formulas here, especially in the total column down on down below. Now, all we really want is the total column. So I could then delete, I could go from column two, all the way down to 18. And just delete all of those now, now that the totals are hard coated, and just delete them.
11:03
So there we have it. And that’s basically the data we want to make our graph from. But normally, we like to see it in a kind of like a vertical fashion. So in other words, I’d like to flip kind of the X and the Y. So what I’m going to do here is I’m going to I’m going to go over here, I’m going to format it in Calibri and 11. Going over here, Home tab, clipboard, and I’m just going to clip the whole sheet. And then I’m going to make these cells into a number format, right clicking on it, formatting the cells, and I’m going to go to currency, and the bracket ID no dollar sign, no decimals, there we have it.
11:39
And then I don’t need these columns on the left. So I’m going to delete these items. Now I’d like to flip, I’m going to select these rows make them a little bit larger the axes now. So one way to do that is you could just type re type it, there’s not much data here clearly. But one way you can do it a fancy way is copy these, copy your data this way. And then I’m going to paste it, let’s paste it over here, right click and paste it, but I’m going to paste it special. And I want to basically go down here and I want to transpose the data, flipping the x and y axis.
12:16
Okay, so now we’ve got the ranges of this side here. And then so I’m going to pull this data on over to the left, so there’s going to be in essence our data, we could add another table to that if we so choose. But we probably don’t need to, I can just select this data. Now I’m going to select this data not including the total on down below and go to an insert. This time, we want to just basically a bar chart, you can also look at the recommended charts.
12:43
So sometimes they’ll give you, you know, a different chart that might be in a format that you like better, but it’s picking the standard bar chart, which is what we want. And then again, you could adjust the title to it and so on. And what I deleted the whole thing I didn’t want delete that I just want delete the title, don’t need to title. And then I’ll make this a little bit smaller here, make that a little bit smaller. So there we have it. So now we’ve got the current is up through here and then and then the one to 30.
13:12
And that maps out pretty closely to what we’ve got in our graph over here. So similar kind of graph, not too difficult to create it. And then again, you got a whole lot more options with color in it, you know, making it 3d. Here’s like your standard options here that you could you could adjust your graph settings to and we could of course change the coloring of it and so on as we so choose with a lot more detail. You got the same kind of components. You could save it to another tab if you wanted to print this all out on one worksheet and so on in a similar fashion as well.