 QuickBooks Online 2023, budgeted income statement, export to Excel and modify part number two. Get ready to start moving on up with QuickBooks Online 2023. Here we are in our get great guitars practice finally sorted up in a prior presentation using the 30 day free trial. We also have open the free QuickBooks Online sample company. If you want the two open at the same time, try using incognito or another browser. Support accounting instruction by clicking the link below, giving you a free month membership to all of the content on our website, broken out by category, further broken out by course. Each course then organized in a logical, reasonable fashion, making it much more easy to find what you need than can be done on a YouTube page. We also include added resources, such as Excel practice problems, PDF files and more like QuickBooks backup files when applicable. So once again, click the link below for a free month membership to our website and all the content on it. You can open incognito if using Google Chrome by selecting the three dots in the browser, incognito window, typing into the search engine, QuickBooks Online Test Drive. We're gonna be comparing the accounting view, the one Get Great Guitars is in and the business view, the one the sample company is in to toggle between the two, hit the cog up top and switch the view down below. We're gonna be duplicating some tabs to put reports in like we do every time. Right click on the tab up top to duplicate it. Right click on the tab up top to once again duplicate ultra vase. Back to the tab to the middle, we're going to the reports on the left so we can pick up and open up a balance sheet standard report. As that's thinking, let's take a look at where the reports are located in the business view. They are of course in the business overview as you may know by this point and the reports. And then we're gonna open up the balance sheet. We're gonna then tab to the right. We're gonna go to the reports on the left, close up the hand boogie on the top and open up the profit and loss on the bottom left and then go to the dates and type in 010123 to 022823. I'm gonna see this on a side-by-side month-by-month breakout, run it to refresh it and then tab it to the middle, close up the boogie and change that range. 010123 to 022823 month-by-month side-by-side to see it, run it to refresh it. That's where we have it. Now we're gonna be doing our budget. Remembering that the budget would be entered in the cog dropdown and then the budgeting, but first we need to create the budget so we can put it in the system and QuickBooks is good at running reports once we have created and imported the budget to run reports like profit and loss or a budget versus actual type of reports. So in prior presentations, we exported a trial balance and constructed it into just the income statement accounts and we double-checked that our balance lines up to the 1-3-2-4 on the income statement for the two months of data. So here we have it here. So we just have the income accounts as positive, the expense accounts negative. We don't have any subtotals in play at this point because that's kind of the easiest streamlined way to work with it. And now I'm gonna say that these are my beginning numbers for two months of data. I'm gonna divide it by two for one month of data and then project that out over the 12 months. And that's gonna be our starting point and then we'll make adjustments from there. So we're gonna project out 12 months. I know this data was pulled from January and February of 2023, but we're gonna kind of pretend it was as of two months of November and December of the prior year so that we can then kind of project out for the full year of 2023 and then run reports, budget versus actual for those two months of data that we actually have in the QuickBooks system. Okay, so to do that, I first I would like to put my months up top, have a header column. So I'd like to pull my data down a little bit one row. So one way to do that is I can select the entire row with a pointer on row one, right click the selected area and insert and that pushes it all down one row. And then I might start here and just say this is gonna be January, let's put a little space between our actual data. I'm gonna make column C a little bit smaller and let's start on column D and say this is January, Feb, that's all I should need and QuickBooks should be able to pick up that trend even with only two points. I'm gonna select those two, put my cursor on the fill handle and drag it to the right. You can see it's picking up June, July and so forth and we wanna go to, of course, December. So there we have it, as it's still highlighted, I'm gonna go to the Home tab, Alignment, Center it and let's also make it black and white like I typically do with a header font. Let's make it black and white on that. So there we have it. And then January, for the January numbers I'm gonna assume it's just gonna be this amount divided by two. So we're just gonna, cause this is for two months of data so I'll just take that and divide it by two. So I'm gonna say this equals this number divided by two and that's basically our starting point. I could copy that all the way down, putting my cursor on the fill handle and I'll just say that's my starting point all the way down, boom. All right, and so there's our starting point so each of these rows is just taking the prior two month total and dividing it by two. Now I could copy that to the right a couple different ways as our starting point. I might say that I wanna copy it like this but if I did that I would need this to be an absolute reference. So I could double click on that and say okay, if I make this an absolute reference by selecting F4 on the keyboard then I could copy it out this way and then I could do that for each of them. So for example, if I went through and I made all of these, if I made all of these F4 then I could copy it out to the right. That's one way that we could do it. We could also use a mixed reference so I could say if I look at what the absolute reference is doing here we've got a dollar sign before the B, a dollar sign before the two and what that's doing in total is saying no matter where I move this thing whether I move it down or to the right I want you to keep the same reference at that 200 for B2. Now specifically the B means that I want you to keep the column B and the two means that I want you to keep the column two. So if I'm gonna say over it I would like to be able to copy it down and when I copy it down I would like the two to be able to move down. However, I don't want the B to be moving. I want you to keep in column B. So what I'm gonna do is just say I'm gonna remove the dollar sign before the two this is a mixed reference. So it's not gonna move the column to the right but it will move the rows as I go down. So that's another way we can do it. If I just copy it down a few times to double check it it works that way and it works this way as well. So I could copy it all the way kind of across and that will work. So that's another method that we could use. Now the method I like to use for this particular purpose is I'm gonna delete this is to just to go to the tab to the right and I'm gonna say this equals the prior period because I want it to be the same all the way across. And now if I copy that across it'll just everything will be equal to the prior period. The reason I think that's useful for a budget is because if I had some system where I'm gonna say I want March to change then April will be dependent on March's change and everything will cascade changing going out into the future. So I think that's the best method to use or that's the one we're gonna use here at least. And so once I do that, January is pulling from the source data everything else is pulling from the prior period. I could copy that both down putting my cursor on the fill handle copying it all the way down and once I've copied all the way down I could copy it all the way across or I could do it all the way across first and then copy all the way down. I can't see how far up the total is for December almost there. So I'm just gonna copy the whole thing over boom. So there we have it. We've nicely made this kind of just generic this generic budget. And if you had no other information and you wanted to give someone like a generic budget based on the prior year this is what you could do, right? You could just basically do that and you have some settings possibly in QuickBooks they can kind of possibly do that kind of automatically for you. So you could do that. Although again, if you just use last year's data you're just running a prior year versus the current year comparative report which you can do anyways using comparative reports. So now the question is generally well what am I gonna do from this point to change it based on our projections going forward? So I'm gonna make the first column a little bit smaller. I'm gonna save this and let's make this green to show that it's our source data. So I'm gonna make it a different color just to show that that's my source data and I'll put some brackets around it maybe. Okay, so now let's go through here basically line by line and think about what changes might happen from each line and we'll make some tweaks to it. And actually before we do that let's total it up down below. So yeah, we've got the total down here but notice I copied it all the way down to the total. I don't want the total calculated this way. I want the total to be summed up this way. So I'm gonna sum up the total like that and then copy it to the right so it sums it up. We might wanna put an underline under this so it will emphasize that. Maybe I put an underline under this and then I'm gonna put a total column to the right which will sum up the entire year. So I'm gonna say total total or year to date or whatever you wanna call it. I'm gonna copy the formatting here, home tap format painter copy the formatting and sum it up equals the SUM one of the most famous or the most famous form you lie. Sum it up January through December then I can copy that down putting my cursor on the fill handle to copy that down. And then I'll put an underline under this one again and copy this to the right. So there's our year to date amount or a total for the entire year amount. Sometimes they put that total by the way upfront at the beginning. So sometimes you might want to put the total at the beginning but whatever there it is. All right, so now we're gonna keep the billable item as is I'm not gonna change that one. I'm gonna imagine that this one, we think it's gonna go up. Now there's a couple general trends that you might use which are the easiest things to think about. You might say, well, I think the income is gonna go up by a percentage each timeframe. So that's one kind of easy thing to put the data input on or you might think that there's gonna be an increase based on a step like after three months you think it's gonna jump up for whatever reason or you might have a system where it goes up by a certain dollar amount. Those are the easiest ones to kind of increase by. So for whatever reason, we're gonna say that we think from January to February and going out into the future, this revenue account is gonna increase by 5%. So how can I calculate that? I could say 1130 times 0.05, 5%. That would be it goes up by 56.50 plus the 1130. Or I could say I wanna have 0.05 plus one, 1.05 or 105% times the 1130 that gets us where we wanna go. So let's do that. I'm gonna double click on February here on February and I'm gonna say this equals the prior amount times the 1.05, 105%, 100% plus 5% in essence. So I'm gonna say boom. So now that brings it up to the 1187. And then I can copy that all the way across and it should nicely populate increase. And notice if I don't do that because I said equal the prior sell, it now takes the new amount instead of taking the original divided by two. Instead of taking this original amount, it's always gonna be taking the new amount. But I want it to continually compounding the increase. So I'm gonna put my cursor on it, fill handle and drag it all the way to the right. And that will then not to the total, but to the December. And now the total totals up to the 17, 986. All right, let's do the next one. And notice these are gonna be the more difficult ones because the revenue is often the things that we're gonna think are gonna change based on what we're gonna do. Possibly we're gonna advertise more. Possibly we're gonna buy more equipment. You know, we're gonna have to make our projections possibly the business environment will change. And then the expenses, a lot of times, some of them will be more fixed. So we can just basically keep the standard, whatever it is all the way across. Utilities will just be pretty much the same all the way across, unless we have a seasonal kind of thing going on there. So in any case, we're gonna say this one's gonna increase by 10% each time. So we'll do the same thing. I'll double click on this. I'm gonna say the prior thing times the 1.1, 100% plus 10%, right, 10%. And then enter. And so then now it steps up for the rest of the timeframe. I'm gonna put my cursor back on it and copy it all the way to the right so it compounds on itself to get to the 624. So we're looking a lot better next year. Now let's do something a little bit different instead of it compounding like that. We might say maybe I'm gonna do something saying it's gonna increase starting in February by $1,000. So we think it's just gonna increase by a set dollar amount instead of compounding on itself. So I'm gonna double click on this and say I'm gonna take the prior amount plus $1,000. So we'll just increase it by 1,000. Now it has stepped up to 1,000 because each cell represents the prior one. And now I'm just gonna copy that one across. So each time it'll just increase by $1,000. So another kind of fairly easy arithmetic type of thing to do. All right, so let's go back on over and then say, okay, the next one is cost of goods sold. Now cost of goods sold is generally gonna be tied to have a profit margin or relationship to the sale of products because it's the cost of the goods that we're selling. So we're gonna have this. So if this one is going up by 10%, you would think there'd be something similar happening down here. So in other words, I could say, okay, well the cost of goods sold if I look at February is two, two, nine, seven, seven, divided by the three, two, one, four, eight, which is here. And say the cost of goods sold is like 0.737% of the sales. So I could use that formula. I could say this equals, I could say this equals this times 0.73767 or whatever, and copy that across. That's one way we could do it. Or I can just say it's gonna increase by that same 1.1 which should end up with the same result and this should be a negative. I'd have to say negative of that amount times whatever. Or I could say this is gonna be equal to the prior amount times 1.1. It's gonna have that percentage increase which is gonna be the same. Across, so we'll do that. And so now, so if I do that, this is at the two, five, two, seven, five, divided by the three, two, one, four, eight. So now we're at 0.7862. And if I copy that across all the way across to December and I do the division here, I could say, okay, this is at 6556 divided by this number, 83384. And we got the 0.786, right? Okay, so then the total's at the 491. Now some of the expense accounts should be a little bit easier down here. So if I look at some of those, I'm gonna say, all right, let's go down here. We got the bank, the bank fees, $35. Let's say that's the same all the way across. It's probably in material, not really worth getting to bothered over on a budget. The liability insurance, now insurance, you kinda gotta think if you're gonna be doing a cash based system or an accrual based system, I'm gonna try to just show when we're gonna pay the insurance at this point just so you can see how you might see it like a tier type of system. So I might say, I'm gonna record the insurance as an expense when I pay for it, for example, and look at it more on a cash flow basis and say, let's delete all of this. And I'm just gonna assume that I'm gonna pay the insurance in February, let's say $6,000 in February, negative $6,000. And then I'm gonna pay again in September, negative $6,000. So you might have something like that if you're trying to do your budget kind of on a cash based system and you might have it like a step up situation that we'll see shortly. Like let's say that the internet for the business, let's say that's gonna be pretty much standard, we'll keep that as is. Then we've got our wages situation, the taxes, this is payroll taxes, I believe, and then the wages. So this is an area you might have a step up kind of situation. You might say, well, I'm gonna pay the same wages, but maybe in June, that's when I'm due to give people raises for cost of living or whatever, raises or something. So you might say, I'm gonna keep this as is until June maybe, and then you have that step up where I'm gonna say it's gonna be the prior amount of times 110%, 10% increase. So I'm gonna say times 1.1, 10, 110%, increasing it by 10%. And now you've got this step up that happened. And maybe you don't expect that to be compounding going forward because that's how wages often work. They're tiered up at one point and then they stay standard for some time after that, unlike possibly revenue, which hopefully has a nice steady flow increase upwards. Let me tweak that one a little bit. I think I wanted us to do the step up in July. So let's say I'm gonna copy this June, I'm gonna copy from May back over to June, and then I'm gonna do in July that step up thing. So I'm gonna say July is gonna equal the prior period times 1.1. So now in July going forward, you'll have that amount out to here, 87, so that looks good. Now, then we've got the payroll tax. So if there's a change to payroll, you would expect payroll taxes to change, which gets complex because the payroll taxes can have different caps on them and whatnot. And so there's a couple of ways you can think about it. It'd be similar to like the cost of good sold relationship to the inventory because this is basically our portion, the employer portion of taxes. So you might think of it as it should have the same relationship that it did before. So I could say, well, taxes were 486 divided by wages of 6,983. So that's about 6.95%. So I could go here and say this is gonna be this times, so I could say this is gonna be equal this times the 0.0695 or something like that. Or I could just increase it by the same amount, which should give me basically the same results. So I could say this is gonna be equal to the prior period times 1.1. And I should have the same kind of jump up. So if I then say this is 535 divided by 7682, we get the 0.069 so on and so forth. So the taxes should jump up in a similar fashion and that would make sense, okay? All right, we're almost there. Continuing on, continuing on. We've got the supplies. Let's go ahead and just keep the supplies as they are. So that's somewhat standard. And then the supplies may or may not be material depending on the business. Telephone, that's usually somewhat standard. So I'll keep that the same going across. Utilities, usually somewhat standard, although there could be changes seasonally to some degree, which may or may not be relevant, gain or loss on sale of investment. Now that's something that doesn't happen every period. It only happens every so often. So because we're not in the business of selling investments, so I'm gonna delete this whole thing and imagine we're not gonna see that happening again. That's just something that happened last time. That's why we put it down below in like the other income area. Depreciation, let's just keep that the same. Now, if you had a depreciation schedules, you can actually calculate what the actual depreciation kind of would be based on your equipment for the current time period. Of course, if you purchased or you're planning on purchasing more equipment, that would kind of skew your depreciation schedules. But I'm just gonna keep that, in essence, the same going forward. And then we've got the interest. So let's say the interest just to show how we could have something actually going down because the interest expense is gonna be based on the loans that we have. So again, we could look at the loans and actually calculate exactly what the interest will be based on the amortization tables. But we might say, hey, look, I think the interest is gonna go down each period because we're gonna be paying off the loan. So it's gonna be decreasing. So let's say we're gonna say the interest starting in February equals the prior period times 0.95, it's 95% of the prior period. And we think that trend is gonna continue each month. So I'll just put my cursor on the prior one and just copy that across. So that's another fairly easy trend that you can put in place. All right, and then we've got the last bit here, scrolling back on over of the miscellaneous. Let's keep that as is. So let's keep that as is. That's what we have thus far. Let's just clean it up a bit. Let's put some brackets around it maybe. I'll select the whole thing and put some brackets around it because that's always fun. Boom. And then we've got our underline down below. We might wanna put like a double underline. I'm holding down control so I can select this one. Oh, hold on, let's just select these down here like this. Don't do the whole control thing. And then I'm gonna put a double underline on that and then I'll put a double underline here just to match it. Okay, so then here's our net income at the end. So we've got a negative amount for February. So that's a little bit scary, which is due in part to this large expense that we put in February for the insurance. That's where you gotta be careful on the cash flows when you're paying a prepayment system. So, and but then we got the income going back in. So we just gotta make sure we got the cash flow to deal with that. And there it is. So this is our total at the end, which we totaled up this way. And you could double check it by totaling it up also by summing up this way. So this is a type of table that you can do those, you know, double check kind of total, which is nice if you can do that. So now we've done it, you know, total it up vertically and horizontally. And so that looks pretty good. So now that we have our data, we're gonna put this back into QuickBooks and by going in and we'll do this next time. Just this is what we'll see in a future episode. It's gonna be great. We're gonna hit the dropdown and we're gonna go to the budgeting and we'll enter the budget into here. Why? When I already constructed it in QuickBooks so we can run budget versus actual reports as time passes. And that's what QuickBooks does well. So we'll do that next time.