 QuickBooks Online 2024, budgeted balance sheet export to Excel part number two. Get ready and some coffee because we're going to be able to generate financials on call with QuickBooks Online 2024. 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 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 accountinginstruction.com or accountinginstruction.thinkific.com. First a quick budgeted balance sheet disclaimer it looks like at this time QuickBooks Online does not have the capacity to run budgeted balance sheet reports. Let's go over a quick history of budgets from QuickBooks to get an idea of the progression over time. I believe that the desktop version of QuickBooks has had the capacity for some time to run budgets for both the profit and loss otherwise known as the income statement as well as the balance sheet. However QuickBooks Online has not had the capacity prior to recently to even do the data input for the budgeted balance sheet only having the capacity for the budgeted income statement otherwise known as the budgeted profit and loss reports. So recently we now have this option to be able to do the data input for the budgeted balance sheet but it doesn't look like we have the related reports that you would think would be generated from that data input such as the budget overview for the balance sheet and the budget versus actual for the balance sheet. Now I would expect or I would think going forward that hopefully QuickBooks would be able to use that data input to then create those reports but I don't believe they are there as of the time of this recording. Here we are in our gig 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 that are on the left we're going to be right clicking on one of those favorites the balance sheet to open a link in a new tab do the same thing with the good old profit and loss otherwise known as the income statement as well as the trustee trial balance and then we'll tab to the right and close that hamburger on the meat of the balance sheet and we're going to be going from 010124 tab 022924 tab selecting the drop down so we can see that meat in the months and we're going to say run it and then we're going to go to the tab to the right close up the hamburger again close the hamburger and change the range even though the hamburger bun is closed yes we're still going to mess with the lettuce on top here we're going to change the range 010124 to 022924 tab and then we're going to select the drop down and make months and run this side by side and then we're going to tab to the right and do the same thing close the hamburger bun and then we'll change the range it's like why don't you why don't you pick out the lettuce before you close the hamburger bun so that's just not how i do it i close the hamburger bun and then i organized the lettuce of the of the dates 010124 tab 022924 tab selecting the drop down months and then we'll run it to refresh it okay so in prior presentations we've been thinking about our budgeting process the major budgeting report typically being the profit and loss or balance sheet that being the performance report we've we generated a budget for the profit and loss now we're working on the balance sheet remembering that the balance sheet is in essence a point in time so the balance sheet is where we stand as of a point in time so when we think about the relationship between the income statement and the balance sheet we're basically saying this is the balance sheet before we started the budget the income statement is kind of what we did how far we ran to get to the end balance sheet the next point in time at the end of the month or the end of the quarter or the end of the year now it's not quite that simple of course because the balance sheet is a bit more complex in terms of getting all the accounts properly you know adjusted we can kind of think about the net income or the income statement as basically the difference between the bottom line of the balance sheet assets minus liabilities equals equity which is basically the owner's claim to the assets of the business but when we start to then allocate out which which assets we have cash versus accounts receivable and what's the proportion of liabilities and whatnot it can get kind of complicated we can use different methods to try to approximate those depending on how sophisticated we want to get so we exported our balance sheet using the trial balance to excel and then we basically removed the income statement last time so we just have the balance sheet accounts now so now we have our balance sheet accounts we've put them in debit and credit format you don't really need to know debits and credits because you could just think of it as the positive numbers you know the assets being positive numbers minus the liabilities and equities two sides of the coin assets are what the company has liabilities and equity who has claimed to them that's basically kind of what debits and credits are on the balance sheet in essence so now we want to we want to take this information and start to think about how we can project where we're going to be in the next 12 months so we're going to use a method of saying let's try to compare everything to the goal of the business and the goal of the business is revenue generation and I know most people are like no I'm saving the world with my business and that's fine but if you're properly saving the world people will probably want to pay you for it you know if you're not getting paid for it you're probably not saving the world right I think Bill Gates saved the world way more when he was actually making money than when he became a tyrannical philanthropist or whatever now I'm not sure he's saving the world anymore he might be doing the opposite right I don't know but so so we're going to base it on revenue generation I'm sorry about the tangent there I don't know where that came from anyways so what I'm going to do is I'm going to I'm going to put the revenue up top from our profit and loss over here and kind of link that over to what we're doing on on our balance sheet so I'm going to add a couple rows I'm going to go from row one row two and I'm going to I'm going to insert some rows above it so I'm going to right click on those and insert some rows above it and then I'm going to remove the formatting or maybe I'll format it like the ones below it so notice this little drop down says I can clear the formatting same as below instead of above so I'm going to say same as below so now it made it the formatting below and then I could go I want to put up top I'm going to say this is the revenue that we've budgeted so let's connect our budgets together so I'm going to say this is going to be where's our revenue let's look at it over here profit and loss we had this is our our numbers for the two months here's my January revenue that we're looking at and this was our this was our two months of revenue so let's compare our I'm going to say this is going to be equal to the sum brackets and I'm going to go back over to the profit and loss the sum of these three divided by two so basically I'm just I'm just saying that's the revenue this is two months of revenue so I'm just adding that up that comes out to 69 to 11 divided by two so I'm just looking at the revenue notice I'm not looking at the expenses because the idea is that the expenses also are consumed for the goal of revenue generation that the revenue generation is kind of the objective here's right and so the asset everything that we have on the balance sheet also we're using for revenue generation the cash that we have we're not just holding on the cash just so we can swim in it like Scrooge McBuck duck no if we wanted to do that we would take it out of the company put it into our personal vault so we can swim in it or whatever so it's in here because we're going to use it to spend to make money to generate revenue the accounts receivable obviously the only reason we're have accounts receivable is because we think it's helpful to help us generate revenue the inventory is there as an investment to help us generate revenue the furniture and equipment is there to help us to generate revenue it's an investment that's going to help us do what we need to do to generate revenue on the liability side the financing that we have is there so that we can get the money so we can buy equipment to generate revenue right it's all an investment to generate revenue so the goal we kind of think is is the is the revenue generation okay so let's add a column here I'm gonna I'm gonna add a column next to column D I'll put my cursor on column D right click and insert and then I'm gonna make column C a little bit larger and what I'm gonna do is say okay well if revenue generation is the goal let's compare all of the balance sheet accounts that we have compared to the revenue generation for one month and we can use that same ratio possibly as we predict revenue to change over time to to predict what the balance sheet will look like over time so here's the idea equals the checking account 95 259 divided by the revenue 34606 enter that gives us three home tab number group adding some decimals so that means to generate one month of revenue the 34606 we had 2.75 times that amount in cash that being the 95 259 this does not necessarily mean that we need that much cash 2.75 times the revenue in cash to generate the revenue it's probably not the cash that helped us generate the revenue it's the other things we purchased with the cash such as the furniture and equipment and the inventory however you would think that if revenue were to change that cash might change in a similar proportion to the change in the revenue that's basically the assumption we're going to make now I'm gonna percentify this I'm just gonna change it to a percent format home tab numbers group and let's make it into a percent I'm gonna make that same assumption going all the way down noting that once we copy it down we'll go back in and then check if that assumption makes sense in other words if revenue goes up would accounts receivable go up you kind of think it would right because you would think we would make more sales on accounts would inventory go up well probably if we have a method of having a larger inventory on hand as revenue goes up you would think it might go up prepaid insurance probably not unless we get to a point where the liability insurance goes up due to us having more risk and whatnot so that's going to be a step up at some point so so let's I'll go through each account but let's first copy it down so if I double click on this I don't want this cell to move down that's in cell b2 I want it to stay the same so I'm going to put my cursor in b2 select f4 put in a dollar sign before the b and the two telling excel don't move that cell down when I copy it down enter I'll put my cursor back on it double click on the fill handle copying it down so now each cell each line item is taking the the amount on the balance sheet divided by the revenue from prior periods for one month of revenue and we made it into a percent so that we can see that percent if I was to sum it up here we're going to do it should come out to zero if we have it right because and I'll percentify that because because we're taking a percent this this is comes out to everything's in balance here and we compared everything to this number so you think all of our percentages would come out to zero okay so now what we're going to do is is do the same kind of revenue idea for each month so I'm going to say this equals the sum of and let's go back on over to the profit and loss the revenue for January so I'm just picking up the revenue not the expenses not the net income top line and so there we have it this one started out at the same because we said January was the same but if I copy it across I'm going to copy this across we can see that revenue is is predicted to go up December revenues at 105 66 if I go to the profit and loss and add that up 105 66 so that looks correct so then the our baseline strategy then is going to use the same ratio as revenue changes and we'll and we'll apply the same kind of concept so so if and the first month is the same so that gives us kind of a check so if to generate this revenue we had on hand that the 95 259 in cash I can then say well if revenue is still this in January times the that that ratio to 2.75 or 275 percent will give us how much cash we expect to have that's going to be the 95 259 right if revenue goes up we're going to say this is going to be then this 275 times the 38 585 enter so now we have revenue increasing now again I mean so now we have cash increasing as revenue increases so note that again this might not be the best method for cash we'll talk about that a little bit later we'll talk about each of these accounts in a little bit more detail and say where would this method be a good thing to apply and where would it not be the best thing to apply and again we have whole other courses on different budgeting strategies if you want to look at that more detail but this is a pretty quick strategy that we can apply so then if I if I look at this first one I would like to copy this both down and across and then make changes to it so if I think about that I can say okay this one is in cell e2 when I copy it to the right I want it to move to the right so I don't want to put any dollar sign before the e but I don't want it to move down so I want a dollar sign before the two that's called a mixed reference so we'll put a dollar sign before the two telling excel don't move it down allow it to move across this one is over here I want to allow it to move down so I don't want a dollar sign before the four but I don't want to see it moving across therefore I want to make the c absolute dollar sign before the c and that way I am going to delete this one for now so I can copy this down show you what I mean I could say all right that should mean that I can copy it down this way down to here and it should do this the proper calculation that we want taking that percent and then I should be able to copy this whole thing to the right through December I'm going to go too far because I can't see the top so I went too far and then I'm just going to delete from q to y right click and delete because I don't need that I went too far because I couldn't see and then and then here it's it looks like it's doing the right thing right we're taking the percent times the December revenue so that's basically our our starting point so now I can go through each of these now I'm not real sure the checking account if that's the best strategy to use for the checking account so I might use the checking account as kind of like the plug when I when I and I'll show you what that what I mean when I go through the rest of them the accounts receivable so you think yeah it's probably so let's make this one yellow let's go like let's make this one yellow so I might go back to that one that's my go back to it maybe color and then accounts receivable you're going to think yeah you know maybe that's an appropriate method for accounts receivable because if revenue goes up you would think that the accounts receivable would go up because we'd make more sales on account so revenue goes up in a similar proportion inventory I'm going to say yeah sounds kind of reasonable because you would think that we would have more inventory on hand if sales go up as a cushion to make sure we don't run out of inventory so I'll say yeah I think revenue or inventory maybe could go up in a similar these two I don't have anything in there so prepaid insurance this one no I don't think that makes sense because the insurance will not go up each time it's going to go it's going to go up at a tiered level right so I'm going to say this is going to be let's just say this is going to be the same all the way across and I think the insurance I actually had I actually had let's copy this across like this I don't think it was 11,000 a month I think we did something a little bit different because because it was 12,000 total but I'm the point here is I'm going to put it at 11,000 a month and the idea being that it shouldn't increase on a monthly basis it's probably going to increase on like a stacked level when we need more insurance kind of thing so I'll just I'm going to keep that one static as for now and then we're going to go to the furniture and fixture now we've already increased the furniture and fixture and bought more stuff so I'm going to assume that we're not going to buy any more stuff now because the store is fully populated what's the stores what's we have everything we need the furniture is there and unless you're in California and they you got the looters in there robbing everything every five seconds until you shut down and move to the somewhere like Texas or something Florida I don't know then you should already have the equipment right so that you can make revenue on so I'm going to assume that that we already bought the stuff and the equipment's going to last for a while here that's why we bought it we're invested for the long haul which isn't something I've recommended to do in certain locations these days but it is what it is you got to make the call and then we're going to say that now the the accumulated depreciation should basically change according to the amortization schedules so you could actually look at the amortization schedules but I'm going to keep it basically the same for our purposes here it's not going to change with revenue that's for sure right it's not going to because it's going to change according to the the depreciation schedules same with I'll do the same thing for these two I'm going to say I'm not going to buy any more equipment we already have the equipment and I'm not going to buy any more accumulated depreciation we have that we're good to roll my equipment's got a bunch of apparently gangster signs on it and some tagging all over it and and you know I don't know I don't know what it says or anything but it still seems my forklift can still lift stuff right now at the moment so at least we got that going for us I just got to shoe off the bum that somehow got in there and slept in it last night and then hose off the defecation and everything but any case then with this you would think the accounts payable would kind of go up in a similar proportion as revenue too because then we would probably have more stuff we're buying from vendors so you think it would kind of increase in a similar fashion the visa we can kind of assume that the the debt might go up with visa similar to revenue as we purchase things on account when we think about the the sales tax sales tax these are all sales tax accounts here and then we had an adjustment account I think the down these are all the sales tax accounts you would think they would change in proportion so I'm going to say that you would think the sales tax would go go up in proportion these are all related to the sales tax and then the interest payable so what do we want to do with the let's say the interest payable is I'll say that's going to be static because we're not going to be taking out any more loans basically because we've already financed our equipment for the most part at this point so we'll say we'll keep that the same and then this one are the loans so so I'm going to say that the loans are are going to go down as we pay them off and we could look at the amortization schedules to get them exact but they're not going to change with regards to revenue we're not going to get more loans as revenue goes up we would we would be paying off the loans possibly faster so I'm going to keep them the same so I'll keep them the same and and if we were getting detailed we would we could look at the loan schedules and see what the loans going down obviously the loans would go up if we were thinking about financing say another piece of equipment or something and whatnot and then the the taxes for payroll so the payroll taxes you would think those liabilities would would the payroll liabilities I would think they would stay somewhat the same actually I think in our other problem we had those go up but I would think they would stay the same unless we hired more people which would would happen on like a tiered basis once we get to a certain point these are all payroll taxes and this is a payroll tax adjustment once we get past a certain point then we would hire more people and then that would go up but I'm going to say for the most part the liability is going to remain the same it's not going to change with revenue until revenue gets to a certain point so payroll liability adjustment and then unearned revenue that could go up as we as we generate more revenue if we so I'll keep that the same and then the chase loan long-term portion of the loan so I'm going to say this one I'm going to say remains the same just like the other loans this is the long-term portion of the loan because we're not financing anything new and then opening balance equity the draws let's say that remains the same we might draw more out this would be equivalent or similar to dividends if it were a corporation but I'm going to say we're going to keep it the same we're going keep lean and mean on the draws even as we generate more revenue put it back in the business continue to grow man and then the owner investment we're not going to invest anymore so I'm going to keep that the same why because we've already bought the equipment and the inventory and hopefully the business is self-sustaining at this point in time so we don't need to put any more investment in and then the owner's equity so so now notice if I look at the sum of this here now if I sum this up it's not going to be in balance because I just went in here and and messed it all up right so if I if I sum this up do my debit sequel my credits we're going to say boom and they actually do the debit sequel the credits that's crazy but wait that's only for the first month because the first month is the same but if I copy this to the next month then now we're off right because this this month was just the same information all the way down and then we either kept it static or we changed it okay so for February then it's off so then no we could then say okay well where should the plug go I'm going to because when I put this back into QuickBooks the the the balance sheet should be in balance and it's out of balance by this 400312 so we could put the plug basically in equity meaning I could take say like this is the negative sum and I'm just going to make it what it needs to be to put this in balance and so wait a second we'll say this is going to be the negative sum and so that's one method we could use but also you could think well the equity should be the the the the the the prior equity plus the the net income minus any draws so I'm going to say instead let's make the cash basically the plug so I'm going to try to say okay what what should equity be well equity should be what it was in the prior month and then I'm going to add to it the the net income subtract because it's a credit so I'm going to subtract the net income so if january's net income uh well wait a sec net january's net incomes over here of january's net income was 1037 not the gross profit but the net income was that so I'm going to say that means that it should be the prior month plus that so now it should be 1037 so that and so and that throws me out of balance now so now I'm going to say let's go up top and change the the checking account I'm going to say the difference is going to go into the checking account by doing my plug formula negative sum of everything below it and so that puts me in balance so I'm going to use this as my plug right the checking account is my plug that my equity makes sense based on my net income because it's going to be the 72 220 plus the net income so I'll copy that across I'll copy my equity across which is just going to be the prior balance plus so this is the prior and now I can't do that hold on a second this one has to be the prior balance which is this number plus that so that should be that should be if I pull up my trustee calculator just to double check this hopefully I'm getting this correct where's my calculator it's not in my favorites we're going to say this is going to be 80 257 plus the February net income which is actually minus because it's a loss three two six four that gets us to the 76993 all right let's copy that across and just check it one more time before I copy it all the way across so this one should be the prior balance 76993 plus net income over here for March which is 4498 and that should be the 814 yeah so that's right so let's copy this across and so now that number I think is correct it would have to be decreased by the draws and whatnot but I'm going to leave it there for now and I can see I'm out of balance that's the problem with the balance sheet because we're not doing the double entry accounting system to we're kind of we're kind of winging it here to basically base everything in a similar ratio on revenue and we adjusted some of the accounts so it's not going to come out to be perfectly balanced I'm going to say based on our revenue this is what the owner's equity would be which would be similar to the retained earnings for a corporation I'm going to put the plug up top again in the checking account by just saying negative sum which is the plug what I need to be in balance so I'm going to say cash is going to be the difference that will basically put us in balance right so again that's just a pretty quick kind of budget way that you can get to a balance sheet basically budget and I get to a net income that ties in makes sense because it kind of ties into what my net income projections are on the income statement so that my bottom line numbers make sense meaning the bottom line of the balance sheet you'll recall is assets minus liabilities equals equity and so so the equity kind of makes sense because it's going to be based on my net income what gets confusing of course again is is basically the categories of assets and liabilities right because as as our as assets minus liabilities equals equity as the book value goes up it's not like the other side is just cash right because all these other accounts are going to be changing in some proportion as well so and so one way again one way we can look at that is try to compare it to the revenue and try to see and give some approximation of what we think are going to be the adjustments to the to the various balance sheet accounts based on that and then we we basically made retained earnings equivalent owner's equity correct according to the the the projected net income and then we use and then we basically just use cash as a plug to make the debits equal the credits right so so again the balance sheet gets a little bit more confusing because of that balancing thing because there's no double check within QuickBooks to make sure that everything is in balance right we have to kind of do it ourselves all right so that's going to be the thing so now I'm going to say let's just clean it up a bit let's put some brackets around it make it look nicer I'm going to say that let's put some brackets around the whole thing we'll say do brackets and maybe some brackets around this bit and then maybe some brackets around here and here for my worksheet and so that looks pretty good that looks pretty good okay I mean I don't want to spend too much time trying to perfect it here I just want to get something now that we can upload into uh into QuickBooks so I think this is going to suffice for now I'm not going to I'm not going to over analyze it here and then we'll use this to then import into uh uh QuickBooks here going back once again to our first tab selecting the drop down we'll go into the budgeting and we'll make another budget we have the budgeted income statement and now we can import or create another budget so that's what we'll do next time