QuickBooks Pro Plus desktop 2022 accounts receivable 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. Here we are in our free QuickBooks sample file sample Rockcastle construction going through the setup process with the view drop down the open windows list on the left hand side company dropped down home page in the middle, maximizing that homepage to the gray area, opening up the major financial statements reports drop down company and financials starting with that balance sheet standard report customizing that report up top changing the end date to 1230 123 font size in the fonts in numbers changing the font to 14 on down below.
00:48
Okay? Yes, please. And okay, same thing for the P and L reports drop down company and financial P and L ended 1230 123. Customizing that report in the fonts and the numbers changing the font to 14 and okay. Yes, please. And okay, so we’re going to be looking now at a graph, we took a look at a graph in terms of the receivable graph a couple presentations ago.
01:17
Now we want to see if we can reconstruct that by exporting data from QuickBooks to excel, creating the graph giving us a whole lot more flexibility with it. So let’s go to the second item down here, our balance sheet, we’re looking at the accounts receivable representing what customers owe us for goods and services, we’ve provided either issuing an invoice or with the issuance of an invoice, let’s look at the supporting reports. Now by going to the reports drop down, let’s take a look at them. And the Report Center within the Report Center where on the left hand side and the customers and receivables, the main reports being the AR Aging reports.
01:58
But we really are looking at this graph, which we looked at last time, let’s go ahead and run that report and take a look at it. And so we’ll run the graph. So this has a static graph, I’m going to change the date up top to 1230 123. And so there we have it, and that adds up to the 103 780 to 93. You’ve got this nice graph that we would like to have more flexibility with however. So let’s go back to the Report Center and say let’s what we would like to see is just simply a list of who owes us money by customer. So we’ll go on down to the customer Balance Summary down here.
02:36
There it is, let’s run that item and see what it looks like changing the date up top to 1230 123 to 1230 123. Now it’s got a lot of subtotals, basically because they’re in the job cost system. But we have this detail by customer. That brings us down to the 1037 8293. Let’s let us take this data now and create our information so that we can use it for our pie chart by exporting this to Excel. So I’m going to go up top to excel, I’m going to create a new worksheet. Now if you already have the workbook set up that we’ve been working on, I’m going to take it to that workbook.
03:18
But if you don’t, then you could just create a new workbook, I’m going to put mine to an existing workbook which we have been working in on selecting that workbook. And then I’m going to say export the report there we have, I’m going to grab that report, I’m going to pull it all the way to the right now. So it’s pulled to the right, I’m going to rename it double clicking on a our graph, I’m going to call it this will be our data, I’m going to increase that data. Now it’s a little bit, it’s a little bit more convoluted here because of these subcategories.
03:49
But that will give us a little bit of work that we could do within Excel to practice our Excel skills, what I’d like to do is remove all of the kinds of formatting that’s going to be involved here. So you could see here that there’s a formula, what I’d like to do is just see these numbers as hard coded, or just like typed in type of numbers. So I’m going to select this whole column, right click on it, right click and copy it. And I’m just going to paste it right back down in the same area, but 123 with the values only, so there are no formulas.
04:20
Next thing I typically like to do is notice that the formatting here is on Arial and eight. And the default formatting is Calibri 11. I like the Calibri 11. So I’m going to I’m going to change everything to Calibri 11. By going on the sell out here, paint, brushing it and then selecting the entire worksheet with the triangle. There we have that then I’m going to format the whole worksheet into a number format. So I’m going to right click on this whole worksheet format the cells and I’m going to make them then instead of general currency, and negatives, bracketed, no dollar sign,
04:56
I’m going to remove the decimals. And so there we go. have that. And so then I’m going to remove anything I do not need, I don’t need this first column up top row, I’m going to delete that delete that row, I don’t need the last row down here, the total, I’m going to delete that total row or column. And then we have these two skinny columns they try to make every time they have an indentation, these kind of skinny columns. And so I don’t need this first one, because that’s where the total was. So I’m going to remove that one. Deleting that, right click, if it lets me right click, it’s kind of freezing on me.
05:34
Right click, there it is delete. And then I’m going to delete all of the all of the items that are that are doubling up, meaning we’ve got the person’s name, and then the detail, and then the total person’s name, and then the detail, and then the total name detail total. So what I want to do is just keep the totals and delete everything else. So I’m going to I’m going to the first ones, my test client, it doesn’t have any subcategory. And this is how it might normally be if he didn’t have this special job cause kind of situation. But what I’m going to do now is remove the added detail. So I’m going to I’m going to select, for example, this one, the to the this row and the row here and just keep the total, which is now hard coded doesn’t have a formula in it.
06:19
And then left click and delete. So I’m going to delete, and then I’m going to select this one, this one and this one, right click and delete. And then we’re going to take this one and this one, right click and delete. And then this one, this one and this one to get down to the total, right click and delete. And then this one and this one, right click and delete. And then this one and this one, right click, and delete. And I’m going to do this on down for the rest of them. So there we have it.
06:53
And then if I total this up and kind of make sure I didn’t mess anything up, I’ll read total, it equals the sum of these items and said, Did I delete anything I shouldn’t have, I still come up to that 1037 83, which should match what’s on QuickBooks, total amount, 10378 threes, and that’s what’s also on the balance sheet. So I didn’t mess anything up. So we’re still in the totals. Now I want to remove these totals all these total columns over here, and just have the name. The first one’s a little bit of a trick, though, because that first one doesn’t have that total column, because I only have the name, all of these that have it. Notice there’s a trend, there’s a total and then the name,
07:31
I want to remove that first word, you can use a fancy formula and Excel to do that. Or you can do this little trick, which I’m going to copy just that one, I’m not going to take the first one, I’m going to paste it over here, widen this row. And then what I’d like to do is basically retype just the name, and Excel will see the trend of need, not including the total. And it’ll then copy that trend. So if I say Allard, comma, Robert, and then enter and then B you are see now it’s it’s recognizing the trend, saying oh, they just wants to skip the total and type the rest of it. And that’s correct.
08:08
And there we have it. So now it’s a nice little trick without any fancy formulas to basically remove a total if you need to. So then I’m going to copy this set, going to Ctrl C, kind of put that right here on top of these items. And then notice I got column A and then B, the skinny column doesn’t have anything in it. So I’m going to take that whole column, delete that column, right click and delete. I’m gonna delete these columns, right click and delete them. So there we have it.
08:39
So now, I hope we just have our numbers I’m going to delete the total for now, then I usually like to insert a table, because what I would like to see next is to see these items in order from from largest to smallest, I could do that by just selecting the data and then going into the data item, and then selecting the largest to smallest with the filters. But I like to basically insert a table oftentimes, because that’ll make it so I don’t mess up the data or kind of scramble the data as easily. So I typically will go into insert a table, just a normal table. And then I’m going to say OK, and there it is. And then I will oftentimes put a total column on it.
09:21
So the total down below so now we got our total. And that gives us our little dropdowns up top, which I can use to sort I’m going to use this to sort from Z to A down. So there we have it. So now we got our information. Now we can make our pie chart from this. This is a perfect information to make a pie chart, because it’s giving us our comparison, you know, or comparative data. But we would it’s going to be a pie chart with too many slices in it because you’ll recall that this pie chart over here stopped at I believe it was 10 it gave us like 10 1-234-567-8910
09:59
And then every Anything else was in other, let’s try to mirror that. And notice you can do it, you can do anything you want, you don’t have to do the same format, but 1-234-567-8910. So let’s stop it there, and then everything below it, I’m going to basically make an other, I’m going to say this is going to be other, so I don’t have too much stuff, I’m going to look at what that number comes out to that comes out to these 6279. Put that right here 6279. And then I’ll just delete everything underneath it.
10:33
And so there we are. So there we have it, and it should still come out to that 1037 83. Now we have the information, I can just insert the table at this point in time, I’m going to grab the names and then the data and go to the Insert tab and just simply insert in the Charts group, a pie chart, entering the pie chart, so there we have it. And so then we’ve got this one, and now you can, you can have a lot more flexibility, adjusting how you would like it to look. So you might want you know, this is your standard options, you’ve got the numbers inside the pie chart here.
11:09
So you’ve got these options, looking like so. So that one looks kind of nice looks kind of neat. That puts the numbers inside there, you can also do what they did, which which was has that 3d pie chart, which again is a little bit tricky. And let me kind of show you what I mean with the with the 3d pie chart. If you make it, five, undo the pie chart. And we make it a 3d pie chart going here, pie chart, 3d, this is kind of mirroring what they gave us. But notice, you can then rotate this chart, and it’s a little deceptive,
11:47
I don’t like using them because they can be used, I feel like it’s, you know, they could be used to DCF. So when I see something 3d like this with a pie chart, it makes it makes me suspicious personally, that’s why I don’t feel like it’s honest sometimes. So I don’t use it. But it’s kind of neat that it looks like that. And if you wanted to like make emphasize one to make it look bigger or smaller, you would take them you could rotate this pie chart, and make like the big icon on the back. So it looks smaller, right? This this blue pie looks smaller than this piece over here, even though they might be you know, similar in nature. So that’s why I typically don’t use this kind of pie chart.
12:23
But you could do that, if you so choose. And so I’m going to go back to the normal pie chart, I’m going to go say, here’s our normal pie chart. And then you might want the data inside, like we had there and in the chart down below, you can remove the title up top, because maybe you got the title on the tab making it a little bit back bigger, possibly you don’t, well, you’re going to need the key down here, you could choose something that has the names on the side, something like this, if you use something like this, you might even want to break it down less than 10.
12:56
So we could even break it down and say I just want the top, you know, 123456 Maybe, and then have everything down below adding up to the to 8879. So I’m gonna say 28879. And you could experiment with how many slices you know of the pie that you want to be putting in place, you might want to re input it in that case, selecting the pie chart insert, pie chart here. And then we now have less slices. So I can I can use that information where the data is like inside of it, something like that could be more appropriate, removing the title tab up top.
13:39
And then you have a lot more flexibility then to see how many slices of the pie that you are going to want to have. And then this one should actually be other than other. So there we go, How many slices of the pie that you’re going to want to have. And then you can rotate it, you got different color options that you could do on the pie chart, of course, here. So you got your coloring options on the pie chart, standard coloring options, and so on.
14:09
And then you can also then rotate the pie chart and change the size of the pie chart, and so on, you have a whole lot more detail with the pie chart. Then once you have the pie chart set up, let’s let’s choose one other thing. Let’s make it so I could see some numbers here.
14:24
So I’d like to see the percentages. Now I’ll keep that but this one color, you might want to change that one color because it’s dark. But in any case, let’s let’s take a look at that. Then you can say okay, well where did these percentages come from? And I could say okay, well if this is the total down here, these are percentages of the total. So I could say this is equal to this divided by the total. And if I make this column, then a percentage column, there’s our percentages. So that 14% is here. The third the 13% is is here.
15:00
Whereas that 13%, Heather looks like it rounded up to 14 here for some reason, the 13% here, the 12, for Robin, and then the 10% on the test customer and then cook at the nine and then the 28 for the other. So those are just some ideas, then then you could hide your data, if you wanted to, you could if you wanted to put this all on your Excel sheet and print out the whole Excel sheet using the CutePDF printer, for example, I could select from here to a right click.
15:34
And I could hide that we could also if we’re thinking about how we can get this chart to print in our information with this Excel worksheet to put it on one PDF file, as we’ve been doing in the past to print it with all these other reports as part of our reports, we could create another tab, make this this the AR graph data, and then put the actual graph on the other sheet. So I’m going to pull this graph, I’m going to cut this piece, and I’m going to put it over here. So that we have it here,
16:04
we could check to see how big we can get this thing. So I’m going to put it till it till it fits the one page, like so. And we might even want to say that this one would be bigger if I could do it landscape, maybe maybe even make it landscape. So on the orientation and make it as big as we can get the chart. And then you can actually hide this first tab, if you wanted to, you can right click on it, and actually hide the tab. So I’m going to hide it. So then when I print it, it should just print the actual graph and not the data. So in other words, if I went up top, and I went to the File tab, and I said I want to print this thing using my cute PDF printer to print it to one PDF file, there it is on that page.
16:47
If I wanted to print the entire workbook, then there’s going to be the how many pages 27. Now let’s just go to the end like page 20. And see what it looks like on page 20 of our data, that if we just scroll through this, we have all the previous information scrolling through to page 22, page 23, page 24, page 25. And then page 26 is not the Data tab, you’ll notice because we hit it, and then it goes to page 27, which should be the actual graph. So we kind of hid the data and we can basically get the graph to be formatted in there. So I’m going to go back on over didn’t, it didn’t seem like it formatted it landscape landscape. In any case, I’ll keep it there.
17:37
So I’ll make this this is the actual graph. So this is the AAR graph. And now I’m going to unhide the Data tab. The way you do that as you click here, and then I’m going to click on the one next to it and holding down shift. So they’re both highlighted, right click on those tabs and unhide them. And so there’s what I want to unhide I’m going to say that’s the one and so now we’ve got our data tab back if we wanted to format it and then we’ve got our actual graph on the next tab to the right. These items and then it doesn’t show it interesting