 QuickBooks Desktop 2023, Budgeted Income Statement, Export to Excel and Modify Part Number 2. Let's do it within 2-its, QuickBooks Desktop 2023. 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. Here we are in QuickBooks Desktop, Get Great Guitars practice file going through the setup process we do every time. Maximize the home page to the gray area in the view drop down. We got the hide icon bar, the open windows list checked off, open windows open on the left. Reports drop down, company and financial looking at the P&L profit and loss change in that range in 010123 to 022823. Let's customize it so I can change the fonts and numbers up to 14. Okay, yes and okay. Once again reports and company and financial, but this time the big balance sheet customizing it changing the range in 010123 to 022823. And then the fonts, the numbers, they are changing up to 14. Okay, yes and okay. Now in a prior presentation, we exported in essence the trial balance and then cut it down to be basically the profit and loss as our seed that we will then use to generate our budget in Excel, which we will then use to populate back into our system, go into the company drop down and then creating our budget from it here so that we can run the comparative reports, for example, in the reports section, the budget report. So the thing that we exported was actually the trial balance here. If I go to the trial balance from 010123 to 022823, customize it, go to the fonts and numbers changing the font up to 14 again. Okay, yes and okay. We then exported this which is in essence the balance sheet on top of the income statement without all the subtotals and then trimmed it down to just the income statement. That's where we left off here on our Excel worksheet. Now to continue working on our Excel worksheet, I'm going to zoom my zoom back down on my settings. I want to bring this back down to 100. And then I'm going to say, okay, so now I'm in Excel at 100 on the zoom. So I'm going to imagine this is the prior two months data. This is two months data. I'm going to imagine that's the only two months I have in the prior two months. And then I imagine that we're going to use that to project 12 months into the future. Now, obviously, if you have 12 months data from the prior period, that might be the best place to start. Take the 12 months, the yearly data, or you can obviously try to print out your data on a month by month basis and try to use that as your starting point. But the easiest thing to do is take your prior year data divided by 12 for each line item for a simple kind of budget layout. So to do that, what I want to do is put my budget over here. So I'm going to make a skinny C column because I want this data to be separate from the budget data table. So I'm going to make this skinny. And then I'm going to put my headers here. Now I want my headers to start. I don't want this as my header, so I want to move everything down one row. I could do that by selecting the entire row, putting my cursor on one, selecting the entire row, right clicking it and insert. It's going to insert an entire row above it. Then I'm going to put my headers, which will just be the months of Jan. I'm going to say Jan and then Feb. And that should be enough for QuickBooks to read the pattern. So I'm going to select those two, put my cursor on the corner here, which is called the fill handle, left click on it and drag it. And it's going to hopefully read the pattern. There's March. I'm going to pull this out until we get to December, December, October, November, December. And then let's center that whole thing. I'll zoom in just a little bit. Let's center that whole thing. And then I'm going to make the headers black and white. That's what I typically do. So I go to the home tab, font group, and then go and make it black. And then the lettering. I'm going to make white. That's what I'll have for the headers. And so then we can add our numbers. Now the most basic numbering format that we can use. Let's see if I can make this a little bit more skinny. Might make it a little bit easier. The basic easiest thing we could do is take our data and just divide it by, you know, divided by however many months are here, which in this case is two months. So why don't I just say, okay, my starting point is just going to be this number divided by two. And so I'm going to say each month I'm going to assume I'm going to have earnings of 1,130 projected out for 12 months. And I'll copy that down. One way you can copy that down is you can put your cursor on the fill handle. And instead of dragging it down, I could just double click on it. And it should copy the relative actually didn't do it. If I copy it down here, it won't do it because I'm not right next to it. All right. I got to drag it down. Put my cursor in the fill handle and drag it down. So there we have that. And so now we've got the same formula every every cell on down. So that looks good. That's our starting point. Now I'm going to say the same thing will be my starting point for February for the whole 12 months because this represents two months of data. This is breaking it down to a month by month. That's going to be my starting projection number. Now I could do it this way. I could double click on this and say I want to make this absolute by selecting F4 on the keyboard. And then I can copy this to the right. That's one way I could do it. But another way I kind of like to do it is to say I'm going to put my cursor in February and say this equals January number. So the default will be equaling the prior month. The reason that's useful is because if I change something like in April, then it will default that may will default to the prior month instead of defaulting all the way back to what was in the current month. So that's what I'd like to do. Now I'm going to copy this all the way down. So now I've got the same numbers here. So now this always equals the prior month and I'm going to take this whole thing now and copy that all the way across to December. So I'm going to put my cursor Phil handle here and copy that through December, which I can't see. I'll just go too far and then I'll delete it. So here and then I went too far from P. I'm going to put my cursor on P all the way to Y and then right click and delete. So now I've got the same data month by month. That's kind of the starting C. That's the easy budget that you can do as an accountant without if you know nothing about the business or what changes they're going to make. And then of course we would have to get into the detail and say, well, what are you going to do and what do you expect the changes to be going forward? And that's where obviously you would need management decision making processes and projections about what we think is going to happen for those changed decisions. So let's just change a couple of these for practice. Now normally when we're thinking about changes, oftentimes we're thinking about the revenue changes because that's where our focus is going to be because that's the goal of the business. So we're going to try to do things to get revenue to go up, right? And then the cost to goods sold will typically go up in relation to the revenue. And then when we get down to the other expenses, oftentimes the other expenses are fairly straightforward and they'll be kind of like the same going through, right? So it might be a little bit easier, although we have a lot of expenses, oftentimes the expenses are much more similar from month to month. And we're going to focus on specific things like advertising or specific things within expenses are going to change or whatever in order to get the revenue to change. And obviously the revenue changing if we sell inventory will have an impact on cost of goods sold. So let's just do a couple just to practice. Let's pretend that the rental income is going to go up by 5%. We just think it's going to go up by 5%. So if I think about that kind of increase, I could say, okay, well that means that the 1130 times 0.05 would be an increase of 56.5 plus the 1130 would be 1187 about or 6.5. I can get there directly by taking 1130 times 1.05, 105% or 5% plus 100%. And that would then give us our number here. That gives us a nice formula to work with. So if I go back on over here and I say, okay, what I'm going to do is take the prior cell and multiply it times 1.05. So I could say this equals this cell times 1.05. And so that changes everything going forward. And if I assume that I'm going to have that same change every time, I can copy this formula all the way to the right. I'll put my cursor on the fill handle, copy it to the right. I'm going to imagine it goes up by the 5% until at December where at the 1,933. Let's also make a total column over here for the year. So I'm going to say this is to be the total for the year. And let's sum this up equals the sum of January through December. Enter. And now I should be able to put my cursor on that double click on the fill handle. Boom. And that should take that all the way down. Let's make this the same format as this cell. I can do that by going to the home tab, clipboard, paintbrush and paintbrush it. Boom. Okay. So then let's continue that the sales item. Let's imagine that the sales are going to go up by 1.1. So a similar kind of thing. I think there's going to be an increase just because we're going to our name is going to catch on and we're going to start selling more stuff. So I'm going to say this equals the prior month. I'll start it in February times 1.1. It's going to increase by 10% 100% plus 10%. That would give us up to the 32 148. And let's imagine that I'm going to do that all the way all throughout the year. It's going to increase 10% until we get through December. That means at December, we'll be up to the 83 384 there. That would be great. Now the other way you might think something's going to increase, let's say this one. Let's say we think it's going to increase by just a flat amount for whatever reason of $1,000. Right. We think it's just going to increase $1,000 per month. So we're not going to have it increase on a percent of prior month, but increase. We think we can generate another $1,000 every month. So I'm going to say this will equal the prior sale plus $1,000. And if I think that's going to continue, that's another way that we can easily copy that formula across, taking the prior sale just plus $1,000. And so it's going to go up by the thousand until we're at the 15 to 50. That would be that would be good. So the next one we have is going to be cost to good soul. Now cost to good soul represents the cost of the inventory, which usually has a direct relationship to the sale of items that are inventory. So these two lines you would think would be related. So for example, if I take the first line here and I say, okay, the cost of good soul as a percent would be 22,977 divided by the sales line, which represents inventory revenue 29226. That's 78 per 78.62 percent about. So so you would think then going forward that if sales is going up by 1.1, that this would also go up by 1.1. Generally, considering the cost is usually tied to the sales price. So we'll make that assumption, meaning that as the sales go up, we're going to assume the cost is still going to remain at that 78%. So I can do that by I can do that two ways. I can I can take I can take this number times the 78 point whatever, or I can just increase it by the same rate, which is the 1.1. Let's do that second method because that'll be easier to mimic in QuickBooks. So I'm going to say the second one is going to be equal to prior sale times 1.1 100% plus 10% 110% copying that all the way across, copy that all the way across. And it'll increase to December to 65556. Now, if I take my trusty calculator, I would assume then I get that same ratio here in December, which would be the sale, the cost of goods sold 65556 divided by the 83384 gives us that 78.62% about. So you get that same relationship, which is kind of what you would expect. Now, a lot of the other expenses will be somewhat routine. So like the bank service fees, we're going to go OK, $35 $18 a month. You would expect those to be somewhat similar going forward. Number one and number two, that's a fairly small dollar amount. So probably might not be material have a big impact on our budget. So I'll just keep that as is the depreciation note that depreciation is a key component where we might think differently between a cash based method and an accrual based method because depreciation is usually a big accrual kind of thing versus cash. Note that if you're using a book depreciation method that's a straight line depreciation method and doing an accrual budget, then you would think it'd be somewhat the same going forward. If you're doing a double declining balances and stuff and whatnot, then you could probably predict what the depreciation will be based on your schedules. But we'll keep it here at the same on the straight line. If we have the insurance, let's imagine that we're going to do the insurance more on a cash based method here and try to say when we're going to pay the insurance and actually show the insurance. Again, you kind of got to think on your budgets. Am I doing an accrual budget or a cash based budget? But just to give a different idea on the data input, you might say, okay, I'm going to pay insurance, let's say in January, I'm going to pay 1000, negative 1000, and then I'm not going to pay it again. I'm going to delete everything until we get to like September and then I pay 1000. So again, that would be more of a cash based kind of idea or a concept there. But that'll be determined on how you're doing your budgets. But we'll put that in just to switch things up in our data input there. And then let's actually bring it up a bit because I think it was higher for the let's bring it up to like 6000. I'm going to say insurance is 6000 here and then I'll make it 6000 here instead of 1000. So then interest expense, Internet expense. Often that's going to be somewhat similar might go up with like inflation or something like that. We'll keep that the same miscellaneous. Hopefully that's usually pretty small and you know, that's random stuff. So hopefully that's going to be somewhat the same and somewhat in material office supplies could change going forward, especially if we think that there's going to be a good big increase in our revenue. But again, we'll keep it the same for our practice problems and move down to the payroll. Now payroll is going to be a huge relative, you know, relative number, typically if you have employees and the increase of payrolls is often kind of like more sticky. It goes up oftentimes maybe not as an even flow, but as a step ladder. So you might be saying, okay, I think my payroll is going to be the same until I get to like some point in the future when I'm going to reanalyze payroll, like in July, let's say, and then I think it's going to go up by 10% in July just to switch stuff up a bit. So if that was the case, I can move out to July and say I'm going to take the prior payroll times 1.1 out in July. And that's going to increase to 7.682. And then it would be the same if we kept up that new tier all the way through. So with now it's equaling the prior sale through December. Now if we increased the payroll, you would expect the payroll taxes to have a similar increase. We got a similar relationship as we did when we looked at the cost of goods sold and the sales related to cost of goods sold. So I'm going to increase it by the same rate 1.1. So I'm going to say this will equal the prior sale times 1.1. And that should follow forward in the same percentage, meaning if I was to have the same relationship prior, this was the 486 divided by the 6983. And that's the .0695. And then after we've got the 535 divided by the 7682, we should have the same relationship there. So we'll keep rolling. So the next items we have in telephone, we're going to keep that the same. And we're going to say the utilities, we'll keep the utilities the same. The gains, this is something that's unusual for our business. We're not in the business of selling stocks. So this was a one time thing. So I'm going to delete the whole thing. And then the interest expense, this would be dependent upon our finances. And if we're not going to take more financing out, this might actually go down from period to period. So I'm going to imagine it's actually going to decrease. Obviously we might take this directly from like the amortization schedule in practice. But I'm just going to say this is the prior period times .95, 95% of the prior period. Going to put my cursor on that and auto fill that across. So it looks like that. And so that one actually goes down as we go across. And then let's total this up and see where we stand. So I'm going to say this is the total. I'm going to sum up here equals the sum of our baseline numbers. And then I'll sum up here equals the sum of our budget for January. I'm going to copy that across and copied it for me a few ways. I'm going to put my cursor on the fill handle, drag it to the right. So there we have it. I want to put an underline under all of this stuff. Let's put an underline here, home group, font, underline. And then I'll put an underline here too. Let's put an underline there as well. Now I'm going to make a change just to match this to my worksheet. This 6000 that we put in the insurance expense. I'm going to change that to February. I'm going to change this to February for it to match our worksheet here. Okay, so I think that matches out. So now I'm going to say, okay, the second month, that's when we get into that loss because we put this on a cash-based method. So again, you've got to choose whether you're on a cash basis or a cruel. But we'll keep it there for now. We're going to go all the way across. There's our total and our total column, which you can find by summing up the total column. Or you could sum up, let's do a double check, sum up the net incomes per month as kind of a check figure, which is nice to have. So we'll check that out this way. And then I'm going to put some borders around this thing. Let's put some borders around our numbers here just to define it a bit more. So scroll down to here. I like going to the home tab, font group, drop down, put some borders around it. Let's put some borders around this one as well. And so we're going to put some borders around it. And that's what we have thus far. So we did that fairly quickly. I know, and I know it's not an Excel course. But now we have the data that we can use to then populate this into our QuickBooks system. And then the QuickBooks system can help us to generate reports as time passes, comparing what we projected to happen to what actually happens.