QuickBooks Pro Plus desktop 2022 budgeted balance sheet Export to Excel part two, get ready because we bookkeeping pros are moving up the hilltop with QuickBooks Pro Plus desktop 2022. Here we are in our geek rake guitars practice file going through the setup process with the view drop down the open windows list on the left hand side, the company dropped down homepage in the middle, maximizing the homepage to the gray area reports drop down company and financial focusing in on the balance sheet standard.
00:33
We’re going to customize the balance sheet standard up top with a range change from a to a 1012 to 202 28 to two and then go to the fonts and numbers change the font size, bring it on up to 14. Okay, yes, please. And okay. So in prior presentations, we took our information here, which was the balance sheet information that we actually got from a trial balance report, we exported it to Excel, formatted it so that we can see the balance sheet portion of it.
01:04
And we’re going to be using that to project forward our budget in Excel. Once done, we will take that information, put it back into the system by going to the company dropped down, we will go to the planning and budgeting and create a budget for the balance sheet side of things so that we can then run reports in QuickBooks, such as a budget overview and budget versus actual for the balance sheet.
01:29
So this is where we left off last time, we’ve got our balance sheet, which we’re imagining to be the balance sheet as of 1231 of the prior year. And then we’re going to use it to project out forward into the current into the current timeframe. And we see that we’re in balanced by the zero down below. So we just adjusted the trial balance having the assets be positive numbers, liability and equity, negative debits and credits, in other words, and that gives us something that can be in balance, which will make it easier for us to do projections into the future.
01:57
Now the question is, Well, how am I going to make these projections in terms of what the balances will be? One way you can do this is try to compare this to like the main goal of of having the assets and liabilities, which is the revenue generation for a business. So what I’m going to do is I’m going to add the revenue lines up top here, and we’re going to use that to kind of base our projections on,
02:18
I’m going to put my cursor on column or row one and two, and then right click on those selected areas and insert shifting them down. Notice that it been formatted over here about the format above it, I’d like to remove that or clear that. So I’m going to go to the formatting here and clear that formatting. And then we might actually want to format below it. So let’s go here again, and actually format it from the cell below it.
02:45
Meaning I want it formatted like the cells that are beneath it, not the cells that are above it, so that we should have the same formatting there. And then I’m going to pull over from the income statement, the revenue items, this is what we’re what we’re generating on a month by month period, which we’ll use for our projections to the balance sheet. So let’s go back to the income to the balance sheet tab, this is going to be our project did sales or revenue, let’s just call it revenue,
03:10
I’m going to put that up top in January, I’m going to sum up what is over here. So I’m going to say this is going to be equal to the sum brackets. Going back to the income statement tab of these three numbers, I’m going to sum these three numbers, close it up.
03:27
And so then I’m going to say OK, and enter. So there we have it, if I double click on that, we’ve got the sum of and this is exclamation means that it’s coming from the income statement, tab over here and pulling up D two to D four. So this is going to if I copy it across, it’ll do the same relative references. So if I copy this across to all the way across like so it does the same relative references. So this is summing up the income statement items here on this tab.
03:59
Oh 2204. So I think that pulled everything over as it should, or we could double check it on the income statement here. If I go to the end, for example, in December and select these three are that’s not December, that’s the total, we get to the 10566. And that was the 10566. I also want to take a look at the revenue for the time period that has happened.
04:22
And I’m going to assume this is gonna be like the last month that the last month of December of the prior year, I’m going to say this is going to be equal to the some brackets back to the income statement tab going all the way to the left of these three, which is actually a two month time frame.
04:40
So I’m going to put the brackets around that and divided by two and that should be the same number that we got over here because we use the same number for January. And so now I’m going to say okay, well if this was the income for the month, and we’re doing it by month because that’s all we have we got the month worse that day. So if this was the income for For the last month of December, and these are balance sheet items, I can use a ratio and try to say,
05:06
Okay, what’s the ratio between the assets and liabilities that I have to the thing that we’re trying to do, which is revenue generation. And then I can, then I can use that ratio analysis to consider Well, if I have my projected revenue going out into the future, you would think possibly I’ve got a similar kind of ratio between my assets and liabilities, and the revenue generation.
05:28
So we’re going to make that assumption, we’re going to say, Okay, let’s take, then the, the 95, two, the 95 to five nine divided by the projected revenue, and that’s going to give us this, let’s make it into a percent number, group percent define. So now we’ve got this checking account number is, is 275%. of basically the revenue generation is the idea.
05:56
And then I can use that then to project out into the future based on what we’re projecting the revenue to be in the future, because we’re going to need similar asset and liability comparisons, you would think to make those differences in the revenue generation feature, that would be the assumption that we’re going to be making. So if I double click on this one, when I copy it down, I’d like to copy that down. But I don’t want this top cell to move on be too.
06:20
So I’m going to put my cursor on b two and say F four on the keyboard dollar sign before the B and a two, you only need a mixed reference. And absolute one works, though. And then I’m going to put my cursor back on this, I’m going to copy this all the way down. So we’ll copy this all the way down. And so now we’ve got our our percentages, so that looks good, you could sum up the percentages, if you so choose, we could sum them up, and that should add up to zero.
06:45
So we’ll sum that up. So there we have that, and then we’re going to use those in order to make our projections out here. So now I’m going to say okay, if if my revenue is to 34 606, this time, I’m going to get the same checking account number, this will be kind of our check figure to see that this kind of works, I get to the same amounts. So 34 606 times the 275, that’s gonna get us back to that 95 to 59.
07:14
Because we use the CIT we’ve got the same revenue here. So we would think that we would need the same kind of asset layout in order to generate the same revenue, same assets and liabilities. Notice, again, this is budgeting kind of stuff, corporate finance kind of stuff, or managerial accounting, we do have courses on that if you want to dive into that, and a bit more detail. But I’m going to copy this down.
07:33
Now, if I double click on this, this cell up top in E two is the one that I do not want to move this one, I do want to move down. So I’m going to put my cursor on a two and say f2 f4, on the keyboard, dollar sign before the E and the two and you’ll end up next to reference but an absolute reference will work. And then I’m going to copy that down. So we can copy that down here.
07:56
And we’re getting the same numbers, and I can sum this up, and the debits equal the credits. So we should then remain in balance with that as well. So let’s go to the second one, we’ll get some different numbers here. If I if I copy this over this way, it’s not going to quite work. And we’ll go it so it’s gonna say okay, that doesn’t work.
08:16
Why because if I double click on it moved this cell over, and it kept this one the same. So I could modify that. So I can copy it over make this a little easier. If I double click on it, this cell up top, we need to change to a mixed reference because I don’t want it to move down.
08:31
Because I want it to stay up top. But I do want it to move to the to the right. So this E i would like to eat a change to an F. So I’m going to say make it a mixed reference, no dollar sign before the e $1. Sign before the two. And then this one right here, which is representing the C four. I would like that when we move down for it not to change. But when I’m sorry, when I moved down, I do want it to change.
08:57
But when I move to the right, I don’t. So we need a mixed reference dollar sign before the see and not the four. And we can kind of double check that we can copy it this way and said does that work? Does that work this way? If I double click on this, now this times that that looks correct.
09:14
And if I copy this down, does it mess anything up. So I’m going to copy this all the way down and see if we get to the same numbers down below. We’re still in balance, everything still ties out. So we should be able to basically copy this on through now.
09:28
So now as I copy this down, let’s let’s copy this one down, we’re going to get some different numbers here. If I copy this down to here, I’m going to sum this up now equals the SU m of these items, summing this up to here and we’re back in balance again. Now you might go through this and say Okay,
09:47
does this work all the way through because if revenue changes and it goes up, then yeah, you would think possibly then that would result in a higher relationship of cash for there. You might say okay, accounts receivable should be reflected that seems kind of reasonable inventory that seems somewhat reasonable.
10:03
The prepaid insurance. Yeah, that seems about you know that you would think that we’d have a relationship there, but maybe not the prepaid insurance, actually, you might say, well, prepaid insurance, I don’t think you know, it’s gonna go up, it’s probably going to remain the same.
10:16
So you might say, Now, maybe not that one, maybe that one should mean should be the same. I would think I don’t think I’m gonna need more assurance, and then the furniture and fixture. So you might think this, this one here, it might go up, but you might not be buying furniture and fixture each month, you might be saying that I you know,
10:35
I think it’s going to go up without that increase in so maybe this one would remain the same on the balance sheet not going upstate and staying with the accumulated depreciation, possibly the same with the machinery and equipment and the accumulated depreciation. So I’m just going to say those equal the prior one. And then this one, we’re going to say the accounts payable Yeah, it would kind of make sense that maybe it goes up, as we as we start to make more sales.
10:59
Same with the VSA, possibly interest payable, maybe not, maybe interest payable, you know, would be around the same or possibly, you know, going down over time. So I’m gonna say, let’s keep that one the same loan payable are we going to are we going to need more financing, as revenue goes up, you would think maybe you would need more, but no, I’m going to say, financing is going to be, you know, around the same, and the loan, the loan, payables might actually be going down.
11:27
And we could look at the amortization schedules to kind of see where the loan payable would be predict how much more cash we would need. Again, we can get to a lot more detail about a cash budget, and so on and so forth, the sales tax payable, you would think that would increase in a similar proportion, possibly.
11:42
So that will keep that unearned revenue, maybe that would go up in a similar proportion, and earn this is unearned revenue ended alone payable long term, I’m going to say that’s the same, and then opening balance, and then we’re not event owners investment, I’ll keep that I’ll keep these the same, I’m not going to keep that the same. And the draws will keep the same.
12:07
And then I’m not imbalanced down below. And I’m just going to re implement the plug down here in the equity, which is the negative sum of these items. Now, that’s just a quick, that’s a quick kind of analysis, I’m just going to use that information to show you something that’s in that’s in balance,
12:26
I’m going to copy that across and just use these numbers to then get back into QuickBooks here and put that in place. But notice, you get you can kind of get an idea of where you think you’re gonna stand, you can get a lot more formal on this, doing a cash budget, and so on. But let’s keep it there. And let’s see if we can copy this across. I’m going to select all of this now and say, Can we copy this whole thing across this way?
12:52
Where we had we had December Lee, that perfectly situated to December here. So that looks good. And then if I double click on this, we’re picking that up correctly, that looks like it’s picking up the right numbers, let’s look at November, that’s picking up the right numbers, that’s picking up the right numbers, that is now just taking the prior month because we don’t think it’s going to change that is just picking up the prior month. I think that’s correct, because we don’t think it’s gonna change.
13:21
And then down here, we’re multiplying the liabilities out, which we’re assuming it’s going to go up as we increase the revenue to this is not going to change, we just took the prior amount. And so I think I think it’s populating the way we want to we’ll just, this is just a quick overview.
13:41
And notice, we had to kind of force it to be unbalanced down here. And that’s one way that you could do that when you put this information into like QuickBooks, you don’t have the same kind of thing that makes you be unbalanced as you do when you enter the data into the normal accounting system, which it kind of forces you to be in balance. So in any case, that’s what we’ll use here.
13:59
Let’s put some brackets around this thing, make it a little bit more fancy. Put some brackets around it, maybe Font Group brackets, what did I do? That’s not brackets, let’s put brackets around it. Let’s put a total of Well, no, we don’t need a total total doesn’t work. That’s where we land as of December.
14:17
So that’s what we expect to be at in December. So then we can use this. I think that’s it. Did I do anything, do anything wrong people do you think do you see anything horribly, horribly wrong, that I should like fix, or the world will end as we know it? It’ll be the end of the world as we know it. But I feel fine. So now we’re going to take that and we’re going to implement this. I think we can just implement this into our QuickBooks system.