QuickBooks Online 2021 budgeted income statement with the help and use of Excel, let’s get into it with Intuit QuickBooks Online 2021. Here we are in our grid guitars practice file, we’re going to be exporting our information from QuickBooks to excel that we can then use to build our budgeted information, which we will then take and put back into QuickBooks so that we can run reports with it.
00:26
So we’re going to do this by going to our tab up top right clicking on it, duplicating that tab, I’m going to do that once again, right click on the tab up top, duplicate that tab. While that’s thinking, I’m going to go back to the first tab, just to remind us that the actual process of the budgeting process within QuickBooks will be in the cog up top. And then we would go down to the tools area, we want to go to the budgeting items, then add the budget here.
00:53
And then we have our budget team information. However, in order to get the information to do the data input into the system, we’re going to export reports from QuickBooks to excel and use it to generate the budget and then do the data input for the budget into QuickBooks. So that we can use QuickBooks to do what it does well, which is generate reports with the data that has been input to it. Let’s go to the tab to the right this time and open up our trusty trial balance over here, we’re going to go to the reports down below to do this.
01:21
And then I’m going to go to the trusty trial balance, the good old trial balance, open that up. And we’re going to arrange change it for the data that we have in here, which is two months. So it’s going to end on Oh 22821, run that report. Close up the hamburger hold down Control, scroll up just a bit. And so there we have it. Now I’m going to go to the tab to the left. Now if I go to the tab to the left, we’re going to open up then go into the reports down below, looking at the income statement, the income statement.
01:52
So I’m going to go to the profit and loss, which is also known as the income statement pretty much and then I’m going to end date it Oh 22821. And run, I’m too zoomed in here, there 125. That’s where I want to be. And then I’m going to close the hamburger. Now the starting point for our budget, we have the information for 2021. All we have is January in February.
02:12
So instead of using last year’s numbers, 2020 numbers, which we don’t have, because it’s a new company, or new data and the software at least, therefore, we’re going to use what we do have, which is the first two months to project out to the end of the year. So that’s going to be then our objective, we can start this by basically exploiting the data that we have either if we had the prior year prior year profit and loss, or we can take the data that we have, which is the current two months that we’re going to use to project out forward.
02:39
Now if I do this with the profit and loss, however, I’ve got all these subtotals that I don’t really need, I don’t really need them for my budget, I just want the straight data input. So I prefer to use something like the trial balance as my starting point. So I’m going to take the trial balance for this two month time period, I’m going to export it to excel. And then I’m going to start to format my Excel worksheet so that we can use it for our baseline budget for the year of 2021. So we’re gonna hit the drop down and do that I’m going to go drop down and open it up in Excel. Because I’m in Chrome, I have it down here. So I’m going to be opening this up in in Excel here.
03:14
And then we’ll do some formatting in Excel. And again, I know this isn’t an Excel course. So we’ll provide this information to you if you don’t want to do the formatting in Excel. But I think it’s useful just to see how you can export data and then format it and then import data and the use of when you want to use Excel possibly, and when you would want to use QuickBooks. So I’m going to hold down Ctrl scroll up just a bit, we’re going to save this, I’m going to go to the File tab, going to save as we’ll put this into our folder, I’m going to call it budget, so we’ll call it budget, and then we’ll save that.
03:46
Okay, so I’m going to do our same kind of thing we did last time with our with our adjusting entry worksheet, or similar thing, I’m going to put this in the format of debits and credits. So I’m gonna I’m gonna take my data and you might want to actually copy the tab down here to copy the tab, I can hold down control, and then drag this tab, left click and drag it, and then you have a copy of it. So if you mess anything up, you can kind of pull the data from the copied tab. And so what I’m going to do here is I’m going to hold Ctrl scroll down just a little bit more.
04:13
And let’s delete the header. So I don’t need the header. So I’m going to just remove those entirely. I’m going to remove the headers, delete those, and then really, I don’t need any of the balance sheet accounts. All I really want is the income statement accounts. So I can then go down here and say maybe I just want to remove everything down to the to the revenue accounts.
04:33
So that’s down here that with unemployment, here’s the equity. There’s my first revenue account right there. So owner’s equity on up. I’m just going to remove all this don’t need it. I’m going to just scroll up and remove all that. delete that. Okay, so that is gone the total column, I’m going to remove the total column. I’m just going to delete the total column.
04:55
Don’t want the total, delete that and then I’m going to get rid of this date thing. Down here to don’t need that. So then this second now we basically have an income statement. These are all basically the expenses. This is the expense side, and this is the income side. So I would like to make all the expenses negative. So I’m not going to use debits and credits here, but rather a plus and minus kind of format. And I’m going to put all the expenses as negative, so that I can do this whole thing in one column.
05:22
So to do that, I’m going to I’m going to easily do that I’m going to right click here, and I’m going to copy this, I’m going to copy this whole thing, I like to test it out on some other columns. So I’ll do it over here. And I want to paste it in a special way, I’m going to paste it, but it’s super special. So I want to go down here and go to the other specials. And then I want to go down and I want to subtract it, making it all negative. So save that. And there it is, it made them all negative. That’s what we want. So I’m going to copy that, or let’s cut it, I’m going to cut it.
05:50
And then I went Ctrl X to cut it, and then I’m going to paste it right on top here. So now I’ve got all these numbers and they’re negative now. So that’s nice. Now I want to format the cell a little bit different. Notice this is Arial, and eight up here. And over here, we’re at Calibri and 11. I don’t want those two different formats, I’d rather have the Colibri and 11, personally. So I’m going to keep that I’m going to put my cursor on some cell out here, go to the Home tab, clipboard format paint, and then paint the entire worksheet so that now it’s all Calibri 11.
06:20
Now I’m going to format it the way I want to format it because it’s my worksheet and I’m going to format it the way and I’m going to right click to do that, go down to the Format Cells down below. And then we’re going to say I’m going to make a currency and dollar sign, no dollar sign. And then let’s do I don’t really need the pennies, I’m going to take away the pennies because this is a budget, so I don’t really need like pennies, and then I’m going to make negative numbers read like that. And then save it. And so there we have it.
06:49
And now I’m just going to pull this stuff into the same column. So I’m just gonna I could highlight it one way I can highlight it, grab the side of it like this, grab that left click and drag, it’s doing the same thing to hit this one, and I say Ctrl x or cut, and then paste, it’s actually the same thing as dragging and dropping, which seems kind of weird, but it’s the same, same thing. And then this is going to be the net income equals the sum of this should be the net income. There’s the net income, hopefully, let’s check it out by double checking it to what’s on QuickBooks over here.
07:22
So if I go back to QuickBooks and go back to my PP and L, here, we got the 6960 85. And notice we got rid of all these subtotals. So we just got a nice clean, just just the numbers report 696 0.85. And then back here, we are at the 696085. So this is the net income, net income. So there we have that I might want to make this like a different color, maybe let’s make this like black and white down here, black and white to say that’s like my, my total column.
07:53
And then up top, we might want another column. So I’m going to I’m going to add another row. And so I’m going to use these numbers to project out into the future. So I’m going to, I’m going to add a row up top, right click and insert. So now I’ve got some header, a header row. So this is this is Jan and February, or let’s just call it our baseline data two months. So this is two months of data. And then I’ll make this up top black and white too. So black and white.
08:23
And let’s center this, I like to make the whole thing bolded for some reason. So I’m gonna make the whole thing bolded which means you can’t make anything extra special, bold, because everything’s bold, but I like it all bolded. So then, then we’re gonna say I’m going to use this to basically project out, I’m going to leave another space here, just so I can see that my projections a little bit different. And then I’m going to say this is going to be January, February, once I have two months, I should be able to drag and drop this, and excel should be able to see the routine here.
08:53
So the next one should be March, right? March, April, May, June, and so on out to December, there we have that, let’s go ahead and then center, THIS HOME tab, alignment center, and then I’m going to font it make it black and white again. So there’s our header column. So then our starting point is going to be straightforward. This is two months. So I’m going to start off just predicting that, you know, one month would be that divided by two, if it was if this was 12 months for the last year, we could take that divided by 12.
09:26
Or we could take the actual data by month from the prior year, and export that and use that as our starting point. But really, I want to kind of I kind of want to kind of make it all even to start with right and then go in and think about the changes that might be happening from month to month, what kind of fluctuations we’ll have from month to month. So I’m going to take this I’m going to say this is going to be equal this divided by two equal that divided by two. And then I want to I want to save it on the rest of the data. I could do that a couple different ways. I could say this is going to be equal to this and then just Drag it on over which I commonly do.
10:02
Or we could say make an absolute reference, I could double click here and say that number I want to F, I want to f4 on it, make it an absolute reference. And then I could drag this, then I can drag this over this way, I don’t have the absolute reference. In this method, I’m going to undo that I kind of like the first item, because note that I’m going to do this all the way down, this equals this divided by two.
10:27
And I don’t really want to make an absolute reference or even a mixed reference, I could make a mixed reference because I want to drag this down. So I’m going to drag this down, like this. And that’ll give me the those numbers, right. So all the way down this divided by two. And then I like to go to the second tab, and I want this to be the same, I’m just gonna say this equals the same thing as the prior tab equals the same thing. And then I can copy that down pretty easily. Like so.
10:54
And I can also take that formula and copy it this way, pretty easily. So without the absolute reference, very simple type of things, I don’t have any absolute reference, it’s kind of a, they’re all linked together type of thing. So then I can copy this all the way across to December to December like so. And so now we have the same data the same starting point. Now, I usually will typically put a grid around this thing. So I’ll put a put a grid around it, Home tab, Font group, and then grid, apply it.
11:28
So there it is gratified, maybe put a grid around here to hometap font gratify, we could probably make this a little, I don’t know why we have so much space here, I can move that down. That’s not necessary, totally unnecessary. And then we can have our net income down here, which is going to be equal to of course, the sum of all this, and the revenues positive expenses negative, there it is, we can make this black and white, this is our bottom line number, bottom line number on the black and white down there. So that would be kind of like our starting point based on based on what we have thus far.
12:05
And then we can go back in here based on what we know, and possibly make changes to this data. So let’s let’s go and think, okay, maybe there’ll be some alterations on these and think about what kind of adjustments we might want. Now, when we think about adjusting these, these numbers, notice the items down below on the expenses, some of those will just be the same. And they’ll be fairly straightforward. But the the revenue is obviously sometimes where we’re focused in on, and we’re hoping that it will basically go up as we go forward.
12:32
So for example, let’s say we think our guitar lessons are going to go up by 10% on on each month, because people just learn about as the word gets out that we’re like, obviously, really good. And you know, everybody loves us. So they’re going to it’s going to include my advertise too, but we’re going to increase it by 10. So what would that be in January, we’re going to say that we have the for one to five, and then it’s going to go up by 10%, which is point oh one, which would be another 4125. And then I would add that to the for one to five, and there we have it.
13:02
But I’d like to do that a little bit faster. So we could say let’s take 100% one plus the point oh five, the point I mean, plus the 10% point one, which is 1.1 1.10 110%. In other words, times the 4125. And that’ll give it that’ll get us there to that four or 537. So let’s do that here. Let’s say we say okay, let’s say this is going to be equal to the prior month, times, and I’m going to say 1.10 110%. So we think it’s going to go up by that amount. There it is, notice everything else changed right after it. Because Because this is equal in the prior cell. Now.
13:41
Now I want to do the same thing, though, I want to if I pull this cell over, notice, what it does is it takes the prior cell times 1.1. If I pull this over, it’s going to it’s going to do that all the way across this nice formatting. So I can just pull this over, and then it’ll just graduate, it’ll just increase 1.1 the whole way across. So there we have it. Now we’ve increased them all by by one point by 10%. Each month, so increased by 10% each month. All right, so we might do the same for our rental or rental here. So let’s do the same thing here, we’re gonna say this is going to increase, let’s say by 5%, we think the rental income is going to increase by 5%.
14:21
Because people we’re gonna inspire people to rock and roll and take our rent our band equipment, so we’re gonna say this is going to be this times 1.05 or 105%. There we have that and once again, I’ll just copy it across. If I copy it to the next cell, then if I copy this to the next cell, then it’s going to take the last cell and times 1.05 and I can copy and then it’s going to go up by 5% all the way out to December. So there we have that so that’s gonna go up. That’s what we think’s gonna happen. And then next. Now, we can also increase it a different way.
14:56
We might say, well, maybe this one, I think it’s not going to increase by a penny. Maybe I want to increase it by a flat amount like like $1,000 or something like that. So this is going to be my starting point, maybe I think this is going to be increasing that plus $1,000. Now I could of course, think of that as a percentage increase, which it might be useful to do in some ways, like $1,000, increase, what’s going to be the percentage increase, but we can use a flat amount, of course, to do that, I can do the same thing all the way across, if I use that formula, if I copy this across, takes the last sale plus another $1,000, that we think we’re going to be increasing by so I’m going to pick that up.
15:32
So we can we can increase in that format as well. Then we have the service items here. So now let’s keep the service where it is, I’ll just leave that where it is. And let’s go to the cost of goods sold, the cost of goods sold is going to be related to typically the sale of the product, because that’s the cost of the inventory that we’re selling if we sell inventory. So typically, we can kind of tie this out to the sales line, we could say, all right, well, if I pull this out a little bit, and I just look at my percent here, I can say, well, we have the cost of goods sold, if I look at the percent is this divided by the sales line up here.
16:09
And if I make that a percent 5%, if I that, bring it out a bit, so I could see it, that’s 78%. So let’s just round it to 80, we think it’s going to be 80% cost of goods sold is going to be 80% of the sale of the products that we sell. So I’m going to just take this whole thing, I’m going to say this is going to be negative, so it’s a negative number of this number, the things that we sell times point 80%. So we’ll just change that to 80%. And then I can just copy that all the way across because it’s it’s once again going to take the line item of the sales times the 80%.
16:42
So as the product sales go up, this should go go up as well, because that relationship will remain the same. So there we have that. So those those are copied across now most of the rest of the expenses are usually going to be somewhat standardized. So for example, bank service fees, this is usually a fairly small amount, and we might just keep it as is it should be fairly constant throughout each month. So that’s pretty much where it will be depreciation, then this could change depending on whether we think that we’re going to be buying more equipment.
17:14
So if we don’t think we’re going to be buying any equipment any more equipment in the next year, then depreciation will be pretty much the same. If we’re using straight line depreciation, if we’re using some kind of accelerated method, then you would want to use like a double declining method or tax depreciation, then you then you might have some different, you might have to, you know, check your depreciation schedules to see what that type of depreciation would be. But if we’re just allocating using a straight line, you would think that that would be much the same.
17:39
The insurance, once again, if we’re on an accrual basis method, you would think the insurance insurance would be allocated basically the same over the months. If you’re using a cash basis method, then it depends on when you’re going to be paying for the insurance. So here, so we’re kind of considering an accrual basis method, you might want to do two budgets, really, you know, you might want this would be my budget for the budgeted income statement, which I would put more on an accrual basis.
18:03
And then we might have another budget, which is just simply a cash flow budget, which is just measuring, do we have the cash flow, we need to keep forward as well, then the internet, we’re talking like an internet service again, that would be pretty much constant, you would think from month to month, not big changes, unless you have a lot of growth or or decline in in terms of your size,
18:24
The office supplies again, unless there’s some big changes would be fairly constant, you would think the payroll taxes and wages unless we were planning on hiring more employees, at which time we would, we would increase that when we think that’s going to happen, we’re going to keep that the same telephone, same thing, unless we’re growing or pulling back a lot you would think would be somewhat the same.
18:43
Same with utilities, the gain on the sale of investment, this is something that we probably wouldn’t expect to happen again, that’s why we put it down here at the bottom. So we had some extra cash, we put it in the investments, and then we sold it. But we’re not in the business of selling, you know, stocks and whatnot. So if this, I would probably just simply delete this and say, I don’t think that’s going to happen again. And then the interest, if we if we have the same kind of loans out there, you would think the interest would be somewhat standard.
19:11
But if we plan on paying off the loans or taking on more debt, than you would think that the interest, you know, you’d have to change that as well. other miscellaneous This is we don’t even know what this is. It’s a fairly small amount. So we’re going to keep it as is as well. I’m going to total it up on the right hand side now. So I’m going to go to the right, I’m going to make a total column total. And then we’ll just sum this up so we can see where we stand on the year, summing up January through and then I’ll just copy that on down. Copy that on down. Copy that Roger out.
19:44
And then we’re going to go up top and I’m going to I’m going to copy the formatting, go into the home tab, Format Painter formatting, and then go down. I’m going to copy this across. Let’s copy this across. And so there’s our There’s our Indian Numbers, let’s go ahead and total this, put the brackets around this. And maybe we make this black and white as well or something like that. So that we can see that that’s our, that’s our total column. So just so it stands out differently possibly. And this is not notice that these down here, I don’t really want to sound the same way like this is picking up the one to the left of it, I’d rather sum this up vertically.
20:24
So I want to sum this up vertically. So I’m going to sum this this way. And so that should be proper, so that we’ll see a difference in the net income as we should, I’m going to copy this all the way across. And this is kind of our double check number over here. So now we got this 90,004 14. If I sum up this way, too, I should I should get to the same number. So that’s kind of a double check. And that works a lot of times with a standard Excel worksheet that’s in a similar format as this.
20:55
Okay, so now we have our worksheet that once again, I think it would be a lot easier to do this kind of adjustments in Excel to do the actual ideas or projections of what you’re thinking are going to happen. And now we’ll take this data, and we’ll put it into QuickBooks. Why? Because QuickBooks is not the tool for us to come up with the budget. QuickBooks is the tool for us to then run reports with the budgeted information, we’ll put this in place. We’ve already ran January and February.
21:20
So then we can imagine a situation where we put the budget in before January and February, January in February then happened, then we can analyze what we projected to happen against what actually happened. And we can imagine then going forward having the budgeted information, you know going forward from that point in time. So that’s what we’ll start with next time.