Effortlessly Manage Your Invoice Tracker Google Sheets for Inventory

Invoice tracker google sheets for Inventory

I've started us out with some headers and we're going to enter an item as we go and I'll explain what you're going to do with each of these. We're going to have an ice cream shop and we're gonna be tracking the flavors. Leave all the extra rows on here in case you add something in subsequent months. They're not going to hurt anything and then each month is going to have an opening balance or beginning balance, if you will. On this first month, we're going to hard-code it. In subsequent months, we're going to link it. I'll show you how to do that. But, for now, let's say five vanilla, we have ten gallons of mint chocolate chip, and one gallon of bubblegum. We're just going to work on the vanilla for now. During the month of January, you purchased four, you sold two and we're going to use a formula here. Your ending balance should be C4 + D4 + C4. For purchase price, let's use the last price that you paid for a gallon of ice cream. That way, it's going to reflect them at the market value pretty closely if it's the last price that you paid. For the ending value, we're going to take the ending balance, which is column F, multiply it by the latest purchase price, which is G4, and that's the ending value of your vanilla. You need to remember to update the purchase price every time you buy it or it's not really going to reflect the current value. Next, we're going to put in a total column. W we'll go down to the lowest it could be just to leave some space. We went into H16. I'm going to type =SUM for my formula. Open up the parentheses and choose the range of all the ending values. I went back in and I filled out some activity for these other two flavors of ice cream. Cell F4 has a formula in it. There's no need to retype that. Activate cell F4 by left clicking it with your mouse, go to the lower right hand corner of the rectangle until the mouse turns into a plus sign, drag this all the way down. That's going to copy that formula down. Do the same thing in column H. Let's put in some purchase prices. Now the whole spreadsheet is calculating the ending value of each flavor and it's coming down to a total. Let's do a little bit of formatting here so it's easier to read. I think you want all of these rows to have two decimal points. it's just called number. Use that formatting now. All the decimal points are lined up. It makes it visually easier. Let's do a bottom border to show that this is the sum at the end of the table. This ending value that you came down to, $197.44. If you're valuing your inventory on your books, you need to adjust it to that $197.44 if that's not what it is right now. But, on that subject, some of your inventory is going to shrink, right? Or, you're going to purchase something and record it wrong. In other words, this ending value over the months is going to become inaccurate no matter how hard you try to keep it right. What you would do is a monthly or a quarterly physical inventory. Let's add a physical count column. Let's recheck the purchase price to make sure you didn't forget any. Then, let's do actual value. You don't need to do this throughout the month you don't really need to do it every month. Do it whenever you want but if you want to double check yourself. This is a great way to do it. I made one of these numbers a little bit different so we'll see what's going to happen. Let's go ahead and select all these formulas and the sum and we're going to copy them over to column K. They're relative so the cells that they're looking at are going to shift to these cells. It's already doing the math for you, the actual value. After you do your physical count and you check your prices, this is the dollar amount that you're going to want to have recorded in your general ledger as your inventory. For the months that you do physical count, you would adjust your inventory to actual and then book the remaining amount to your cost of goods sold. Let me bring this bottom border over just to make things look pretty. Now let's say we're done for the month of January. Go to the bottom left-hand corner, where you see the tab for the sheet that you're on, left-click on the triangle and select duplicate. That's going to carry over all of the data. We did that on purpose, I'll show you why. Let's rename it to February. A few things are going to happen here. Keep these items like they are in case you add something at the bottom. The beginning balance isn't right because this is the beginning balance from the last month. What we want to do is, put your cursor in C4 at your equal sign, we're going to go and pick up the ending balance from the previous month. Since we did an actual count, we're going to pick that up as that's the best value so pick up the physical count at I4 and hit enter. That link brought the new value in from January. Drag that down all the way. Highlight your purchases and your sales and just delete those for now since we're starting fresh. The ending balance right now should be right if this is the beginning of the month because no activity has occurred. Purchase prices should still be good. You did not do a physical so come and delete these. You're ready to go. The activity for February should just come in here as purchases and sales. This example is made for retail. If it was, let's say manufacturing, this might be this might be created or made and this could be sold if it's finished goods. If it's raw materials, it could be purchased and then used. It's going to work the same basic way and it's going to come down and give you the value of your inventory at the end, which is really what you want to accomplish here. That's all, just repeat this every month. Add on the number of the year if you want to keep it going for more than a year which hopefully you will. Then you have your own monthly inventory system in a spreadsheet. Hopefully that was helpful, if you liked that and you want to see other videos like it, please subscribe in the lower right-hand corner. Thanks!