i'm going to show you how you can control your inventory using excel on
this excel sheet
we have several pages already made up now on the first sheet we have the items
that we sell and I have named these items in a range and in this range we
have the part number
the description and the price of the items and so on the second sheet
you have the received items
so in this first column
you would enter the part number and the second column you would manually enter
the quantity that you received and the the description is pulled using the
if the vlookup and iferror just so that we don't have any errors on the on
the form now we have named ranges here we have we have received part numbers
it goes right to the bottom and we have received quantity which goes right to
the bottom which is going to allow you to enter to the bottom of the
sheet
ok so now we have the sold items once again we have the part numbers
the quantity the description is pulled from the items using if error and
vlook up because we drag this right to the bottom and we don't want any
any error messages on the blank ones
so the description and the price are both pulled from the items sheet
we have also named ranges in here we have we have item sold which is the part
number of the item sold and then we have quantity sold
ok let's go to our inventory sheet so on our inventory sheet we have the parts
that we sell with the description description is pulled from our items
list using vlookup and the received quantity is using the sum if now it's
using the named ranges so we have the receive parts number so it look it's
going to look at the receiver part number for this part number and then
it's going to bring the quantity it's going to total the quantity of the
number of of these that you've received and so then set the simple simple thing
here is we have received quantity and we subtract the sold quantity and then you
have quantity that's on had so just just for demonstration port so right now we
have 16 on hand of part number one two three
so let's go to receive quantity here and let's put in another one two three part
and let's say that we and here we have the description is already filled in
are we received another 50 of them so it was 16 before so let's go to the
inventory now you see that we have 66 because we have received the others
so let's go to the invoice
it's a simple invoicing you could make any invoice you like but you would
enter the customer name here
now let's enter part part 1 2 3 and then we're going to enter we so let's say 10
of them and so the the part numbering and the quantity or is entered the
description is pulled up the prices pulled up and the total is just is just
the quantity times the price so that would be and I haven't if they're in
there because i have this all set up so you just have to put it in and we'll
figure it out and then the total is obviously just the automatic sum of
this column
now let's 3 4 5 and let's say we sold five of these and once again you have
your totals and your descriptions pulled up for you sell these these
this invoice worth of quantities and you decide well now would you
so what we're going to do is we are going to leave this this invoice is
finished and shipped so now what we're going to do is we're going to select the
part number and the quantity
we're going to select it we're going to go ctrl-c to copy and we're going to go
to the sold items and we're going to go in the first part number that's below
here and press enter
and then you can drag down your price if you're interested in having a price
there and your inventory your inventory now is 56 the inventory is as as
automatically updated so there is a very simple method controlling your inventory
on excel
please subscribe