 QuickBooks Online 2023 Progress Invoicing Example 2 Excel Worksheets Setup Get ready to earn the skills needed to boost your bank books on up with QuickBooks Online 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 then 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 our QuickBooks Online test company file we started up in a prior presentation note that we're in the accounting view as opposed to the business view you can toggle between the two views by going to the cog up top switch the view down below let's open up a few tabs duplicating some tabs to put reports in right click the tab up top to duplicate it right click the duplicate a tab to duplicate again back to the tab to the middle reports on the left hand side we're looking for the balance sheet the balance sheet report and i'm going to date range that from 0101 and let's say uh 25 to 0632 5 all we have in there thus far is going to be the amount that we put the deposit into the checking account the other side in the billings there's nothing on the profit and loss but let's verify that by going to the reports the profit and loss closing up the boogie and changing the range 010125 to 0632 5 and run it nothing nothing there thus far all right let's go back to the first tab now we're going to be i'm going to put this in an excel worksheet as well because i think sometimes to see the transparent format of excel is useful so i'm going to build an excel worksheet and if you don't want to build an excel worksheet you don't have to uh we can put it into quickbooks but i just want to show you what i'm doing from the ground up so that if you if you wanted to follow along with it uh you can so what i'm going to do is i'm going to create like a journal entry which is more transparent way to see something than the database database program in quickbooks and then i'll make a little trial balance which will allow us to see what's going on uh on one page so what i'm going to do is i'm going to make a skinny column over here i'm going to make it the same it's the same skininess by selecting column c home tab format painter going to make a skinny column over here i'm going to put our transactions in a journal entry format here so i'm going to say the date will be in this column account that will be affected and then i'm going to say debit debit i'm going to put debit and then credit on the second line credit and the credits i'm going to put in as negative numbers if you're not familiar with debits and credits that's okay because the concept will still be there and we can introduce some debits and credits i'm going to pull this down i'm going to cut it i'm going to cut and then put it right here so it lines up to the bottom i'm going to make this a header format for me and that usually means i'm going to select the whole thing go to the first tab font i'm going to make it letters white and the background black and then typically i'll center it and this second one i'm going to make my credits be negative because negative numbers are going to be red i'm going to make it red because negative numbers are going to be red so the credits will be red and bracketed so i'm going to put the credits in the the debits and the credits in the same column and represent that they're going to be credits i'm going to make this wider by the credits being negative numbers so that might be new to some people sometimes that bothers people but it's actually the most efficient way to put it into like an excel sheet which is quite useful so then i'm going to say uh the next let's put let's build our trial balance let's make another skinny so i'm going to take this skinny and make a skinny j and i'm going to put the accounts let's do the same the same format i'm going to say accounts and then i'll say the entries entries and then i'm going to say i'm sorry this is going to be the accounts these are going to be the beginning trial balance and then these are going to be the entries or let's say journal journal entries and then the ending trial balance i'm abbreviating balance something like that and then i'm going to center these again and make them my headers black and white center them and then this one i'm going to make black and white and i won't center it i'll keep that as it sometimes i don't center this one too i'll make that uncentered and so there we so there we have that and then the accounts i'm just going to put the normal accounts that are going to be affected so we have the checking account checking that we're going to deal with accounts accounts receivable we're going to be dealing with a work in progress account we're going to have a billings account we're going to have a retained earnings or equity account let's just call it equity account retained earnings something like that and then depends if you're sole proprietorship partnership corporation but revenue or income cost of goods let's say goods sold and let's say equity account let's be kind of consistent here with my capitalization and cost of goods sold and then i'm going to have the total total debits and say credits bracketed the credits making it red again to show that the credits are going to be negative and there we have it so the beginning balances are just going to be zeros because we're just going to start from a blank slate so there's nothing in our worksheet yet so i'll just sum it up probably don't even need this column because of that but i think it's still useful to have this format and then the middle column that's going to be our journal entries i'm going to make that blue that's what i usually do when we're doing the data inputs i'm going to right click here i'm going to go to the the bucket and i'm going to call that blue and then i need to put some definite definition so i like to go to the format and then add the borders with the all borders and then the ending balance is going to be the beginning balance plus the journal entries which i can sum up equals the sum of these two you'll see how this works once we post those first two journal entries we already did so then i'm going to copy that down i'm going to put my cursor on this fill handle right here drag it on down and that just does the same formula all the way down and then i want to sum it up at the bottom so i'm going to grab this formula grab the fill handle drag it to the right which will sum up each column sum it up each column and then i'm going to underline here so i'm going to go to the home tab font group underline there we have it and then i'm going to have a net income calculation at the bottom net income i like to differentiate the income statement from the balance sheet components which is right there there's the defining account in the middle of our trial balance i'm going to go to the home tab font group and make that dark blue and uh and white just to show that's the middle point this is the income statement this is our balance sheet accounts and then i'll sum up the income statement which will be equals the sum of the revenue minus the expenses the expenses the revenue will be credits and the expenses will be uncredited so it'll subtract them by formula so i'll say there it is and i'll copy that across now this might look quite abstract but let's go ahead and i'll make this whole thing font group brackets so there's our structure and then i'll make some blue data input fields over here which i'm going to right click and say this will be blue and bordered and then our date field on this side we need to format the date so i'm going to right click format the cells and we'll say i need to make it like some kind of date field i don't want the year really so i just need the month and the days i'll pick this one and okay so now our first transaction is on one one so remember what we did in quickbooks is we we entered an estimate so there is no transaction no journal entry for the estimate because there's no effect on the financial statements so then the first transaction we did after that was we sent out an invoice for the deposit but we told the invoice to to record it a little bit to the to the billing's account so let me just check that out one more time and we'll record that first transaction just so you can see what i'm talking about so we're going to say we had an invoice and invoices increase accounts receivable but then we and then the other side usually goes to revenue driven by the item but then we've reduced the revenue and instead put it into the billing's account therefore we had an increase in accounts receivable and the other side going to the billing's account so let's do that i'm going to say all right we know i'm going to i'm going to do this by going equals accounts receivable is going to be the debit account and then the other side is going to be equals the billing's account and the amount is going to be i'm going to pull it from my data on the left it's going to be equal to that ten thousand dollars the the billing's is going to be negative because that's how we're going to reflect our credits so negative of the number of above it is the billing's account ten thousand if i record that to my trial balance i can go into my my accounts receivable in m four say equals and go over to my accounts receivable ten thousand and then in i four or i can go to m six and say equals and go over to my billings so there's the first transaction we put in place and it sums back up to zero right before we recorded the second one it was out of balance now it sums back up to zero and then if i record the second thing that happened on 115 we received a payment so if i go back on over and center at the next thing that happened uh is that is that we got a payment here so the payment's going to reduce the the accounts receivable and go into the checking account so i'm going to say equals the checking account goes up with a debit accounts receivable goes down and it's also going to be for the ten thousand so i'm just going to say equals the ten thousand from the journal entry above negative of the one above it is going to be that and then i'm going to go checking account in m three it's going to be equal to we'll pick up the ten thousand and then the accounts receivable is going to go down there's something in accounts receivable already so i'm going to double click on it go to the end of it and say plus this number which is negative because it's a credit from a from a plus and minus standpoint it's going to bring this one down to zero right debits and credit plus and minus brings it back down to zero so there we have it now i'm going to add one more little touch to this i'm going to i'm going to this is our check number down here that we are in balance so what i'd like to do is say if this is out of balance i want to make it red and if it's in balance i want to make it green so i'm going to do a conditional formatting by going to the home tab style conditional formatting and i'd like the rules and say if it's greater than greater than i'm going to see that there's i'm going to account for the possibility for there be rounding error of let's say two dollars rounding error so if it's off by two dollars high i would like you to turn it red so i'm going to say okay so if i delete this for example it turns red because it's off it's high by ten thousand if it's low by ten thousand i'm going to select these i want you to turn it red conditional formatting highlight if it's less than negative two turn it red so in other words if i delete this one now negative ten thousand turns red positive negative turns red if it's between negative two and two we want it to be green so i'm going to go home tab style conditional formatting between negative two and two if it's within that range because it should be zero but if it's in the range of negative two to two i want you to be green so that we can account for give it a little room for rounding all right and so now it's green so if it goes negative we're out of balance turns red if it's positive out of balance if it's zero or within the range of one to two you know negative two then we're still good because hopefully that's just rounding and we're still okay all right so there's our there's our general worksheet again if you don't want to put together the worksheet that's okay because we can but i think it's a useful tool to follow along with the worksheet so that we can see the journal entries and the impact on just like a trial balance type of format and then we can take that and add the added layers of quickbooks which adds in like reporting the the financial statements by date and reporting the financial statements by class you know and by project and and that kind of stuff that adds like a dimension instead of a two-dimensional thing that we're looking at here that kind of adds like another kind of three-dimensional kind of component that we can manipulate the data in that way let's spell check it over here spell check all right that's our process so going forward we will uh we'll continue recording what we'll start to think about what actually happens with the costs and then we'll continue our billing process and think about the revenue recognition kind of issues as we think about our billing