QuickBooks Pro Plus desktop 2022 budgeted income statement Export to Excel and modify port number to get ready because we bookkeeping pros are moving up the hill top with QuickBooks Pro Plus desktop 2022. Here we are in our get great guitars practice file in our previous episode, we took a look at the income statement the profit and loss for the two months of operations, we basically reconstructed it in an Excel sheet, not by exporting the profit and loss.
00:31
But by exporting the trial balance, which is in essence, a list of accounts, exporting it to our Excel worksheet, and then modifying it to basically just the income statement. So we have our list of accounts and we double checked our net income on the bottom here to the net income in the profit and loss.
00:50
And so it looks good, we’re good to make our budget from it. So now we’re just going to construct in essence, our budget from this lineup. So I’m going to do a little bit of modification in to this worksheet. And think about how we can use this data to kind of project out to 12 months into the future.
01:08
We’re imagining now that this information, these two months were the prior two months, like November and December of the prior year, so that we have a basis on which to project out our 12 month time frame, which is what we’ll work on now. So first, I’d like to have my headers up top here. So I need another space, I’m going to put my cursor on the number one, we’re going to right click on that entire row and insert and that’ll insert above it.
01:34
And then I’m going to type up here just January J in Feb, and I think that will be enough for for the system to then let’s let’s actually leave another space, I’m going to I’m going to cut and paste it. Let’s take these two and CTRL X and paste it right here. And let’s make a skinny column first column C, let’s make column C skinny, we’ll make that a skinny. So look, there we go.
01:58
So now it’s not right next to each other. And then I’m going to select these two and see if we can copy this to the right, putting our cursor on the fill handle and see if it recognizes the pattern it does, you can see it’s telling me the months that it’s going to be laying down. So there we go, we’re going to put it up to December. And there we have it, Let’s center it while we’re here, I’m going to go to the alignment and center it, I’m going to make this my header column,
02:20
I usually make them black and white. So I’m going to go up top and say let’s make this black. And let’s make the letter in white. So now I’m going to I’m going to start in January and use what you would think would be the normal kind of baseline from January and then we’ll go forward.
02:37
Now the typical progression that she would have on these types of accounts is that you would expect either the income and expenses to be the same all the way across. Or you would expect possibly the income to have a gradual increase over time. Those would be the the normal two, two kind of patterns that you would think on the income side, the expenses, same thing you would expect, either they’d be the same all the way across, or possibly have a gradual increase.
03:04
Or possibly, you could have some expenses that are going to be in certain months for one reason or another that would be a kind of a more unusual thing. On an accrual basis. If you were doing a cash basis type of of system that that might be more likely to happen. So January, we’re just going to start off with what we were kind of expect here for each of the line items, I’m
03:22
going to take this one and divided by two, because this was two months worth of data. So if I take one month’s worth of data, we would start out at the 1130. Now let’s assume this one’s going to be the same all the way across. So one way, there’s a couple ways we could do it, I’m going to do it this way, I’m going to put my cursor in February and just say equals the one before it.
03:43
And now I’m just going to copy that across, which will mean each one across will be equal to the one before it. So I’m just going to say let’s copy that one, all the way across. So there is like kind of a standard format you would expect. And let’s just start by doing that all the way across.
03:59
This is how you can make kind of a quick budget. So if I did this all the way across, notice this is that cell divided by two, if I copy that all the way down. If I just copy that all the way down to here, it’s gonna take the relative cell reference. So these are all taking the cell over here and dividing it by two. And then if I was to, if I was to take this cell now, which is equally the one prior to it and copy it all the way down, it’s just going to do the same thing. So it’s going to just copy the same number all the way across. And I can do that all the way across.
04:33
Now I can select all of these cells, and then simply copy it all the way down. And then we’ve got kind of our most basic type of budget that you would that you could basically consider doing. And then we’ll add some modifications we’d have the total over here, and the total for the for the 12 months would equal to the sum of that’s the backwards brackets, these brackets the sum of all this not including the first column have the 2000 to 60 and enter. And so that would be the total.
05:03
And I can copy that down, copying it down, putting my cursor on the fill handle and just dragging it down, there’s our total, I can then format paint that total column by going to the Home tab, clipboard, paintbrush format paint it. And there’s our most kind of basic type of budget that will do some modifications, I can also copy the income down, this is the sum of these write the sum of these and I can copy that across, which it just it automatically did it. So it copied it across, let’s put an underline on all this stuff, underline here. And then we can double check this last number. By calculating it,
05:47
we calculated it this way. So horizontally, I can double check it calculating it vertically, as well. So it should be the sum of this. So same thing, so that looks good. So that’s going to be basically our starting point. So now let’s go in and modify some of these items. Now let’s imagine that we think that the rental income is going to go up by a certain amount.
06:11
So let’s say let’s say we’re going to say it’s going to increase by a percentage, like a 5% increase. So if I was to think about that, I can say okay, let’s pull up a trusty calculator here, and say, we’re going to have a 5% increase all the way on a month by month basis. So I could say okay, then that would mean the 1130 times point oh five, plus the 1130 would be the 1186. Or I can take the 100% plus 2.05, the 5% 105% times the 1130, to get to the 1186. So that’s what I’ll do in February,
06:47
what I’m going to do, instead of just taking the prior number, I’m going to take it times the 1.05. And there we have it, and then I’m going to copy that across, which will do the same relative format references all the way to December. And so each cell is taking the one before it times 1.05. So that’s one one way we might say I, that’s what I expect to happen going forward with what we’re projecting to happen to go forward for whatever reason, right?
07:15
And then on the sales side of things, let’s imagine this one, let’s do a similar kind of thing. We expect it to go up, let’s say by 10%, on a budget basis. So let’s say I’m going to take the February number and multiply it times the 1.1 110%. And then again, we can copy that all the way, all the way across to December.
07:39
And so we’re going to say it’s going to be gradually increasing. Hopefully, that’s our plan. And let’s say the other way it could increase, we could say, well, what if we want it to increase by just $1 amount, like it’s going to increase by $1,000, we think and not by a percentage. So we might say, Okay, let’s take the February take that, plus $1,000. So which that’s the other way we can kind of do this.
08:00
And then again, we can copy that across. And it’ll take each cell prior to it, plus $1,000. So that’s another way, another way that we can kind of kind of think about how it’s going to increase. Obviously, these types of increases would depend on future circumstances economy, the budget, and so on and so forth, the cost of goods sold typically has a relationship to the sales item.
08:22
So if I look at the cost of goods sold, oftentimes we have a markup that’s somewhat consistent with the types of inventory that we have. So we can see that the cost of goods sold is the if I take the 22977 divided by the 29 226, we’ve got about 78% Cost of Goods 78% of the of the sales, the sales represent sales of inventory. So the cost of goods sold, it’s kind of related to the sales of inventory account.
08:49
And so so if this, if this one is going up by 10%, you would expect the cost of goods sold to go up by 10%, as well. Or you could try to think I’m going to take the cost of goods sold to be all the time. Point 787 78.5% of the sales line. So what I’m going to do is I’m going to I’m going to take this February and do the same thing here, I’m going to take this times to 1.1. And then copy that across. So Cost of Goods Sold should be going up by the 10% all the way across.
09:23
And let’s double check this last number see if it makes sense. If I take this 65556 divided by the sales number of 83384. We still have that point 787 8.6% Then the expenses are much more likely to be more consistent. So the bank service charges Yeah, I would expect that to be about the same each month. The depreciation, it depends on the depreciation schedules, we might get more detailed on the depreciation schedule. But if it was a straight line depreciation method that we’re using,
09:57
you would expect it to be somewhat the same per month. You get that from the tax, as we talked about the depreciation, get a more accurate number, notice that that’s not a cash related number, that’s going to be an accrual concept number, the insurance expense. If we’re on an accrual basis, you would expect it to be somewhat similar going across, if you’re on a cash basis, then you might only pay the insurance once or twice a year.
10:18
And therefore you might then have have a difference in your in when you’re going to be putting your insurance in place. So if it was like, if there was a cat, just let’s just pretend here, if it was a cash basis, I might say I got one in, we paid I think it was in February. I’m 1000, negative 1000. And then you might have this big stagger thing happening.
10:41
Until some future point, let’s just pretend I know this is different than what we did in our let’s just say we got it, we got another one out here that we expect to pay it again in October, or let’s say September 1000. Right. And then we could say, so you might have something like that that’s much more likely to happen on a cash basis. Because on an accrual basis, we would, we would even across the time, you know, the timeframe. And so then we’re going to say that we’re gonna say we got the internet expense.
11:11
And it might be more something like this, like it was closer because we said it was 12,000 for a year. So you might say it was more like, you know, 6000, that it would be here. And then we’ll say we’ll say 6000 here. So let’s do that. Any case, anyone’s interested internet, you would expect that to be pretty similar. You might say, hey, inflation is going to happen.
11:35
So maybe you you know, increase it with that or but over a year, it shouldn’t be that significant, you would think one year’s timeframe. So and then, of course, the miscellaneous should be somewhat standard office supplies you would think would be somewhat standard, the payroll you think be would be pretty standard, unless you were unless you were to be thinking that you’re going to, you’re going to level up on the number of employees that you’re going to have.
11:58
So you might have an increment there or raises. So you might say, yeah, you might say that might increase. So if the payroll was to increase, let’s say, let’s say the payroll is going to increase, we think you could do that periodically, or just say, let’s say we’re going to say it’s an increase times point. Well, let’s say it’s probably an incremental increase in payroll.
12:19
So let’s say that we’re going to, we think by July, it’s going to increase by, you know, 10%, better. So we’ll take this times point one, or 1.1. Increase there, and then we’ll keep it there for the rest of the timeframe. So you might have some kind of increase like that. Let’s kind of try to estimate the payroll taxes, we’ll say, well, what’s the payroll tax gonna do then? Well, we can say the payroll tax before was 486.
12:47
Over the 6983. Was point 695 percent about so this payroll would be equal to this times point oh, 69597. About so that, does that make sense? Because we got this number. So we got 535 divided by the 768 2.0696. Is that the same? I can’t remember, I think it is. Point Oh, 696. And then 486, divided by the 698 3.0695. Close enough. So that’s the payroll. So then we’re going to say telephone, we would expect that to be somewhat similar.
13:29
So if unless we increase the business, this one, we would say utilities, probably the same, the gains, let’s just say I don’t really expect the gains, because we sold the gains, we sold our stock. So this is an unusual thing. So maybe that’s not something we expect to really be happening. And the interest really depends on our financing. If we expect the financing to be the same, it might actually go down, because you would expect it to be going down periodically.
13:53
So let’s say that that one actually goes down this times point, nine, five, say, so it’s gonna decrease and then I’ll copy that all the way across, something like that. algo como es? Well copy that across. So that’s where we’re gonna stand here. So that, let’s make it a little fancy here. Let’s put some borders around it. Let’s take this stuff. And put some borders around it maybe by going to the Font group and putting some borders in place. That looks pretty good.
14:27
So now in the next time now we’re going to take this we’re gonna imagine this to be our budget. And then is there anything wrong with it? Let me know if you’ve seen anything that I messed up on. Did I mess up on anything here is something Did I do something horribly, horribly wrong? How come I got the sense the net incomes the same all the way across? Is it really because it’s no it’s not?
14:50
That’s something that’s horribly wrong. I hear you. I hear you this should be summing up this way. This should be summing up this way. And then we’ll sum this up this way. And so then let’s pull this one all the way. I like doing this one all the way across this way. Okay, and then I’ll double check number we’ll sum up this way. That makes more sense. Now we’ve made sense.
15:23
Okay, so that’s the double check. And so now we got this loss that happened over here, we got to make sure we got the cash flow to cover the loss. Okay, so that’s pretty good. So now we’re gonna next time I’m going to save this. We’ll enter this into QuickBooks. Now that we have it. So now we’re going to go to QuickBooks, we’re going to go to the company. We’re going to go to the budget and planning and enter the data so that we can run reports with it.