QuickBooks Online 2022 budgeted income statement, Export to Excel and modify part number one, get ready because it’s go time with QuickBooks Online 2022. Here we are in our get Craig guitars practice file we set up with a 30 day free trial holding down control,
00:19
scrolled it up a bit to get to the one to 5% currently in the homepage, otherwise known as the get things done page and the business view, as compared to the accounting view.
00:28
If you want to change to the accounting view, it’s something you can do by going to the cog up top switch to accounting view down below, we will be toggling back and forth between the two views,
00:37
either here or by jumping over to the sample company file currently in the accounting view, back on over to the get great guitars, we’re going to open a few tabs to put reports and go into the tab up top right clicking on it,
00:51
duplicating that tab, we’re going to go back to the tab to the left, right click on it again and duplicate it again. As that is thinking I’m going to see what the reports are located over here and the accounting view, which is on the reports on the left hand side. Going back to the Business View in the get great guitars, we’re in the second tab,
01:11
I’m going to be looking at an income statement reports and a trial balance. Let’s open those up by going to the business overview. We’re going to go into the reports on the left hand side closing up the hamburger,
01:23
I’m not opening up the balance sheet, this time changing things up changing things up Income Statement profit and loss here, the P and L range to the change from Oh 101 to 2202 28 to two, we’re going to run that report. Let’s go to the tab to the right, this time a trial balance going into the business overview.
01:45
We’re going into the reports on the left hand side and I’m going to close up the hamburger let’s do that first. And then type in trial balance to find it and open up the trusty T be the good old trial balance.
01:59
Let’s do the range change up top from Oh 1012 to 202 28 to two and run it. So now we’re going to be looking at the budgeting process. We talked last time about the fact that the budgeting process back to the tab to the left on the income statement is in essence generally something that’s outside of kind of the strict definition of what’s done during the accounting process in the accounting department.
02:23
Because the accounting department is there to record prior or past transactions or current transactions as they are happening in such a way that we can then construct their financial statements from them on which in part, internal users such as management can then make future decisions based on things like the budget, the budget than taking past information,
02:44
projecting out into the future with it, to try to give us a give us a plan into the future. So that means that the QuickBooks system from an accounting standpoint is not designed to create a budget in essence, it’s designed really to once budget is created, implemented into the QuickBooks system.
03:02
So QuickBooks can do what it does well, which is to generate reports, especially a profit a actual versus the budget number type of report. So what we’re going to do here is take the past period number,
03:15
export it, then to excel, and then try to try to then think about what’s going to happen into the future based on last year’s numbers, make some changes where necessary, and then import it back into Excel,
03:28
putting it into the budget area, that’s going to be the general plan, we’re going to be primarily focusing on the income statement, which is going to be the performance type of statement. So this is what we have done for the last two months. This is the only two months of data we have in the system.
03:43
This is our performance for the prior two months, we’re going to kind of imagine here for our budgeting purposes that this happened basically, in the last two months, meaning in like November and December of the prior year, so that we can imagine it happened before.
03:58
And then we can make a budget based on that information that can be a standard 12 month budget out into the future. So a little bit of a variation there. Hopefully, that’s not too confusing, but that’s the idea.
04:09
If you go to the first tab, notice I’m going to where the budget is located, I’m going to go into the cog drop down, we’re going to go then into the tools over here.
04:18
And then we have the budgeting tool. That’s where we’re going to put the budget back in once we’ve created it. But to create the budget, I’m going to export the past data, the income statement,
04:28
and then create the budget and then come back in here and put it back into the system so that the QuickBooks system can report give us reports like the actual versus the budget. Now, I actually don’t want to use the income statement numbers directly,
04:41
because I’ve got all the subtotals I don’t want all these subtotals I just want a straight you know accounts. So the easiest thing to actually export in this case will probably be the trial balance, which we can then just delete the balance sheet half of it and then create an income statement from it.
04:57
So I know this is not an Excel course so I’m going to do this fairly quickly, if you want to skip over this and get back to the budgets that we’re going to do, pretty much next time, you can do that. But we’re going to do some Excel work here. So in order to do this,
05:10
I’m going to export this to Excel, this is the plan I would typically have, when doing the budget for a company, I would take the data, export it to Excel, do the budget in Excel, and then put it back in the system.
05:20
Alright, Export to Excel, I’m going to put that into our folder here, I’m going to call it a budget teen folder, that’s where it’s going to go. I’m going to drag it and drop it, I’m going to grab it, drag it and drop it. That is mean to do to the file, it’s just a file,
05:35
it’s just a file, you grabbed it, you dragged it, and you dropped it into this pit of a folder. Okay, so then I’m going to right click on it over here, let’s make it large. By the way, let’s make it a large icon. So we can see it better, then right click on it and rename it, this is going to be the budget. We’ll just keep it at that. And now let’s open it up.
05:59
So now I’m just going to clean this thing up and make a set like a single a single step income statement, instead of a multiple step income statement with all the with all the subtotals on the way down, I just want income minus expenses. That’s it this simple income statement.
06:13
So I’m going to go I’m going to make it a little bit larger, I’m at the 145, we can even keep it there. And then I’m going to just delete a bunch of stuff. If I mess anything up, then I’m not worried about it.
06:23
Because I can always I can always create it again and do it again. So I don’t need the header, then I’m going to, I’m going to delete that put my cursor on one, the whole column.
06:32
And I’m going to go down to four. And I’m going to do this quickly, because it’s not an Excel course. But I’ll try to, you know, explain what I’m doing. So I’m going to right click on that, and delete that.
06:41
So there we have that. Now I’d like to format the whole worksheet in a uniform formatting. So you can see here I’m in Arial, eight. But if I’m over here, I’m in Colibri 11, I’m going to go to collibra 11,
06:53
because that’s more what I’m used to as my default position. So I’m going to take this item, I’m going to hit the Format Painter. And I’m going to paint brush the entire worksheet by then clicking on the little triangle.
07:04
And then I’m going to format this entire worksheet in the format that I want it to be in. So now it’s in like the basic Excel formatting, the starting point for Excel, I’m going to right click on it, and then format this thing.
07:16
And I’m going to try to make the whole thing a uniform format, which I’m going to make currency negative, our net red and bracketed and the dollar signs removed, should I keep the pennies, I don’t really need a pennies because it’s a budget. So I’ll get rid of the pennies because it’s going to be rounded anyway,
07:33
so it’ll be a little bit cleaner, possibly. So there we have it. Okay, that looks good. I’m going to make the whole thing bold, too, because that might make it easier for you to see in a presentation purpose format.
07:44
Okay, so then what we want to do is I just want the income statement side of things. So remember, this is an order of balance sheet on top of the income statement. So assets, liabilities, equity, and then the income statement.
07:58
So the income statement actually starts, like right there, that billable expense. So everything above that I don’t need, I’m just going to remove it, I’m going to put my cursor on row 25, left click on it,
08:10
go all the way up to the top, right click on the selected area and just delete that entire thing. I also don’t need the totals down here. Let’s put my cursor on call on row 18. It’s a row, it’s not a column to row, there’s only there’s columns are vertical rows,
08:27
I got, I got it, I got it. Okay, so now we’re gonna we’re gonna make the these all these expenses, the debits are expenses on the income statement, and the credits are income, I’m going to make the expenses negative, I’m not doing debits and credits here, I’m going to say all of the income is positive, all of the expenses are negative.
08:46
And so I’m going to I want to make all these numbers negative, so I could double click on them, each one of them and put a negative sign in front of them. But that’s tedious.
08:54
And there’s a little trick that we can do to do it faster. So let’s do that. Let’s do the trick. I’m going to put my cursor on the column B, this is a column this time Ctrl C, C, I know what the difference is.
09:07
And then I’m going to go back on over here. I’m going to right click, and I’m going to then I’m going to state I’m going to paste it but special. Paste it in a special way. And I’m going to go down here and I want to put the subtraction,
09:19
the special subtraction paste, boom special paste and then just paste them all like negatives. So now that looks good. So I’m just going to copy and paste that back on over here. So that those are all negatives. Now.
09:33
That looks good. I’m going to right click on this one, delete it. And then I’m just going to move these over to the same column. grabbing them a couple ways I can move them I could grab like this column by putting it right there and move it over there.
09:47
I think the easiest way is actually to cut not copy but cut which you could do by right click and cut or you could say CTRL X and then Ctrl C CTRL X and CTRL C I mean Ctrl X And then Ctrl V control x and then control V. That’s what I meant. That’s what I meant.
10:06
And so this is going to be the net income at the bottom. And I can sum this up and expect when I do just for suspensive purposes here, what do we expect to happen? If I go to the income statement, we expect to have net income at one three to 405? Is that what’s going to happen here?
10:24
Let’s check it out equals to some brackets. And this is the trustee some the most popular function. And there it is 1324. That’s it, because I rounded it right 1324, so we didn’t mess anything up.
10:41
Everything is on messed up. So that’s what we have. So that can be then our starting point, this is in essence going to be our income statement without any of the of the subtotals
10:52
I’ve just got a clean income statement, just positive numbers, income, negative numbers, expenses that don’t have a subtotal for the cost of goods sold, or subtotal for all the income accounts,
11:02
or subtotal for gross profit, or subtotal for the general expenses, the payroll, the total expenses, the other income, the other expenses, and blah, blah, blah, blah, it’s just if it’s going, if it’s income, it’s positive.
11:18
And if it’s an expense, it’s negative, that gets us to the bottom line in a single step, simple as possible, and easiest on which to build a budget from next time, we’re going to assume this, in essence, been the prior two months of data. And we’re going to kind of assume it was before January.
11:37
So this is like last November and December, pretending that was the first two months for example, and then we’ll then we’ll make a projection 12 months out based on it that we can put back into the QuickBooks system and run reports,
11:50
budget versus actual for the first two months, and then we’ll have just budget reports out into the future imagining we have not yet of course, been in that point don’t have any actual data because that’s in the future. So we’ll do that in future presentations. It will be great