QuickBooks Online 2021 add inventory items and the related opening balances for inventory. Let’s get into it within two its QuickBooks Online 2021. Here we are in our great guitars practice file, we’re now taking a look at inventory items to do. So let’s go to the sales tab on the left hand side, we’re going to go to the product and services up top. In prior presentations, we entered the service items here.
Now we’re thinking about those items that will involve inventory, we will be tracking the inventory within the system, meaning We’ll track not only the dollar amount of it, but also the units of inventory that will be involved, we’re going to be using then the perpetual type of inventory system. Now to do that, notice also that we have this beginning balance in here. If we jump back on over to our trial balance, if we’re entering a beginning balance into our system, and using a perpetual inventory system, such as we are here, and we have this beginning balance,
I need to support that beginning balance with with the items that will be involved here. So as I enter the items, we will also be entering the beginning balances for these items. Now if you don’t have a beginning balance, then you don’t have to worry about this as you set up the items. Or if you’re using a periodic inventory system, not tracking the inventory by units within the system, then you could just enter this beginning balance with a journal entry or something like that, you don’t have to enter actual items to get to it.
But if we are tracking the items, then we need to enter the items related to that beginning balance. Okay, so let’s go back on over. And we could do this, if we were going to do this one at a time, we could hit the New button here, and we would be entering the inventory items. And we would have to enter these one at a time. If we want to enter them a few at a time or a lot at a time, then we can use the import feature with either an Excel worksheet or something similar to it like a CSV type of worksheet, similar fashion as we saw last time, so I won’t go into it in detail.
But we’ll just look at the practice one here. So we have the import. If I hit the drop down and go to import, then I’ve got my sample file. So I’m going to try to have our information lineup to this sample file. I’m going to open up this sample file, I’m going to save it and then I’m going to see whatever documentation we have for our inventory items that it lines up to this information in the sample file or at least has the same headers for it so that when I import it into the QuickBooks system, it will line up as easily as possible.
So I’ll actually save this item as is. Even if you don’t have the inventory items laid out yet, it might be easier to do the data input just basically in excel in this format, and then upload it. So what I’m going to do is save this one go to File up top, we’re going to say Save As I’m going to browse to see where do we want to put it, I’m going to put it up top and the Browse. And I’m going to call it then I’m going to call it inventory items. This time, I’m just going to call it inventory items.
So let’s say inventory items, and then I’m going to change it it’s using like the old version here of Excel, I’m going to change it to the new version. So I’m just going to make it an XLS x file, like that, and then save it. So there we have it. Now I’m going to delete the data that’s currently in it. So we can put our data into just like we did before. And now we’re going to populate just some more of the of the cells, I’m going to remove this column nine or row nine, because it’s yellow, and I don’t want it to be yellow.
And then I’m going to zoom in a little bit, we’re at the 145 percentage here. Now, whatever Excel worksheet you have, what you want to do is basically line up the headers here, because that’ll make the mapping as easy as possible when you import it or upload it to the QuickBooks system. Also note that if I use any kind of, you know, references or like formulas, when I do the data input, then I’m going to I’m going to show you how to kind of get rid of those in Excel or you can save it as a CSV file to kind of get rid of those.
Okay, so we’re gonna say that we have the product or service name I’m going to give now I’m actually going to give the abbreviated name here, and then I’m going to give the full description here. So I’m going to give it an abbreviated name. And this would be like the item name and abbreviation of an E LP in our case that’s going to be standing for an Epiphone Les Paul. So if I spell this wrong, I apologize Epiphone, Les Paul, so I’m going to say that’s the sales description.
The sales description is what’s going to be shown on the invoice when we create an invoice for an E LP type of item. And it’s going to be on the the, the, the invoice or the sales receipt. Then we’ll also put that same name over here on the purchase description that will be used when we populate it to say a purchase order or a bill or a check or an expense form. When we buy the inventory.
The SKU is going to be the stock keeping unit. I’m not going to put one here, so I’m just going to minimize that or you can actually remove this altogether. We don’t need all the columns if we’re not going to be putting populating them. So I’m going to remove that. And then the type we’re going to have, this is going to be service versus inventory. So I’m gonna say inventory is going to be our type, and then the sales price is going to be 500. That’s what we sell them for the account that we’re going to hit for the sales items.
When we sell inventory. Let’s go back to QuickBooks to just check this out. Again, just to make sure we’re picking up the right item, I’m going to duplicate this tab, right click Duplicate the tab, I want to take a look at then the chart of accounts to see what the income account is, or would be that would be appropriate to hit and add one if we don’t have one. So I’m going to close this back this one out here, I’m going to go down to our accounting down below, take a look at our chart of accounts. So Chart of Accounts, we want to take a look at the inventory items down below.
Notice we had one last time we set it up for our service items. And then we want one for the inventory, which is called sales of product income. That’s very descriptive, I might just call it sales, you know, to abbreviate it for the hit for the for the income line item and versus service for the for the items that are inventory and non inventory. But sale of product income is very descriptive. So let’s let’s keep that sale of product income. All right, and then we’re going to say that the purchase cost is going to be we’re going to buy them for 400.
So we buy them for 400, we sell them for 500. That’s going to be what we do here. Now the expense account is going to be cost of goods sold. That’s the expense account that will be hit when we create an invoice when we create an invoice or sales receipt, because that’s going to be we’re going to reduce the inventory at that point. And then record the expense to cost of goods sold the income then go into the income account of the sales of product income, the amount on hand, now we’re gonna
This is where we’re going to tell the system how much do we have on hand, which is going to create at this point in time, that beginning balance in the in the inventory account on the balance sheet. So the reorder point I’ll put, you know, at zero, let’s just say the inventory asset account. So the inventory asset account, let’s go back on over to QuickBooks, it should just be called inventory, I would assume. But let’s go back up top and see if we have an inventory account we do.
It’s called inventory, we have two of them for some reason, we have inventory and inventory assets, gonna delete one of those, or make it inactive. So I’m going to I’m going to make it inactive. And let’s just deal with the inventory one, so I don’t get confused. So I just want one inventory account right now. So we’ll just call it in, then Tori. And then the quantity on hand as of this point of time is going to be one. And then let’s add one more, because I don’t think we have the as of date as of date, meaning I want the date as of when we’re going to be putting that one unit on hand.
And I’m going to put it on hand as of 1231 to zero 1231 to zero, because we’re gonna start our bookkeeping in January 1 2021. So I want to make sure it’s before the current period that we’re going to be doing our bookkeeping, that we put this on book on the books as of So then I’m going to go back on over I’m going to keep on doing this, the next one is going to be abbreviated as an EP SP, that’s gonna stand for an EP EP, phone, stand or Pro, and then I’m gonna I’m gonna copy that Epiphone standard CRO Ctrl. C,
I’m going to paste it over here in this description, it’s going to be an inventory item. So I’m going to copy that and paste it, I’m going to say the cost is going to be 480. For this one is the costs, the income account is going to be the same. And then the, I’m sorry, the sales price is not 480, we’re selling them for 600. We buy them for 480. And then the cost of goods sold is going to be there.
And then we’re going to say that we have one of these on hand reorder reorder point we’ll say is zero. inventory account is the same, and we have one of them on hand and as of date is the same. I didn’t know that I did that kind of quickly, but I just want to get these got a few of them here and it’ll be the same kind of routine. So then we have an EPR. This is an F phone, Riviera rivey era. And then I’m going to copy that.
We’re going to put it in the purchase description as well. inventory is going to be same same, we sell these ones for 550. And income account is the same and then we buy them for we buy these for for 40. Cost of Goods Sold account same. We have one of these on hand as well apparently reorder point at zero inventory. counts the same. And we have the quantity as of date on sorry, here, this is the as of date, I put it there twice. So let’s copy these and put them right there.
They had it right. And I’m going to delete this column. Okay, so let’s go back on over, let’s keep on going here, the next one’s going to be an E p s h, and this is going to be an Epiphone the phone, semi hollow body, we’re going to copy that, paste it here, and the description inventories the same, so bring that down, and the semi hollow bodies $400. And we have the same account here for the income account.
And then we buy those semi hollow bodies for 320 cost of goods sold, ring that down, and apparently, we have two, we’ve got two semi hollow bodies, we’re gonna say reorder point still at zero inventory account, and then we’ll copy down the as of date to all of them. Okay, so then we also have a GI USA, which we’re going to say stands for a Gibson, USA, it’s going to be an inventory, same, we those are our we sell them for 380. And the Gibson USA, is here, the income accounts going to be the same. And we purchased those Gibson’s for 304. Cost of Goods Sold here same, and we have three of them on hand apparently, and zero for the reorder point, inventory account remains the same.
And then we have the same date as the as of which is the as of date, and then we have the, I’m going to say d u k, which is the DI ma on the head, you go lately, we also have a ukulele. That’s nice. So ukulele Diamond Head ukulele inventory account, and we sell those for $32 $32. That’s just to get people in the store, and then we sell them, then we once they’re inside, we sell them that Epiphone standard pro or whatever.
Anyways, this is going to be the purchase, we purchased them for 20 for cost of goods sold, here, and we have one of those on hand, one of those on hand reorder point, zero inventory accounts the same, and the as of date is also the same, okay, so like I say, if I, if I misspelled any of the names of these things, or anything improperly, you know, bear with us, we’re gonna have to deal with it for the whole practice problem, too. So, but whatever it is what it is. And now Now, you might want to then save this. And of course, then you could like copy the whole thing,
I might copy the whole thing here, and then paste it 123, so that I can kind of make sure that I don’t have any formulas in there. And if I do that, so that looks good. It didn’t mess anything up. And then you also might, I’m going to save that want to save it if there’s any problems as a as a CSV file. So if I save it, then and I browse, and I say I save it, then as a CSV type of a file, like here, or like here, and let’s go ahead and do that, we’re going to go ahead and save it, I’m going to save it as a CSV type of files, we’ll say CSV, I’m going to pick this one.
And then just to see what that looks like. So now it’s a CSV file, I’m going to say save it. And this is common for some database programs that you have to import, you know, you might have to open it as a CSV. Now, if I go and find it, there it is, with the extension of a CSV. If I open it, I’m, I’m still pretty much opening it in Excel, but it kind of stripped away some of the formatting.
So let’s go ahead and try that one. Let’s try this format. Notice it’s got some some items here that look funny because it’s squished up the cell. So all the cells are the same size, but it’s got the same data in it. And then it’s stripped away all the other info. So it should be an easy one to then upload.
So let’s go ahead and I’m going to copy the location, and see if we can upload that. So I’m going to go back to QuickBooks, I’m going to go back to the first tab. This is where we have the upload. So I’m going to close it back out just so we can see it sales products and services, we’re going to go to the new drop down and up and upload. Then I’m going to be on the left side,
I’m going to find where we want to find that file. And it’s going to be a CSV file. So this is the one that’s a CSV this time I’m going to pick that one up. And then let’s go ahead and say next. Now I’ll provide the CSV file for you as well as you If you don’t want to do all the data input in Excel, so I’ll provide this file. But in any case, now it lines them up. So we got the product name, so that lines up to the proper column. So we have the column over here, let’s open this up again. inventory, this is the CSV file that I’m opening up. And that’s why it’s important to have the headers, correct.
So that header right there, that’s the same header, and we’re just lining up the headers. And so this is what is in QuickBooks, this is what we have is we aligned them up perfectly. There’ll be okay, this one we didn’t have any, that’s okay, we didn’t, we don’t have any column for that, because we don’t need one. So it’s not going to mess anything up. The type, we have this, that column lines up perfectly, of course, because we use their worksheet to do it sales description, the sales price.
Now, if anything, these didn’t line up if I just called it rate or something like that. And QuickBooks says, Hey, I don’t know where to put it, then we’ll just like, that’s fine. Just put it there. And the sales price slash rate, it’s the same thing. We just named our header differently than you have it, that’s fine. So but we lined them up perfectly here. So that they are no problems as we pull them in no problems what so ever. So now we’re going to say next, and it should go ahead and import.
So now we’re on the Import side. So notice the added checkmarks we have over here, it’s gonna we sell them, we also buy them and we track them, meaning we’re going to track the actual units of the inventory here, we’ve got the name, we didn’t put anything on the SKU, we have the type, pulling in the sales description, pulling in the sales price pulled in the income account, if for some reason has given us this, this item here, if I just hit the drop down, the one I want is this sale of product income.
So maybe I didn’t type it in completely perfectly. So maybe I have to go in and just say Oh, sale of product income. If you gonna force me to go in and kind of do this, that’s fine. I’ll do it. And so we’ll do that. And then the costing and then we got the purchase. And the same thing happened for the cost of goods sold. So we’re gonna say yes, the cost of goods sold is the one we want. Obviously, not sure why you’re having a problem with that. But we’ll just go in and unratified them. And then we’ve got the quantity we got the reorder point the inventory account, then as of
And remember, you have to have it in there, we want it in there as of the last day of the prior period. So that when we rolled into the current period, we have everything in place. And and nothing will be affecting, like if they put anything, because what this will do is this will increase the inventory account. And notice the double entry accounting system needs another side to the transaction, which they’re probably going to put to opening balance equity, but they might put it to an income account. And if they put it in income account, it would roll into equity anyways.
So that’s why we want to have it in the prior period just to make sure it doesn’t mess anything up. Because our data is going to start in, we’re going to start entering data in January 1 2021. So we’re gonna say import, let’s import this thing. See what happens. It says six of six, product and service were sex successfully imported. That’s good news. Let’s close this out and check it out, closing it out, so we can check out the successful importation. So we’re in the sales tab on the left hand side product services up top, scrolling down, we have these items.
So these items have now been imported successfully. It looks like let’s go to the second tab over here and test it out. Let’s test it. So I’m going to go then to the new up top. And let’s say we’re going to go to like an invoice and we’re going to make an invoice, make an invoice. And then if I tested one of these items, now we’ve got our products here. So I’m going to just grab one, and say it keeps giving me this intro thing, which I don’t want it to do anymore. So I’m going to grab one of these.
And so then it and then it pulled over I pulled over the ukulele. And then we’re gonna say there’s the $400. Now notice, of course, it pulls in the sales price that when we actually sell for 400 for $400. And we buy it for the 320. So we got the item over here and then the description is pulling up in the middle. And then and there we have it now the sales tax, we’re going to talk about the sales tax later because we are going to apply sales tax to these to these items.
Notice these will also populate the, the purchase items. So if I was to like buy inventory with like an expense or a check or a bill or make a purchase order, then we’re going to go to this item. We have them I’m sorry, not the items, they’re going to be down here and the items not in the then we have our items that we could be purchasing down here as well. So we have those items as well so we can populate them in that type of document. I’m going to close that out.
Do you want to leave I want to leave Yeah. Now let’s take a look at our balance sheet because we’re hoping that we’re going to see this 289 There. So let’s go to our balance sheet by going to the reports, let’s go to the reports, open up our favorite balance sheet report balancing the sheets, I can bounce like five sheets at a time. And then we’re going to say this is going to be Oh 101 to zero to 1230 120, run that report. So this is as of the end of last period.
And then we’re going to start in January, I’m going to close the hamburger hold down Control, scroll up a little bit to that one to five. So there we have it. There’s our inventory item right there. And look what it did it put the other side to the opening balance equity. So what is opening balance equity, it’s a made up account, QuickBooks just made it up. And they’re basically saying, hey, look, we did what we had to do to put this number correct.
And then we put the other one over here, we didn’t know where it should go. So we put it there. And then you can adjust it at the end, right. So once I enter all my beginning balances, I’ll take it out of opening balance equity, and put it into the proper equity account, which might be retained earnings, or a capital account, or partners equity or something like that. That’s how the process is going to work. Now, if I go into this, if I drill down on this, what will happen because I didn’t have an actual form that we put in place, it’s going to make up this item, the transaction type inventory starting value.
It’s basically saying there is no form, right, because we just basically said, Hey, here’s the starting point of our inventory. And that’s what it that’s what it gave us there. If I click on it again, drill down to the data input, it gives us this kind of starting form, we don’t actually use this form. But in normal practice starting inventory value, it’s kind of like an adjustment type of form, that it’s going to be going to the other side then go into the opening balance equity.
Now in the future when we increase the inventory, obviously, it’ll be something like a bill or an expense form or cheque type form. And then when we decrease the inventory, it’ll be something like an invoice or sales receipt. So that’s what will happen in the future. So we’re It looks good thus far, we can check off this item. We’ve got that item in our beginning balances and we’ve got our inventory items set up so that we can populate our invoices our sales receipt as well as the purchase forms for inventory, including purchase order bill check form, and so on.