 QuickBooks Online 2023, Budgeted Income Statement, Export to Excel, and Modify Part Number 1. Get ready to start moving on up with QuickBooks Online 2023. Here we are in our Get Great Guitars practice file. We started up in a prior presentation using the 30-day free trial. We also have open the free QuickBooks Online sample company. You can open the two at the same time by using Incognito. 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. Oh, or another browser. 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 going to be comparing the Accounting View, the one Get Great Guitars is in, and the Business View, the one the sample company is in. You can toggle between the two by going to the cog up top, switch the view down below. We're going to be duplicating some tabs to put reports in, right click on the tab up top to duplicate, right click on the tab up top to duplicate again. Back to the tab to the middle, we want to go down to the reports on the left hand side. We're going to close up the hand buggy. We're going to open up the balance sheet, one of the favorites. Where's that located in the Business View? You know where it's at. It's under the Business Overview and then the reports on the left. Back to the Accounting View tab into the right down to the reports on the left. This time the income statement, the profit, the loss, closing up the buggy, changing the range. We're going from 010123 to 022823. Let's take a look at the side by side view of things by going to the months and then run it to refresh it. Tab to the middle and let's open up, let's range change. 010123 to 022823. See that on a side by side month by month. Run it to refresh it. There's where we have it. We're doing our budget this time. We talked about the budget overview last time remembering that the budget data input is in the cog up top and then in the budget. If you don't enter the budget this way, you're not going to have any budget reports. QuickBooks is quite good at running the budget reports comparing actual to budget, but to actually construct the budget, I would export past data from Excel, I mean from QuickBooks to Excel and start there and then make changes to our budget and then import that data back into the system so that we can then use and run budget versus actual. Now the budget we usually focus in on the profit and loss. We could think about where we're going to be at the end of balance sheet as well, but our main focus is usually on the performance statement, the profit and loss statement. So we could export this P&L or something like it to Excel and start to manipulate that. However, I think it's a little easier to try to just use less subtotals here. So I want to export something that has less subtotals. So for me, oftentimes I like to export the trial balance and then just trim it down so I don't have to deal with all these subtotals. So let me show you what that looks like. So I'm going to right-click on the tab up top, duplicate it, and then I'm going to go down to the reports on the left-hand side. The reports, close up the hand boogie, type in trial balance, trial balance, and there it is. So I'm going to make a year to date or just for the first two months trial balance. I'm going to go from 010123 to 022823. I'm not going to do the month by month side by side, just run it as is. There's our trial balance for January and February. Now for our practice problem, we have entered just two months of data. I'm going to export this two months of data and I'm going to pretend basically that came from the prior period so that we can budget out for 12 months January through December so that when I import it back into QuickBooks we can do a side by side comparison for January and February. So I'm going to imagine this is past data here, which it is in our practice problem. We're going to then export it to Excel and then I'm going to trim it down to just seeing the income statement numbers down below and I'm going to try to format it so income is positive and expenses are negative so I can get down to my net income number without all the subtotals and junk in it. So let's do that. I'm going to export it by going to the drop down and just export to Excel and then I'm going to pull that into my folder over here. So I've got my folder that I'm going to put that in. So I like to do that first. So I'm just going to pull that into my folder and I'm going to call this, I'm going to make it large, rename it, right click and let's rename it and I'm going to call this my budget for 2022 let's say 2023 let's say. Okay, then I'll open it up and then I'm just going to manipulate this Excel sheet to just be showing me the income statement accounts. So I know this is not an Excel course so I'm going to do this fairly quickly but this is what this is how I would think about the budgeting process. Now note that first of all you got kind of like some formatting issues were over here for example it's an aerial eight and up here you've got different formatting 14 and so on. So I'd like to start off with everything simply the same formatting and I usually start off with the Excel formatting over here standard Excel Calibri 11 so I'm going to go in my cursor over here home tab clipboard paintbrush and then put my paintbrush down on the triangle paint brushing the entire thing so that it's just standard formatting and then I'm going to right click on it and format what I want it to be right click format the cells and then I'm going to go to usually I go to currency negative numbers bracketed and red get rid of the dollar sign and we don't really need the pennies in a budget so I'm going to get rid of the decimals as well because it's going to be rounded because it's a budget anyways so I'm going to say okay I also like making it bold for the practice problem because I think that might stand out in the videos a little bit more so you may not need to do that but I'm going to do that there we have it now all of the data in here notice how it still kind of looks like a formula it has an equal sign in front of it I'd like to make everything just hard coded numbers and not have any formulas when I start to manipulate the data so what I'm going to do is I'm going to take these two columns entirely I'm going to right click and copy them and then I'm going to right click and paste it one two three values only so you don't have any formulas they're just hard coded numbers now no equal signs the totals down here are now worthless because there's no equal sign or summing so I can get rid of that whole column now I'm just going to trim this thing down to what I want I don't need the header so I can put my cursor on column or row one this is a row not a column I got to get these straights I don't need the debits and credits I'm going to delete that right click and delete and then everything down to the income statement I can remove so it's an order assets and then liabilities and then equity and then the income statement which starts right about here right there exactly so everything above there I don't need I want to remove it so I'm going to put my cursor on column or row 24 and drag up to the top and then right click and delete all that stuff so that's gone I also don't need this junk down below the totals doesn't do anything for us I'm going to put my cursor on call or row 18 and drag down to 25 or so right click and delete so we trimmed it down to what we need what I'd like to do now is have everything in the same in the same column and possibly have the income numbers which are on the credit side to be positive and the expense numbers to be negative that'll be the easiest thing for most people to read so we're not doing debits and credits we're just doing plus and minus income positive and the expenses negative so all the ones on the debit side are decreasing the income statement so I could go to each of these and kind of put a negative in front of it but there's a trick to do this we could just copy the whole thing I'm going to put my cursor on column B right click and copy it and I'm going to copy it because I could cut it but I'm going to copy it just in case I mess up I got to make sure I'm in row one because I copied the whole column and I'm going to right click and I'm going to paste it special I want to paste it special it's a super special pasting and I want to click this subtract button which means it should paste everything but flip the sign making it a negative number let's see if it does boom it does so that looks good so now I'm going to say okay now I'm just going to copy that and I'm going to put it right back down over here because that's where I want it to be paste it the only reason I don't do that at the beginning is because I kind of test it out you could have just copied and pasted it right on top of the same numbers and flipped the sign would have been a little bit faster I'm going to put my cursor on column E and right click and delete it don't need it now and now I'm going to move all this stuff to the same column so I could do that by I could go one by one and drag it over like this or I can right click and cut and paste it which is I think an easier way to do it or I can take I can control X on these two this is where I'll do it from here on and control V that's the easiest way to do it it's the same as basically moving it with your cursor here to just cut it control X and control V alright and now we should have net income as the total so this is going to be net income and I'm going to sum this up and this is the moment of truth because we should come up to the net income that ties out to what is on the income statement down here at the one three two four oh five except we rounded it so there won't be any pennies so let's try it out equals the sum of all this stuff and enter and we're at the one three two four and there's no pennies so it looks good I think we're we're set up and we're on the way here also just something to quick note here is that when you have the sub accounts on the trial balance they put the actual parent of the account here general business expenses and then bank fees and service charges so you might go into some of these and remove that long name because I really only want to say okay bank fees and service I don't need the parent account for the sub accounts this one says insurance liability insurance maybe I don't need the insurance I can just call it liability insurance payroll expenses payroll taxes that's the parent maybe I don't need payroll expenses then this one I can delete the payroll expenses just to trim it up a little bit and so there we have it then I can make this a little bit shorter maybe and that's our starting point so we're going to imagine this was our past data for two months and then we'll use it to project out into the future 12 months so if I have two months of data I can then divide it by two and say well that's my monthly data for example and then and then try to use that as my ground floor or baseline assumption for the months that we're going to project out into the future the other way you could do it obviously is you could take the actual numbers I could have exported January and February themselves and then use the use the month the differences in the month from the past and try to project what's going to happen in the future from from that as well so there's a couple different variations that you might use in other words like if I had a whole years of data in the past for 2022 let's say and I was trying to project for 2023 one method I could take the whole year's data the total for the whole year divide it by 12 for each account and say that's basically the average income and expense per account per month and average that is what we're going to have each month but you might have a seasonal business where you might say hey maybe it would be better if I take that prior year data and just run a month by month profit and loss and use each month as my starting point to project what's going to happen in the following year so you might that's another method that you can use that method or you might be in a situation where you're saying hey look my business is changing a lot so the prior the prior months that in January of prior of last year are not reflective of my current business practice the later months are reflective of my current business practice because we've improved so you might be saying I can't really rely on January months because I think we've improved so maybe you use the later months in the year because you think that's where you're currently standing that's where you're going to project going forward due to simply business improvements possibly purchases of fixed assets and whatnot which has increased your capacity so those are kind of things that you keep in that you know could change the way you think about how you might construct your budget but we're going to say this is our two months of data we'll basically divide it by two to get a monthly budget and then project it out for 12 months as our starting point and then we'll make some tweaks from there we'll see that next time so stay tuned and we'll be back