 QuickBooks Online 2022, a budgeted income statement export to excel and modify part number two. Get ready because it's go time with QuickBooks Online 2022. Here we are in our get great guitars practice file. We set up with a 30 day free trial holding down control, scrolling up a bit to get to the one to five percent. We're currently in the home page otherwise known as the get things done page in the business view as compared to the accounting view. If you want to change to the accounting view, it's something you can do by going to the cog up top, switch to the accounting view down below. We will be toggling back and forth between the two views either here or by jumping to the sample company file currently in the accounting view. Back to the get great guitars open in a few tabs to put reports in. We're just going to open two tabs this time, right-clicking on the tab up top to do so, duplicating it back to the tab to the left, right-clicking again and duplicating again. As that is thinking, let's see what the reports are located over here in the accounting view, which is on the reports on the left-hand side. Jumping back on over to the business view for get great guitars. We're in the second tab. We're going to go into the business overview. We're just going to open up two reports this time in the reports area, income statement and the trial balance closing up the hamburger. Let it think a little bit. Let it think. Don't rush it. And then we're going to go into the profit and loss or the income statement, profit and loss or income statement range change up top 010122 to 022822 and run it. We'll go to the tab to the right and then back down to the business overview. We're going into the reports closing up the hamburger this time searching for the trustee trial balance, the good old T to the B going into it and we'll do the range change up top from 010122 to 022822 and run it. And the prior presentation, we discussed that we're going to be doing a budget for the income statement or based on the income statement, income statement type budget. The budget you'll recall being found in the first tab. If we go to the cog up top, that's where the budget is, but we need the information to implement the budget. And if we have any kind of a complexity with the budget, if we're not just going to copy the prior period, exactly, then we might want to export it to Excel, which is what we have done here. Think about the adjustments and then put them back into the system using this area so that we can then run reports in Excel, including a budget versus actual report as time passes. So what we did is we exported the income statement, but not this income statement because it has all these subtotals. We want just a straight plain income statement with all the subtotals that are involved in the actual report. Therefore, we exported the trial balance and formatted it to, in essence, a single step income statement even more simplified than that. In essence, positive numbers, income, negative numbers, expenses. And then we got the bottom line number that ties out to net income for our two month time period here, 1-3-2-4-0-5. It looks like this. So there's our income statement 1-3-2-4. We don't have the 0-5 because we got rid of the pennies because it's just a budget. So if we're going to now assume, if I go back to the income statement, this is actually what happened. This is our actual data for January and February. That's what actually happened in our practice problem. We're going to kind of assume that this data that we put into Excel is the data from the prior period, which would be November and December, so that we can then base a 12 month budget on it and then put that 12 month budget into the system and run actual versus the budget reports for the two month period that have passed in our practice problem January and February and then just budgeted numbers out past that point. So let's go back on over to the income statement. This is where we have. Now this is going to be some Excel stuff here. I know it's not an Excel course. I'm going to do it fairly quickly, but just to give you an idea of what we're going to do here for the budget, I want to take this. I know this is two months of data. I'm going to imagine it's prior two months of data for November and December and then use it to budget out into the future for the next 12 months. So to do that, I'm going to put some headers up top. I'm going to put my cursor on column one. I need to get up above that. So I'm going to right click on it and insert so I can put some headers and I just want to put my dates up top for the headers. I'm going to close this one up a little bit. I don't need this long of a title. And notice I have some accounts down here that they're showing that their parent accounts. So this one is actually general business expense and is the category and then it's got the subcategory of bank fees and services. I don't really need that first category. So some of these, I might make them smaller by just saying, I really only need the actual account, which is the sub-account. These two like payroll expenses. It doesn't really bother me because it's not too long there. But that first one was super long. And so I'm going to get rid of it. Get it out of here. Get it out. I'm going to scroll up a little bit. Okay. So now we're going to say this is going to be Jan and Feb. I'm going to put my cursor on those two, selecting them, grab that auto fill handle, drag that all the way to the right till we get to December because we're doing a 12 month thing here. November, December. There it is. Before I click anywhere else, I'm going to go to the home tab, alignment while those are selected and center them. I'm also going to make them black and white, which is what I often do for the header areas to distinguish them from the non-header areas by going to the home tab, font group, paint brush thing or paint bucket thing. And there's that. And then the letters need to be white if they're going to stand out. Otherwise you can't see them because they were black on black. You need the contrast to make things work. So now we're going to then say that we got to divide the two. Now this is two months. So the most basic thing I can do, the most basic budget is to take basically the last year's budget, if it was 12 month, divide it by 12 and say that's what you expect to happen on a monthly basis going forward. Or you could get a little bit more specific. You could say what exactly happened on a month by month basis for the last 12 months I expect to happen for the next 12 months. I'm going to start with that first kind of baseline except I only have two months of data. So I'm going to start just populating our budget and say I'm just going to say whatever is over here, I'm going to divide it by two because this is two months and these are all one month and then that'll be my baseline for my 12 month projection going out into the future. It'll look something like this. This equals the prior tab divided by two. So there it is. The next one would be equal to the prior tab divided by two. The next one of course would be equal to the prior tab divided by two and so on and so forth. So now the question is well how can I do that as easily as possible? So there's a couple of different methods you could do. For example you could go to the second tab all the way over here and it's always going to be equal to the one prior to it and that way you could copy it across this way and I can even copy it down this way. That's one method pretty easy to do. The other method is you could say well I can use an absolute method. I'm going to delete that and say I'm going to double click on this and make that absolute by saying the F4 on the keyboard or dollar sign before the B and the two and that will allow me to copy it across this way so I could do that to each one of the cells because absolute reference means don't change this cell. Always pick up that 200 and that's what it's going to do so it's going to pick up the 200 or I could use the mixed reference. I could say well how can I just do the minimum amount of things. I can delete these two below. I want to be able to just do the minimum amount here and be able to copy it both down and to the right. For that we can use a mixed reference and I could say okay if I move to the right I don't want the B to move. That's what the dollar sign is before the B but when I move down over here on the rows I do want it to move so I don't want a dollar sign before the two so I can allow that to move like it normally would and that's going to be the shortest way that we could just copy this all the way through and get what we would expect to get I hope. So let's try it out. If I copy it to the right you could see if I double click on it it says okay yeah took that same cell B2 because the B didn't move and I didn't move down so the two is not a problem in this case and then divided by two. If I copy it down this way then we're going to say does that work we're going to say if I double click on that yeah took this one divided by two because the B column didn't move but the three moved down the two went to three which is what we want and the two divided by two remains the same. So I can copy that all the way across then I can say alright let's just copy this down all the way to here not to the net income line this is the total line right down there and all the way across we could double check it and say does that do what we want it sure does do what we wanted it to so let's let's then I'm going to select this whole column then and copy it all the way to the right all the way to the right till we get to December which I can't see I went too far that's okay I'll just delete these last couple columns we'll just clean that up clean it up delete and then we'll have a total on the end totown the tote I'm going to copy the formatting over here which is in the homepage clipboard format painter let's copy the format bring it on over and then I'm going to sum up on this side equals the sum the trusty sum function you could double click here or I like using the keyboard whenever possible because it's more geeky as long as it makes me look more geeky that's what I'm looking for and then I go to the left I'm holding down shift and then go into the left with my arrows not to not to this 200 but to this 100 through January and then enter and then we can copy that we could do that with it with the arrow too but I'll do that with a mouse so we can see it right click and copy and then I'm going to paste that all the way down control the so there's our totals and then we can sum it up this way did I sum it up we could sum up the net income so this net income for example is all this we could sum up this net income and you might want to make this whole column a different color just to show that it's like your source data we possibly should have even put a space between it but let's make it like this is like my source data numbers a different color we'll put some brackets around it ok and then we'll sum up over here equals the sum of these this column and we'll copy that and then I'll just copy that all the way across to the right putting my cursor on it the fill handle grabbing the fill handle left clicking on it getting the firm grip and dragging it all the way to the right so now we've got that double clicking on it looks good I can double check this last number by summing it this way too I could sum it this way for all the totals to see that it should come out to the same number there it does that gives me my double check put two checks by that one if you would because I double checked it that's what I'm talking about and then I can put an underline font group and underline ok so there we go that looks good and then this that's my totals alright that's the starting point now we might want to say ok based on my other kind of things that could happen based on the economy for example based on what I'm going to do for my purposes in terms of increasing production, increasing prices lowering prices, advertising what kind of changes do I expect to be happening so there's a couple kind of ways these changes could take place on a kind of systematic way you could say well maybe maybe I'm going to have this amount increase by like a percentage amount for example you might say well I think it's going to increase by some consistent rate as we go forward that's one way you might think of it or you might say well I think it's going to go up by a specific dollar amount each time that's another common way that you'd say I'm just going to increase it by this dollar amount each time or you might say that well those are basically going to be the common two ways based on the items where on the expense side we think we're going to have them incurred at one month in the middle or something like that and you could have some differences if you're thinking about a cash basis method versus an accrual basis method on how you might construct the budget and how your bookkeeping are going to be set up from a cash to an accrual basis kind of component as well in essence it would be nice if you can actually do two budgets a cash basis budget and an accrual basis budget for the most kind of basis being covered so let's look at a couple of these kind of methods we might see I'm going to keep the first one as it is and then on the second one that's when I'm going to be saying that I'll keep January the same and then think that February is going to increase by 5% let's say we think that for the equipment rental it's going to increase by 5% all the way through so how can we calculate that well if January is going to be the same we're going to say right January is 1130 it's going to increase times 5% plus .05 that's going to be 56.5 so then if I add that to the 1130 that's going to be the 1186 so that would be the 1186 I could get there a little bit faster but by saying well if I took 100% 1 plus the .05 that would be 105% or 1.05 times the 1130 that would give us the 1186 a bit faster allowing us to put a formula in place here so in February this time I'm going to change it I'm going to say this equals the one before it and then multiply it times the 1.05 so February I expected to go up to the 1187 so 1187 then I can just copy that I can put my cursor on the fill handle and it'll always take the one before it times the 1.05 not to the total but down to the December and there we get our 17 986 so that's one kind of way that we might see that it increase let's do the same kind of thing for this one we're going to say in February it's going to increase let's say it's going to increase by 10% so I'm going to do in February same kind of thing this is going to equal the prior period times 0.1 and again where do I get 10% how do I think it's going to increase by 10% we're going to have to do some projections on the economy we're just basically trying to think what could possibly happen based on whatever information that we have so we can project out into the future and see if we can determine any kind of trends that are happening that will help us with our calculations let's do this one so that looks good I'm going to put my cursor on this one on the fill handle and drag it all the way across through December so there we have it now the next one let's try the next one a little bit different we might say well I think this one is going to increase by a steady $1,000 per month for whatever reason instead of increasing by a rate so I'm just going to say I'm just going to increase it by the same $1,000 each month so I'm going to double click on this one and say I think this is going to be the prior sell time plus $1,000 plus $1,000 and I'll take that one all the way across on the service revenue that's another way that we might do a pretty simple calculation that will be uniform across our entire budget next we have the cost of goods sold now the cost of goods sold is linked to the income from the products here so if we say that there's an increase in the income of the products of 10% per period you would expect the cost of goods sold to follow that unless we're changing our price levels meaning you would expect the difference or the relationship between sales and the cost of goods sold to be related if you're selling basically inventory unless again you're changing the level of the inventory that you're selling so in other words if I'm saying that cost of goods sold here was $22,977 divided by the sales price of the $29,226 that would be 78.61% is the cost of the goods that we're selling I would think that that would be remaining constant going through unless we did some kind of change to the sales price so what we're going to do here is we're going to say let's say that this is going to take the same level increase as we saw with the sale of the income so I'm going to go in February this equals the prior sale times the 1.1 and so that's going to increase to the 25 I'm going to copy that across but before I do let's double check it we were at the .761 so you would think then that the 25275 divided by the 32148 the increase in the product would still be the .7862 or so on and so it looks like it's doing what we would expect let's copy it across we'll put our cursor on that sale grab the fill handle drag it to the right there we have the cost of the goods that are sold now a lot of the expense accounts we would expect to be basically the same all the way across especially if we're using like an accrual type of method because you expect them to be somewhat similar so if I go everything below this bank service charges $18 it's probably in material for budgeting purposes in other words probably not big for decision making purposes so $18 I'll keep that all the way across that's fine the insurance now the insurance is one of those types of things where if you're on a cash basis method you might say hey I'm going to pay the insurance periodically whereas if you're on an accrual basis method then you're going to have the uniform insurance across the board let's just imagine more of a cash basis method here just to switch things up so you might say for example you've got the 1000 here let's just imagine insurance is going to be paid we're going to say on February I would say it was a $6000 payment in February that we're going to have we'll have another 6000 in September so if you're on more of a cash basis that's going to be the full 1212 right there so you might do something like that to kind of better better calculate if you're on but so we'll just do that for that one just to see how you could have un-uniform items if you're on an accrual basis that you're doing in likely it being more of a uniform kind of basis so if we look at the rest of them we've got the internet expense we would think would be somewhat constant we're going to say that the payroll let's imagine a payroll that the other kind of change you might have is you might say okay I think payroll will be constant but maybe like somewhere in the middle say like July I expect to increase payroll and so you might have a step up in the expenses because you're going to level up on your payroll at that point in time so you might say okay maybe in July that's when I think it's going to level up at that point and jump to the next level so you might say this is going to be the prior point let's say for it right here times that 1.1 so it's going to increase by the 10% there and then for the rest of the time period it's going to be equal to that same amount and it'll be at that new tier level at that new step and we'll copy that all the way across here now if that happens to the payroll then you would think you might have the same kind of relationship just for an estimate for payroll taxes which get quite complex the payroll taxes but in theory you might say okay well if the payroll taxes before this is my portion of the payroll taxes Social Security and Medicare were whatever percent I could say well if the payroll taxes here were 486 divided by the 6983 then they're about 0.0695% of the payroll so you would think then that same amount times the 7682 would be about the increase here so you would think maybe payroll taxes would be the 534.6 or 535 let's say on the payroll taxes right because you would say the relationship would be the same if I'm here it's 535 divided by the payroll 7682 would be 0.0696 versus prior it was 486 divided by the 6983 was the 0.0695 and whatever and so on so that's the idea there so then I'll just copy those across let's say after that this is going to be equal to the prior one and this is going to be equal to the prior one not negative of it and then we'll put our cursor on the fill handle there and drag it across so dragging it across so that's where we stand on the payroll and the taxes back to the left supplies would be much the same we would expect we're going to say utilities we're going to keep it much the same the gain now the gains was a sale of stock and we're not in the business of selling stock we're in the business of selling guitars so maybe we're thinking that's a one-time thing we don't expect gains to happen and we're not really planning on the gains that's just some income that's why it was in other income so maybe I just delete this altogether so I'm just going to remove the gains for the sale of stock and then the depreciation would be according to the depreciation schedule so we might want to make we could go through and make that more specific according to whatever depreciation schedule we're on if it's a straight line double declining we might get that from the tax preparer that would be an accrual component by the way as opposed to a cash component but we're going to keep it the same here and then the interest expense that would depend on the type the level of financing that we're going to have we might expect it to actually decrease if we think we're not going to include any more financing but just simply be paying off the loan because we would be paying off the balance so let's imagine that one is actually going to go down so if I go to like February here we might say we're going to take the prior month times 0.95 so it's going to decrease because we're taking 95% of the prior area instead of increasing where we took like 110% in the past so I'm going to say that goes down to the 318 let's copy that across put our cursor on that and copy it across so we have that decreasing okay so let's just use this this might be something we can use to then implement notice the bottom line down here the net income this was our two month time frame this is in essence the one month time frame for January we didn't really have any changes we just divided by two and then we have this loss here for the 6000 that's why it's useful to kind of look at a cash flow basis too because that can be an impact you're going to need cash flow if you're going to be having a loss on one of the months possibly and then we've got our net income here we're just summing up the total and then the net income is going up as we have the increases in our revenue and so we're thinking here it's going to end up at that 125 964 summed up this way which we can double check summing it up that way so now we've got our month by month breakout that we can put into our our QuickBooks system so I'm going to go let's make it a little bit nicer I'm going to put some brackets around this entire thing let's take this whole thing and put some borders around it why not and then we're going to take that we're going to go to the home page font group I'm going to put some borders around it so that looks good and then our totals are on the right hand side here if your numbers don't tie out to these totals then you could change them to these totals if you want or try to figure out what the difference is but we're just going to use this as practice anyway we're not going to change any data in the QuickBooks file so you could use whatever you want to do to practice with the budget would be fine this would just be how you might think about putting together a 12 month budget that you can then do the data input which we'll do next time back into the QuickBooks system