Collaborate on Quickbooks Invoice Example for Product Quality with Ease Using airSlate SignNow

Quickbooks invoice example for Product quality

hi folks in this video we're going to be talking about how to get profitability by invoice in quickbooks online now for some context in order for you to track profitability on an invoice there needs to be costs against the sales in order to calculate profit inside the invoice and the only way for there to be a cost inside the invoice is for you to sell an inventory part so for the purposes of this video profitability per invoice it's only pertaining to the profits of an inventory part otherwise if you only sell services the invoices are quote 100 profitable there's no way to tie a service cost to an invoice you can only tie an inventory cost to an invoice that being said we also have to keep in mind that you have to be working on quickbooks online plus or quickbooks online advance in order to track inventory now in this video i'm going to show you two ways to track profitability by invoice i'm going to show you one way if you have quickbooks online plus or advanced so either way where you can view it inside the invoice by running a journal and a journal a transaction journal report and that's really sort of a manual way and then if you have quickbooks online advance which is the best edition i'm going to show you a special report a custom report you can run to do that so let's go right into quickbooks online and what i have on the screen just set up for this video i have one tab here with my profit and loss for march as you can see as you can see no sales no cost so far i have another tab where i have my products and services as you can tell i got three parts here part one two and three and they have a cost and a sales price associated with it and they have um quantity on hand and on the third tab here i have my inventory evaluation report that shows you that my inventory actually has or carries an average cost so that's going to be a setup of the most common scenario where you would watch this video and see okay that's exactly what i need you got to have those inventory parts you got to have them in stock and they have to have some sort of calculated average value for this to work otherwise where's the cost going to come from all right so that being said i'm going to go back here to our march p l there's nothing there i'm going to go ahead and create a couple of invoices so i'm going to go to new and go to invoice and by the way this will work with the sales receipt as well so it works with an invoice or with a sales receipt for now we're focused only on invoices so i'm going to pick a customer here triple a construction notice that this is long invoice number up here i could change that that's just a number that i have here but if you wanna simplify your life i'm gonna do one zero zero zero one and just use the regular uh invoice numbers that people are used to i'm also going to use a custom field which is only available in quickbooks online advanced just for the context of running the reports later on so we can see the value of having a profitability by invoice report so i'm going to select here mike and then let's say i'm going to sell a couple of products to this customer so let's say i'm going to sell 17 of part one and then i'm gonna sell let's say five of part two and that's it that's all i'm doing i'm selling this one customer a couple of items for the sake of just going a little bit further in this i'm also going to pick a location again assuming that you work with locations you don't have to do that this is just an illustration again so when we do reports it makes uh sense why this would be valuable and i'm going to go ahead and click on save and that's one invoice i'm going to save a new year and then i'll pick let's say the same customer just to kind of change things up and then a different salesperson and then we'll pick a couple of different parts here to sell so i'm going to sell one of each so let's say for this one's just going to be one of each to simplify our life here and this will still be the miami office then i'm gonna click on save and then i'll be save and new and again i'm just creating invoices so we start filling in uh quickbooks with information that way when we start looking at the report we start seeing the sales and the cost and all that stuff so let's sell here part zero one and then part zero two and let's do let's say two of this and let's make this the new york office and we'll make this mic then we'll click on save and then we'll do save a new and then let's just do one more one more sale here so we'll pick a customer here and then we have part zero three let's say we sold 25 of these and then part zero two let's say we sold one of it okay um and what's the price uh wrong item here let me pick zero two part yep anyways uh let's say it's two of them okay let's get two of them let's do two instead and then let's leave this as a new york office and then do mike as a salesperson and that's it we have a couple of invoices thrown here i'm going to click on save and then x out of that so we can go back into our report let's run the report for march again okay and then we have some sales some costs and then some profit so right now at this point i don't get a profitability by invoice i could get profitability by customer if i wanted so i could open up this report and do columns by customers and click on run report yes i could do that but really sometimes you want to do it at the invoice level and we want to do it at the invoice level it gets a little bit trickier because if you remember with triple a construction i'm going to click on that so you can see it there was two invoices right so if i want to know my profitability by invoice maybe because i also have a salesperson to pay commission to or something like that that's just a little bit of the context here i do want to see that okay so let's start with now that you understand what's going on let's start with looking at the profitability one invoice one at a time when you have quickbooks online plus or advanced again if you don't have advanced you can do the second thing that i'm going to show you after this one okay so i'm going to go into the the the invoice here i'm going to open up the underlying invoice in itself and right now the only information we have here is that the invoice amount is 18475 there happens to be some some sales tax there but that's fine we'll ignore the sales tax so we have 18 475 and we want to know how much money we're making on this invoice so the only way to do it again if you only have plus is to go into the more button down here and then click on transaction journal that's what i meant by that with the transaction journal and in the transaction journal you're going to see basically all the debits and the credits assigned to this particular transaction so this is going to calculate debits and credits to the particular transaction right now at this point it's there's a lot of numbers going back and forth it's very hard to know what the profitability is we essentially need to hide accounts receivable we need to hide inventory asset and we need to hide sales tax so in order to do that i have to export this to excel i'm going to click on this export to excel button right here click on export to excel and then i'm going to open up the excel file and drag that here so you can see it so there's my excel file and i'll just do i'll just delete all these things that are down here and now that we have sort of a flat view of this information we just want to filter out certain accounts so i'm going to go into data and then click on sort i happen to be on on a mac actually a filter sorry that sort happened to be in a mac and a pc might look a little different and after we do a filter i'm going to sort these by the accounts and then i'm just simply going to delete i don't need accounts receivable i don't need sales tax and i don't need inventory asset so we really at this point all we really want is income accounts and cost to control accounts so down here in the bottom i can simply just do my sum so grab all my debits and then grab all my credits and then i can do a quick formula credits minus debits from the context of an invoice would be profitability and my profitability at this point is um and ninety five 4595. so this is doing at the individual invoice level it does take a couple steps i hate it i know uh there's a way of doing it across all the invoices with quickbooks doing pivot tables so i'm gonna show you that after the next one that way i don't kind of lose you into excel nerdum so i'm going to close out of that now you know how to do it if you have quickbooks online plus or quickbooks online advanced per at the invoice level that's how you would do it so let's talk about the actual report that you can pull like a one-click report you can pull in quickbooks to get profitability per invoice across all your invoices so i'm going to come here into the reports menu and click on reports and we are going to be doing this in quickbooks online advanced you won't see this in quickbooks online plus or any of the lesser versions and then right here you're going to see custom report builder and we're going to start with an invoice report it's the best way easiest way to start we click on invoice report and quickbooks will build a custom report using invoices then i'm going to click on the drop down menu here and then click on custom dates and pick the date range that i want to use so let's say we want to do march 1st through march 31st because we're only looking at march at the moment and then we'll let this refresh and at the moment we're seeing the four invoices that we created if i scroll to the right all i'm gonna see is the overall amount that includes sales tax so that's no good i'm gonna collapse this left navigation bar here so we have a little bit more real estate and then here where it says customize report on the right hand on this of the screen if you don't happen to have that just click on customize you're now going to choose what detailed information you want to see off in this report so right now we have turned on invoice date due date invoice number and amount so i'm gonna give it a due date for a second because let's say i don't care about that and i'm also gonna get rid of amount that amount is the overall invoice amount i don't need that at all and then we also have open balance so i'm good with having customer name invoice number and invoice date uh let me give it a shipping date i don't need that and that's probably it i think three things it's good enough yeah this is perfect three things is good enough i could try changing the order of these things so i'm going to go to customize scroll up all the way up click on layout and then grab inverse number all the way at the beginning and then maybe i'll grab customer name and the invoice name that way we're starting to now um configure this the way we want so we have invoice number customer member state now we need the individual items inside of the invoice to be reporting here so we can figure out what our profit is so i'm going to click on customize one more time i'm going to go into columns and then we're going to scroll down to invoice line items again this works with sales receipts as well if you were to start from scratch and build it as a sales receipt and then from here what we what we are going to need is ledger amount is probably the single most important one and then the second one is we're going to pick account line so we have account line we have ledger amount and then we can do uh product and service as well that way we know the specific products that are being sold so we close that and now we have um account line product and service everything is laid out here for us now we don't want to see as we did in the other example we don't want to see accounts receivable uh inventory asset or um the sales tax line so there's a couple ways we can approach it we can go to filter and then go to filter and then we can click on um account line then go to the operation and do not equals then i can click the drop down on value and i can search each of them so i can look for accounts receivable i think it's the one that has ar in it and then we can search for what is the other one it was inventory asset so let's come up here to the search inventory asset and the other one was something with sales tax so it's florida something with sales tax for the permanent revenue okay so now we have uh the three accounts here accounts receivable um sales tax and the inventory asset and that's one way to do it see how cleanly gets rid of that and just keeps our um our income accounts and our cost to control account so that's one approach of doing it i'm gonna show you a slightly different approach i'm gonna go back into filter i'm gonna get rid of this filter now we come back at this point now notice that there's uh empty boxes uh empty uh boxes empty lines where the boxes you see empty values in the boxes where products and services are we are going to now do a different approach we'll go to filter we'll filter by product service and then we'll do it's not empty and then what this will do is it will only grab the information that's coming with inventory part so that that takes care of half the problem we still have the issue where inventory asset is being reported here at the moment so now we're going to go back into filter go to add and then on the filter at this point right then we would do account line and then where it says uh operation we can do not equals and then at this point we can do inventory and that's just because it might be easier to approach it this way again whichever approach you want to use that works just fine we're going to save this later on okay good so now up here where it says invoice report we're going to call this profitability by invoice okay so we're gonna customize the report name then we're gonna click on save that way it's now a permanent report if i screw things up i could always go back in and pull this up so now i'm trying to figure out what is my profitability by invoice we got the filters going notice that every single time something is sold something is posting into income as a negative amount that's how debits and credits work uh credits are negative and credits are sales and then uh debits are positives and debits are cos that's how it works so think of sort of the opposite of what mechanically in your head debits and credits mean or negative and positives mean so now we want to do is want to group them so we're going to click on group click on the drop down menu and then do group by inverse number wow awesome stuff i know i get excited for very simple things so now that we have that now we're grouped by invoice number here what i probably want to do is bring that ledger amount a little bit to the left so i'm going to customize go to layout and then just bring let's do ledger amount all the way to the left so it's a little bit easier to read and then we're going to scroll all the way to the right and then now what i can do and i can manipulate these columns a little bit now i can expand see as i expand them here i get to now visually i get to see uh the ledger amounts or the invoice level what i don't have here now is the subtotal so back into group here i'm going to click on edit group calculations i can click on select column select ledger amount and then calculate and that would be a total so what that would do is now that would allow me to see that profitability while these numbers are collapsed so at this point we got exactly what we wanted we have our invoices uh our invoices have multiple lines in it right the the sale and the cost side for each of the items that's why it says four because it's two items with a sale and a cost side next to it um and then the profitability for each invoice that's it that's that's what we wanted it's exactly what we wanted so i can save this report one more time if i wanted to so i have all all the things that we did to customize it i probably want to learn how to export so i can export this to excel and we can see what this looks like when we export to excel which looks like this in excel um the the grouping is doesn't follow so the excel is just going to be a flat file so if you wanted to sort of do the same thing in excel you would select all the information and then go into data and then go into insert and then click on pivot table and again we're nerding out around excel pivot tables that's not what this is meant to do but just to show you an example so we're going to bring invoice number over to rows and then let your amount here into values and that will give me essentially the same thing let me make zoom these in just a little bit easier for you to read so that will give me essentially the same set of information um that i was getting here again negative means profit i know that's a weird one but that's my profitability per invoice so in excel it's a it's a it's a pretty simple concept now earlier when we created the invoices we added some locations and we added some custom fields for salesperson this is really where the beauty comes in we can go into customize go back into columns here and then we can go to the invoice at the general invoice grouping here go to show all columns and now i can bring in for example location should be here somewhere uh where's my sales person there we go salesperson custom field and a location should be here somewhere oh there it is if you have a tough time finding it you can also type location in the search box and spell it correctly and then figure out which of these is going to have which of these fields you want to bring in naturally at one location under invoice so now that i brought those in which are going to be here on the right hand side i can now group even further so i'm going to go to customize i'm going to go to layout and then let's say i want to start with location then go to sales person then your invoice number then go to ledger amount so that's that's a possibility then i'm going to scroll to the right here and then the grouping i can do it in sub groupings so i can kill this grouping and then i can start by grouping by location then i can go to grouped by salesperson that i can do three subgroup by invoice number and then edit group calculations we're gonna do ledger amount and then calculate by total again one um what we did earlier so now it's essentially the same report but now it's i have all my miami office sales and my profitability across the board all the invoices from a miami sales and then i can see that in miami mike had four 4500 in profitability and stephen had 613 dollars in profitability i can open new york office and i can see that mike had 5500 in uh profitability and i can open them up and then see the profitability of the specific invoices even at that level and i could even flip it um even further i can go to group and i can kill this invoice number this sales person this location and now bring in here sales person i don't even have to change the order just the grouping and the second one would be let's say the invoice number and then let's say the third one is going to be location and then edit group calculations i'm still going to pick ledger amount is only going to let you pick from numerical values and do a total and add that so now we have my total profits across the board for mike and actually let me bring in i'm going to lay out and bring ledger amount next to the salesperson just to make it easier to read so i have mike with ten thousand dollars worth of profits steve with six hundred dollars worth of profits and i can open it up and see uh which invoice number happened to be the one that they got the profit for and we can take it one step further open up the invoice and then see exactly what uh location it came from so you can play with this custom reports however you see fit i think that the custom reporting tool inside quickbooks online advanced is the most advanced reporting tool in a cloud accounting software this makes the 200 price tag for quickbooks online advanced worth every penny and this is just one example of the application of this custom reporting tool now i'm going to go back and fulfill on the campaign promise that i'm going to show you how to if you have quickbooks online plus um how to do this without the custom reporting tool and to get this information across multiple invoices so first thing we'll do is we're gonna go to reports and then we're gonna go search for a transaction detail report okay so we're gonna do transaction detail report and we can do in this case let's say we can do by customer that's that's transaction detail by account that's the one we're gonna pick so transaction detail by account and then we'll do the same filtering here so we're looking at the exact same information so 0 3 31 2022 run that report and then we have basically all the information in here now what's really neat is when we go when we do this in reports mode this actually becomes a lot easier where it says group by i'm going to go none because actually i need to group it by anything would be nice if i can group by invoice number that would be incredible you can't which is why we have to do it this way so no need to group by then we're going to customize then we're going to go into filter and this is what we tell it specifically what we want so transaction type we're going to pick here invoice and sales receipts would also go it also counts so if you happen to have a mixture of invoices and sales receipts or all sales receipts this makes sense for this if you want to include your uh your refunds you would include your refund receipts and also your credit memos assuming that that's in your account these are these are the ones that you would include in the filter then under the distribution account is when i click on the drop down menu and click on where it says all income and expense accounts that gets rid of accounts receivable it gets rid of inventory asset so i click on that and then click on run report so now what we see here is essentially a flat file that contains date invoice number customer name uh in this case location part number or not another part number but the memo and the description of the part number the split which we don't need amount and balance so we can start getting rid of things that we don't need in this case i'm going gonna click on the on the show more and we definitely definitely don't need balance we're not gonna need balance i think product and service is valuable so we're gonna bring uh that in and um we don't need transaction type but actually we can leave transaction type that's that's not going to hurt us and then we click out of that oh and we said we didn't want to split that's right so i'm going to click on the gear menu and then get rid of split where are you useless split there we go click on the split just get rid of it and then click out of it and there we go so now we have date transaction type invoice number uh customer name location class which we don't have anything on there memo description amount product and service so now we can do is now we can take this report first of all i would save it and i would do i would call it invoice profitability detail so i'm saving this as a report that i can go back and pull later click on save and now i'm going to export this into excel okay because and this works in google sheets too if you have quickbooks online advanced you can export to google sheets so i'm going to close out my old excel file and bring in my new excel that i just exported which is this file that contains all the information i'm going to view and zoom this in so it's easier to read for the video and now we have all the data so just to simplify my life i'm gonna grab the first four columns delete that i'm gonna take the first that was the first four rows sorry then the first column delete that and usually i delete the last four rows as well that way we have a totally flat information then i select all this information here i'm going to go into insert pivot table depending on the version of excel it could be on the data pivot table i'm going to go to pivot table and click ok and then this is what we can do essentially the same thing let me have this in 200 zoom same thing we were seeing before on the previous example we can go here to location then we can go to let's say invoice number then we can go to customer name and then we can go to amount in this case we bring them out here over to values okay and also we wanted uh profitability so let me just go back here and check for a second um all these amounts are containing sales and costs unfortunately there's no context here for sales and cost say so there's actually a mistake in this process and i'm kind of glad we did it because i don't i don't have the cost component in here this is my error 100 right um so i'm gonna close out of that and we're not gonna scratch everything there's just one little thing i missed and again i'm not gonna edit this i'll keep this piece in so i'm gonna go to uh the gear menu and what i need to do is instead of having a mount i need to have debit and credit so that was my mistake that i was going with just raw amount uh the regular reporting system doesn't have this ledger amount that does the positive and negatives which is what causes this confusion so i'm gonna do debit and credit excited that so we can allow the system to report uh the sales on one side and the cost on the other side that's what we had essentially uh missed so that was the only thing that was missed so i'm gonna save customization save that report over okay so no no i'm done go back and export this to excel again open up the excel file i'm gonna bring it here over from my other screen go to view and do a 200 percent maximize this here as we mentioned earlier first four rows delete those bad boys first column delete that and the last four delete that there we go so i'm gonna add another column here called uh profit and then what i'll do is i'm gonna convert these debits and credits into negatives and positives because i don't have a way of doing that in in quickbooks from scratch so i'm gonna do equals and i'll i'll flip the positives and negatives now so it's just easier so i'm going to do the credits as a as a positive number and then the debits as a negative number in this specific case i'm flipping them and i'll keep that formula and then i scroll this down that way there's negatives and positives across the board so we have essentially the cost and the sales price here the sales price is positive cost is negative again we're flipping the formula compared to the custom reports but we couldn't do that okay so now that now that we have that and i'll select the entire data set i'm going to insert pivot table go to pivot table click ok and then go into view let's do 200 percent and now we can group this however we want to so we can start with location first and then we can move it to customer name and then we can move it to grab invoice number then find profit and then bring that down into value so essentially we're gonna have location name and number on the rows and uh profit under values that's going to give me my profitability across the board so now i can see okay my profitability for the miami office versus new york office very very clearly stated here i can expand new york office and then i can see my profitability for each customer or each invoice if i don't want to see customer i just click and drag customer back out there and now i can i can see it only at the invoice level anyway hope you enjoyed it and i'll see you on the next one