 QuickBooks Online 2024 budgeted income statement export to excel and modify part number one get ready and some coffee because we don't just do data input we get totally into it within tuits quickbooks online 2024 here we are in our get great guitars 2024 quickbooks online sample company file we set up in a prior presentation opening up the major financial statement reports like we do every time the reports located on the left hand side we're in the favorites opening up the balance sheet which is everybody's favorite report at least one of them open link in new tab right clicking on the profit and loss otherwise known as the income statement open link in a new tab right clicking on the trial balance to open a link in a new tab let's tab to the right i'm going to close up the hamburger and then we'll do the changing of the range to the data points where there's actually information in this practice file that being first a word from our sponsor yeah actually we're sponsoring ourselves on this one because apparently the merchandisers they don't want to be seen with us but but that's okay whatever because our merchandise is better than their stupid stuff anyways like our trust me i'm an accountant product line yeah it's paramount that you let people know that you're an accountant because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions if you would like a commercial free experience consider subscribing to our website at accounting instruction dot com or accounting instruction dot think of it dot com oh one oh one two four tab oh three thirty one two four tab selecting the drop down so we can see it broken out month by month and then running it to refresh it let's tab to the right same thing with the profit and loss otherwise known as the income statement closing the hamburger changing the range oh one oh one two four tab oh three thirty one two four tab from totals to months and then run it and then we'll do this one more time tapping to the right on the trial balance closing the hamburger changing the range oh one oh one two four tab oh three thirty one two four and then selecting months and run it so now we're going to be starting the construction of our budget remembering that within quickbooks the budgeting is something that you can do a data input into and make a very basic budget based on past information however to do a more detailed budget you might want to take past information such as we're starting on the income statement information export it to excel use that to make projected budgets from then import it back into quickbooks why do that because quickbooks is good at running the reports such as budget versus actual so you can compare what actually happens as time passes to what you predicted to happen so if I go back to the first tab just to note where the budgets are located in quickbooks it's in the cogs drop down and then we have the tools and then we have the budgeting so we can create a budget here we can make balance sheet budgets and both profit and loss and balance sheet budgets the profit and loss is probably the first place to start when you're thinking about how to budget it being the performance type report so we'll get back to here when we import our budget back into quickbooks so that we can run reports the reports being found under the reports here and we'll then have our our budget reports that basically we can run such as the budget versus actual okay so let's go back to the income statement so our plan is going to be this we're going to take the prior information that's usually the starting point to create the budgets going forward if we had a whole year's worth of data one strategy might be that you take the whole year of last year budget it out month by month export month by month and then and then use that as your starting point that works good if you have a seasonal business method number two you take the whole year's worth of information from the prior and just run it as a total right we'll just do the totals only that we only have two months but if it was for a year you run the totals and then say i'm going to take those totals divided by 12 and that will be basically my starting point for each month averaging out my income for the prior year as my starting point that might be a good method if you're in a situation where you don't have a seasonal business and you want kind of like the average performance over the year if you're somewhat static over the year if you've been growing over the years then you might say hey look i don't i want to take december's numbers because i think they are in alignment with my growth trend which i believe will continue going forward therefore i'm going to start with december and then do my budget uh from december mapping out december as what i think is going to happen as my baseline budget and then adjusting from there for us we only have two months of data input so i'm going to kind of imagine as though uh those two months of data input we have two and a half months we did some in march but only two full months so we're going to kind of imagine that those two months were actually last period uh november and december export them to excel adjust the excel budget and then import them back into our system so we can run reports budget versus actual for the first two months let me show you what i mean now you could do it with the income statement but i don't like exporting the income statement as much if i'm going to adjust the budget because there's all these subtotals i have to deal with on the income statement what i'd like to do is just have income minus expenses basically and so the easiest starting point might be the trial balance for that kind of report so i have january and february notice the trial balance has the balance sheet on top of the income statement so i have all my balance sheet accounts cash down to uh owners equity and then the income statement starts here at income on down so i can export this and just delete the balance sheet accounts leaving me with the income statement accounts that's what i'm going to do now i could do this again month by month but instead of doing that i'm going to just take my full two months not including march here just the two months of january and february numbers by the way you can't really export as easily the trial balance if you wanted to do an income statement period by period because quick bus closes out the the the income statement on a yearly basis so so that so just be aware of that you'd have to export the income statement in that case if you wanted to export like a month by month income statement generally okay but for us we're going to go for oh two let's go from 010124 to 022924 so we have two months of data that's all we have thus far in our business we're going to imagine that's going to be our starting point for the budgeting process so let's go ahead and open or export that to excel i'm going to export to excel now i'm going to do some excel work i'm going to do it fairly quickly just to give you an idea because it's not an excel course but i'll try to go slow enough that if you have some excel background then you know you could follow along with this as well will also give you the end result so that you can just import it to quickbooks if that's what you would like to do as well okay so hold on i messed up here i messed up there it was a mess up so that's it okay so let's go back i'm going to close that out and then let's make this back go back to totals only totals only and then i'll export it okay so there we have it now we'll export it to do export to excel and open it up okay this is going to be my starting point all right so i'll enable editing gonna save it okay so now what i'd like to do i'm gonna i'm going to scroll in so i'm at 100 percent i'm going to hold control and scroll in a bit okay the first thing i tend to do is i like to have the whole thing formatted in the same font so i have weird different font here from over here this is eight and this is eight but then over here i have 11 and this is bold and this isn't bold so what i do is i'm going to start with my baseline excel font which is 11 and so i'm and then i'm just going to go to the home tab and paint brush it on the entire worksheet so that's my starting point it changed all the cell references to non-numeric cell references so now i'm going to right click on the whole sheet and format this to my baseline underline format that i would like which i usually choose currency uh negative numbers bracketed i'm not going to get any uh the dollar signs let's remove it and then i don't really need the decimals because it's a budget it's going to be an estimate so this will round it but i'm just going to remove the decimals so it's a little bit easier to see and okay i'm going to make the entire sheet bold which i think is easier to see in a screen recording home tab font group bold you don't have to do it bold on your end if you don't want to do that all right so then i don't need the header so i'm going to put my cursor from one down to four i'm just going to delete all of that there i don't even need the debit and credit thing i don't need the debits and credits let's get rid of that right click and delete get out of here and then i'm going to go down below i don't need the totals so wow that they did it with a long formula that is interesting i'm going to put my cursor on 50 on down i don't need those for the double checking i'm going to right click and delete that now i'm going to delete all of the uh balance sheet accounts now if i have a problem with this i'll stop saying now i keep saying now don't if i have a problem with this i can always double check this to what's on my income statement so i'll double i can double check my net income to what is in the income statement let's go as of 0 to 29 to 4 so so so i have a check number and if i mess up i can always you know do it again right so i'm going to delete from row one or is that a row yeah rows not columns rows one down to owner's equity that's the entire balance sheet so the income statement starts on this billable expense income so i'm going to right click on all that and just delete it and now all i have is my income statement so i have the income statement in terms of debits and credits so so notice if i sum this up right the credits are here that's the income basically and the debits are here if i sum that up the difference then should be net income so 1324 so 1324 net income dude let's double check that 1324 plus i got rid of the pennies all right so that so it looks like that's good so now i'm going to delete that i don't want it in debit and credit format i want to make all of my my let's make all of the expenses negative numbers so i can see these as negatives and these as positives and then it'll net out to net income so there's a trick to do that i can right click i can copy this i'm going to try it over here so i don't mess anything up and right click and paste special and then i want to transpose down here transpose make uh is that it no no that's not what i do i want to subtract subtract which should make it negative so i'm going to say okay so now it pasted the same thing but they're all negative so now i can just copy this and put it back over here boom and so so there we have it notice these all have formulas like funny formulas it says equals even though i don't need like an equal sign in there so i'm going to take this whole thing and copy and paste it just one two three so it's just values values only so now i don't have any weird formulas okay so then i can put everything in one column so i can take this whole thing and drag it into one column or equivalently i can right click and cut it or control x and then control p or v or i can paste it right so i can do this control x control v control x control v okay so there is that and then down here notice the subcategories get kind of ugly on the trial balance because it gives me the parent category and then the subcategory with a colon i don't really need the parent category so what i typically do is i go in here double click and just get rid of the parent category meaning i'll just show you what i mean if i go to the income statement we have some accounts that we made like a parent account and then a subsidiary to it insurance the subsidiary on the trial balance it doesn't have a triangle drop down therefore it shows that it's a subsidiary by putting the parent colon and then right up and then the subsidiary account but i don't need that so i just want the subsidiary accounts i'm going to double click and get rid of all the ones with a colon so get out of here that this taxes payroll tax adjustments all i need so i'll get rid of all the parent account references then i can make this a lot smaller see i can make this column smaller and then my net income so my net income i can calculate by just summing this up equals the sum of these boom and it should still be the same one three two four which i can double check over here net incomes at one three two four okay so now i just i have just a basic income statement with the income as positives and the expenses as negatives i can calculate that by just summing it up there's my income statement that's a great starting point for me then to budget out i'm what i'm going to do next time is basically map out the months i'm going to imagine this is two months worth of data and i'm going to kind of imagine that it was last year meaning november and december of the prior year and then map out january to december and use use these numbers to try to figure out what i should populate on a monthly basis going forward so that's going to be our starting point let's save that and we'll continue from there uh next time so if i go back on over we'll continue to map this out and then and then we'll go back and import it once we map it out we'll import it into uh into the budget here on a profit and loss type budget and then we'll see if we can run uh reports we'll run reports with it that's what quickbooks does well so quickbooks isn't really generating the budget you can generate a basic budget but if we want to do anything more than that excel i think is a much better tool but quickbooks is still in the in the process because then we want to put it back into quickbooks to run mainly my opinion best report the comparative reports to see what actually happened to what we budgeted to happen