I've just started us out with some headers and we're just gonna enter an
item. As we go, 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 going to 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. 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 so
your formula here should be =C4+D4-E4. For purchase
price, let's use the last price that you paid for a gallon of ice cream and then
that way it's going to reflect them at the market value pretty closely if it's
the last price that you paid. Then, 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.
We'll go down to the lowest it could be. Just to leave some space. So, we went into
H16. I'm going to hit the equals sign and type some for my formula. Open up the
parentheses and choose the range of all the ending values. I went back in 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 the 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 places. it's just called number. Use that
formatting. Now, all the decimal points are lined up. It just makes it visually
easier. Let's do a bottom border to show that this is the sum. At the end of
the table, the ending value that you came down to and 84.78. If you're valuing your
inventory on your books, you need to adjust it to that $84.78, 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, then
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, do a monthly or a quarterly physical inventory. This a physical
count column. Let's recheck the purchase price to make sure you didn't forget any
and then let's do the actual value. You don't need to do this throughout the month, you
don't even really need to do it every month. Just to 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 here.
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 remain
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 one. Left click on the triangle, 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. Now, a few things are going to happen here. Keep these
items like they are. If you add something, add it at the bottom. But, 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, hit your equal sign, we're gonna go
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. Pick up the
physical count at I4. 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 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. But, 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. On a related note, if you are using Google Sheets and maybe
Google Docs, Google Drive for your business, you can also use Gmail
with your own custom email address. I have an affiliate link down in the
description and there's a little 20% off code for you once you sign up, if you're
interested. Go check it out. 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. If you want to see more about the wild, wonderful world of
accounting using Google sheets, next up is a depreciation schedule that you can
use. It should be showing in the thumbnail right now if you want to click
through it. Of course, if you like this video, it'd be great if you could
subscribe to my channel. Thanks!