 QuickBooks Desktop 2023 Budgeted Balance Sheet Export to Excel Part Number 2. Let's do it within 2-its QuickBooks Desktop 2023. 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. Here we are in QuickBooks Desktop get great guitars practice file we started up in a prior presentation going through the setup process we do every time maximize the home page to the great area in the view drop down. We've got the hide icon bar open windows list checked off open windows open on the left reports drop down company and financial profit and loss the income statement change in the range from 010123 to 022823. Customize the report so we can fonts and numbers change to 14 okay yes and okay reports drop down again company and financial again but this time the balance sheet customize the balance sheet. We're going to change the range Moe 1 0123 to 022823 and then go to the fonts change them up to as has been our custom 14 oh yeah okay that's the setup process we've been doing every time we're working on our budgeting information remembering that from the accounting side of things the budget isn't something that we just do automatically kind of by ourselves because what we do is build the financial statements based on past information and we're going to need added kind of assistance to make the budget. Into the future from the management decisions and all that kind of stuff although accountants will typically be integral in the process because we're the ones that know how to actually format financial statements and so on and so forth. So last time the major budget in prior presentations is the P and L measuring performance now we're focusing in on the balance sheet which is going to try to estimate where we will stand at some future date in time. So it's a less common kind of budget there's a couple different ways we can get to there but we exported we're going to start from the same process as we did with the income statement we exported from. QuickBooks a trial balance that we constructed a budget from that doesn't have all the subtotals in it and then we're going to format it in the balance sheet helping out with our income state information and making some assumptions. And then we will import it back into QuickBooks here as we did with the income statement so that we can run reports such as. The budget reports budget overview budget versus actual which is what QuickBooks is good at doing. So last time we left off on the balance sheet here I'm going to make my screen smaller in the settings I'm going to bring it back to 100%. And then I'm zoomed in here a bit we've we've got our trial balance just for the balance sheet we are imagining this is the beginning balance. And our goal then is to see where we're going to stand as of the end of each month. So remember that the income statement we did last time if I go to the income statement that's the performance statement. This is this is how you know how far we went in terms of net income in terms of revenue. And then over here the balance sheet this is where we're going to stand after we have gone that far. So one way you can think about this and say OK well if this is the balance sheet where I stood. And then the income statement net income is you would think going to roll into basically equity. However the rest of the balance sheet gets a little bit kind of messy because what about what's going to happen to cash. And accounts receivable and inventory and so on you know as we go forward. So there's a bunch of different ways we can think about the budgeting process going forward. We have courses on finance courses and courses on managerial accounting that gets into this in a little bit more detail. But we'll just do like a basic strategy just so we can get something here we won't spend a whole lot of time on them on those concepts. But I want to get to a budget that we can then import into QuickBooks so you could show how that works. So one thing we can one way we can visualize this is to say hey look the assets that we have are here in order to generate revenue. That's why we have assets. So we can try to give a comparison of revenue generation our goal from a business standpoint to you know the stuff that we have and the financing that we have in order to generate it. So I'm what I'm going to do is I'm going to add a couple rows here. So I'm going to I'm going to insert a couple cells above this. I'm going to select like these four cells. I'm going to right click on them and insert and shift these cells down. And then I'm going to call this revenue. So this is going to be my income statement revenue. I'm not going to use net income. I'm going to use the revenue that kind of objective and I'm going to get that from the income statement over here. Now this revenue is actually two months of revenue. The base with the way we did this and all these are going to be one month. So I'm just looking at the top three which comes out to the 69 to 11. And then I'm going to divide it by two. I will do that with a formula in the balance sheet by saying this equals the sum back to the income statement of these three closing up the brackets divided by two. I'll double click and show you this formula so you can see it more clearly. Hold on a sec. I didn't put the divide there divided by two. So there it is. There's the formula. And so that's the 34606 which is just these three numbers the 69 to 11 divided by two. I'm going to do the same thing for the income lines as well. So January is going to be equals the sum of I'm going to go back to the income statement. These three I don't have to divide them by two because that's one month at a time. I'm going to copy that all the way to December and the relative reference should be applied. So I'm just going to auto fill that all the way out through the end through December. So there we have that. I think I haven't added column. I'm going to delete column. Well let's keep that column there. I'll keep that there. And then what I'm going to do is say let's compare each line item on the balance sheet to its goal that the reason we have these items that's revenue generation. So we'll do a ratio in order to do that. So this checking account for example 95 to 59 divided by the 34606 one month of revenue the ratio is percent. I'm going to make that a percent to 75 percent for that one month of revenue. I'm going to do that same kind of ratio all the way down and then try to use that ratio to then project where our balance sheet accounts will be in the future based on it. So if I double click on this when I copy it down I don't want cell B2 to move down. I do want cell B4 to move down. So I'm going to put myself my cursor in B2 select F4 putting a dollar sign before the B and the two dollar signs having nothing to do with currency just telling Excel don't copy this cell down or don't move it when I copy it down. So I'm going to say enter put my cursor on this again. Let's just double click on the fail handle button and then it copies it down. If I double click it looks like it's doing what we want. We're taking each of these numbers divided by the revenue. If I sum this up at the bottom it should add up to zero because we're we're taking a percent of the revenue number every time. So there we go. Okay. So now I'm going to make column D a little bit smaller and I'm just going to use that ratio to then calculate over here. So in terms of what my income now this one has the same income in January because we kept it the same. So we'll end up with the same numbers just so we could see what that looks like. So all I'm going to do is I'm going to take this number my revenue projected in January. I'm going to select F4 on the keyboard because when I copy it down I don't want that to move down and then I'm going to multiply that times this percent which will give me that same 95 to 59 enter. There it is. I'm going to double click on that fill handle pulling it down and we should get the same. It doesn't pull down because I got to drag it down because it's not right next to the other one but I'll drag it down. Okay. If you're going to make me drag it down and then I'll sum it up and we should be in balance again because we just we've got we just generated the same numbers because we based it on the same revenue. Now it would be different if we do it for February. So I'm going to say February equals this number which I'm going to say F4 on times this number. So now I have an increase in the revenue for February. So we have an increased amount here relative to the prior revenue account. Right. And then I'm going to double click on the fill handle and copy that down and then we'll copy this over. And so we're still at zero at this point in time because we use that same ratio for every account which might not you know that might not apply to every account we might have to in practice we might have to go through there and look at each account a little bit in more detail. But I mean the idea would be well if we had more revenue you would think that the accounts receivable would go up a little bit as well right because some of the sales would be sales would be made on account. We probably would have more inventory basically on hand holding on to more inventory in relation to increase in the sales prepaid insurance insurance is one of those items where you think maybe that would be the same it probably wouldn't go up just with revenue there probably be a step at some point where we need to pay for more liability insurance the furniture and fixture again you would think that it would go up as revenue goes up although it might go up more in like a step pattern because the reason you buy furniture and fixture is in order to generate revenue machinery the payables again you would think that the liabilities to finance the assets and whatnot would go up in relation to an increase in revenue because our business would be going up we'd probably have more payables same with a credit card interest the interest might not change in relation but again it would probably change if we needed to finance some more so these two might not go up like in the same ratio the payroll liabilities would probably have a similar kind of relationship because you would think that maybe the payroll would increase but again it might be in more of a stepped motion as well as revenue increases so in any case so let's actually adjust a couple of them just to make things a little bit more complicated so I'm going to say prepaid insurance I'm going to imagine that doesn't change right that's going to stay the same furniture and equipment I financed it we don't need any more furniture and equipment let's say that stays stays the same the depreciation let's say that stays the same and then the machinery that stays the same and accumulated depreciation stays the same accounts payable we'll we'll let that ride and then the loans let's let's imagine the interest stays the same that one might actually go down over time we're going to say that the loan will keep that the same because we're not going to finance it anymore and then then we also got the owner investment so let's keep that the same I should probably well we'll keep that there and then the draws we'll keep that the same and then the owner's equity I'm also going to keep this one the same on the loan so that looks good now the main thing I just want to point out there is that then of course we're not in balance because now I've adjusted everything so I'm going to assume the plug is going to I'm just going to put the plug into equity and you kind of have to do this in QuickBooks as well because if you if you make a balance sheet in QuickBooks there's nothing forcing you for the budget to be in balance right so you'll end up with a budget that's out of balance unless you unless you account for it whereas when you do the data input in QuickBooks for the normal budget it forces you to be in balance so I'm going to make this the plug I'm going to make it the plug by saying negative sum negative sum of everything above it not the revenue but on the balance sheet and that so that's going to be our plug so that's the general idea so then I can copy that same kind of these same assumptions to the right right so this one I'm going to say that if I copy that to the right then I want this cell to move to the right but I do not want this cell to move to the right so I'm going to actually adjust my my absolute references and I could have done this a little bit faster by adjusting the mixed references before I went in and kind of adjusted everything down here but now when I copy it to the right I'm going to do it this way I'm going to say f4 f4 f4 I don't want f2 to be absolute I want that one to move to the right now I want the c4 to be the one that's going to be absolute and again I can use mixed reference instead of absolute references which would be more efficient way to do this but I'm just going to do it line by line here so I'm going to copy this one across so there it is and then I'm going to do the same thing here so this one I'm going to say let's say f4 f4 f4 and c2 I'm going to say f4 and I'm going to copy that to the right boom and then this one we had the same thing so I'll say f4 f4 f4 and then this is going to be f4 and then I'll copy that let's do this one is different so I'll copy this one across like this and then this one is just equaling the prior cell so I'm going to keep that the same I can copy that across to December and then these two are just zero so no nothing to do there this one is the same these this is the same as the prior cell all the way down to here so I'm going to say these three or four I'm going to copy them across and they should just be the same all the way across is what I've put in those and then the accounts payable if I double click on it it's going up so I'm going to do the same thing f4 f4 f4 and then f4 here this one I'm going to say f4 f4 f4 here and then copy those two across auto fill them and then this one is the same this one's the same same so these three I'm going to copy them across because they're all the same and then this one I had going up so I'm going to do the f4 thing f4 f4 f4 and f4 this one I have to do that too so I'm going to say f4 f4 f4 and f4 this one I had changed so I say f4 f4 f4 and f4 this one I'm going to do the same f4 f4 f4 and f4 and so those four I'm going to select and copy them across and then we've got the chase which is the same so I'm just going to this one's the same this one it's a zero balance so nothing so so I'll just copy this one across which is the same all the way across I'll skip the zero this one's the same all the way across and then this is our plug number this one's the same all the way across and then this one's our plug number and our sum to double check so those are good I'll copy them all the way across and there's our general our general data thus far so I know we did that you know quickly I think there's you know we could have done that a little bit more efficiently using mixed references but this isn't an excel course our our goal is just to get an idea you know how the balance sheet how that can be a you know how you might approach that now we're going to use this information to then populate it back into quickbooks which is really what we do on kind of our bookkeeping side of things at this point again if you want to get into more detail on budgeting in general we've got finance courses and managerial accounting courses that kind of dive into that stuff in more detail it'll it'll be more complicated and less complicated if you have inventory for example and if you make inventory if you have a job cost system a process cost system and so on and so forth but this is where we stand for now let's put let's make a little bit more fancy let's put some borders around these I'll put some borders around this one and go boom and then I'll put some borders around all of this maybe so borders around that stuff boom and so that looks good so that's where we stand as of this point in time I'll make it a little smaller so you can see everything maybe you know basically at one time here and so that's where we stand okay so next time we're going to enter at least a couple months into the system and and we want to enter at least January and February and possibly like March because that will allow us to run the reports that will be the budget versus actual information into the system so that's what we will do next time