 QuickBooks Online 2024 budgeted income statement export to excel and modify part number two. Get ready and some coffee because we're moving on up with QuickBooks Online 2024. First a word from our sponsor. 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 is better than their stupid stuff anyways like our crunchy numbers is my cardio product line now i'm not saying that subscribing to this channel crunchy numbers with us will make you thin fit and healthy or anything however it does seem like it worked for her just saying so yeah subscribe hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine if you would like a commercial free experience consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com here we are in our get great guitars 2024 QuickBooks Online sample company file we set up in a prior presentation opening the major financial statement reports as we do every time the reports are located on the left hand side in the favorites right clicking on that balance sheet to open a link in a new tab right clicking the p&l otherwise known as the income statement open a link in a new tab same with the trustee trial balance the tb if you don't have that trial balance in the favorites you can search for it we're gonna tab to the right close up the hamburger we'll change the range we're going from 010124 tab let's go to 022924 tab and then select the months so we can see it on a month by month and run it and then we'll tab to the right this is the report that we're working on here or working off of at least the income statement the profit and loss closing the hamburger changing the range let's start off with 010124 tab 022924 tab selecting the drop down looking at it from a month by month breakout and run it and then we'll tab to the right same thing with the trustee trial balance closing the hamburger change in the range going from 010124 tab 022924 tab we will run to refresh and hold on a second I didn't do the month's breakout let's do the month by month breakout and then run it okay let's go back to the income statement we're looking at our budgeting process thinking about the budget report that we first think of usually that being the income statement or the profit and loss whatever you want to call it because that's the timing report that's going to measure performance so we're going to judge how far we're going to go how high is income going to go next time how high is the expense is going to go how high are we going to get on that net income let's raise the bar people let's raise the bar so how do we do that well the first thing would be that maybe we want to export last year's income statement into excel so that we can make adjustments to it there taken into consideration not only last year's data but also what we think is going to happen going forward what changes we're going to make so that we can improve things and we're going to stop listening to crazy people right from the entertainment company stop listening to you know the the crazies that keep telling you like that that if you do stupid things your income is going to go up we've seen the results we've seen the results and we see that the budget is not is not how what actually happened and then we make changes so we're going to make decisions that make sense now and then once we do that we'll adjust the budget we'll import it back into QuickBooks by going to the tab to the left or go into the icon up top and then we'll go into the budgeting and then we'll insert the budget so we can run reports like budget versus actual now when we export the budget to excel we could have used the income statement here if we had a whole year's worth of data and give the whole income statement but we're going to export the total starting with the trial balance because the trial balance in our case is easier so so i would like to export the total so i make this totals instead of months and we export of the entire trial balance and then just removed everything that is not the income statement and then we made the the revenue positive numbers the expenses negative numbers and we result in a nice easy income statement without any subtotals that looks like this we double checked that it's okay because the net income down here ties out to what's a net income for the total profit and loss for january and february that being one three two four we rounded it one three two four that's our starting point okay so now what we're going to do is we're going to use that and i'm going to imagine like basically that's the last two months meaning november and december of last year and it would be and so what i'm going to do is try to take the average of those two and say that's going to be my my average income on a monthly basis going forward that's my baseline and then we'll move from there so i'd like to make headers that will have the months up top so to do that one way i can do that as i could select the entire column or row one right click and insert and that will shift down so that i so that i have the headers uh and then i can put the columns or the rows underneath it so i'm also going to make a skinny c to separate so i'm going to put my cursor in between the c so that i can separate like my budget data my on the left that's going to help me create my budget on the right or my actual data on the left to help me create my budget on the right i'm going to type in jan feb and quickbooks should be able to read that and see that as months so if i put my cursor on those two items and then on the fill handle drag it to the right uh this isn't quickbooks this is excel excel should see and be able to predict that we want month by month there it goes on up to december and boom easy peasy so let's go ahead and center this now we're going to go to the home tab uh alignment let's center it i usually that's not center that's not center what are you doing oh hold on uh i'm not my soundboard i'm still not good at it and now i messed up my whole thing okay sorry about that i'm working on it it's going to be good once i know how to do it right so any case i'm going to go to the home tab font group i like to make it black and white on the headers so i know it's a header and then all i'm going to do is my starting data i'm just going to take this is going to equal that which is for two months and then i'll divide it by two right so i'm going to say if it was two months two hundred dollars i'm going to start with a baseline of each row for that for that billable expense income to be one hundred dollars that's what we're going to start with as our prediction going forward so i would like to copy this across to start with uh how can i do that well a couple ways we can do that i could go to february and just say equals the one before it and i kind of actually like that method better because if i do that then then if i make a change here right everything after it changes the other method you could use is to say i'm going to take this and because this is coming from my data outside of of where i'm currently working it's coming from my data source i would make it f4 or absolute dollar sign before the b and the two and then divide it by two and then i can copy that across and it will not move this cell so but the problem with that is if i change this one for example to 50 it doesn't change the ones after it which is probably more likely what we would like to happen so i actually like this first method better i'm going to say this equals this number divided by two this is my monthly amount and then in february i'm going to say this equals the one prior to it and then i can copy that formula across and then each month will be equal to the one prior to it and so that if i change any number along the way it will be a change that will resound across the rest of the the rest of the numbers so i think that's the best way to do it so i'm going to do that all the way down then i can copy this one down so i could say let's copy this one down and that's easy to do i'm not going to put it on net income here because i'll redo net income i can copy this formula and put it here so it's just summing up i'm just summing up that column and then this one's just equaling the one prior so i can just copy that down not to the total and then i could do that all the way across i can select all of these and i could just copy them down and so there we have it and then i can copy this across this is my total and that's my baseline that's how easy it is to make kind of the baseline again you could use different methods to do this this is basically averaging what happened last year as my starting point now if you have a seasonal business if you might choose some other method because then you have some months that might be typically higher than other months if you have a business last year where you're just where you're growing and you think december is more representative of what's going to happen going forward you might start with the december numbers to populate everything right as your baseline and then move from there okay but so we're gonna but we're gonna take the average i took the total which only two months here if it was a year i would take the whole years in totals divided by 12 as my baseline number and then make my adjustments from there so now let's start making adjustments so i'm gonna highlight the ones i'm making adjustments on let's make this one like green so i'll say let's make adjustments starting here just so i can see where what i'm working on and so here i'm going to say let's keep january the same but then say that it's going to go up in february now now we're going to say we think there's going to be constant growth happening so this is the the optimists in there right the the the sales lady that we got in the advertiser that we brought in from from bud light is saying dude the advertising we did is going to cost it's going to we've we've helped you out here we're going to we're going to advertise for the equipment rental and it's going to increase your revenue by let's what let's do a percent like by five percent per month all right so we're going to say okay okay uh you're you seem advertisers know what they're doing so let me see if what would that look like so if i took 1130 times 0.05 or five percent that would be an increase of 5650 uh per month right and then if i took that plus the 1130 that would be meaning month two would be at 118650 right and and i could shortcut that by saying i'm just going to take the 1130 times 100 plus uh five did i say five percent five percent we'll say that would be 1.05 i hope i i might have did 50 i said five i think so that would be the 1186 right so i can easily make a formula if we're going to make a nice smooth increase i think it's all right you think it's going to increase five percent i'm just the bookkeeper here i'm not uh your advertising scheme looks a little dubious to me but what do i i'm just i'm just the bookkeeper right so yeah you're going to say it's going to increase by 1.05 all right so then and then we can say that copied all the way across and then we could we could put our cursor here and then copy this formula across so if i copy this across now each cell is picking up the one before it and multiplying it times 1.05 so we have a constant increase based on the prior number of 1.05 now quick books i mean excel is trying to give me this little error message saying that the formula is not uniform here and so we could go into each of these and say like i could select all of these sometimes that's a helpful formula but i'm going to hit the drop down and say uh ignore error right and then it gets rid of those little triangles if those bother you because they do bother me it's kind of annoying so then we'll say total but maybe you're less botherable than i am i'm either i'm easily botherable so we'll go to the home tab format paint and make it the total let's make a total over here equals the sum of each of these for the 12 months boom and then we'll copy that down bam not to the not to the net income right there and then we'll sum up this way so it sums up so now we can see the total all right so that means that according to this marketing person uh we're going to get it's going to go up to 17986 if we do if we do the advertising recommended not only that but she says that the product sales will increase because people will be like so into it will have all these new people with these really colorful new guitars that we've made these gaudy colored guitars they're going to sell they're going to sell like hot cakes so that that means this one's going to go up by the same kind of thing we're going to say this one's going up by 10 percent we're like i don't know those i wouldn't buy that guitar that looks like that but if you say so if we're going to say it's goes up by 110 percent we're just the accountant so if that's what you tell me that's going to happen i can calculate it if that's what you if that's the plan so we plan on these going up uh to here right it's it's not my it's not my place to say that you sound kind of crazy right now but here we go not only that they say that this marketing on the service income because they're going to be really happy with their super colorful guitars that we've advertised for them that they're going to want to come in to take guitar lessons because they're going to think that your guitar teachers are super cool with uh due to the advertising that when we made them look so appealing with the advertising because their hair was like 10 different colors at one time every strand of hair was a different color and so and that uh is helpful apparently so we're going to say that this is going to go up by $1,000 each time so this is the other kind of format that you can do a pretty easy progression so instead of it going up by a rate we're going to say it's going to go up by a constant amount a thousand dollars each time so this is the other way we can kind of do a pretty easy increase so we'll say a thousand and then i'm going to copy that across so it's just going to repeat a thousand dollars each time it's like all right we're going to i don't know where you're coming up with the you know people are going to pay us a thousand dollars more each time but uh that's what you say we'll put it into the we'll put it into the budget here okay so then based on that information that we have i can look at the cost of goods sold now most of the expenses by the way will be pretty easy because the expenses might be somewhat repetitive so even if the sales go up my utility bill will probably remain somewhat the same unless we're going to keep the shop open all night or something like that and our electric bill is going to go up or something like that or we have a crazy band that has our amplifiers blasting all the i don't know so but the cost to goods sold is an account that will typically change in alignment with the sale of the products the sale of the products uh is a sale of our inventory and usually uh if if we sell inventory most businesses have a pretty good idea of what the profit margin is you know between the sales line and the cost of goods sold that's their that's the profit margin so we can we can say okay well if you know for example if i pull up the trusty calculator we're going to say that you know before we had the cost of goods sold was 45954 divided by the the the sales of inventory of of of our inventory 58451 so we have the the cost of goods sold is 78% of the of the sales price right so and our and our our profit margin then would be like we had the 58451 minus the 45954 that's a 12497 profit of the sales price minus the cost of the guitars if I divide that by the sales price we get 58451 right 21.3% right so i can do so the same so i that same relationship you would think would follow if there's an increase to the the sales so the easiest way to do that is to say well i'm just going to if if you're going to have a nice flow of an increase we said of 10 per cent per period on the sales we would expect the cost of goods sold to go up roughly equally because we're going to have to buy the guitars to make those sales right so i'm going to say let's go into here and do the same thing this is going to be times 1.1 percent and we'll have this nice smooth i'll copy that across increase there and so then i can take i can take the same ratio and just check it i can say okay well 45954 divided by this number 58451 was 78.6 percent let's just check this one i should get the same percent right 27802 divided by this number the 35363 is 78.6 percent right and then the total i'll stop saying right i'm get i'm i feel like i'm developing a twitch i'm saying right all the time am i right microphone could you tell me am i right 491 347 on the total divided by the 627966 is the 78.6 percent so same relationship all the way across the board okay so we did the cost of goods sold let's just highlight that one say we did that one and now we're like i'm going to call i'm going to say the bank feeds i'm not even going to do anything with the bank feeds because it's one in material most likely not going to have a big impact on our decision making due to the dollar sign the dollar amount being small and because i would expect it to be somewhat the same so so then we have the insurance now the insurance there's kind of an issue with whether we're not we're looking at this on a cash basis or a cruel basis we've been putting our books on an accrual basis meaning if we pay for the insurance uh at one time for a year then then we'd still allocate it on an accrual basis now if you were doing a full service budgeting system then you would want to do both the cash based budget and an accrual based budget right you'd have two budgets but uh uh we'll just do i'm just going to imagine for the sake of our data input just to see it a little bit different that we're going to do a kind of a cash based system here paying for it two times a year so i'm going to delete this whole thing and i'm just going to say that we're going to pay uh uh we're going to pay liability insurance in february we'll say february it's going to actually cost six thousand we're going to imagine and then september i'm going to say six thousand so that's going to be a that's what we're projecting to happen next year on the liability insurance we're going to say just to switch up the routine so you might have some kind of costs that will be higher in particular areas remembering again that when that is the case it's likely that you might be doing an accrual thing to it if you were doing a full service accrual accounting system in which case you might again want two budgets cash based versus cruel accrual based budgets okay so continuing on internet i'm going to say is basically the same so we're going to think that's going to be basically the same all the way through the year and because it's going to be somewhat constant unless there's like inflation or something that's happening or we need to upgrade or whatever and then taxes that we have this is uh payroll taxes so these both of these two lines are payroll taxes payroll taxes and payroll tax uh adjustment so if i net the if i if i was to net these two out it comes out to uh 48 so what i can do then is i'm i'm going to i should probably try to put these on one line so let me do this i'm going to say i'm going to this cell i'm going to say is equal to the sum of these two close the brackets divided by two instead of having two separate lines and then i'll say enter so now we've got the this amount and then i can delete this adjustment account i don't need an adjustment account i'll put it both in the payroll tax line selecting that and deleting it that's still uh hold on a second let me undo undo that i have to do that here as well so i'm going to say these two should be 972 so i'm going to say here this is going to be uh well let's do it this way this is going to be 972 negative negative and then i don't have to sum the two up this is just going to be this divided by two okay that looks right and then i can delete this whole line delete and then the total is still equivalent to what's on my in 1324 uh 13 uh 1325 rounded okay that's fine all right so then so then i'm going to say this one maybe we think there's going to be a step up so let's say that wages are the same and then like in june let's say we're planning a pay raise so june let's go let's go to june and say we're going to increase it here by uh 1.1 it's going to go up by 10 and uh so this is going to be times 1.1 times 1.1 okay let's undo this whole thing i don't know what is happening let's going to go into this again this is going to be equal to prior one times 100 plus 10 1.1 going up by 10 in june enter okay so that's a step up so notice i'm not going to continue it going up 10 now i'm going to say it stayed the same up until here and then it's going to go up by the 10 uh going into after that point let's actually do it in july i think i want to do it in july so now i'm going to mess it up again so i'm going to say let's let's delete it here and then i'm going to do it in july so i'm going to go in here times 1.1 and so i'm also going to do the wages so i'm going to go into the wages here and say this is going to be that times 1.1 so now the wages are up to 7682 now we have a similar relationship notice between the taxes and the wages that we had because these are payroll taxes as we did when we when we looked at like the cost to goods sold and the sales meaning as wages go up you would expect the payroll taxes to go up in some similar proportion so in other words if so if i look at the relationship between these two 486 divided by the 6983 is 6.9 percent so i could have done this and the other way i could have done this is say i believe that 6.9 percent should stay basically constant and i could put this equal to this times 6.9 percent right or i can just have an increase by the same amount as we did here so i now i have the 535 divided by the 7682 and still 6.9 percent so we still have that relationship the point being as we increase wages you would expect you would expect payroll taxes which is our portion of the taxes not the employee portion their taxes will go up too but that's not our expense that's their expense these are our portion of social security medicare and futa that will go up okay so that's that all right so let's keep going supplies i'm going to imagine that's the same you would think that if the revenue goes up then supplies might go up but it also might be in material depending on the industry you're in so i'm going to say it's either the same or possibly in material not really relevant to decision-making possibly telephone again i'm going to say that's somewhat the same maybe the telephone goes up when revenue goes up but usually telephone will go up in this staggered kind of way like we had here right because that we might have to get a higher plan at some point in which case you're going to have a step up in costs similarly with utilities you would think it would be much the same unless we needed you know to level up a whole another shop or something where the utility bills would go up in which case it would go up in a step up the gain on sale of investment because these were actually a one-time thing we had short-term investments that we sold i don't expect this to repeat at all so i'm actually going to delete this whole thing and remove that one and then we have depreciation depreciation is something that you could get directly from the depreciation schedules and it might not be uniform if you're using something like an accelerated depreciation method but if you're using a straight line method you would expect it to be somewhat uniform so you could make that kind of more exact at this point in time it would be significantly different or change if you plan on buying more equipment so note that if you're doing a full a more expansive budgeting process then you would also have a cash flow budget that would include pot include equipment purchasing budgets right which could help you to then calculate your depreciation depreciation being something that would be on an accrual basis and you'd also might be have a cash flow budget which doesn't include depreciation because cash is not impacted with depreciation but we're going to keep it the same here going forward so we'll just let it roll and then the interest expense interest is something on the loans so you could think well if i've already financed the business and i'm not taking on any new loans then the interest expense would be somewhat standard but you can actually get the exact interest expense from the amortization tables right because it should go down each month according to the amortization tables uh we're just going to estimate it here let's let's try to do another method we might say the interest i think is going to go down let's just imagine it goes down by a constant rate so i'm going to take the prior period times 95 percent so i'm going to say prior the prior amount times 0.95 same concept but this time it's going to go down each time instead of increasing right so we'll do something like that uh with the interest and then so let's go ahead so we did that one i'm not going to do anything for the rest so that's going to be basically it so let's close this we're going to say hey hey hold on a second so so that's going to be basically it okay so now let's just kind of clean it up so i'm going to select this whole bit right here let's put brackets around it i'm going to go home tab font group and we'll put brackets around it we could put brackets around this side as well brackets i'm going to ungreenify it and there we have it let's put an underline here home tab font group underline let's put an underline here home tab font group underline okay so at the end next year we're looking 125 126 thousand almost on the net income that's what i'm talking about that's as long as that advertising goes the way it's planned and who who would doubt who would doubt the genius of this lady that we hired after her skills in the entertainment in hollywood and and then and then with the with the beverage with the beverage people anyways i don't know we're just the accountant just just shut up and put the numbers in like they told you to put the numbers in there okay okay we're going to make 120 revenues going to soar people it's going to soar according to the data input we got okay so that's going to be the that's going to be it so in future presentations we're going to take this stuff put it back into quickbooks so that we can then generate our budgets in quickbooks allowing us to do comparisons between actual and budget